{"id":1396,"date":"2015-11-18T18:30:05","date_gmt":"2015-11-18T18:30:05","guid":{"rendered":"http:\/\/tech-no.104.210.61.21.xip.io\/?p=1396"},"modified":"2015-11-18T18:31:51","modified_gmt":"2015-11-18T18:31:51","slug":"improving-mssql-performance-by-reindexing","status":"publish","type":"post","link":"https:\/\/tech-no.org\/?p=1396","title":{"rendered":"Improving MSSQL performance"},"content":{"rendered":"

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\u00a0turned me on to this script that analyzes Microsoft SQL server for performance tuning \u00a0recommendations regarding indexes.<\/p>\n

According the the author of the script (except taken from\u00a0http:\/\/blog.sqlauthority.com\/2011\/01\/03\/sql-server-2008-missing-index-script-download\/<\/a>)<\/p>\n

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.<\/p>\n

Here is the script from my script bank which I use to identify missing indexes on any database.<\/p>\n

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.<\/p>\n

Any way, the scripts is good starting point. You should pay attention to\u00a0Avg_Estimated_Impact when you are going to create index. The index creation script is also provided in the last column.<\/p><\/blockquote>\n

 <\/p>\n

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\u00a0did 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.<\/p>\n

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\/<\/a><\/p>\n

Here are some of the queries I used to look into SQL Servers memory usage and troubleshoot the issue<\/p>\n

\n

 <\/p>\n

SELECT name, value, value_in_use, [description]<\/p>\n

FROM sys.configurations<\/p>\n

WHERE name like ‘%server memory%’<\/p>\n

ORDER BY name OPTION (RECOMPILE);<\/p><\/blockquote>\n

 <\/p>\n

\n

 <\/p>\n

SELECT
\n(physical_memory_in_use_kb\/1024) AS Memory_usedby_Sqlserver_MB,
\n(locked_page_allocations_kb\/1024) AS Locked_pages_used_Sqlserver_MB,
\n(total_virtual_address_space_kb\/1024) AS Total_VAS_in_MB,
\nprocess_physical_memory_low,
\nprocess_virtual_memory_low
\nFROM sys.dm_os_process_memory;<\/p><\/blockquote>\n

\n

 <\/p>\n

select
\nname
\n,sum(pages_allocated_count)\/128.0 [Cache Size (MB)]
\nfrom sys.dm_os_memory_cache_entries
\nwhere pages_allocated_count > 0
\ngroup by name
\norder by sum(pages_allocated_count) desc<\/p><\/blockquote>\n

\n

SELECT
\nDB_NAME(database_id) AS [Database Name]
\n,CAST(COUNT(*) * 8\/1024.0 AS DECIMAL (10,2)) AS [Cached Size (MB)]
\nFROM sys.dm_os_buffer_descriptors WITH (NOLOCK)
\nWHERE database_id not in (1,3,4) — system databases
\nAND database_id <> 32767 — ResourceDB
\nGROUP BY DB_NAME(database_id)
\nORDER BY [Cached Size (MB)] DESC OPTION (RECOMPILE);<\/p><\/blockquote>\n

\n

 <\/p>\n

SELECT * FROM sys.dm_os_memory_clerks ORDER BY (single_pages_kb + multi_pages_kb + awe_allocated_kb) desc<\/p><\/blockquote>\n

 <\/p>\n

 <\/p>\n

\n

SELECT [name] AS [Name]
\n,[configuration_id] AS [Number]
\n,[minimum] AS [Minimum]
\n,[maximum] AS [Maximum]
\n,[is_dynamic] AS [Dynamic]
\n,[is_advanced] AS [Advanced]
\n,[value] AS [ConfigValue]
\n,[value_in_use] AS [RunValue]
\n,[description] AS [Description]
\nFROM [master].[sys].[configurations]
\nWHERE NAME IN (‘Min server memory (MB)’, ‘Max server memory (MB)’)<\/p><\/blockquote>\n

\n

 <\/p>\n

SELECT [total_physical_memory_kb] \/ 1024 AS [Total_Physical_Memory_In_MB]
\n,[available_page_file_kb] \/ 1024 AS [Available_Physical_Memory_In_MB]
\n,[total_page_file_kb] \/ 1024 AS [Total_Page_File_In_MB]
\n,[available_page_file_kb] \/ 1024 AS [Available_Page_File_MB]
\n,[kernel_paged_pool_kb] \/ 1024 AS [Kernel_Paged_Pool_MB]
\n,[kernel_nonpaged_pool_kb] \/ 1024 AS [Kernel_Nonpaged_Pool_MB]
\n,[system_memory_state_desc] AS [System_Memory_State_Desc]
\nFROM [master].[sys].[dm_os_sys_memory]<\/p>\n

\n

 <\/p>\n

SELECT object_name, cntr_value
\nFROM sys.dm_os_performance_counters
\nWHERE counter_name = ‘Total Server Memory (KB)’;<\/p><\/blockquote>\n

\n

 <\/p>\n","protected":false},"excerpt":{"rendered":"

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 …<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":[],"categories":[5],"tags":[],"_links":{"self":[{"href":"https:\/\/tech-no.org\/index.php?rest_route=\/wp\/v2\/posts\/1396"}],"collection":[{"href":"https:\/\/tech-no.org\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/tech-no.org\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/tech-no.org\/index.php?rest_route=\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/tech-no.org\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1396"}],"version-history":[{"count":2,"href":"https:\/\/tech-no.org\/index.php?rest_route=\/wp\/v2\/posts\/1396\/revisions"}],"predecessor-version":[{"id":1398,"href":"https:\/\/tech-no.org\/index.php?rest_route=\/wp\/v2\/posts\/1396\/revisions\/1398"}],"wp:attachment":[{"href":"https:\/\/tech-no.org\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1396"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/tech-no.org\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1396"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/tech-no.org\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1396"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}