How to Identify and Eliminate Expensive Queries in SQL Server

Introduction

In any database environment, a small number of expensive queries often consume a disproportionate amount of resources. Identifying and optimizing these resource-intensive queries is one of the most impactful performance tuning activities a DBA or developer can perform. This guide walks you through practical techniques to find, analyze, and fix your most expensive SQL Server queries.

Section 1: Identifying Expensive Queries

1.1 Using SQL Server's Built-in Tools

Dynamic Management Views (DMVs):

-- Top 50 most CPU-intensive queries
SELECT TOP 50
    qs.total_worker_time/qs.execution_count AS avg_cpu_time,
    qs.total_elapsed_time/qs.execution_count AS avg_duration,
    qs.execution_count,
    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) AS query_text,
    qt.text AS full_query_text,
    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_worker_time DESC;

Query Store (SQL Server 2016+):

-- Find top resource consumers in Query Store
SELECT TOP 10
    rs.avg_cpu_time,
    rs.avg_duration,
    rs.avg_logical_io_reads,
    rs.count_executions,
    qt.query_sql_text
FROM sys.query_store_query q
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_plan p ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
ORDER BY rs.avg_cpu_time DESC;

1.2 Using SQL Server Profiler and Extended Events

Extended Events Session for High-Cost Queries:

CREATE EVENT SESSION [HighCostQueries] ON SERVER 
ADD EVENT sqlserver.sql_statement_completed
(
    WHERE ([duration] > 1000000) -- 1 second
    ACTION (
        sqlserver.sql_text,
        sqlserver.plan_handle,
        sqlserver.client_hostname,
        sqlserver.username
    )
)
ADD TARGET package0.event_file(SET filename=N'HighCostQueries')
WITH (MAX_MEMORY=4096 KB, MAX_DISPATCH_LATENCY=30 SECONDS);

1.3 Using Performance Dashboard Reports

SQL Server Management Studio includes built-in performance reports that can quickly surface expensive queries:

  1. Right-click your server in Object Explorer

  2. Select Reports > Standard Reports

  3. Choose Performance - Top Queries by Average CPU Time or other relevant options

Section 2: Analyzing Expensive Queries

2.1 Understanding Query Patterns

Common causes of expensive queries:

  • Large result sets

  • Missing/inadequate indexes

  • Complex joins

  • Excessive sorting/aggregation

  • Cursor-based operations

  • Implicit conversions

2.2 Key Metrics to Evaluate

  • CPU Time – High computation usage

  • Duration – Total time including waits

  • Logical Reads – Memory pressure

  • Physical Reads – Disk I/O

  • Execution Count – High frequency = high impact

  • Wait Stats – Resource bottlenecks

2.3 Execution Plan Analysis

  • Capture the Actual Execution Plan

  • Look for:

    • Table/Clustered Index Scans

    • Key Lookups

    • Sort Operations

    • Spools

    • Implicit Conversions

  • Focus on high-cost operators first

Section 3: Eliminating Expensive Queries

3.1 Query Rewriting Techniques

Reduce unnecessary columns:

-- Before
SELECT * FROM Orders WHERE OrderDate > '2023-01-01';

-- After
SELECT OrderID, CustomerID, OrderDate 
FROM Orders 
WHERE OrderDate > '2023-01-01';

Avoid nested views:

-- Before
SELECT * FROM vw_OrderDetails WHERE Status = 'Shipped';

-- After
SELECT o.OrderID, o.OrderDate, c.CustomerName
FROM Orders o
JOIN Customers c ON o.CustomerID = c.CustomerID
WHERE o.Status = 'Shipped';

3.2 Index Optimization Strategies

Create missing indexes:

-- Review missing indexes
SELECT * FROM sys.dm_db_missing_index_details;

-- Example index
CREATE INDEX IX_Orders_Status_Date 
ON Orders(Status, OrderDate) 
INCLUDE (CustomerID, TotalAmount);

Maintain indexes:

-- Rebuild fragmented index
ALTER INDEX IX_Orders_CustomerID ON Orders REBUILD;

-- Update outdated statistics
UPDATE STATISTICS Orders WITH FULLSCAN;

3.3 Parameterization & Plan Reuse

Fix parameter sniffing:

-- Option 1
SELECT * FROM Orders 
WHERE OrderDate >= @StartDate 
OPTION (OPTIMIZE FOR (@StartDate = '2023-01-01'));

-- Option 2
DECLARE @LocalStartDate DATETIME = @StartDate;
SELECT * FROM Orders 
WHERE OrderDate >= @LocalStartDate;

3.4 Batch Processing

Avoid massive single-batch operations:

-- Batched delete
WHILE EXISTS (SELECT 1 FROM OrderHistory WHERE OrderDate < '2020-01-01')
BEGIN
    DELETE TOP (5000) FROM OrderHistory 
    WHERE OrderDate < '2020-01-01';
    
    WAITFOR DELAY '00:00:01';
END

Section 4: Proactive Monitoring Strategies

4.1 Alerts for Expensive Queries

-- Create alert for long-running queries
EXEC msdb.dbo.sp_add_alert 
    @name = N'LongRunningQueries',
    @message_id = 0, 
    @severity = 0, 
    @enabled = 1, 
    @delay_between_responses = 60, 
    @include_event_description_in = 1,
    @condition_name = N'SQLServer:SQL Statistics|Batch Requests/sec|>|100',
    @job_id = N'00000000-0000-0000-0000-000000000000';

4.2 Query Store Best Practices

  • Enable Query Store:

ALTER DATABASE YourDatabase SET QUERY_STORE = ON;

  • Monitor top regressions

  • Configure storage limits

  • Use forced plans carefully

4.3 Regular Performance Health Checks

  • Weekly:

    • Top queries by CPU/IO/frequency

  • Monthly:

    • Index rebuilds/statistics updates

  • Quarterly:

    • Benchmark regression reports

Section 5: When to Consider Architectural Changes

Sometimes tuning a query isn’t enough. Consider:

5.1 Database Design Changes

  • Partitioning

  • Denormalization

  • Columnstore Indexes

5.2 Application Adjustments

  • Add caching

  • Use reporting replicas

  • Read/write splitting

5.3 Infrastructure Upgrades

  • More RAM

  • SSDs/NVMe

  • More CPU cores

Conclusion

Eliminating expensive queries in SQL Server means:

  1. Proactively monitoring with DMVs and Query Store

  2. Analyzing execution plans for root causes

  3. Tuning intelligently with rewrites and indexing

  4. Documenting and validating every change

  5. Evolving continuously as workloads grow

Database performance isn’t one-and-done it’s an ongoing journey. By implementing the strategies above, you’ll ensure your SQL Server environment stays fast, efficient, and ready for whatever workload comes next.

Comments

Post a Comment

Popular posts from this blog

Migrating SQL Server to Azure SQL Database: A Step-by-Step Guide

Migrating from on-premises SQL Server to Azure SQL Database offers scalability, cost-efficiency, and built-in high availability. However, the process requires careful planning to avoid downtime and compatibility issues. This guide covers: ✅ Pre-Migration Assessment ✅ Choosing the Right Azure SQL Option ✅ Step-by-Step Migration Methods ✅ Post-Migration Validation ✅ Common Pitfalls & Best Practices 1. Pre-Migration Assessment A. Check Compatibility Issues Azure SQL Database has some limitations compared to SQL Server. Verify compatibility using: 1. Microsoft Data Migration Assistant (DMA) Download & Install : Microsoft DMA Steps : Create a new assessment project. Select "Azure SQL Database" as the target. Analyze for: Unsupported features (e.g., SQL Agent Jobs , Cross-DB Queries ) Syntax differences (e.g., WITH (NOLOCK) → WITH (READUNCOMMITTED) ) 2. Azure SQL Migration Extension (VS Code) Useful for automated schema and T-SQL validat...
Read more

MS SQL Server Performance Optimization: Best Practices & Tips

Microsoft SQL Server is a powerful relational database management system, but as data grows and queries become more complex, performance can take a hit. Optimizing SQL Server performance is crucial for ensuring fast query execution, efficient resource utilization, and a seamless user experience. In this blog, we’ll explore essential MS SQL Server performance optimization techniques, covering indexing, query tuning, memory management, and more. 1. Optimize Indexing Strategy Indexes play a vital role in improving query performance by speeding up data retrieval. Here’s how to optimize them: Use the Right Index Types Clustered Index : Stores data in a sorted structure. Best for primary keys and frequently searched columns. Non-Clustered Index : Improves query performance on frequently filtered columns. Filtered Index : Ideal for queries that filter a subset of data. Avoid Over-Indexing Too many indexes slow down INSERT, UPDATE, DELETE operations. Regularly analyze index usag...
Read more

Common Causes of Slow Queries in SQL Server and How to Fix Them

Slow queries are one of the most common performance issues in SQL Server environments, often leading to frustrated users and degraded application performance. Identifying and resolving these bottlenecks is crucial for maintaining an efficient database system. In this article, we'll explore the most frequent causes of slow queries in SQL Server and provide actionable solutions to address them. 1. Missing or Inefficient Indexes Problem: One of the most common reasons for slow queries is the lack of proper indexes or the existence of inefficient indexes. Symptoms: High table scan operations (shown in execution plans) High logical reads in query statistics Slow performance on WHERE, JOIN, or ORDER BY clauses Solutions: -- Create appropriate indexes CREATE INDEX IX_Customer_LastName ON Customers(LastName) INCLUDE (FirstName, Email); -- For complex queries, consider filtered indexes CREATE INDEX IX_Orders_Recent ON Orders(OrderDate) WHERE OrderDate > DATEADD(year, -1...
Read more