After hearing of complaints of poor application performance for one of my clients Healthcare Referral systems, i started looking into the underlying infrastructure only to find that there was no smoking gun. I ran some queries to see how the memory was being consumed by MSSQL since task manager pretty much just shows allocation, not usage. The output from these scripts appeared to show that no deadlocks were currently present, and that SQL really was using an awful lot of memory. One thing i have found is issues like these sometimes go away with a reboot and the root cause can be the default maximum memory settings allowing SQL to compete with the server for memory. I planned to implement some memory reservations and check with one of our DBA’s to see if he had other recommendations. He turned me on to this script that analyzes Microsoft SQL server for performance tuning recommendations regarding indexes.
According the the author of the script (except taken from http://blog.sqlauthority.com/2011/01/03/sql-server-2008-missing-index-script-download/)
Performance Tuning is quite interesting and Index plays a vital role in it. A proper index can improve the performance and a bad index can hamper the performance.
Here is the script from my script bank which I use to identify missing indexes on any database.
Please note, if you should not create all the missing indexes this script suggest. This is just for guidance. You should not create more than 5-10 indexes per table. Additionally, this script sometime does not give accurate information so use your common sense.
Any way, the scripts is good starting point. You should pay attention to Avg_Estimated_Impact when you are going to create index. The index creation script is also provided in the last column.
One nice thing about the script is the output gives you the commands to create the indexes as well as information on expected performance increase expected. What I did was just work with our DBA to review the proposed indexes and create a small list that we would implement to gauge the performance impact. Since the server i am working on is SQL 2008 I am also settings memory reservations for the operating system since older MSSQL seems to have an issue when the server is starved for memory.
Read here for recommendations for reserving memory: http://www.sqlservercentral.com/blogs/glennberry/2009/10/29/suggested-max-memory-settings-for-sql-server-2005_2F00_2008/
Here are some of the queries I used to look into SQL Servers memory usage and troubleshoot the issue
SELECT name, value, value_in_use, [description]
FROM sys.configurations
WHERE name like ‘%server memory%’
ORDER BY name OPTION (RECOMPILE);
SELECT
(physical_memory_in_use_kb/1024) AS Memory_usedby_Sqlserver_MB,
(locked_page_allocations_kb/1024) AS Locked_pages_used_Sqlserver_MB,
(total_virtual_address_space_kb/1024) AS Total_VAS_in_MB,
process_physical_memory_low,
process_virtual_memory_low
FROM sys.dm_os_process_memory;
select
name
,sum(pages_allocated_count)/128.0 [Cache Size (MB)]
from sys.dm_os_memory_cache_entries
where pages_allocated_count > 0
group by name
order by sum(pages_allocated_count) desc
SELECT
DB_NAME(database_id) AS [Database Name]
,CAST(COUNT(*) * 8/1024.0 AS DECIMAL (10,2)) AS [Cached Size (MB)]
FROM sys.dm_os_buffer_descriptors WITH (NOLOCK)
WHERE database_id not in (1,3,4) — system databases
AND database_id <> 32767 — ResourceDB
GROUP BY DB_NAME(database_id)
ORDER BY [Cached Size (MB)] DESC OPTION (RECOMPILE);
SELECT * FROM sys.dm_os_memory_clerks ORDER BY (single_pages_kb + multi_pages_kb + awe_allocated_kb) desc
SELECT [name] AS [Name]
,[configuration_id] AS [Number]
,[minimum] AS [Minimum]
,[maximum] AS [Maximum]
,[is_dynamic] AS [Dynamic]
,[is_advanced] AS [Advanced]
,[value] AS [ConfigValue]
,[value_in_use] AS [RunValue]
,[description] AS [Description]
FROM [master].[sys].[configurations]
WHERE NAME IN (‘Min server memory (MB)’, ‘Max server memory (MB)’)
SELECT [total_physical_memory_kb] / 1024 AS [Total_Physical_Memory_In_MB]
,[available_page_file_kb] / 1024 AS [Available_Physical_Memory_In_MB]
,[total_page_file_kb] / 1024 AS [Total_Page_File_In_MB]
,[available_page_file_kb] / 1024 AS [Available_Page_File_MB]
,[kernel_paged_pool_kb] / 1024 AS [Kernel_Paged_Pool_MB]
,[kernel_nonpaged_pool_kb] / 1024 AS [Kernel_Nonpaged_Pool_MB]
,[system_memory_state_desc] AS [System_Memory_State_Desc]
FROM [master].[sys].[dm_os_sys_memory]
SELECT object_name, cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name = ‘Total Server Memory (KB)’;