Technical Architects Zone (TAZ)

SQL Server Performance Tuning Scripts

CPU, I/O, Blocking, Waits, Indexes, Memory, TempDB & Query Performance

Introduction

SQL Server performance troubleshooting should start with evidence rather than assumptions. When users report that an application is slow, the actual cause could be blocking, an expensive query, excessive logical reads, CPU pressure, memory-grant pressure, TempDB usage, storage latency, an inefficient execution plan, or several factors working together.

This page provides practical T-SQL scripts for identifying expensive workloads and investigating common SQL Server performance problems.

Important: DMV results provide evidence, not automatic tuning instructions. Compare results with the normal workload baseline and validate any change in a controlled environment before implementing it in production.

Contents

1. Top CPU-Consuming Queries 2. Top Logical Reads 3. Top Duration Queries 4. Currently Running Expensive Queries 5. Blocking Analysis 6. Wait Statistics 7. Missing Index Candidates 8. Index Usage Analysis 9. Index Fragmentation 10. TempDB Usage 11. Memory Grants 12. Database File Growth 13. Performance Health Dashboard 14. Performance Troubleshooting Workflow

1. Top CPU-Consuming Queries

This query identifies cached statements with the highest cumulative CPU consumption and also calculates average CPU per execution.

SELECT TOP (20)
    DB_NAME(st.dbid) AS DatabaseName,
    qs.execution_count,
    qs.total_worker_time / 1000.0 AS TotalCPU_ms,
    (qs.total_worker_time /
        NULLIF(qs.execution_count, 0)) / 1000.0 AS AvgCPU_ms,
    qs.last_execution_time,
    LEFT(st.text, 1000) 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;

Interpret carefully: A frequently executed lightweight query can have high total CPU, while an infrequently executed query may have high average CPU. Review both metrics.

2. Top Logical Reads

High logical reads can indicate large scans, inefficient access paths, poor selectivity, inappropriate indexes, or simply a legitimately large workload.

SELECT TOP (20)
    DB_NAME(st.dbid) AS DatabaseName,
    qs.execution_count,
    qs.total_logical_reads AS TotalLogicalReads,
    qs.total_logical_reads /
        NULLIF(qs.execution_count, 0) AS AvgLogicalReads,
    qs.last_execution_time,
    LEFT(st.text, 1000) 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;

3. Top Duration Queries

SELECT TOP (20)
    DB_NAME(st.dbid) AS DatabaseName,
    qs.execution_count,
    qs.total_elapsed_time / 1000.0 AS TotalDuration_ms,
    (qs.total_elapsed_time /
        NULLIF(qs.execution_count, 0)) / 1000.0 AS AvgDuration_ms,
    qs.last_execution_time,
    LEFT(st.text, 1000) 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_elapsed_time DESC;

Elapsed time can include both execution time and time spent waiting. A high-duration query therefore does not automatically mean that CPU is the bottleneck.

4. Currently Running Expensive Queries

SELECT
    r.session_id,
    DB_NAME(r.database_id) AS DatabaseName,
    r.status,
    r.command,
    r.cpu_time AS CPU_ms,
    r.logical_reads,
    r.reads,
    r.writes,
    r.wait_type,
    r.wait_time AS WaitTime_ms,
    r.blocking_session_id,
    r.total_elapsed_time / 1000.0 AS DurationSeconds,
    LEFT(t.text, 1000) AS QueryText
FROM sys.dm_exec_requests AS r
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE r.session_id <> @@SPID
ORDER BY r.cpu_time DESC;

5. Blocking Analysis

Blocking can make a fast query appear slow because the request spends time waiting for another transaction rather than actively consuming CPU.

SELECT
    r.session_id,
    r.blocking_session_id,
    DB_NAME(r.database_id) AS DatabaseName,
    r.status,
    r.wait_type,
    r.wait_time AS WaitTime_ms,
    r.wait_resource,
    s.login_name,
    s.host_name,
    s.program_name,
    t.text AS QueryText
FROM sys.dm_exec_requests AS r
INNER JOIN sys.dm_exec_sessions AS s
    ON r.session_id = s.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE r.blocking_session_id <> 0
ORDER BY r.wait_time DESC;

Do not automatically kill a blocker. Identify the head blocker, application process, transaction age, business activity, and expected rollback impact before taking action.

6. Wait Statistics Analysis

SELECT TOP (20)
    wait_type,
    waiting_tasks_count,
    wait_time_ms / 1000.0 AS WaitSeconds,
    signal_wait_time_ms / 1000.0 AS SignalWaitSeconds,
    (wait_time_ms - signal_wait_time_ms) / 1000.0
        AS ResourceWaitSeconds
FROM sys.dm_os_wait_stats
WHERE wait_type NOT LIKE 'SLEEP%'
ORDER BY wait_time_ms DESC;

Common Wait Categories

Example Wait	Area to Investigate
PAGEIOLATCH_*	Storage latency, physical reads, query access pattern, memory pressure
LCK_M_*	Blocking, transaction design, isolation level, query duration
WRITELOG	Transaction log latency, transaction volume, commit pattern
RESOURCE_SEMAPHORE	Query memory grants, concurrency, estimates, available memory
CXPACKET / CXCONSUMER	Parallel-query behavior in workload context

Wait names are diagnostic clues, not root causes by themselves. Investigate the workload and resource responsible for the wait.

7. Missing Index Candidates

SELECT TOP (20)
    migs.user_seeks,
    migs.user_scans,
    migs.avg_total_user_cost,
    migs.avg_user_impact,
    mid.statement AS TableName,
    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_user_impact DESC;

Do not create every suggested index. Review existing indexes, query plans, selectivity, write overhead, storage, duplicate indexes, and the overall workload before implementing a new index.

8. Index Usage Analysis

SELECT
    OBJECT_SCHEMA_NAME(i.object_id) AS SchemaName,
    OBJECT_NAME(i.object_id) AS TableName,
    i.name AS IndexName,
    i.is_unique,
    i.is_primary_key,
    i.is_unique_constraint,
    COALESCE(s.user_seeks, 0) AS UserSeeks,
    COALESCE(s.user_scans, 0) AS UserScans,
    COALESCE(s.user_lookups, 0) AS UserLookups,
    COALESCE(s.user_updates, 0) AS UserUpdates
FROM sys.indexes AS i
LEFT JOIN sys.dm_db_index_usage_stats AS s
    ON i.object_id = s.object_id
   AND i.index_id = s.index_id
   AND s.database_id = DB_ID()
WHERE i.index_id > 0
  AND i.is_hypothetical = 0
ORDER BY
    UserUpdates DESC;

Usage statistics can reset after SQL Server restart and other events. Never drop an index only because the current counters show zero reads.

9. Index Fragmentation Analysis

SELECT
    OBJECT_SCHEMA_NAME(ps.object_id) AS SchemaName,
    OBJECT_NAME(ps.object_id) AS TableName,
    i.name AS IndexName,
    ps.index_type_desc,
    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
ORDER BY
    ps.avg_fragmentation_in_percent DESC;

Avoid using a single fragmentation percentage as a universal rebuild rule. Page count, workload, storage characteristics, maintenance cost, statistics, and actual performance benefit should influence the maintenance decision.

10. TempDB Usage

SELECT
    CAST(
        SUM(user_object_reserved_page_count)
        * 8.0 / 1024 AS decimal(18,2)
    ) AS UserObjectsMB,

    CAST(
        SUM(internal_object_reserved_page_count)
        * 8.0 / 1024 AS decimal(18,2)
    ) AS InternalObjectsMB,

    CAST(
        SUM(version_store_reserved_page_count)
        * 8.0 / 1024 AS decimal(18,2)
    ) AS VersionStoreMB,

    CAST(
        SUM(unallocated_extent_page_count)
        * 8.0 / 1024 AS decimal(18,2)
    ) AS FreeSpaceMB

FROM tempdb.sys.dm_db_file_space_usage;

11. Query Memory Grants

Queries waiting for execution memory can contribute to performance problems, particularly under high concurrency or when estimates lead to large memory requests.

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

12. Database File Growth Configuration

SELECT
    DB_NAME(database_id) AS DatabaseName,
    name AS LogicalFileName,
    type_desc,
    physical_name,
    CAST(size * 8.0 / 1024
        AS decimal(18,2)) AS SizeMB,

    CASE
        WHEN is_percent_growth = 1
        THEN CAST(growth AS varchar(20)) + '%'
        ELSE CAST(
            CAST(growth * 8.0 / 1024
                AS decimal(18,2))
            AS varchar(30)
        ) + ' MB'
    END AS AutoGrowth

FROM sys.master_files

ORDER BY
    DatabaseName,
    type_desc,
    LogicalFileName;

This reports current file size and autogrowth configuration. Historical growth requires monitoring data collected over time rather than a single DMV snapshot.

13. Quick Performance Health Dashboard

This read-only script provides a quick starting point during a performance incident.

/* =========================================
   SQL SERVER PERFORMANCE HEALTH CHECK
   Technical Architects Zone (TAZ)
   ========================================= */

-- Server
SELECT
    @@SERVERNAME AS ServerName,
    GETDATE() AS CurrentTime;


-- Active Requests
SELECT
    COUNT(*) AS ActiveRequests
FROM sys.dm_exec_requests
WHERE session_id <> @@SPID;


-- Blocked Requests
SELECT
    COUNT(*) AS BlockedSessions
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0;


-- Current Expensive Requests
SELECT TOP (10)
    r.session_id,
    DB_NAME(r.database_id) AS DatabaseName,
    r.cpu_time AS CPU_ms,
    r.total_elapsed_time / 1000.0
        AS DurationSeconds,
    r.logical_reads,
    r.wait_type,
    r.blocking_session_id,
    LEFT(st.text, 1000) AS QueryText
FROM sys.dm_exec_requests AS r
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
WHERE r.session_id <> @@SPID
ORDER BY r.cpu_time DESC;


-- Top Wait Statistics
SELECT TOP (10)
    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%'
ORDER BY wait_time_ms DESC;


-- Operating-System Memory
SELECT
    total_physical_memory_kb / 1024
        AS TotalMemoryMB,
    available_physical_memory_kb / 1024
        AS AvailableMemoryMB,
    system_memory_state_desc
FROM sys.dm_os_sys_memory;


-- Query Memory Grants
SELECT
    session_id,
    requested_memory_kb,
    granted_memory_kb,
    wait_time_ms
FROM sys.dm_exec_query_memory_grants
ORDER BY wait_time_ms DESC;


-- Transaction Log Usage
DBCC SQLPERF(LOGSPACE);

14. Performance Troubleshooting Workflow

There is no universal diagnostic order suitable for every SQL Server incident, but the following workflow provides a practical starting point.

Users report performance problem
            |
            v
Confirm scope and timeframe
            |
            v
Check currently running requests
            |
            v
Check blocking and waits
            |
            v
Check CPU / I/O / memory pressure
            |
            v
Identify expensive queries
            |
            v
Inspect execution plans
            |
            v
Review statistics and indexes
            |
            v
Check TempDB and storage
            |
            v
Compare against normal baseline
            |
            v
Identify root cause
            |
            v
Apply targeted correction
            |
            v
Measure again

Practical rule: Do not start troubleshooting by rebuilding every index, clearing the plan cache, shrinking files, restarting SQL Server, or killing sessions. Collect evidence first.

Production Performance Checklist

• Workload: determine exactly what became slow and when.
• Blocking: identify prolonged blocking and transaction chains.
• CPU: correlate server CPU with expensive SQL workloads.
• I/O: review logical reads, physical reads and storage latency.
• Duration: compare query duration with the established baseline.
• Waits: identify meaningful changes in wait behavior.
• Plans: inspect expensive-query execution plans.
• Statistics: check cardinality estimates and statistics where applicable.
• Indexes: evaluate missing, overlapping, duplicate and low-value indexes.
• Memory: investigate memory grants and overall memory pressure.
• TempDB: review user objects, internal objects, version store and contention.
• Files: check autogrowth, disk capacity and growth trends.
• Configuration: review relevant instance and database configuration.
• Validation: measure again after making the change.

Key Takeaways

• Performance troubleshooting should start with evidence from the affected timeframe.
• Total resource consumption and average resource consumption answer different questions.
• High query duration can result from CPU consumption, I/O, blocking, or another wait.
• Missing-index DMVs provide candidates for investigation, not automatic indexing instructions.
• Low index-usage counts are not sufficient evidence to drop an index.
• Fragmentation percentage alone should not determine an index-maintenance action.
• Buffer-cache hit ratio should not be used as a universal standalone memory-health target.
• Memory grants can reveal concurrency and query-estimation problems.
• TempDB usage should be traced back to the workload responsible for it.
• Always compare performance before and after any tuning change.

Conclusion

SQL Server performance tuning is not about finding one expensive query or running a collection of maintenance commands. The real objective is to determine where the workload is spending time and why.

CPU statistics identify computationally expensive workloads. Logical reads reveal data-access cost. Blocking and wait statistics expose resource contention. Memory grants help identify query-memory pressure. TempDB usage provides another view of workload behavior, while index DMVs and execution plans help explain how SQL Server accesses the underlying data.

The practical tuning cycle is: measure → identify the bottleneck → understand the root cause → make a targeted change → measure again.

Author: Ajit Kumar Patnaik

Technical Architects Zone (TAZ)

Practical Solutions | Real-World Experience | Continuous Learning