SQL Server Snippets | Chris Chedgzoy

Currently Running Queries

Shows all queries currently executing on the server with their session ID, status, CPU time and elapsed time. Use when: the server feels slow and you want to see what's running right now.

SELECT
    sqltext.TEXT AS [Query],
    req.session_id AS [Session],
    req.status AS [Status],
    req.command AS [Command],
    req.cpu_time AS [CPU (ms)],
    req.total_elapsed_time AS [Elapsed (ms)],
    req.reads AS [Reads],
    req.writes AS [Writes]
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
ORDER BY req.total_elapsed_time DESC

Current Connections by Database

Shows how many connections each login has open, grouped by database. Use when: you suspect connection pool exhaustion or want to see who's connected.

SELECT
    DB_NAME(dbid) AS [Database],
    COUNT(dbid) AS [Connections],
    loginame AS [Login]
FROM sys.sysprocesses
WHERE dbid > 0
GROUP BY dbid, loginame
ORDER BY COUNT(dbid) DESC

Top 10 Slowest Queries

Finds the 10 worst-performing queries by average elapsed time, including their execution plan. Use when: fine-tuning performance. Look at the query plan column for missing index hints.

SELECT TOP 10
    st.text AS [SQL Statement],
    qs.last_execution_time AS [Last Run],
    (total_logical_reads + total_physical_reads + total_logical_writes)
        / execution_count AS [Avg IO],
    (total_worker_time / execution_count) / 1000000.0 AS [Avg CPU (sec)],
    (total_elapsed_time / execution_count) / 1000000.0 AS [Avg Elapsed (sec)],
    execution_count AS [Executions],
    qp.query_plan AS [Query Plan]
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY total_elapsed_time / execution_count DESC

Database Sizes

Lists every database on the server with its data file size, log file size and total size in MB. Use when: checking disk usage or planning capacity.

SELECT
    db.name AS [Database],
    CAST(SUM(CASE WHEN mf.type_desc = 'ROWS' THEN mf.size END) * 8.0 / 1024 AS DECIMAL(10,2)) AS [Data (MB)],
    CAST(SUM(CASE WHEN mf.type_desc = 'LOG' THEN mf.size END) * 8.0 / 1024 AS DECIMAL(10,2)) AS [Log (MB)],
    CAST(SUM(mf.size) * 8.0 / 1024 AS DECIMAL(10,2)) AS [Total (MB)]
FROM sys.databases db
JOIN sys.master_files mf ON db.database_id = mf.database_id
GROUP BY db.name
ORDER BY SUM(mf.size) DESC

Table Sizes (Current Database)

Shows row counts and total size in MB for every table in the current database. Use when: finding which tables are eating the most space.

SELECT
    s.name + '.' + t.name AS [Table],
    p.rows AS [Row Count],
    CAST(SUM(a.total_pages) * 8.0 / 1024 AS DECIMAL(10,2)) AS [Total (MB)],
    CAST(SUM(a.used_pages) * 8.0 / 1024 AS DECIMAL(10,2)) AS [Used (MB)]
FROM sys.tables t
JOIN sys.schemas s ON t.schema_id = s.schema_id
JOIN sys.indexes i ON t.object_id = i.object_id
JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
JOIN sys.allocation_units a ON p.partition_id = a.container_id
WHERE i.index_id <= 1
GROUP BY s.name, t.name, p.rows
ORDER BY SUM(a.total_pages) DESC

Index Fragmentation

Shows fragmentation percentage for all indexes in the current database. Indexes over 30% should be rebuilt; 10-30% should be reorganised. Use when: queries are getting slower over time, especially after bulk inserts or deletes.

SELECT
    OBJECT_NAME(ips.object_id) AS [Table],
    i.name AS [Index],
    ips.index_type_desc AS [Type],
    CAST(ips.avg_fragmentation_in_percent AS DECIMAL(5,1)) AS [Fragmentation %],
    ips.page_count AS [Pages]
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
WHERE ips.avg_fragmentation_in_percent > 5
    AND ips.page_count > 100
ORDER BY ips.avg_fragmentation_in_percent DESC

Blocking Queries

Shows sessions that are blocked and which session is blocking them, along with the SQL text of both. Use when: queries are hanging and you suspect locking or deadlocks.

SELECT
    blocked.session_id AS [Blocked Session],
    blocked.blocking_session_id AS [Blocking Session],
    blocked.wait_type AS [Wait Type],
    blocked.wait_time / 1000 AS [Wait (sec)],
    blockedtext.text AS [Blocked Query],
    blockertext.text AS [Blocker Query]
FROM sys.dm_exec_requests blocked
CROSS APPLY sys.dm_exec_sql_text(blocked.sql_handle) blockedtext
LEFT JOIN sys.dm_exec_requests blocker
    ON blocked.blocking_session_id = blocker.session_id
OUTER APPLY sys.dm_exec_sql_text(blocker.sql_handle) blockertext
WHERE blocked.blocking_session_id > 0
ORDER BY blocked.wait_time DESC

Missing Indexes

SQL Server tracks which indexes would have helped past queries. This shows the top suggestions ranked by potential improvement. Use when: looking for easy performance wins. Add the suggested indexes and watch query times drop.

SELECT TOP 20
    OBJECT_NAME(mid.object_id) AS [Table],
    mid.equality_columns AS [Equality Columns],
    mid.inequality_columns AS [Inequality Columns],
    mid.included_columns AS [Include Columns],
    migs.user_seeks AS [Seeks],
    migs.user_scans AS [Scans],
    CAST(migs.avg_user_impact AS DECIMAL(5,1)) AS [Avg Impact %],
    migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)
        AS [Improvement Score]
FROM sys.dm_db_missing_index_details mid
JOIN sys.dm_db_missing_index_groups mig ON mid.index_handle = mig.index_handle
JOIN sys.dm_db_missing_index_group_stats migs ON mig.index_group_handle = migs.group_handle
WHERE mid.database_id = DB_ID()
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC

Kill a Process

Terminates a specific session by its ID. Replace XX with the session_id from the running queries view. Use when: a query is stuck and you need to force-kill it. Use with caution on production.

KILL XX -- Replace XX with the session_id

Last Backup Dates

Shows when each database was last backed up (full, differential and log). Any NULLs mean that backup type has never run. Use when: auditing your backup schedule or after a scare.

SELECT
    d.name AS [Database],
    MAX(CASE WHEN b.type = 'D' THEN b.backup_finish_date END) AS [Last Full],
    MAX(CASE WHEN b.type = 'I' THEN b.backup_finish_date END) AS [Last Diff],
    MAX(CASE WHEN b.type = 'L' THEN b.backup_finish_date END) AS [Last Log]
FROM sys.databases d
LEFT JOIN msdb.dbo.backupset b ON d.name = b.database_name
WHERE d.database_id > 4    -- Exclude system DBs
GROUP BY d.name
ORDER BY MAX(CASE WHEN b.type = 'D' THEN b.backup_finish_date END) ASC

Find a Column Across All Tables

Searches every table in the current database for columns matching a name. Replace email with the column name you're looking for. Use when: you've inherited a database and need to find where a field lives.

SELECT
    s.name + '.' + t.name AS [Table],
    c.name AS [Column],
    ty.name AS [Data Type],
    c.max_length AS [Max Length],
    c.is_nullable AS [Nullable]
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
JOIN sys.schemas s ON t.schema_id = s.schema_id
JOIN sys.types ty ON c.user_type_id = ty.user_type_id
WHERE c.name LIKE '%email%'    -- Change this to your search term
ORDER BY s.name, t.name, c.name

Top Memory-Consuming Queries

Shows the top 10 queries by memory grant size. Large grants can starve other queries. Use when: the server is running low on memory or queries are waiting for memory grants.

SELECT TOP 10
    st.text AS [Query],
    mg.session_id AS [Session],
    mg.requested_memory_kb / 1024 AS [Requested (MB)],
    mg.granted_memory_kb / 1024 AS [Granted (MB)],
    mg.used_memory_kb / 1024 AS [Used (MB)],
    mg.query_cost AS [Query Cost]
FROM sys.dm_exec_query_memory_grants mg
CROSS APPLY sys.dm_exec_sql_text(mg.sql_handle) st
WHERE mg.granted_memory_kb IS NOT NULL
ORDER BY mg.granted_memory_kb DESC

Unused Indexes

Finds indexes that have never been used for reads but are still being maintained on every write. Dropping these can speed up inserts, updates and deletes. Use when: cleaning up a database that's been running for a while. Check since last SQL Server restart.

SELECT
    OBJECT_NAME(i.object_id) AS [Table],
    i.name AS [Index],
    i.type_desc AS [Type],
    us.user_seeks + us.user_scans + us.user_lookups AS [Total Reads],
    us.user_updates AS [Total Writes],
    CAST(SUM(ps.used_page_count) * 8.0 / 1024 AS DECIMAL(10,2)) AS [Size (MB)]
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats us
    ON i.object_id = us.object_id AND i.index_id = us.index_id AND us.database_id = DB_ID()
JOIN sys.dm_db_partition_stats ps
    ON i.object_id = ps.object_id AND i.index_id = ps.index_id
WHERE OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1
    AND i.index_id > 1    -- Exclude clustered/heap
    AND (us.user_seeks + us.user_scans + us.user_lookups) = 0
GROUP BY i.object_id, i.name, i.type_desc, us.user_seeks, us.user_scans, us.user_lookups, us.user_updates
ORDER BY us.user_updates DESC