SQL Server Performance Troubleshooting: Essential Scripts Every Database personnel or Architect Should Have

TECHNICAL ARCHITECTS ZONE

SQL Server Performance Troubleshooting

A practical collection of SQL Server scripts and diagnostic techniques for investigating CPU, I/O, blocking, waits, memory, TempDB and query-performance issues.

Performance issues are among the most common challenges handled by SQL Server DBAs. A typical incident starts with a broad statement such as “the application is slow,” but that statement does not identify whether the underlying problem is blocking, CPU pressure, storage latency, memory pressure, TempDB contention or an inefficient query.

Effective troubleshooting begins by collecting evidence from SQL Server before making configuration or application changes. This article provides practical Dynamic Management View queries that can help identify where to begin.

TAZ Practical Note

Capture evidence before restarting SQL Server, clearing the procedure cache, terminating sessions or rebuilding indexes. Many DMVs contain cumulative information that can be lost after a restart or cache reset.

Why Performance Monitoring Matters

Performance troubleshooting becomes faster when the investigation follows a structured sequence. The scripts in this article can help:

Identify expensive and frequently executed queries
Detect blocking and long-running transactions
Review CPU-intensive workloads
Find queries performing excessive logical reads
Analyze important wait types
Review missing-index recommendations
Check index fragmentation
Monitor TempDB consumption
Identify queries waiting for memory grants

Recommended Investigation Order

Confirm the incident window and affected application or database.
Check blocking and identify the head blocker.
Review active requests and current wait types.
Check CPU and logical reads for expensive cached queries.
Review server waits for the broader resource pattern.
Check I/O, TempDB and memory grants.
Analyze execution plans for the affected queries.
Validate the proposed fix against a representative workload.

1. Identify Top CPU-Consuming Queries

High CPU usage may be caused by inefficient execution plans, repeated compilations, missing indexes, excessive parallelism or a sudden increase in workload volume.

SELECT TOP (20)
    qs.execution_count,
    qs.total_worker_time / 1000 AS TotalCPU_ms,
    qs.total_worker_time / NULLIF(qs.execution_count, 0) / 1000 AS AvgCPU_ms,
    DB_NAME(st.dbid) AS DatabaseName,
    LEFT(st.text, 2000) AS QueryText
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY qs.total_worker_time DESC;

What to check

High total CPU combined with a large execution count
High average CPU even when execution count is low
Queries with scans, large joins, sorts or hash operations
Plan changes or parameter-sensitive execution behavior

Query-statistics DMVs represent cached plans. Results can disappear after restart, recompilation, plan eviction or procedure-cache clearing.

2. Find Queries Generating Excessive Logical Reads

High logical reads are frequently associated with table scans, inefficient joins, non-sargable predicates or indexes that do not support the query.

SELECT TOP (20)
    qs.execution_count,
    qs.total_logical_reads,
    qs.total_logical_reads / NULLIF(qs.execution_count, 0) AS AvgLogicalReads,
    qs.total_logical_writes,
    DB_NAME(st.dbid) AS DatabaseName,
    LEFT(st.text, 2000) AS QueryText
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY qs.total_logical_reads DESC;

Typical causes

Large table or index scans
Functions applied to filtered columns
Implicit data-type conversion
Missing or poorly designed indexes
Returning more rows or columns than the application requires

3. Detect Active Long-Running Requests

Long duration alone does not establish that a query is inefficient. A request may be actively consuming CPU, waiting for I/O, blocked by another transaction or waiting for a memory grant.

SELECT
    r.session_id,
    DB_NAME(r.database_id) AS DatabaseName,
    r.status,
    r.command,
    r.blocking_session_id,
    r.wait_type,
    r.wait_time,
    r.cpu_time / 1000.0 AS CPUSeconds,
    r.total_elapsed_time / 1000.0 AS DurationSeconds,
    LEFT(t.text, 2000) AS QueryText
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE r.session_id > 50
  AND r.total_elapsed_time > 60000
ORDER BY r.total_elapsed_time DESC;

Interpretation

Duration and CPU are both high: investigate query efficiency.
Duration is high but CPU is low: investigate waits or blocking.
Blocking session is non-zero: identify the head blocker before terminating anything.

4. Monitor the Current Active Workload

SELECT
    r.session_id,
    DB_NAME(r.database_id) AS DatabaseName,
    r.status,
    r.command,
    r.cpu_time,
    r.logical_reads,
    r.reads,
    r.writes,
    r.wait_type,
    r.blocking_session_id,
    r.total_elapsed_time / 1000.0 AS DurationSeconds,
    LEFT(t.text, 2000) AS QueryText
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE r.session_id > 50
ORDER BY r.cpu_time DESC;

This query provides a real-time view of active requests. Capture several samples during the incident instead of relying on a single snapshot.

5. Analyze Blocking Sessions

SELECT
    r.session_id AS BlockedSessionId,
    r.blocking_session_id AS BlockingSessionId,
    DB_NAME(r.database_id) AS DatabaseName,
    r.status,
    r.wait_type,
    r.wait_time,
    r.wait_resource,
    LEFT(t.text, 2000) AS BlockedQuery
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE r.blocking_session_id <> 0
ORDER BY r.wait_time DESC;

Common causes

Long-running or uncommitted transactions
Large update/delete operations
Missing indexes extending transaction duration
Sessions left open by an application
Schema-modification locks during deployment or maintenance

Production caution: Do not terminate a blocking session only because the session appears at the top of a blocking chain. Confirm the owner, transaction purpose, rollback impact and business consequences first.

6. Review Wait Statistics

SELECT TOP (20)
    wait_type,
    waiting_tasks_count,
    wait_time_ms / 1000.0 AS WaitSeconds,
    signal_wait_time_ms / 1000.0 AS SignalWaitSeconds
FROM sys.dm_os_wait_stats
WHERE wait_type NOT LIKE 'SLEEP%'
  AND wait_type NOT IN
  (
      'BROKER_EVENTHANDLER',
      'BROKER_RECEIVE_WAITFOR',
      'BROKER_TASK_STOP',
      'CLR_AUTO_EVENT',
      'CLR_MANUAL_EVENT',
      'LAZYWRITER_SLEEP',
      'SQLTRACE_BUFFER_FLUSH',
      'XE_DISPATCHER_WAIT',
      'XE_TIMER_EVENT'
  )
ORDER BY wait_time_ms DESC;

Wait type	Common interpretation
PAGEIOLATCH_*	Waiting for data pages to be read from storage
WRITELOG	Waiting for transaction-log flushes
LCK_M_*	Lock-related blocking
RESOURCE_SEMAPHORE	Queries waiting for execution memory grants
CXPACKET / CXCONSUMER	Parallelism-related waits that require workload context

Wait statistics are cumulative from the last SQL Server service start or manual reset. Always correlate waits with the incident period and current workload.

7. Review Missing-Index Recommendations

SELECT TOP (20)
    DB_NAME(mid.database_id) AS DatabaseName,
    migs.user_seeks,
    migs.avg_total_user_cost,
    migs.avg_user_impact,
    mid.statement,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns
FROM sys.dm_db_missing_index_group_stats AS migs
INNER JOIN sys.dm_db_missing_index_groups AS mig
    ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid
    ON mig.index_handle = mid.index_handle
ORDER BY
    migs.avg_total_user_cost *
    migs.avg_user_impact *
    (migs.user_seeks + migs.user_scans) DESC;

TAZ Practical Note: Missing-index DMVs provide suggestions, not production-ready index designs. Review existing indexes, key order, included columns, write overhead, storage requirements and overlapping recommendations before creating an index.

8. Check Index Fragmentation

SELECT
    OBJECT_SCHEMA_NAME(ps.object_id) AS SchemaName,
    OBJECT_NAME(ps.object_id) AS TableName,
    i.name AS IndexName,
    ps.avg_fragmentation_in_percent,
    ps.page_count
FROM sys.dm_db_index_physical_stats
(
    DB_ID(), NULL, NULL, NULL, 'LIMITED'
) AS ps
INNER JOIN sys.indexes AS i
    ON ps.object_id = i.object_id
   AND ps.index_id = i.index_id
WHERE ps.index_id > 0
  AND ps.page_count >= 1000
  AND ps.avg_fragmentation_in_percent >= 10
ORDER BY ps.avg_fragmentation_in_percent DESC;

Avoid maintaining tiny indexes based only on fragmentation percentage. Page count, workload, storage performance, maintenance duration and transaction-log growth are equally important.

9. Check TempDB Usage

SELECT
    SUM(user_object_reserved_page_count) * 8.0 / 1024 AS UserObjectsMB,
    SUM(internal_object_reserved_page_count) * 8.0 / 1024 AS InternalObjectsMB,
    SUM(version_store_reserved_page_count) * 8.0 / 1024 AS VersionStoreMB,
    SUM(unallocated_extent_page_count) * 8.0 / 1024 AS FreeSpaceMB
FROM tempdb.sys.dm_db_file_space_usage;

High TempDB consumption may be caused by:

Large sorts or hash operations
Temporary tables and table variables
Row-versioning workloads
Index creation or rebuild operations
Long-running snapshot transactions
Queries spilling because of insufficient memory grants

10. Monitor Query Memory Grants

SELECT
    session_id,
    request_time,
    grant_time,
    requested_memory_kb,
    granted_memory_kb,
    required_memory_kb,
    used_memory_kb,
    max_used_memory_kb,
    wait_time_ms
FROM sys.dm_exec_query_memory_grants
ORDER BY wait_time_ms DESC;

Queries waiting for memory grants may be delayed before execution. Large grants can also reduce concurrency by consuming memory required by other requests.

A Practical Production Scenario

Assume users report that order-processing screens have become slow during peak business hours.

Confirm the affected application, database and start time.
Check active requests and blocking chains.
Capture the query text, wait type, duration, CPU and logical reads.
Review the execution plan for scans, spills, poor estimates and lookups.
Check whether statistics, indexes or parameter-sensitive execution are involved.
Test the change outside production with representative parameter values.
Monitor duration, CPU, reads and waits after implementation.

What Not to Do During a Performance Incident

Do not restart SQL Server without capturing diagnostic evidence.
Do not clear the entire procedure cache as a first response.
Do not terminate sessions without evaluating rollback impact.
Do not create every index suggested by the missing-index DMVs.
Do not rebuild every index solely because fragmentation exceeds a fixed percentage.
Do not change MAXDOP, memory or cost-threshold settings without workload evidence.
Do not treat one DMV snapshot as a complete root-cause analysis.

DBA Performance Troubleshooting Checklist

Record the incident time and affected workload
Check active requests and blocking
Review request-level waits
Review server-level wait statistics
Identify expensive CPU and I/O queries
Capture execution plans
Check storage latency, TempDB and memory grants
Review indexes and statistics
Test the proposed correction outside production
Compare before-and-after measurements
Document the root cause and preventive action

SQL Server Scripts Repository

Visit the TAZ scripts repository for additional SQL Server monitoring, health-check, backup, Always On and troubleshooting scripts.

Open the SQL Server Scripts Repository

Final Thoughts

SQL Server performance tuning should be based on evidence rather than assumptions. DMVs, execution plans, wait statistics and workload baselines help DBAs distinguish symptoms from root causes.

The scripts in this article provide a practical starting point, but each result must be interpreted in the context of the affected application, database design, infrastructure and business workload.

Important: Review and test all scripts before running them in production. Some DMVs require elevated SQL Server permissions, and the results can vary by SQL Server version, workload and configuration.

Author: Ajit Kumar Patnaik

Technical Architects Zone (TAZ)

Practical Solutions | Real-World Experience | Continuous Learning

Search This Blog

Technical Architects Zone