Bits of .NET

Application running slow but can’t figure out which queries are the culprits? Query Store is SQL Server’s built-in performance tracker that records everything.

Enable Query Store (SQL Server 2016+):

-- Enable for your database
ALTER DATABASE YourDatabaseName  
SET QUERY_STORE = ON (
    OPERATION_MODE = READ_WRITE,
    DATA_FLUSH_INTERVAL_SECONDS = 900,
    INTERVAL_LENGTH_MINUTES = 60,
    MAX_STORAGE_SIZE_MB = 1000,
    QUERY_CAPTURE_MODE = AUTO
);

-- Verify it's enabled
SELECT 
    name,
    is_query_store_on,
    query_store_retention_days
FROM sys.databases
WHERE name = 'YourDatabaseName';

Find Top 10 Slowest Queries:

SELECT TOP 10
    qsq.query_id,
    qsqt.query_sql_text,
    CAST(qsrs.avg_duration / 1000000.0 AS DECIMAL(10,2)) AS avg_duration_sec,
    qsrs.count_executions,
    CAST(qsrs.avg_cpu_time / 1000000.0 AS DECIMAL(10,2)) AS avg_cpu_sec,
    CAST(qsrs.avg_logical_io_reads AS BIGINT) AS avg_reads,
    qsrs.last_execution_time
FROM sys.query_store_query qsq
JOIN sys.query_store_query_text qsqt 
    ON qsq.query_text_id = qsqt.query_text_id
JOIN sys.query_store_plan qsp 
    ON qsq.query_id = qsp.query_id
JOIN sys.query_store_runtime_stats qsrs 
    ON qsp.plan_id = qsrs.plan_id
WHERE qsrs.last_execution_time >= DATEADD(hour, -24, GETUTCDATE())
ORDER BY qsrs.avg_duration DESC;

Real Output Example:

query_id | avg_duration_sec | count_executions | avg_cpu_sec | avg_reads
---------|------------------|------------------|-------------|----------
12845    | 45.23            | 1,200            | 38.5        | 1,200,000
  SQL: SELECT * FROM Orders o 
       JOIN OrderItems oi ON o.OrderId = oi.OrderId 
       WHERE o.CustomerId = @CustomerId  
  ↑ Missing index on CustomerId causing table scans

8392     | 12.67            | 5,800            | 10.2        | 450,000
  SQL: SELECT COUNT(*) FROM Products WHERE CategoryId IN (SELECT...)
  ↑ Subquery could be rewritten as JOIN

Find Queries with Degraded Performance:

-- Compare current vs historical performance
WITH QueryPerformance AS (
    SELECT 
        qsq.query_id,
        qsqt.query_sql_text,
        qsrs.runtime_stats_interval_id,
        CAST(qsrs.avg_duration / 1000000.0 AS DECIMAL(10,2)) AS avg_duration_sec,
        qsrsi.start_time,
        ROW_NUMBER() OVER (
            PARTITION BY qsq.query_id 
            ORDER BY qsrsi.start_time DESC
        ) AS rn
    FROM sys.query_store_query qsq
    JOIN sys.query_store_query_text qsqt ON qsq.query_text_id = qsqt.query_text_id
    JOIN sys.query_store_plan qsp ON qsq.query_id = qsp.query_id
    JOIN sys.query_store_runtime_stats qsrs ON qsp.plan_id = qsrs.plan_id
    JOIN sys.query_store_runtime_stats_interval qsrsi 
        ON qsrs.runtime_stats_interval_id = qsrsi.runtime_stats_interval_id
)
SELECT 
    current.query_id,
    current.query_sql_text,
    previous.avg_duration_sec AS previous_avg_sec,
    current.avg_duration_sec AS current_avg_sec,
    CAST(
        ((current.avg_duration_sec - previous.avg_duration_sec) / previous.avg_duration_sec) * 100 
        AS DECIMAL(10,2)
    ) AS degradation_percent
FROM QueryPerformance current
JOIN QueryPerformance previous 
    ON current.query_id = previous.query_id 
    AND previous.rn = 7  -- Compare to 7 intervals ago (7 hours if interval=1h)
WHERE current.rn = 1
    AND current.avg_duration_sec > previous.avg_duration_sec * 1.5  -- 50% slower
ORDER BY degradation_percent DESC;

Force a Specific Query Plan:

-- If a query had better performance with an older plan
EXEC sp_query_store_force_plan 
    @query_id = 12845, 
    @plan_id = 15234;  -- The plan_id from when it was fast

-- View forced plans
SELECT 
    qsq.query_id,
    qsp.plan_id,
    qsp.is_forced_plan,
    qsqt.query_sql_text
FROM sys.query_store_query qsq
JOIN sys.query_store_plan qsp ON qsq.query_id = qsp.query_id
JOIN sys.query_store_query_text qsqt ON qsq.query_text_id = qsqt.query_text_id
WHERE qsp.is_forced_plan = 1;

Monitor Query Store Health:

-- Check Query Store disk usage
SELECT 
    current_storage_size_mb,
    max_storage_size_mb,
    CAST(
        (current_storage_size_mb * 100.0 / max_storage_size_mb) 
        AS DECIMAL(5,2)
    ) AS storage_percent_used
FROM sys.database_query_store_options;

-- Clean up old data if needed
ALTER DATABASE YourDatabaseName 
SET QUERY_STORE CLEAR ALL;

Automatic Tuning (SQL Server 2017+):

-- Enable automatic plan correction
ALTER DATABASE YourDatabaseName
SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON);

-- SQL Server will automatically detect plan regressions
-- and revert to the last known good plan