Performance Tuning — Statistics, Hints, and Query Plans

Performance diagnosis order:
  1. Identify the slow query (DMVs, Query Store, application logs)
  2. Check the execution plan (missing indexes, table scans, key lookups)
  3. Check wait statistics (what is SQL Server waiting on?)
  4. Fix the highest-impact issue first
  5. Measure again — validate the improvement
  6. Repeat

Common mistake: adding indexes without checking execution plans first.
Common mistake: changing application code when the database is the bottleneck.

Three root causes of SQL Server performance problems:
  → I/O: too many rows read (missing indexes, table scans)
  → CPU: complex operations (sorts, hash joins, excessive parallelism)
  → Blocking: transactions holding locks too long (see transactions article)

Measure before and after:
  Every performance fix needs a baseline — how slow, and what metric?
  "Queries over 500ms" is measurable. "It felt slow" is not.

-- Current executing queries — what's running right now:
SELECT
    r.session_id,
    r.status,
    r.blocking_session_id,
    r.wait_type,
    r.wait_time                / 1000.0 AS wait_seconds,
    r.total_elapsed_time       / 1000.0 AS elapsed_seconds,
    r.cpu_time                 / 1000.0 AS cpu_seconds,
    r.logical_reads,
    SUBSTRING(t.text, (r.statement_start_offset / 2) + 1,
        ((CASE WHEN r.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), t.text)) * 2
               ELSE r.statement_end_offset END
          - r.statement_start_offset) / 2) + 1) AS current_statement
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.session_id > 50  -- exclude system sessions
ORDER BY r.total_elapsed_time DESC;

-- Top queries by total logical reads (I/O intensive):
SELECT TOP 20
    qs.total_logical_reads         / qs.execution_count AS avg_reads,
    qs.total_elapsed_time          / qs.execution_count / 1000.0 AS avg_ms,
    qs.execution_count,
    SUBSTRING(st.text, (qs.statement_start_offset / 2) + 1,
        ((CASE WHEN qs.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), st.text)) * 2
               ELSE qs.statement_end_offset END
          - qs.statement_start_offset) / 2) + 1) AS query_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY avg_reads DESC;

-- Wait statistics: what is SQL Server waiting on?
-- The top waits reveal the class of performance problem

SELECT TOP 15
    wait_type,
    waiting_tasks_count,
    wait_time_ms / 1000.0                     AS total_wait_seconds,
    max_wait_time_ms / 1000.0                 AS max_wait_seconds,
    (wait_time_ms - signal_wait_time_ms) / 1000.0 AS resource_wait_seconds
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN (
    -- Filter out benign background waits:
    'SLEEP_TASK', 'WAITFOR', 'LAZYWRITER_SLEEP', 'SLEEP_DBSTARTUP',
    'SLEEP_DBTASK', 'CHECKPOINT_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH',
    'XE_TIMER_EVENT', 'BROKER_TO_FLUSH', 'XE_DISPATCHER_WAIT',
    'FT_IFTS_SCHEDULER_IDLE_WAIT', 'SQLTRACE_BUFFER_FLUSH',
    'CLR_AUTO_EVENT', 'DISPATCHER_QUEUE_SEMAPHORE', 'DBMIRROR_EVENTS_QUEUE',
    'HADR_WORK_QUEUE', 'SNI_HTTP_ACCEPT')
ORDER BY wait_time_ms DESC;

-- Key wait types and what they mean:
-- PAGEIOLATCH_SH / PAGEIOLATCH_EX → disk I/O for data pages — missing indexes, cold cache
-- LCK_M_* (LCK_M_S, LCK_M_X...) → blocking — long transactions holding locks
-- CXPACKET / CXCONSUMER            → parallelism coordination — may need MAXDOP tuning
-- WRITELOG                         → log write latency — I/O on log drive
-- RESOURCE_SEMAPHORE               → memory pressure — queries waiting for grant
-- SOS_SCHEDULER_YIELD              → CPU pressure — too many queries competing
-- TEMPDB_*                         → TempDB contention — sort/hash spills or temp tables

// Clinical lists (ward round, prescription queue) are paginated
// Bad pagination: OFFSET/FETCH on large tables is slow at high page numbers

// EF Core pagination — BAD for large offsets:
var page3 = await _context.Prescriptions
    .OrderBy(p => p.CreatedAt)
    .Skip(200)          // OFFSET 200 — reads and discards first 200 rows
    .Take(20)
    .ToListAsync(ct);

// EF Core keyset pagination — GOOD: seek from a bookmark instead of skipping rows
// After page 1, store the last item's (CreatedAt, Id) as the cursor
public async Task<PagedResult<PrescriptionSummaryDto>> GetPageAsync(
    DateTime? afterCreatedAt,
    Guid?     afterId,
    string    status,
    int       pageSize = 20,
    CancellationToken ct = default)
{
    var query = _context.Prescriptions
        .Where(p => p.Status == status)
        .AsQueryable();

    // Apply cursor (skip rows before the cursor — no OFFSET needed):
    if (afterCreatedAt.HasValue && afterId.HasValue)
    {
        query = query.Where(p =>
            p.CreatedAt > afterCreatedAt.Value ||
            (p.CreatedAt == afterCreatedAt.Value && p.Id > afterId.Value));
    }

    var items = await query
        .OrderBy(p => p.CreatedAt)
        .ThenBy(p => p.Id)     // tie-break for consistent ordering
        .Take(pageSize + 1)    // fetch one extra to detect if there's a next page
        .Select(p => new PrescriptionSummaryDto(
            p.Id, p.MedicationName, p.PatientMrn, p.Status, p.CreatedAt))
        .ToListAsync(ct);

    var hasMore = items.Count > pageSize;
    if (hasMore) items.RemoveAt(items.Count - 1);

    var cursor = items.LastOrDefault() is { } last
        ? new PageCursor(last.CreatedAt, last.Id)
        : null;

    return new PagedResult<PrescriptionSummaryDto>(items, cursor, hasMore);
}

public sealed record PageCursor(DateTime CreatedAt, Guid Id);
public sealed record PagedResult<T>(
    IReadOnlyList<T> Items,
    PageCursor?      NextCursor,
    bool             HasMore);

-- Memory pressure: queries spilling to disk because they can't get enough memory
-- Check for memory spills in execution plans: Sort → right-click → Properties → Memory Spill Level
-- Check for recent spills:
SELECT
    execution_count,
    total_spills / execution_count AS avg_spills_per_execution,
    total_grant_mb,
    query_sql_text
FROM sys.query_store_query_text qt
JOIN sys.query_store_query       qq  ON qq.query_text_id = qt.query_text_id
JOIN sys.query_store_plan        qp  ON qp.query_id      = qq.query_id
JOIN sys.query_store_runtime_stats qs ON qs.plan_id = qp.plan_id
WHERE qs.total_spills > 0
ORDER BY qs.total_spills DESC;

-- Common fix: add indexes to avoid sorts (the sort spills, not the data itself)
-- Or: set an explicit MAXDOP hint to limit parallelism for spilling queries

-- TempDB contention — check allocation contention:
SELECT
    wait_type,
    waiting_tasks_count,
    wait_time_ms / 1000.0 AS total_wait_seconds
FROM sys.dm_os_wait_stats
WHERE wait_type IN ('PAGELATCH_UP', 'PAGELATCH_EX', 'PAGELATCH_SH')
ORDER BY wait_time_ms DESC;

-- If PAGELATCH_* contention is high and SQL Server version supports it:
-- ALTER DATABASE TempDB ADD FILE ... -- add more TempDB files (one per CPU core, max 8)
-- TRACE FLAG 1118: enable uniform extent allocations (reduces TempDB allocation contention)

// Application-level query performance tracking with EF Core interceptors

public sealed class SlowQueryInterceptor(ILogger logger, TimeSpan threshold)
    : DbCommandInterceptor
{
    public override async ValueTask<DbDataReader> ReaderExecutedAsync(
        DbCommand                         command,
        CommandExecutedEventData          eventData,
        DbDataReader                      result,
        CancellationToken                 ct)
    {
        if (eventData.Duration > threshold)
        {
            logger.LogWarning(
                "Slow query detected: {Duration}ms | SQL: {Sql}",
                eventData.Duration.TotalMilliseconds,
                command.CommandText[..Math.Min(500, command.CommandText.Length)]);
        }
        return result;
    }
}

// Register:
builder.Services.AddDbContext<ClinicalDbContext>(options =>
    options.UseSqlServer(connectionString)
           .AddInterceptors(new SlowQueryInterceptor(
               logger,
               threshold: TimeSpan.FromMilliseconds(500))));

// Application Insights custom metric for slow queries:
public sealed class MetricsQueryInterceptor(TelemetryClient telemetry)
    : DbCommandInterceptor
{
    public override ValueTask<DbDataReader> ReaderExecutedAsync(
        DbCommand command, CommandExecutedEventData eventData, DbDataReader result, CancellationToken ct)
    {
        telemetry.TrackMetric("Database.QueryDuration",
            eventData.Duration.TotalMilliseconds,
            new Dictionary<string, string>
            {
                ["table"] = ExtractTableName(command.CommandText)
            });

        return new ValueTask<DbDataReader>(result);
    }

    private static string ExtractTableName(string sql)
    {
        var match = Regex.Match(sql, @"FROM\s+\[?(\w+)\]?", RegexOptions.IgnoreCase);
        return match.Success ? match.Groups[1].Value : "unknown";
    }
}