If you haven’t already done so then please read the following https://tenbulls.co.uk/enlightenment/scripts/
Redgate is a fantastic little software company that is based in Cambridge in the United Kingdom. They are the home of many fantastic Tools and Solutions such as the famous SQL Compare and frequently win awards for being one of the best Companies to work for in the UK.
The DMV Starter pack was written by Glenn Berry (blog|twitter), Louis Davidson (blog|twitter) and Tim Ford (blog|twitter), comes with an excellent EBook of the same title and both can be downloaded from here.
-- Script 1
-- Get a count of SQL connections by IP address
SELECT ec.client_net_address ,
es.[program_name] ,
es.[host_name] ,
es.login_name ,
COUNT(ec.session_id) AS [connection count]
FROM sys.dm_exec_sessions AS es
INNER JOIN sys.dm_exec_connections AS ec
ON es.session_id = ec.session_id
GROUP BY ec.client_net_address ,
es.[program_name] ,
es.[host_name] ,
es.login_name
ORDER BY ec.client_net_address ,
es.[program_name] ;
-- Script 2
-- Get SQL users that are connected and how many sessions they have
SELECT login_name ,
COUNT(session_id) AS [session_count]
FROM sys.dm_exec_sessions
GROUP BY login_name
ORDER BY COUNT(session_id) DESC ;
-- Script 3
-- Look at current expensive or blocked requests
SELECT r.session_id ,
r.[status] ,
r.wait_type ,
r.scheduler_id ,
SUBSTRING(qt.[text], r.statement_start_offset / 2,
( CASE WHEN r.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.[text])) * 2
ELSE r.statement_end_offset
END - r.statement_start_offset ) / 2) AS [statement_executing] ,
DB_NAME(qt.[dbid]) AS [DatabaseName] ,
OBJECT_NAME(qt.objectid) AS [ObjectName] ,
r.cpu_time ,
r.total_elapsed_time ,
r.reads ,
r.writes ,
r.logical_reads ,
r.plan_handle
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS qt
WHERE r.session_id > 50
ORDER BY r.scheduler_id ,
r.[status] ,
r.session_id ;
-- Script 4
-- Top 3 CPU-sapping queries for which plans exist in the cache
SELECT TOP 3
total_worker_time ,
execution_count ,
total_worker_time / execution_count AS [Avg CPU Time] ,
CASE WHEN deqs.statement_start_offset = 0
AND deqs.statement_end_offset = -1
THEN '-- see objectText column--'
ELSE '-- query --' + CHAR(13) + CHAR(10)
+ SUBSTRING(execText.text, deqs.statement_start_offset / 2,
( ( CASE WHEN deqs.statement_end_offset = -1
THEN DATALENGTH(execText.text)
ELSE deqs.statement_end_offset
END ) - deqs.statement_start_offset ) / 2)
END AS queryText
FROM sys.dm_exec_query_stats deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.plan_handle) AS execText
ORDER BY deqs.total_worker_time DESC ;
-- Script 5
-- Use Counts and # of plans for compiled plans
SELECT objtype ,
usecounts ,
COUNT(*) AS [no_of_plans]
FROM sys.dm_exec_cached_plans
WHERE cacheobjtype = 'Compiled Plan'
GROUP BY objtype ,
usecounts
ORDER BY objtype ,
usecounts ;
-- Script 6
-- Script 7
-- Top Cached SPs By Total Logical Reads (SQL 2008 only).
-- Logical reads relate to memory pressure
SELECT TOP ( 25 )
p.name AS [SP Name] ,
qs.total_logical_reads AS [TotalLogicalReads] ,
qs.total_logical_reads / qs.execution_count AS [AvgLogicalReads] ,
qs.execution_count ,
ISNULL(qs.execution_count /
DATEDIFF(Second, qs.cached_time, GETDATE()),
0) AS [Calls/Second] ,
qs.total_elapsed_time ,
qs.total_elapsed_time / qs.execution_count AS [avg_elapsed_time] ,
qs.cached_time
FROM sys.procedures AS p
INNER JOIN sys.dm_exec_procedure_stats AS qs
ON p.[object_id] = qs.[object_id]
WHERE qs.database_id = DB_ID()
ORDER BY qs.total_logical_reads DESC ;
-- Script 8
-- Top Cached SPs By Total Physical Reads (SQL 2008 only)
-- Physical reads relate to disk I/O pressure
SELECT TOP ( 25 )
p.name AS [SP Name] ,
qs.total_physical_reads AS [TotalPhysicalReads] ,
qs.total_physical_reads / qs.execution_count AS [AvgPhysicalReads] ,
qs.execution_count ,
ISNULL(qs.execution_count /
DATEDIFF(Second, qs.cached_time, GETDATE()),
0) AS [Calls/Second] ,
qs.total_elapsed_time ,
qs.total_elapsed_time / qs.execution_count AS [avg_elapsed_time] ,
qs.cached_time
FROM sys.procedures AS p
INNER JOIN sys.dm_exec_procedure_stats AS qs
ON p.[object_id] = qs.[object_id]
WHERE qs.database_id = DB_ID()
ORDER BY qs.total_physical_reads DESC ;
-- Script 9
-- Shows the memory required by both running (non-null grant_time)
-- and waiting queries (null grant_time)
-- SQL Server 2008 version
SELECT DB_NAME(st.dbid) AS [DatabaseName] ,
mg.requested_memory_kb ,
mg.ideal_memory_kb ,
mg.request_time ,
mg.grant_time ,
mg.query_cost ,
mg.dop ,
st.[text]
FROM sys.dm_exec_query_memory_grants AS mg
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
WHERE mg.request_time < COALESCE(grant_time, '99991231')
ORDER BY mg.requested_memory_kb DESC ;
-- Shows the memory required by both running (non-null grant_time)
-- and waiting queries (null grant_time)
-- SQL Server 2005 version
SELECT DB_NAME(st.dbid) AS [DatabaseName] ,
mg.requested_memory_kb ,
mg.request_time ,
mg.grant_time ,
mg.query_cost ,
mg.dop ,
st.[text]
FROM sys.dm_exec_query_memory_grants AS mg
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
WHERE mg.request_time < COALESCE(grant_time, '99991231') ORDER BY mg.requested_memory_kb DESC ; -- Script 10 -- Monitoring transaction activity SELECT st.session_id , DB_NAME(dt.database_id) AS database_name , CASE WHEN dt.database_transaction_begin_time IS NULL THEN 'read-only' ELSE 'read-write' END AS transaction_state , dt.database_transaction_begin_time AS read_write_start_time , dt.database_transaction_log_record_count , dt.database_transaction_log_bytes_used FROM sys.dm_tran_session_transactions AS st INNER JOIN sys.dm_tran_database_transactions AS dt ON st.transaction_id = dt.transaction_id ORDER BY st.session_id , database_name -- Script 11 -- Look at active Lock Manager resources for current database SELECT request_session_id , DB_NAME(resource_database_id) AS [Database] , resource_type , resource_subtype , request_type , request_mode , resource_description , request_mode , request_owner_type FROM sys.dm_tran_locks WHERE request_session_id > 50
AND resource_database_id = DB_ID()
AND request_session_id <> @@SPID
ORDER BY request_session_id ;
-- Look for blocking
SELECT tl.resource_type ,
tl.resource_database_id ,
tl.resource_associated_entity_id ,
tl.request_mode ,
tl.request_session_id ,
wt.blocking_session_id ,
wt.wait_type ,
wt.wait_duration_ms
FROM sys.dm_tran_locks AS tl
INNER JOIN sys.dm_os_waiting_tasks AS wt
ON tl.lock_owner_address = wt.resource_address
ORDER BY wait_duration_ms DESC ;
-- Script 12
-- Missing Indexes in current database by Index Advantage
SELECT user_seeks * avg_total_user_cost * ( avg_user_impact * 0.01 )
AS [index_advantage] ,
migs.last_user_seek ,
mid.[statement] AS [Database.Schema.Table] ,
mid.equality_columns ,
mid.inequality_columns ,
mid.included_columns ,
migs.unique_compiles ,
migs.user_seeks ,
migs.avg_total_user_cost ,
migs.avg_user_impact
FROM sys.dm_db_missing_index_group_stats AS migs WITH ( NOLOCK )
INNER JOIN sys.dm_db_missing_index_groups AS mig WITH ( NOLOCK )
ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid WITH ( NOLOCK )
ON mig.index_handle = mid.index_handle
WHERE mid.database_id = DB_ID()
ORDER BY index_advantage DESC ;
-- Script 13
--- Index Read/Write stats (all tables in current DB)
SELECT OBJECT_NAME(s.[object_id]) AS [ObjectName] ,
i.name AS [IndexName] ,
i.index_id ,
user_seeks + user_scans + user_lookups AS [Reads] ,
user_updates AS [Writes] ,
i.type_desc AS [IndexType] ,
i.fill_factor AS [FillFactor]
FROM sys.dm_db_index_usage_stats AS s
INNER JOIN sys.indexes AS i ON s.[object_id] = i.[object_id]
WHERE OBJECTPROPERTY(s.[object_id], 'IsUserTable') = 1
AND i.index_id = s.index_id
AND s.database_id = DB_ID()
ORDER BY OBJECT_NAME(s.[object_id]) ,
writes DESC ,
reads DESC ;
-- Script 14
-- List unused indexes
SELECT OBJECT_NAME(i.[object_id]) AS [Table Name] ,
i.name
FROM sys.indexes AS i
INNER JOIN sys.objects AS o ON i.[object_id] = o.[object_id]
WHERE i.index_id NOT IN ( SELECT s.index_id
FROM sys.dm_db_index_usage_stats AS s
WHERE s.[object_id] = i.[object_id]
AND i.index_id = s.index_id
AND database_id = DB_ID() )
AND o.[type] = 'U'
ORDER BY OBJECT_NAME(i.[object_id]) ASC ;
-- Script 15
-- Possible Bad NC Indexes (writes > reads)
SELECT OBJECT_NAME(s.[object_id]) AS [Table Name] ,
i.name AS [Index Name] ,
i.index_id ,
user_updates AS [Total Writes] ,
user_seeks + user_scans + user_lookups AS [Total Reads] ,
user_updates - ( user_seeks + user_scans + user_lookups )
AS [Difference]
FROM sys.dm_db_index_usage_stats AS s WITH ( NOLOCK )
INNER JOIN sys.indexes AS i WITH ( NOLOCK )
ON s.[object_id] = i.[object_id]
AND i.index_id = s.index_id
WHERE OBJECTPROPERTY(s.[object_id], 'IsUserTable') = 1
AND s.database_id = DB_ID()
AND user_updates > ( user_seeks + user_scans + user_lookups )
AND i.index_id > 1
ORDER BY [Difference] DESC ,
[Total Writes] DESC ,
[Total Reads] ASC ;
-- Script 16
-- Table and row count information
SELECT OBJECT_NAME(ps.[object_id]) AS [TableName] ,
i.name AS [IndexName] ,
SUM(ps.row_count) AS [RowCount]
FROM sys.dm_db_partition_stats AS ps
INNER JOIN sys.indexes AS i ON i.[object_id] = ps.[object_id]
AND i.index_id = ps.index_id
WHERE i.type_desc IN ( 'CLUSTERED', 'HEAP' )
AND i.[object_id] > 100
AND OBJECT_SCHEMA_NAME(ps.[object_id]) <> 'sys'
GROUP BY ps.[object_id] ,
i.name
ORDER BY SUM(ps.row_count) DESC ;
-- Script 17
-- Get Free Space in TempDB
SELECT SUM(unallocated_extent_page_count) AS [free pages] ,
( SUM(unallocated_extent_page_count) * 1.0 / 128 ) AS [free space in MB]
FROM sys.dm_db_file_space_usage ;
-- Quick TempDB Summary
SELECT SUM(user_object_reserved_page_count) * 8.192 AS [UserObjectsKB] ,
SUM(internal_object_reserved_page_count) * 8.192 AS [InternalObjectsKB] ,
SUM(version_store_reserved_page_count) * 8.192 AS [VersonStoreKB] ,
SUM(unallocated_extent_page_count) * 8.192 AS [FreeSpaceKB]
FROM sys.dm_db_file_space_usage ;
-- Script 18
-- Calculates average stalls per read, per write, and per total input/output
-- for each database file.
SELECT DB_NAME(database_id) AS [Database Name] ,
file_id ,
io_stall_read_ms ,
num_of_reads ,
CAST(io_stall_read_ms / ( 1.0 + num_of_reads ) AS NUMERIC(10, 1))
AS [avg_read_stall_ms] ,
io_stall_write_ms ,
num_of_writes ,
CAST(io_stall_write_ms / ( 1.0 + num_of_writes ) AS NUMERIC(10, 1))
AS [avg_write_stall_ms] ,
io_stall_read_ms + io_stall_write_ms AS [io_stalls] ,
num_of_reads + num_of_writes AS [total_io] ,
CAST(( io_stall_read_ms + io_stall_write_ms ) / ( 1.0 + num_of_reads
+ num_of_writes)
AS NUMERIC(10,1)) AS [avg_io_stall_ms]
FROM sys.dm_io_virtual_file_stats(NULL, NULL)
ORDER BY avg_io_stall_ms DESC ;
-- Script 19
-- Look at pending I/O requests by file
SELECT DB_NAME(mf.database_id) AS [Database] ,
mf.physical_name ,
r.io_pending ,
r.io_pending_ms_ticks ,
r.io_type ,
fs.num_of_reads ,
fs.num_of_writes
FROM sys.dm_io_pending_io_requests AS r
INNER JOIN sys.dm_io_virtual_file_stats(NULL, NULL) AS fs
ON r.io_handle = fs.file_handle
INNER JOIN sys.master_files AS mf ON fs.database_id = mf.database_id
AND fs.file_id = mf.file_id
ORDER BY r.io_pending ,
r.io_pending_ms_ticks DESC ;
-- Script 20
-- Total waits are wait_time_ms (high signal waits indicates CPU pressure)
SELECT CAST(100.0 * SUM(signal_wait_time_ms) / SUM(wait_time_ms)
AS NUMERIC(20,2)) AS [%signal (cpu) waits] ,
CAST(100.0 * SUM(wait_time_ms - signal_wait_time_ms)
/ SUM(wait_time_ms) AS NUMERIC(20, 2)) AS [%resource waits]
FROM sys.dm_os_wait_stats ;
-- Script 21
-- Isolate top waits for server instance since last restart
-- or statistics clear
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 ,
ROW_NUMBER() OVER ( ORDER BY wait_time_ms DESC ) AS rn
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN ( 'CLR_SEMAPHORE', 'LAZYWRITER_SLEEP',
'RESOURCE_QUEUE', 'SLEEP_TASK',
'SLEEP_SYSTEMTASK',
'SQLTRACE_BUFFER_FLUSH', 'WAITFOR',
'LOGMGR_QUEUE', 'CHECKPOINT_QUEUE',
'REQUEST_FOR_DEADLOCK_SEARCH',
'XE_TIMER_EVENT', 'BROKER_TO_FLUSH',
'BROKER_TASK_STOP',
'CLR_MANUAL_EVENT',
'CLR_AUTO_EVENT',
'DISPATCHER_QUEUE_SEMAPHORE',
'FT_IFTS_SCHEDULER_IDLE_WAIT',
'XE_DISPATCHER_WAIT',
'XE_DISPATCHER_JOIN' )
)
SELECT W1.wait_type ,
CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s ,
CAST(W1.pct AS DECIMAL(12, 2)) AS pct ,
CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct
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.pct
HAVING SUM(W2.pct) - W1.pct < 95 ; -- percentage threshold
-- Script 22
-- Recovery model, log reuse wait description, log file size,
-- log usage size and compatibility level for all databases on instance
SELECT db.[name] AS [Database Name] ,
db.recovery_model_desc AS [Recovery Model] ,
db.log_reuse_wait_desc AS [Log Reuse Wait Description] ,
ls.cntr_value AS [Log Size (KB)] ,
lu.cntr_value AS [Log Used (KB)] ,
CAST(CAST(lu.cntr_value AS FLOAT) / CAST(ls.cntr_value AS FLOAT)
AS DECIMAL(18,2)) * 100 AS [Log Used %] ,
db.[compatibility_level] AS [DB Compatibility Level] ,
db.page_verify_option_desc AS [Page Verify Option]
FROM sys.databases AS db
INNER JOIN sys.dm_os_performance_counters AS lu
ON db.name = lu.instance_name
INNER JOIN sys.dm_os_performance_counters AS ls
ON db.name = ls.instance_name
WHERE lu.counter_name LIKE 'Log File(s) Used Size (KB)%'
AND ls.counter_name LIKE 'Log File(s) Size (KB)%' ;
-- Script 23
-- Hardware information from SQL Server 2008
-- (Cannot distinguish between HT and multi-core)
SELECT cpu_count AS [Logical CPU Count] ,
hyperthread_ratio AS [Hyperthread Ratio] ,
cpu_count / hyperthread_ratio AS [Physical CPU Count] ,
physical_memory_in_bytes / 1048576 AS [Physical Memory (MB)] ,
sqlserver_start_time
FROM sys.dm_os_sys_info ;
-- Hardware information from SQL Server 2005
-- (Cannot distinguish between HT and multi-core)
SELECT cpu_count AS [Logical CPU Count] ,
hyperthread_ratio AS [Hyperthread Ratio] ,
cpu_count / hyperthread_ratio AS [Physical CPU Count] ,
physical_memory_in_bytes / 1048576 AS [Physical Memory (MB)]
FROM sys.dm_os_sys_info ;
-- Script 24
-- Get CPU Utilization History for last 30 minutes (in one minute intervals)
-- This version works with SQL Server 2008 and SQL Server 2008 R2 only
DECLARE @ts_now bigint = (SELECT cpu_ticks/(cpu_ticks/ms_ticks)FROM sys.dm_os_sys_info);
SELECT TOP(30) SQLProcessUtilization AS [SQL Server Process CPU Utilization],
SystemIdle AS [System Idle Process],
100 - SystemIdle - SQLProcessUtilization AS [Other Process CPU Utilization],
DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS [Event Time]
FROM (
SELECT record.value('(./Record/@id)[1]', 'int') AS record_id,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int')
AS [SystemIdle],
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]',
'int')
AS [SQLProcessUtilization], [timestamp]
FROM (
SELECT [timestamp], CONVERT(xml, record) AS [record]
FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
AND record LIKE N'%%') AS x
) AS y
ORDER BY record_id DESC;
-- Script 25
-- Get Avg task count and Avg runnable task count
SELECT AVG(current_tasks_count) AS [Avg Task Count] ,
AVG(runnable_tasks_count) AS [Avg Runnable Task Count]
FROM sys.dm_os_schedulers
WHERE scheduler_id < 255
AND [status] = 'VISIBLE ONLINE' ;
-- Script 26
-- Is NUMA enabled
SELECT CASE COUNT(DISTINCT parent_node_id)
WHEN 1 THEN 'NUMA disabled'
ELSE 'NUMA enabled'
END
FROM sys.dm_os_schedulers
WHERE parent_node_id <> 32 ;
-- Script 27
-- Good basic information about memory amounts and state
-- SQL Server 2008 and 2008 R2 only
SELECT total_physical_memory_kb ,
available_physical_memory_kb ,
total_page_file_kb ,
available_page_file_kb ,
system_memory_state_desc
FROM sys.dm_os_sys_memory ;
-- Script 28
-- SQL Server Process Address space info (SQL 2008 and 2008 R2 only)
--(shows whether locked pages is enabled, among other things)
SELECT physical_memory_in_use_kb ,
locked_page_allocations_kb ,
page_fault_count ,
memory_utilization_percentage ,
available_commit_limit_kb ,
process_physical_memory_low ,
process_virtual_memory_low
FROM sys.dm_os_process_memory ;
-- Script 29
-- Look at the number of items in different parts of the cache
SELECT name ,
[type] ,
entries_count ,
single_pages_kb ,
single_pages_in_use_kb ,
multi_pages_kb ,
multi_pages_in_use_kb
FROM sys.dm_os_memory_cache_counters
WHERE [type] = 'CACHESTORE_SQLCP'
OR [type] = 'CACHESTORE_OBJCP'
ORDER BY multi_pages_kb DESC ;
-- Script 30
-- Get total buffer usage by database
SELECT DB_NAME(database_id) AS [Database Name] ,
COUNT(*) * 8 / 1024.0 AS [Cached Size (MB)]
FROM sys.dm_os_buffer_descriptors
WHERE database_id > 4 -- exclude system databases
AND database_id <> 32767 -- exclude ResourceDB
GROUP BY DB_NAME(database_id)
ORDER BY [Cached Size (MB)] DESC ;
-- Breaks down buffers by object (table, index) in the buffer pool
SELECT OBJECT_NAME(p.[object_id]) AS [ObjectName] ,
p.index_id ,
COUNT(*) / 128 AS [Buffer size(MB)] ,
COUNT(*) AS [Buffer_count]
FROM sys.allocation_units AS a
INNER JOIN sys.dm_os_buffer_descriptors
AS b ON a.allocation_unit_id = b.allocation_unit_id
INNER JOIN sys.partitions AS p ON a.container_id = p.hobt_id
WHERE b.database_id = DB_ID()
AND p.[object_id] > 100
GROUP BY p.[object_id] ,
p.index_id
ORDER BY buffer_count DESC ;
-- Script 31
-- Find long running SQL/CLR tasks
SELECT os.task_address ,
os.[state] ,
os.last_wait_type ,
clr.[state] ,
clr.forced_yield_count
FROM sys.dm_os_workers AS os
INNER JOIN sys.dm_clr_tasks AS clr
ON ( os.task_address = clr.sos_task_address )
WHERE clr.[type] = 'E_TYPE_USER' ;
-- Script 32
-- Get population status for all FT catalogs in the current database
SELECT c.name ,
c.[status] ,
c.status_description ,
OBJECT_NAME(p.table_id) AS [table_name] ,
p.population_type_description ,
p.is_clustered_index_scan ,
p.status_description ,
p.completion_type_description ,
p.queued_population_type_description ,
p.start_time ,
p.range_count
FROM sys.dm_fts_active_catalogs AS c
INNER JOIN sys.dm_fts_index_population AS p
ON c.database_id = p.database_id
AND c.catalog_id = p.catalog_id
WHERE c.database_id = DB_ID()
ORDER BY c.name ;
-- Script 33
-- Check auto page repair history (New in SQL 2008)
SELECT DB_NAME(database_id) AS [database_name] ,
database_id ,
file_id ,
page_id ,
error_type ,
page_status ,
modification_time
FROM sys.dm_db_mirroring_auto_page_repair ;
