SQL Server Performance Tuning with Dynamic Management Views

SQL Server Performance Tuning with Dynamic Management Views

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 deeper insights into SQL Server.

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’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

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
 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

–Usefull 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

Ad

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 run the wait stats query again…

select * from sales.SalesOrderDetail
 order by LineTotal DESC
 OPTION (MAXDOP 2)
 GO

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.