Application performance problems aren't always caused by inefficient code. In many production systems, the real bottleneck is the database. Slow queries, missing indexes, blocking sessions, fragmented indexes, and excessive I/O can significantly impact application performance.
Fortunately, SQL Server provides built-in Dynamic Management Views (DMVs) and system catalog views that help developers quickly identify performance issues. Knowing which queries to run—and how to interpret their results—can dramatically reduce troubleshooting time.
Rather than relying on trial and error, this article introduces ten essential SQL Server queries that every .NET developer should have in their performance troubleshooting toolkit.
Note: Some queries in this article require the
VIEW SERVER STATEpermission. Always execute diagnostic queries in production environments carefully and avoid making changes without understanding their impact.
Why SQL Performance Matters
Database performance directly affects application responsiveness.
Common symptoms of database-related performance problems include:
Slow API responses
High CPU utilization
Blocking and deadlocks
Excessive disk I/O
Long-running queries
Connection pool exhaustion
Increased timeout exceptions
Before optimizing application code, verify whether the database is the actual bottleneck.
Query #1 – Find the Most Expensive Queries
SQL Server keeps execution statistics for recently executed queries.
SELECT TOP 10
qs.execution_count,
qs.total_worker_time / qs.execution_count AS AvgCPU,
qs.total_elapsed_time / qs.execution_count AS AvgDuration,
qt.text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
ORDER BY AvgCPU DESC;
This query helps identify queries consuming the most CPU time.
Query #2 – Identify Missing Indexes
SQL Server records index recommendations based on query execution.
SELECT
migs.avg_total_user_cost,
mid.statement,
mid.equality_columns,
mid.included_columns
FROM sys.dm_db_missing_index_group_stats migs
JOIN sys.dm_db_missing_index_groups mig
ON migs.group_handle = mig.index_group_handle
JOIN sys.dm_db_missing_index_details mid
ON mig.index_handle = mid.index_handle
ORDER BY migs.avg_total_user_cost DESC;
Missing indexes can significantly increase execution time for frequently executed queries.
Query #3 – Detect Blocking Sessions
Blocking occurs when one session prevents another from accessing required resources.
SELECT
blocking_session_id,
session_id,
wait_type,
wait_time
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0;
Investigating blocking chains early helps prevent widespread application slowdowns.
Query #4 – Find Long-Running Queries
Long-running queries often indicate inefficient execution plans or missing indexes.
SELECT
session_id,
status,
start_time,
command
FROM sys.dm_exec_requests
WHERE status = 'running';
Review these queries during periods of high database activity.
Query #5 – Check Index Fragmentation
Fragmented indexes increase disk reads and reduce query performance.
SELECT
OBJECT_NAME(object_id) AS TableName,
avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats
(
DB_ID(),
NULL,
NULL,
NULL,
'LIMITED'
);
High fragmentation may indicate that index maintenance is required.
Query #6 – Monitor Wait Statistics
Wait statistics reveal where SQL Server spends most of its time.
SELECT TOP 10
wait_type,
wait_time_ms
FROM sys.dm_os_wait_stats
ORDER BY wait_time_ms DESC;
Understanding dominant wait types is often the fastest way to diagnose performance bottlenecks.
Query #7 – Find Unused Indexes
Unused indexes increase storage requirements and slow data modification operations.
SELECT
OBJECT_NAME(i.object_id) AS TableName,
i.name
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats s
ON i.object_id = s.object_id
AND i.index_id = s.index_id
WHERE s.user_seeks IS NULL;
Review unused indexes before removing them from production databases.
Query #8 – Monitor Active Connections
A sudden increase in active sessions may indicate application or connection pool issues.
SELECT
COUNT(*) AS ActiveConnections
FROM sys.dm_exec_sessions
WHERE status = 'running';
Tracking connection growth helps identify resource exhaustion before failures occur.
Query #9 – Identify Large Tables
Large tables often require partitioning, archival, or indexing improvements.
SELECT
t.name,
SUM(p.rows) AS TotalRows
FROM sys.tables t
JOIN sys.partitions p
ON t.object_id = p.object_id
WHERE p.index_id IN (0,1)
GROUP BY t.name
ORDER BY TotalRows DESC;
Understanding table growth helps guide capacity planning and optimization efforts.
Query #10 – View Query Execution Plans
Execution plans reveal how SQL Server executes a query.
Join the conversation! Your thoughts help the community grow.