Consolidated DMV scripts – Part 1 – Day 78 – One DMV a Day

Hello Geeks and welcome to the Day 78 of the long series of One DMV a day. In this series of blogs I will be exploring and explaining the most useful DMV s in SQL Server. As we go ahead in this series we will also talk about the usage, linking between DMV s and some scenarios where these DMV s will be helpful while you are using SQL Server. For the complete list in the series please click here.

Till yesterday I have talked about various most useful DMV s. In today and tomorrow’s posts I will consolidate the most useful DMV queries I used in this series for a quick reference. BTW if you want to see how I look while I blog…

To check Index fragmentation: know more…

SELECT * FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(<tablename>), NULL, NULL, 'DETAILED')

Using sys.dm_db_index_usage_stats: know more…

SET NOCOUNT ON
GO
--create dump table for select
IF EXISTS(SELECT 1 from tempdb.dbo.sysobjects where name like '#dump%')
       DROP TABLE #dump
SELECT * INTO #dump FROM publogger_tbl WHERE 1 = 0
 
--take a dump of sys.dm_db_index_usage_stats
IF EXISTS(SELECT 1 from tempdb.dbo.sysobjects where name like '#x1%')
       DROP TABLE #x1
SELECT *
INTO #x1
FROM sys.dm_db_index_usage_stats
WHERE database_id = 6 and object_id = 245575913 and index_id = 1
 
--run a index seek operation 100 times
DECLARE @i INT = 0
 
WHILE @i < 100
BEGIN
       INSERT INTO #dump SELECT * from publogger_tbl where eId = 10
       SET @i = @i + 1
END
 
--Check the differential change in user_seeks count
SELECT curr.user_seeks - old.user_seeks AS Total_Seeks,
              old.last_user_seek AS last_Seek,
              curr.last_user_seek AS curr_seek
FROM sys.dm_db_index_usage_stats curr
INNER JOIN #x1 old
ON     curr.database_id = old.database_id AND
       curr.object_id = old.object_id AND
       curr.index_id = old.index_id
 
SET NOCOUNT OFF
GO

Checking Missing Indexes: know more…

SELECT * FROM sys.dm_db_missing_index_details WHERE database_id = DB_ID(<DBName>) AND object_id = OBJECT_ID(<tableName>)

SELECT * FROM sys.dm_db_missing_index_columns(<index_handle>)

Checking Operational Stats: know more…

SELECT DB_NAME(database_id) AS DBName, OBJECT_NAME(object_id) AS TableName, i.name AS IndexName,
              leaf_insert_count, leaf_delete_count, leaf_update_count,
              nonleaf_insert_count, nonleaf_delete_count, nonleaf_update_count,
              row_lock_count, row_lock_wait_count, row_lock_wait_in_ms,
              page_lock_count, page_lock_wait_count, page_lock_wait_in_ms,
              page_latch_wait_count, page_latch_wait_in_ms,
              page_io_latch_wait_count, page_io_latch_wait_in_ms
       FROM sys.dm_db_index_operational_stats(DB_ID(<DBName>), OBJECT_ID(<tableName>), NULL, NULL) iop
       INNER JOIN sysindexes i ON iop.index_id = i.indid AND iop.object_id = i.id

IO Related DMVs:

Checking IO pending requests: know more…

SELECT ipir.io_type, ipir.io_pending,
              ipir.scheduler_address, ipir.io_handle,
              os.scheduler_id, os.cpu_id, os.pending_disk_io_count
FROM sys.dm_io_pending_io_requests ipir
INNER JOIN sys.dm_os_schedulers os
ON ipir.scheduler_address = os.scheduler_address

Check IO issues files relating to IO requests: know more… 

SELECT * FROM sys.dm_io_virtual_file_stats(DB_ID(), NULL) ivfs
INNER JOIN sys.dm_io_pending_io_requests ipir
ON ipir.io_handle = ivfs.file_handle

TempDB Related DMVs:

File space usage details: know more…

--SQL Server 2012 +
SELECT file_id,
       filegroup_id,
       total_page_count,
       allocated_extent_page_count,
       unallocated_extent_page_count,
       mixed_extent_page_count,
       version_store_reserved_page_count,
       user_object_reserved_page_count,
       internal_object_reserved_page_count
FROM sys.dm_db_file_space_usage
 
--SQL Server 2008 R2 and lower versions till 2005
SELECT file_id,
       unallocated_extent_page_count,
       mixed_extent_page_count,
       version_store_reserved_page_count,
       user_object_reserved_page_count,
       internal_object_reserved_page_count
FROM sys.dm_db_file_space_usage

Session space usage: know more…

SELECT session_id,
        user_objects_alloc_page_count/128 AS user_objs_total_sizeMB,
        (user_objects_alloc_page_count - user_objects_dealloc_page_count)/128.0 AS user_objs_active_sizeMB,
        internal_objects_alloc_page_count/128 AS internal_objs_total_sizeMB,
        (internal_objects_alloc_page_count - internal_objects_dealloc_page_count)/128.0 AS internal_objs_active_sizeMB
FROM sys.dm_db_session_space_usage
ORDER BY user_objects_alloc_page_count DESC

Task space usage: know more…

SELECT t.task_address,
       t.parent_task_address,
       tsu.session_id,
       tsu.request_id,
       t.exec_context_id,
       tsu.user_objects_alloc_page_count/128 AS Total_UserMB,
       (tsu.user_objects_alloc_page_count - tsu.user_objects_dealloc_page_count)/128.0 AS Acive_UserMB,
       tsu.internal_objects_alloc_page_count/128 AS Total_IntMB,
       (tsu.internal_objects_alloc_page_count - tsu.internal_objects_dealloc_page_count)/128.0 AS Active_IntMB,
       t.task_state,
       t.scheduler_id,
       t.worker_address
FROM sys.dm_db_task_space_usage tsu
INNER JOIN sys.dm_os_tasks t
ON tsu.session_id = t.session_id
AND tsu.exec_context_id = t.exec_context_id
WHERE tsu.session_id > 50
ORDER BY tsu.session_id

OS related DMVs:

OS tasks: know more…

SELECT task_address,
       task_state,
       context_switches_count AS switches,
       pending_io_count AS ioPending,
       pending_io_byte_count AS ioBytes,
       pending_io_byte_average AS ioBytesAvg,
       scheduler_id,
       session_id,
       exec_context_id,
       request_id,
       worker_address,
       parent_task_address
FROM sys.dm_os_tasks
WHERE session_id > 50

OS workers: know more…

select ot.session_id,
       ow.pending_io_count,
       CASE ow.wait_started_ms_ticks
              WHEN 0 THEN 0
              ELSE (osi.ms_ticks - ow.wait_started_ms_ticks)/1000 END AS Suspended_wait,
       CASE ow.wait_resumed_ms_ticks
              WHEN 0 THEN 0
              ELSE (osi.ms_ticks - ow.wait_resumed_ms_ticks)/1000 END AS Runnable_wait,
       (osi.ms_ticks - ow.task_bound_ms_ticks)/1000 AS task_time,
       (osi.ms_ticks - ow.worker_created_ms_ticks)/1000 AS worker_time,
       ow.end_quantum - ow.start_quantum AS last_worker_quantum,
       ow.state,
       ow.last_wait_type,
       ow.affinity,
       ow.quantum_used,
       ow.tasks_processed_count
FROM sys.dm_os_workers ow
INNER JOIN sys.dm_os_tasks ot
ON ow.task_address = ot.task_address
CROSS JOIN sys.dm_os_sys_info osi
WHERE ot.session_id > 50
AND is_preemptive = 0

OS threads: know more…

SELECT s.scheduler_id,  s.status, w.worker_address, w.is_preemptive, r.command, r.status, th.os_thread_id
FROM sys.dm_os_workers w
JOIN sys.dm_os_schedulers s
ON w.scheduler_address = s.scheduler_address
LEFT OUTER JOIN sys.dm_os_tasks t
ON t.task_address = w.task_address
LEFT OUTER JOIN sys.dm_exec_requests r
ON r.session_id = t.session_id
LEFT OUTER JOIN sys.dm_os_threads th
ON th.thread_address = w.thread_address
GROUP BY s.scheduler_id, s.status, w.worker_address, w.is_preemptive, r.command, r.status, th.os_thread_id, th.started_by_sqlservr
ORDER BY s.scheduler_id

OS Schedulers: know more…

SELECT work_queue_count,
       scheduler_id,
       current_tasks_count,
       runnable_tasks_count,
       current_workers_count,
       active_workers_count
FROM sys.dm_os_schedulers
ORDER BY 1 DESC

OS wait stats: know more…

CREATE TABLE #CSS_Waits_Repository(wait_type NVARCHAR(100));
 
INSERT INTO #CSS_Waits_Repository VALUES ('ASYNC_IO_COMPLETION');
INSERT INTO #CSS_Waits_Repository VALUES ('CHECKPOINT_QUEUE');
INSERT INTO #CSS_Waits_Repository VALUES ('CHKPT');
INSERT INTO #CSS_Waits_Repository VALUES ('CXPACKET');
INSERT INTO #CSS_Waits_Repository VALUES ('DISKIO_SUSPEND');
INSERT INTO #CSS_Waits_Repository VALUES ('FT_IFTS_SCHEDULER_IDLE_WAIT');
INSERT INTO #CSS_Waits_Repository VALUES ('IO_COMPLETION');
INSERT INTO #CSS_Waits_Repository VALUES ('KSOURCE_WAKEUP');
INSERT INTO #CSS_Waits_Repository VALUES ('LAZYWRITER_SLEEP');
INSERT INTO #CSS_Waits_Repository VALUES ('LOGBUFFER');
INSERT INTO #CSS_Waits_Repository VALUES ('LOGMGR_QUEUE');
INSERT INTO #CSS_Waits_Repository VALUES ('MISCELLANEOUS');
INSERT INTO #CSS_Waits_Repository VALUES ('PREEMPTIVE_XXX');
INSERT INTO #CSS_Waits_Repository VALUES ('REQUEST_FOR_DEADLOCK_SEARCH');
INSERT INTO #CSS_Waits_Repository VALUES ('RESOURCE_QUERY_SEMAPHORE_COMPILE');
INSERT INTO #CSS_Waits_Repository VALUES ('RESOURCE_SEMAPHORE');
INSERT INTO #CSS_Waits_Repository VALUES ('SOS_SCHEDULER_YIELD');
INSERT INTO #CSS_Waits_Repository VALUES ('SQLTRACE_BUFFER_FLUSH ');
INSERT INTO #CSS_Waits_Repository VALUES ('THREADPOOL');
INSERT INTO #CSS_Waits_Repository VALUES ('WRITELOG');
INSERT INTO #CSS_Waits_Repository VALUES ('XE_DISPATCHER_WAIT');
INSERT INTO #CSS_Waits_Repository VALUES ('XE_TIMER_EVENT');
 
DECLARE @Waits TABLE (
    WaitID INT IDENTITY(1, 1) not null PRIMARY KEY,
    wait_type NVARCHAR(60),
    wait_time_s DECIMAL(12, 2),
       resources_wait_s DECIMAL(12, 2),
       signal_wait_s decimal(12, 2));   
 
WITH Waits AS
(SELECT wait_type, wait_time_ms / 1000. AS wait_time_s,
    100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,
       (wait_time_ms - signal_wait_time_ms)/1000. AS resources_wait_s,
       signal_wait_time_ms/1000. AS signal_wait_s,
    ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn
FROM sys.dm_os_wait_stats
WHERE wait_type IN(SELECT wait_type FROM #CSS_Waits_Repository)) -- filter out additional irrelevant waits
INSERT INTO @Waits(wait_type, wait_time_s, resources_wait_s, signal_wait_s)
SELECT W1.wait_type,
  CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,
  CAST(W1.resources_wait_s AS DECIMAL(12, 2)) AS resources_wait_s,
  CAST(W1.signal_wait_s AS DECIMAL(12, 2)) AS signal_wait_s
FROM Waits AS W1
INNER JOIN Waits AS W2
ON W2.rn <= W1.rn
GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.resources_wait_s, W1.signal_wait_s, W1.pct
HAVING SUM(W2.pct) - W1.pct < 95; -- percentage threshold
 
WAITFOR DELAY '00:01:00';
 
WITH Waits AS
(SELECT wait_type, wait_time_ms / 1000. AS wait_time_s,
    100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,
       (wait_time_ms - signal_wait_time_ms)/1000. AS resources_wait_s,
       signal_wait_time_ms/1000. AS signal_wait_s,
    ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN(SELECT wait_type FROM #CSS_Waits_Repository)) -- filter out additional irrelevant waits
INSERT INTO @Waits(wait_type, wait_time_s, resources_wait_s, signal_wait_s)
SELECT W1.wait_type,
  CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,
  CAST(W1.resources_wait_s AS DECIMAL(12, 2)) AS resources_wait_s,
  CAST(W1.signal_wait_s AS DECIMAL(12, 2)) AS signal_wait_s
FROM Waits AS W1
INNER JOIN Waits AS W2
ON W2.rn <= W1.rn
GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.resources_wait_s, W1.signal_wait_s, W1.pct
HAVING SUM(W2.pct) - W1.pct < 95; -- percentage threshold
 
SELECT wait_type,
       MAX(wait_time_s) - MIN(wait_time_s) WaitDelta_total,
       MAX(resources_wait_s) - MIN(resources_wait_s) WaitDelta_resource,
       MAX(signal_wait_s) - MIN(signal_wait_s) WaitDelta_signal
FROM @Waits
GROUP BY wait_Type
ORDER BY WaitDelta_total Desc

Waiting tasks: know more…

SELECT owr.session_id,
       owr.wait_duration_ms,
       owr.wait_type,
       owr.blocking_session_id,
       owr.resource_description,
       er.wait_resource,
       er.command,
       er.status,
       est.text
FROM sys.dm_os_waiting_tasks owr
INNER JOIN sys.dm_exec_requests er
ON owr.session_id = er.session_id
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) est
WHERE owr.session_id > 50

Memory related DMVs:

OS process memory: know more…

SELECT physical_memory_in_use_kb AS Actual_Usage,
      large_page_allocations_kb AS large_Pages,
      locked_page_allocations_kb AS locked_Pages,
      virtual_address_space_committed_kb AS VAS_Committed,
      large_page_allocations_kb + locked_page_allocations_kb + 427000
FROM sys.dm_os_process_memory

OS memory clerks: know more…

SELECT type,
       name,
       pages_kb,
       virtual_memory_reserved_kb,
       virtual_memory_committed_kb,
       awe_allocated_kb,
       shared_memory_reserved_kb,
       shared_memory_committed_kb,
       page_size_in_bytes
FROM sys.dm_os_memory_clerks
ORDER BY pages_kb desc

OS memory objects: know more…

SELECT type, SUM(pages_in_bytes)
FROM sys.dm_os_memory_objects
GROUP BY type
ORDER BY 2 DESC

OS memory pools: know more…

SELECT type,
        name,
        max_free_entries_count,
        free_entries_count,
        removed_in_all_rounds_count
FROM sys.dm_os_memory_pools
ORDER BY removed_in_all_rounds_count DESC

Memory cache counters: know more…

/***** SQL Server 2005 through 2008 R2 *****/
SELECT name,
       type,
       SUM(single_pages_kb + multi_pages_kb) AS Size,
       SUM(single_pages_in_use_kb + multi_pages_in_use_kb) AS Used_Size,
       SUM(entries_count) AS Entries,
       SUM(entries_in_use_count) AS Used_Entries
FROM sys.dm_os_memory_cache_counters
GROUP BY name, type
ORDER BY 4 DESC
 
/***** SQL Server 2012 or later versions *****/
SELECT name,
       type,
       SUM(pages_kb) AS Size,
       SUM(pages_in_use_kb) AS Used_Size,
       SUM(entries_count) AS Entries,
       SUM(entries_in_use_count) AS Used_Entries
FROM sys.dm_os_memory_cache_counters
GROUP BY name, type
ORDER BY 4 DESC

Memory cache entries: know more…

SELECT TOP 10 OBJECT_NAME(est.objectid, EST.dbid) AS ObjectName,
       omce.name,
       omce.in_use_count,
       omce.is_dirty,
       omce.disk_ios_count,
       omce.context_switches_count,
       omce.original_cost,
       omce.current_cost,
       omce.pages_kb
FROM sys.dm_exec_cached_plans ecp
CROSS APPLY sys.dm_exec_sql_text(ecp.plan_handle) est
INNER JOIN sys.dm_os_memory_cache_entries omce
ON ecp.memory_object_address = omce.memory_object_address
ORDER BY is_dirty DESC

Memory cache hash tables: know more…

SELECT name,
       buckets_count,
       buckets_in_use_count,
       buckets_avg_length,
       hits_count,
       misses_count
FROM sys.dm_os_memory_cache_hash_tables
WHERE type = 'CACHESTORE_SQLCP' OR type = 'CACHESTORE_OBJCP'
ORDER BY buckets_count DESC

Memory brokers: know more…

SELECT pool_id,
       memory_broker_type,
       allocations_kb,
       allocations_kb_per_sec,
       predicted_allocations_kb,
       target_allocations_kb,
       future_allocations_kb,
       overall_limit_kb,
       last_notification
FROM sys.dm_os_memory_brokers

Memory cache clock hands: know more…

SELECT name,
       type,
       clock_hand,
       clock_status,
       rounds_count,
       removed_all_rounds_count,
       updated_last_round_count,
       removed_last_round_count,
       last_round_start_time
FROM sys.dm_os_memory_cache_clock_hands
ORDER BY removed_last_round_count DESC

OS buffer descriptors: know more…

--Get buffer pool utilization by each database
SELECT DBName = CASE WHEN database_id = 32767 THEN 'RESOURCEDB'
                           ELSE DB_NAME(database_id) END,
       Size_MB = COUNT(1)/128
FROM sys.dm_os_buffer_descriptors
GROUP BY database_id
ORDER BY 2 DESC
 
--Get buffer pool utilization by each object in a database
SELECT DBName = CASE WHEN database_id = 32767 THEN 'RESOURCEDB'
                           ELSE DB_NAME(database_id) END,
       ObjName = o.name,
       Size_MB = COUNT(1)/128.0
FROM sys.dm_os_buffer_descriptors obd
INNER JOIN sys.allocation_units au
       ON obd.allocation_unit_id = au.allocation_unit_id
INNER JOIN sys.partitions p
       ON au.container_id = p.hobt_id
INNER JOIN sys.objects o
       ON p.object_id = o.object_id
WHERE obd.database_id = DB_ID()
AND o.type != 'S'
GROUP BY obd.database_id, o.name
ORDER BY 3 DESC
 
--Get clean and dirty pages count in each database
SELECT Page_Status = CASE WHEN is_modified = 1 THEN 'Dirty'
                           ELSE 'Clean' END,
       DBName = CASE WHEN database_id = 32767 THEN 'RESOURCEDB'
                           ELSE DB_NAME(database_id) END,
       Pages = COUNT(1)
FROM sys.dm_os_buffer_descriptors
GROUP BY database_id, is_modified
ORDER BY 2

Rest of the DMV s will be continued tomorrow.

Happy Learning,
Manu

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook |
Follow me on Twitter | Follow me on FaceBook