The Ultimate DBA Toolbox of Useful Queries

In the heat of a high pressure situation there is nothing worse than trying to lay your hands on your favorite troubleshooting queries. Especially when they are not somewhere easy to find.

Here in this (soon to be) mega post, I am listing the queries I have found most useful over the years.

Note: it is a work in progress, I will be continually updating this article with new queries and updates. Make sure you bookmark it for reference.

If you have a useful query that you use, that is either not on our list, or is an upgrade of one of the below please share via a comment.

Quick jump:

Indexes
File System
– File I/O
Locks, Blocks and Deadlocks
Memory
Troubleshooting Queries
-Wait Stats
-Log Usage

Blocks, Locks and Deadlocks

Deadlocks

To set the trace flags (1204 and 1222) so that SQL will store information around deadlocks in the error log you can use these.

DBCC TRACEON (1204, -1)
DBCC TRACEON (1222, -1)
------------------------------------------------------------------
--- to turn back off -----
DBCC TRACEOFF (1204, -1)
DBCC TRACEOFF (1222, -1)
------------------------------------------------------------------

Identify Blocking Script

SELECT
db.name DBName,
tl.request_session_id,
wt.blocking_session_id,
OBJECT_NAME(p.OBJECT_ID) BlockedObjectName,
tl.resource_type,
h1.TEXT AS RequestingText,
h2.TEXT AS BlockingTest,
tl.request_mode
FROM sys.dm_tran_locks AS tl
INNER JOIN sys.databases db ON db.database_id = tl.resource_database_id
INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address
INNER JOIN sys.partitions AS p ON p.hobt_id = tl.resource_associated_entity_id
INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_id
INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_id
CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2
GO
------------------------------------------------------------------

Ref: SQL Authority

Memory

Database and Objects using Memory

--find out how big buffer pool is and determine percentage used by each database

DECLARE @total_buffer INT;
SELECT @total_buffer = cntr_value   FROM sys.dm_os_performance_counters
WHERE RTRIM([object_name]) LIKE '%Buffer Manager'   AND counter_name = 'Total Pages';
;WITH src AS(   SELECT        database_id, db_buffer_pages = COUNT_BIG(*) 
FROM sys.dm_os_buffer_descriptors       --WHERE database_id BETWEEN 5 AND 32766       
GROUP BY database_id)SELECT   [db_name] = CASE [database_id] WHEN 32767        THEN 'Resource DB'        ELSE DB_NAME([database_id]) END,   db_buffer_pages,   db_buffer_MB = db_buffer_pages / 128,   db_buffer_percent = CONVERT(DECIMAL(6,3),        db_buffer_pages * 100.0 / @total_buffer)
FROM src
ORDER BY db_buffer_MB DESC;

--then drill down into memory used by objects in database of your choice

USE db_with_most_memory;

WITH src AS(   SELECT       [Object] = o.name,       [Type] = o.type_desc,       [Index] = COALESCE(i.name, ''),       [Index_Type] = i.type_desc,       p.[object_id],       p.index_id,       au.allocation_unit_id   
FROM       sys.partitions AS p   INNER JOIN       sys.allocation_units AS au       ON p.hobt_id = au.container_id   INNER JOIN       sys.objects AS o       ON p.[object_id] = o.[object_id]   INNER JOIN       sys.indexes AS i       ON o.[object_id] = i.[object_id]       AND p.index_id = i.index_id   WHERE       au.[type] IN (1,2,3)       AND o.is_ms_shipped = 0)
SELECT   src.[Object],   src.[Type],   src.[Index],   src.Index_Type,   buffer_pages = COUNT_BIG(b.page_id),   buffer_mb = COUNT_BIG(b.page_id) / 128
FROM   src
INNER JOIN   sys.dm_os_buffer_descriptors AS b  
 ON src.allocation_unit_id = b.allocation_unit_id
WHERE   b.database_id = DB_ID()
GROUP BY   src.[Object],   src.[Type],   src.[Index],   src.Index_Type
ORDER BY   buffer_pages DESC;
------------------------------------------------------------------

Indexes

Index Usage Stats

select db_name(database_id) as DatabaseName,object_name(object_id,database_id) as TableName,index_id,
user_seeks,user_scans,user_lookups,user_updates
from sys.dm_db_index_usage_stats  
where database_id > 4 and object_id > 50000
------------------------------------------------------------------

Recommended Indexes

-- Missing Index Script
-- Original Author: Pinal Dave (C) 2011
SELECT TOP 25
dm_mid.database_id AS DatabaseID,
dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact,
dm_migs.last_user_seek AS Last_User_Seek,
OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName],
'CREATE INDEX [IX_' + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + '_'
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),', ','_'),'[',''),']','') +
CASE
WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN '_'
ELSE ''
END
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),', ','_'),'[',''),']','')
+ ']'
+ ' ON ' + dm_mid.statement
+ ' (' + ISNULL (dm_mid.equality_columns,'')
+ CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN ',' ELSE
'' END
+ ISNULL (dm_mid.inequality_columns, '')
+ ')'
+ ISNULL (' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_Statement
FROM sys.dm_db_missing_index_groups dm_mig
INNER JOIN sys.dm_db_missing_index_group_stats dm_migs
ON dm_migs.group_handle = dm_mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details dm_mid
ON dm_mig.index_handle = dm_mid.index_handle
WHERE dm_mid.database_ID = DB_ID()
ORDER BY Avg_Estimated_Impact DESC
GO
------------------------------------------------------------------

Ref: Pinal Dave

Troubleshooting Queries

Wait Stats

;WITH [Waits] AS
    (SELECT
        [wait_type],
        [wait_time_ms] / 1000.0 AS [WaitS],
        ([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS],
        [signal_wait_time_ms] / 1000.0 AS [SignalS],
        [waiting_tasks_count] AS [WaitCount],
        100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage],
        ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum]
    FROM sys.dm_os_wait_stats
    WHERE [wait_type] NOT IN (
        N'CLR_SEMAPHORE',    N'LAZYWRITER_SLEEP',
        N'RESOURCE_QUEUE',   N'SQLTRACE_BUFFER_FLUSH',
        N'SLEEP_TASK',       N'SLEEP_SYSTEMTASK',
        N'WAITFOR',          N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
        N'CHECKPOINT_QUEUE', N'REQUEST_FOR_DEADLOCK_SEARCH',
        N'XE_TIMER_EVENT',   N'XE_DISPATCHER_JOIN',
        N'LOGMGR_QUEUE',     N'FT_IFTS_SCHEDULER_IDLE_WAIT',
        N'BROKER_TASK_STOP', N'CLR_MANUAL_EVENT',
        N'CLR_AUTO_EVENT',   N'DISPATCHER_QUEUE_SEMAPHORE',
        N'TRACEWRITE',       N'XE_DISPATCHER_WAIT',
        N'BROKER_TO_FLUSH',  N'BROKER_EVENTHANDLER',
        N'FT_IFTSHC_MUTEX',  N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
        N'DIRTY_PAGE_POLL',  N'SP_SERVER_DIAGNOSTICS_SLEEP')
    )
SELECT
    [W1].[wait_type] AS [WaitType],
    CAST ([W1].[WaitS] AS DECIMAL(14, 2)) AS [Wait_S],
    CAST ([W1].[ResourceS] AS DECIMAL(14, 2)) AS [Resource_S],
    CAST ([W1].[SignalS] AS DECIMAL(14, 2)) AS [Signal_S],
    [W1].[WaitCount] AS [WaitCount],
    CAST ([W1].[Percentage] AS DECIMAL(4, 2)) AS [Percentage],
    CAST (([W1].[WaitS] / [W1].[WaitCount]) AS DECIMAL (14, 4)) AS [AvgWait_S],
    CAST (([W1].[ResourceS] / [W1].[WaitCount]) AS DECIMAL (14, 4)) AS [AvgRes_S],
    CAST (([W1].[SignalS] / [W1].[WaitCount]) AS DECIMAL (14, 4)) AS [AvgSig_S]
FROM [Waits] AS [W1]
INNER JOIN [Waits] AS [W2]
    ON [W2].[RowNum] <= [W1].[RowNum]
GROUP BY [W1].[RowNum], [W1].[wait_type], [W1].[WaitS],
    [W1].[ResourceS], [W1].[SignalS], [W1].[WaitCount], [W1].[Percentage]
HAVING SUM ([W2].[Percentage]) - [W1].[Percentage] < 95; -- percentage threshold
------------------------------------------------------------------

Ref: Paul S. Randal

Find slow queries

-- Top ten queries
SELECT TOP 10 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
					  ((CASE qs.statement_end_offset
							WHEN -1 THEN DATALENGTH(qt.TEXT)
							ELSE qs.statement_end_offset
						END - qs.statement_start_offset)/2)+1),
					  qs.execution_count,
					  qs.total_logical_reads, 
					  qs.last_logical_reads,
					  qs.total_logical_writes, 
					  qs.last_logical_writes,
					  qs.total_worker_time,
					  qs.last_worker_time,
					  (qs.total_elapsed_time/qs.execution_count)/1000000 AS "Avg Duration (S)",
					  qs.total_elapsed_time/1000000 total_elapsed_time_in_S,
					  qs.last_elapsed_time/1000000 last_elapsed_time_in_S,
					  qp.query_plan
		  FROM sys.dm_exec_query_stats qs
		 CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
		 CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
		 ORDER BY qs.total_logical_reads DESC 

Query the default trace – “The Black Box”

Although it will soon be deprecated you can sometimes gain useful information by taking advantage of the SQL Server “Black Box” – the default trace.

SELECT *
FROM    sys.fn_trace_gettable(CONVERT(VARCHAR(150), ( SELECT TOP 1
                                                              f.[value]
                                                      FROM    sys.fn_trace_getinfo(NULL) f
                                                      WHERE   f.property = 2
                                                    )), DEFAULT) T
        JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id
------------------------------------------------------------------

Ref: Simpletalk

Implicit Conversions

DECLARE @dbname SYSNAME 
SET @dbname = QUOTENAME(DB_NAME());

WITH XMLNAMESPACES 
(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan') 
SELECT 
stmt.value('(@StatementText)[1]', 'varchar(max)'), 
t.value('(ScalarOperator/Identifier/ColumnReference/@Schema)[1]', 'varchar(128)'), 
t.value('(ScalarOperator/Identifier/ColumnReference/@Table)[1]', 'varchar(128)'), 
t.value('(ScalarOperator/Identifier/ColumnReference/@Column)[1]', 'varchar(128)'), 
ic.DATA_TYPE AS ConvertFrom, 
ic.CHARACTER_MAXIMUM_LENGTH AS ConvertFromLength, 
t.value('(@DataType)[1]', 'varchar(128)') AS ConvertTo, 
t.value('(@Length)[1]', 'int') AS ConvertToLength, 
query_plan 
FROM sys.dm_exec_cached_plans AS cp 
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp 
CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS batch(stmt) 
CROSS APPLY stmt.nodes('.//Convert[@Implicit="1"]') AS n(t) 
JOIN INFORMATION_SCHEMA.COLUMNS AS ic 
ON QUOTENAME(ic.TABLE_SCHEMA) = t.value('(ScalarOperator/Identifier/ColumnReference/@Schema)[1]', 'varchar(128)') 
AND QUOTENAME(ic.TABLE_NAME) = t.value('(ScalarOperator/Identifier/ColumnReference/@Table)[1]', 'varchar(128)') 
AND ic.COLUMN_NAME = t.value('(ScalarOperator/Identifier/ColumnReference/@Column)[1]', 'varchar(128)') 
WHERE t.exist('ScalarOperator/Identifier/ColumnReference[@Database=sql:variable("@dbname")][@Schema!="[sys]"]') = 1

File System

File I/O

You can get some pretty good info from some of the system tables around file I/O.

select d.name as DbName, mf.name as FileName, mf.physical_name, NumberReads,BytesRead,IoStallReadMS,NumberWrites,BytesWritten,IoStallWriteMS,
IoStallReadMS/NumberReads as AvgReadLatencyMS, IoStallWriteMS/NumberWrites as AvgWriteLatencyMS, convert(numeric(12,2),BytesOnDisk/1024./1024./1024.) as FileSizeGB
from sys.databases as d
join sys.master_files as mf on d.database_id = mf.database_id
CROSS APPLY fn_virtualfilestats(d.database_id,-1) x
------------------------------------------------------------------

Log Usage

Check Space Used

 DBCC SQLPERF('logspace')

What is using the Log?

Sometimes you can’t shrink a log because it’s being used. To give you a clue what that might be you can run this.

SELECT name, log_reuse_wait_desc FROM sys.databases

Full Text Search

Check names and sizes of catalogs

SELECT 
   [table] = OBJECT_SCHEMA_NAME(table_id) + '.' + OBJECT_NAME(table_id), 
   size_in_KB = CONVERT(DECIMAL(12,2), SUM(data_size/1024.0))
 FROM sys.fulltext_index_fragments
 -- WHERE table_id = OBJECT_ID('dbo.specific_table_name')
 GROUP BY table_id;
Rob StGeorge
Senior SQL Server Database Administrator residing in Auckland, NZ

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.