SQLSQL Server Performance Tuning with Dynamic Management Views March 18, 20164446 views0Share Performance Tuning By IG ShareSQL Server Performance Tuning with Dynamic Management ViewsIn 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 deeper insights into SQL Server.Note: If you buy something from our links, we might earn a commission. See our disclosure statement.SQL Server 2005 introduced 89 DMO’s (Dynamic Management Objects) and SQL Server 2008 introduced 136. The newer versions supports much more than that, and the SQL Server development team exposed more metadata and internal information for DBA’s to find information and data of 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’sselect * 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 threadsselect 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 explorerselect * from master.sys.sysprocesses where spid= 51— getting to know the session from threadselect 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 Ad–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 GOIndex Monitoring–Missing indexesSELECT 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–Usefull Indexselect 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 UsedSELECT 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 UsageSELECT 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 FragmentationDBCC SHOWCONTIGMemory Monitoringselect * from sys.dm_os_memory_nodes— this breaks in 2012select * from sys.dm_os_memory_clerks order by single_pages_kb DESC— this breaks in 2012select * from sys.dm_os_memory_clerks order by multi_pages_kb DESC— For SQL Server 2012, 2014,2016select 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 summaryWITH 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 AdSELECT 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,2016SELECT type, virtual_memory_committed_kb, pages_kb FROM sys.dm_os_memory_clerks WHERE virtual_memory_committed_kb > 0 OR pages_kb > 0For example, you can use the following DMV query to find thetotal amount of memory consumed (including AWE) by the buffer pool:Breaks in 2012SELECT 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,2016SELECT 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) DESCClerks breaks in 2012SELECT [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,2016SELECT [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,2016SELECT [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,2016SELECT [name], [type], pages_kb AS total_kb, entries_count FROM sys.dm_os_memory_cache_counters ORDER BY total_kb DESC ;— buffer poolSELECT 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 cacheSELECT 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 cacheDBCC FREESYSTEMCACHE('SQL Plans')— first clear the wait statsDBCC SQLPERF('sys.dm_os_wait_stats',CLEAR);— observe the wait statsSELECT 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 DESCRun 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 GOClear the wait stats againDBCC 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 run the wait stats query again…select * from sales.SalesOrderDetail order by LineTotal DESC OPTION (MAXDOP 2) GOI hope these DMV’s were useful. Let me know in the comment’s section if you have any specific needs for a custom DMV. Affiliate Disclosure: Faceofit.com is a participant in the Amazon Services LLC Associates Program. As an Amazon Associate we earn from qualifying purchases. Share What's your reaction? Excited 0 Happy 0 In Love 0 Not Sure 0 Silly 0
Enterprise TechHow to Run Microsoft SQL Server 2016 on Google Cloud Step by StepGoogle Cloud Services recently added Microsoft SQL Server to its offerings. Google’s move is primarily ...
SQLHow to Shrink SQL Server Database Log in Availability Groups using TSQLIn my previous posts, I have already spoken about the best practices of Shrinking Log ...
Enterprise TechHow to Manage your SQL Server Log and Database File Size GrowthHow to Manage your SQL Server Log and Database File Size Growth Managing the Database ...
SQLHow to Upgrade from Expired SQL Evaluation EditionHow to Upgrade from Expired SQL Evaluation Edition SQL Server Evaluation Edition is a full-blown ...