SQL SQL Server Performance Tuning with Dynamic Management Views March 18, 2016861 views0 Share By IG Share In my previous posts, I have already spoken about SQL Server Performance tuning basics, as well as the new Performance features in SQL Server 2016. Dynamic Management Views was first introduced in SQL Server 2005, and it was a big step in the right direction enabling DBA’s to get more in-depth insights into SQL Server. SQL Server Performance Tuning with Dynamic Management Views SQL Server 2005 introduced 89 DMO’s (Dynamic Management Objects), and SQL Server 2008 introduced 136. The newer versions support much more than that, and the SQL Server development team exposed more metadata and internal information for DBA’s to find information and data on resource consumption, index usage, and other statistics. So, here are some DMV’s which you can use for your day to day job working with SQL Server. Let me help you get started. CPU Diagnostics using DMV’s select * from sys.dm_os_schedulers select * from sys.dm_exec_requests where session_id>50 select * from sys.dm_os_workers select * from sys.dm_os_threads select session_id,* from sys.dm_os_tasks where session_id>50 select * from sys.dm_exec_sessions where session_id > 50 –max worker threads select max_workers_count From sys.dm_os_sys_info select count(*) from sys.dm_os_threads — CPU waits : select runnable_tasks_count,work_queue_count,pending_disk_io_count, * from sys.dm_os_schedulers select * from sys.dm_os_workers select * from sys.dm_os_threads select * from sys.dm_os_tasks where session_id > 50 task_state = 'SUSPENDED' or task_state = 'PENDING' AND select * from sys.dm_os_waiting_tasks where session_id > 50 select * from sys.dm_exec_requests where session_id > 50 select * from sys.dm_os_memory_objects SELECT MO.memory_object_address, MO.type, MO.pages_allocated_count, page_size_in_bytes FROM sys.dm_os_memory_objects MO inner join sys.dm_os_workers OW on MO.memory_object_address = OW.memory_object_address inner join sys.dm_os_tasks OT on OW.worker_address = OT.worker_address inner join sys.dm_exec_requests ER on OT.task_address = ER.task_address where ER.session_id > 50 –Take the kpid and kill it from OS using process explorer select * from master.sys.sysprocesses where spid= 51 — getting to know the session from thread select T.thread_address, W.worker_address, TA.task_address, S.session_id from sys.dm_os_threads T inner join sys.dm_os_workers W on T.worker_address = W.worker_address inner join sys.dm_os_tasks TA on W.task_address = TA.task_address inner join sys.dm_exec_sessions S on TA.session_id = S.session_id where S.session_id > 50 –Worker-level waits? SELECT wt.wait_type AS task_wait, wt.wait_duration_ms AS task_wait_time, w.last_wait_type AS worker_wait, ( SELECT ms_ticks FROM sys.dm_os_sys_info ) - w.wait_started_ms_ticks AS worker_wait_time FROM sys.dm_os_waiting_tasks AS wt JOIN sys.dm_os_tasks AS t ON t.task_address = wt.waiting_task_address JOIN sys.dm_os_workers AS w ON w.worker_address = t.worker_address WHERE wt.session_id = 68 --session_id from above GO –lastwaittype == worker-level –waittype == task-level –to translate, use an XE DMV (2008+ only) SELECT s.lastwaittype, s.waittype, m.map_value FROM sys.sysprocesses as s INNER JOIN sys.dm_xe_map_values AS m ON m.name = 'wait_types' AND m.map_key = s.waittype WHERE s.spid = 68--session_id from above GO Index Monitoring –Missing indexes SELECT statement AS [database.scheme.table],column_id , column_name, column_usage, migs.user_seeks, migs.user_scans, migs.last_user_seek, migs.avg_total_user_cost, migs.avg_user_impact FROM sys.dm_db_missing_index_details AS mid CROSS APPLY sys.dm_db_missing_index_columns (mid.index_handle) INNER JOIN sys.dm_db_missing_index_groups AS mig ON mig.index_handle = mid.index_handle INNER JOIN sys.dm_db_missing_index_group_stats AS migs ON mig.index_group_handle=migs.group_handle ORDER BY mig.index_group_handle, mig.index_handle, column_id GO –Useful Index select d.* , s.avg_total_user_cost , s.avg_user_impact , s.last_user_seek ,s.unique_compiles from sys.dm_db_missing_index_group_stats s ,sys.dm_db_missing_index_groups g ,sys.dm_db_missing_index_details d where s.group_handle = g.index_group_handle and d.index_handle = g.index_handle order by s.avg_user_impact desc go — Index Not Used SELECT o.name Object_Name,i.name Index_name, i.Type_Desc FROM sys.objects AS o JOIN sys.indexes AS i ON o.object_id = i.object_id LEFT OUTER JOIN sys.dm_db_index_usage_stats AS s ON i.object_id = s.object_id AND i.index_id = s.index_id WHERE o.type = 'u' -- Clustered and Non-Clustered indexes AND i.type IN (1, 2) -- Indexes without stats AND (s.index_id IS NULL) OR -- Indexes that have been updated by not used (s.user_seeks = 0 AND s.user_scans = 0 AND s.user_lookups = 0 ); — Index Usage SELECT o.name Object_Name, SCHEMA_NAME(o.schema_id) Schema_name, i.name Index_name, i.Type_Desc, s.user_seeks, s.user_scans, s.user_lookups, s.user_updates FROM sys.objects AS o JOIN sys.indexes AS i ON o.object_id = i.object_id JOIN sys.dm_db_index_usage_stats AS s ON i.object_id = s.object_id AND i.index_id = s.index_id WHERE o.type = 'u' -- Clustered and Non-Clustered indexes AND i.type IN (1, 2) -- Indexes that have been updated by not used AND(s.user_seeks > 0 or s.user_scans > 0 or s.user_lookups > 0 ); — Index Fragmentation DBCC SHOWCONTIG Memory Monitoring select * from sys.dm_os_memory_nodes — this breaks in 2012 select * from sys.dm_os_memory_clerks order by single_pages_kb DESC — this breaks in 2012 select * from sys.dm_os_memory_clerks order by multi_pages_kb DESC — For SQL Server 2012, 2014,2016 select pages_kb as pages_kb_, * from sys.dm_os_memory_clerks order by pages_kb_ DESC select type, virtual_memory_committed_kb as a, * from sys.dm_os_memory_clerks order by virtual_memory_committed_kb DESC select * from sys.dm_os_performance_counters where counter_name like '%mem%' select * from sys.dm_os_memory_clerks where type = 'MEMORYCLERK_SQLBUFFERPOOL' select * from sys.dm_os_sys_memory select physical_memory_in_use_kb/1024 as physical_memory_in_use_MB, large_page_allocations_kb/1024 as large_page_allocations_MB, locked_page_allocations_kb/1204 as locked_page_allocations_MB, total_virtual_address_space_kb/1024 as total_virtual_address_space_MB, virtual_address_space_reserved_kb/1024 as virtual_address_space_reserved_MB, virtual_address_space_committed_kb/1024 as virtual_address_space_committed_MB, virtual_address_space_available_kb/1024 as virtual_address_space_available_MB, available_commit_limit_kb/1024 as available_commit_limit_MB from sys.dm_os_process_memory select * from sys.dm_os_process_memory –VAS summary WITH VASummary(Size,Reserved,Free) AS (SELECT Size = VaDump.Size, Reserved = SUM(CASE(CONVERT(INT, VaDump.Base)^0) WHEN 0 THEN 0 ELSE 1 END), Free = SUM(CASE(CONVERT(INT, VaDump.Base)^0) WHEN 0 THEN 1 ELSE 0 END) FROM ( SELECT CONVERT(VARBINARY, SUM(region_size_in_bytes)) AS Size, region_allocation_base_address AS Base FROM sys.dm_os_virtual_address_dump WHERE region_allocation_base_address <> 0x0 GROUP BY region_allocation_base_address UNION SELECT CONVERT(VARBINARY, region_size_in_bytes), region_allocation_base_address FROM sys.dm_os_virtual_address_dump WHERE region_allocation_base_address = 0x0 ) AS VaDump GROUP BY Size) SELECT SUM(CONVERT(BIGINT,Size)*Free)/1024 AS [Total avail mem, KB] ,CAST(MAX(Size) AS BIGINT)/1024 AS [Max free size, KB] FROM VASummary WHERE Free <> 0 SELECT type, virtual_memory_committed_kb, multi_pages_k FROM sys.dm_os_memory_clerks WHERE virtual_memory_committed_kb > 0 OR multi_pages_kb > 0 — For SQL Server 2012, 2014,2016 SELECT type, virtual_memory_committed_kb, pages_kb FROM sys.dm_os_memory_clerks WHERE virtual_memory_committed_kb > 0 OR pages_kb > 0 For example, you can use the following DMV query to find the total amount of memory consumed (including AWE) by the buffer pool: Breaks in 2012 SELECT SUM(multi_pages_kb + virtual_memory_committed_kb + shared_memory_committed_kb + awe_allocated_kb)/1024 AS [Used by BPool, MB] FROM sys.dm_os_memory_clerks WHERE type = 'MEMORYCLERK_SQLBUFFERPOOL'; For SQL Server 2012, 2014,2016 SELECT SUM(pages_kb + virtual_memory_committed_kb + shared_memory_committed_kb + awe_allocated_kb)/1024 AS [Used by BPool, MB] FROM sys.dm_os_memory_clerks WHERE type = 'MEMORYCLERK_SQLBUFFERPOOL'; –check the dirty pages. SELECT db_name(database_id) AS 'Database',count(page_id) AS 'Dirty Pages' FROM sys.dm_os_buffer_descriptors WHERE is_modified =1 GROUP BY db_name(database_id) ORDER BY count(page_id) DESC Clerks breaks in 2012 SELECT [type], memory_node_id, single_pages_kb, multi_pages_kb, virtual_memory_reserved_kb, virtual_memory_committed_kb, awe_allocated_kb FROM sys.dm_os_memory_clerks ORDER BY virtual_memory_reserved_kb DESC ; — For SQL Server 2012, 2014,2016 SELECT [type], memory_node_id, pages_kb, virtual_memory_reserved_kb, virtual_memory_committed_kb, awe_allocated_kb FROM sys.dm_os_memory_clerks ORDER BY virtual_memory_reserved_kb DESC ; Cache breaks in SQL Server 2012, 2014,2016 SELECT [name], [type], single_pages_kb + multi_pages_kb AS total_kb, entries_count FROM sys.dm_os_memory_cache_counters ORDER BY total_kb DESC ; — For SQL Server 2012, 2014,2016 SELECT [name], [type], pages_kb AS total_kb, entries_count FROM sys.dm_os_memory_cache_counters ORDER BY total_kb DESC ; — buffer pool SELECT count(*)*8/1024 AS 'Cached Size (MB)' ,CASE database_id WHEN 32767 THEN 'ResourceDb' ELSE db_name(database_id) END AS 'Database' FROM sys.dm_os_buffer_descriptors GROUP BY db_name(database_id) ,database_id ORDER BY 'Cached Size (MB)' DESC — plan cache SELECT count(*) AS 'Number of Plans', sum(cast(size_in_bytes AS BIGINT))/1024/1024 AS 'Plan Cache Size (MB)' FROM sys.dm_exec_cached_plans –plan cache size by cached object type: SELECT objtype AS 'Cached Object Type', count(*) AS 'Number of Plans', sum(cast(size_in_bytes AS BIGINT))/1024/1024 AS 'Plan Cache Size (MB)', avg(usecounts) AS 'Avg Use Count' FROM sys.dm_exec_cached_plans group by objtype — free system cache DBCC FREESYSTEMCACHE('SQL Plans') — first, clear the wait stats DBCC SQLPERF('sys.dm_os_wait_stats',CLEAR); — observe the wait stats SELECT TOP 10 wait_type , max_wait_time_ms wait_time_ms , signal_wait_time_ms , wait_time_ms - signal_wait_time_ms AS resource_wait_time_ms , CASE WHEN wait_time_ms = 0 THEN 0 ELSE 100.0 * wait_time_ms / SUM(wait_time_ms) OVER ( ) END AS percent_total_waits , CASE WHEN signal_wait_time_ms = 0 THEN 0 ELSE 100.0 * signal_wait_time_ms / SUM(signal_wait_time_ms) OVER ( ) END AS percent_total_signal_waits , CASE WHEN wait_time_ms = 0 THEN 0 ELSE 100.0 * ( wait_time_ms - signal_wait_time_ms ) / SUM(wait_time_ms) OVER ( ) END AS percent_total_resource_waits FROM sys.dm_os_wait_stats WHERE wait_time_ms > 0 -- remove zero wait_time AND wait_type NOT IN -- filter out additional irrelevant waits ( 'SLEEP_TASK', 'BROKER_TASK_STOP', 'BROKER_TO_FLUSH', 'SQLTRACE_BUFFER_FLUSH','CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT', 'LAZYWRITER_SLEEP', 'SLEEP_SYSTEMTASK', 'SLEEP_BPOOL_FLUSH', 'BROKER_EVENTHANDLER', 'XE_DISPATCHER_WAIT', 'FT_IFTSHC_MUTEX', 'CHECKPOINT_QUEUE', 'FT_IFTS_SCHEDULER_IDLE_WAIT', 'BROKER_TRANSMITTER', 'FT_IFTSHC_MUTEX', 'KSOURCE_WAKEUP', 'LAZYWRITER_SLEEP', 'LOGMGR_QUEUE', 'ONDEMAND_TASK_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT', 'BAD_PAGE_PROCESS', 'DBMIRROR_EVENTS_QUEUE', 'BROKER_RECEIVE_WAITFOR', 'PREEMPTIVE_OS_GETPROCADDRESS', 'PREEMPTIVE_OS_AUTHENTICATIONOPS', 'WAITFOR', 'DISPATCHER_QUEUE_SEMAPHORE', 'XE_DISPATCHER_JOIN', 'RESOURCE_QUEUE' ) ORDER BY wait_time_ms DESC Run the following query turn on the execution plan after you run the query go back and run the ‘wait stats query’ again. use AdventureWorks2008 GO select * from sales.SalesOrderDetail order by LineTotal DESC GO Clear the wait stats again DBCC SQLPERF('sys.dm_os_wait_stats',CLEAR); Try the same with MAXDOP option turn on the execution plan after you run the query go back and rerun the wait stats query. select * from sales.SalesOrderDetail order by LineTotal DESC OPTION (MAXDOP 2) GO Conclusion I hope these DMV’s were useful. Let me know in the comment’s section if you have any specific needs for a custom DMV. Disclaimer: The Questions and Answers provided on https://www.gigxp.com are for general information purposes only. We make no representations or warranties of any kind, express or implied, about the completeness, accuracy, reliability, suitability or availability with respect to the website or the information, products, services, or related graphics contained on the website for any purpose. Share What's your reaction? Excited 0 Happy 0 In Love 0 Not Sure 0 Silly 0 IG Website Twitter
TSQL How to Effectively split a string using STRING_SPLIT function in SQL server By DataGeekApril 15, 2019
Azure How To Change Azure SQL DB MAXDOP Settings Using T-SQL Command In this article, we are going to discuss how to change Azure SQL db MAXDOP ...
Azure Azure SQL Standard vs Premium Specification Comparison & Differences In this article, we will be comparing Azure SQL Standard vs Premium service tiers. We ...
Azure Azure SQL Database vCores vs DTU Feature Comparison In this article, we are going to compare Azure SQL Database vCores vs DTU pricing ...
Azure How To Do Azure AD Authentication with SSIS in VM Running SQL Server Can you do Azure AD Authentication with SSIS running in a Virtual Machine on the cloud? ...