SQL Server Support, Consulting & Remote DBA

T-SQL · Query Store: queries with divergent plans, then force the good one

-- Query Store: queries with more than one plan where the slowest plan averages 3x the fastest (7 days)
WITH plan_stats AS (
    SELECT p.query_id,
           p.plan_id,
           p.is_forced_plan,
           SUM(rs.count_executions)                                   AS execs,
           SUM(rs.avg_duration * rs.count_executions)
             / NULLIF(SUM(rs.count_executions), 0) / 1000.0           AS avg_ms   -- avg_duration is in µs
    FROM sys.query_store_plan                   AS p
    JOIN sys.query_store_runtime_stats          AS rs ON rs.plan_id = p.plan_id
    JOIN sys.query_store_runtime_stats_interval AS i  ON i.runtime_stats_interval_id = rs.runtime_stats_interval_id
    WHERE i.start_time >= DATEADD(DAY, -7, SYSUTCDATETIME())
    GROUP BY p.query_id, p.plan_id, p.is_forced_plan
)
SELECT query_id,
       COUNT(*)                                            AS plans,
       CAST(MIN(avg_ms) AS DECIMAL(12,2))                  AS best_plan_ms,
       CAST(MAX(avg_ms) AS DECIMAL(12,2))                  AS worst_plan_ms,
       SUM(execs)                                          AS execs,
       MAX(CASE WHEN is_forced_plan = 1 THEN plan_id END)  AS forced_plan_id
FROM plan_stats
GROUP BY query_id
HAVING COUNT(*) > 1
   AND MAX(avg_ms) > 3 * MIN(avg_ms)
ORDER BY (MAX(avg_ms) - MIN(avg_ms)) * SUM(execs) DESC;

-- Force the known-good plan; reversible with sys.sp_query_store_unforce_plan
EXEC sys.sp_query_store_force_plan @query_id = 1842, @plan_id = 41;   -- illustrative IDs

SQL Server Support sizes Query Store storage and capture mode per database, so it stays in read-write mode during the incidents where it matters most.

T-SQL · sqlcmd: verify, confirm, fail over, validate

-- Planned manual failover of an availability group (CLUSTER_TYPE = WSFC); run on the synchronous secondary
-- Run with: sqlcmd -v AgName="ag_prod" ConfirmAg="ag_prod" -i planned_failover.sql
-- Pacemaker (CLUSTER_TYPE = EXTERNAL) fails over through the cluster manager instead.

-- 1. Verify before: every database SYNCHRONIZED and HEALTHY, both queues near zero
SELECT ag.name                        AS ag_name,
       ar.replica_server_name,
       DB_NAME(drs.database_id)       AS database_name,
       drs.synchronization_state_desc,
       drs.synchronization_health_desc,
       drs.log_send_queue_size        AS log_send_queue_kb,
       drs.redo_queue_size            AS redo_queue_kb
FROM sys.dm_hadr_database_replica_states AS drs
JOIN sys.availability_replicas           AS ar ON ar.replica_id = drs.replica_id
JOIN sys.availability_groups             AS ag ON ag.group_id  = drs.group_id
WHERE ag.name = N'$(AgName)';

-- 2. Confirmation gate
IF N'$(ConfirmAg)' <> N'$(AgName)'
BEGIN
    RAISERROR (N'Confirmation mismatch. No failover.', 16, 1);
    SET NOEXEC ON;
END;

-- 3. Planned failover without data loss
ALTER AVAILABILITY GROUP [$(AgName)] FAILOVER;

-- 4. Validate after: this replica reports PRIMARY, the former primary rejoins as SECONDARY
SELECT ar.replica_server_name, ars.role_desc, ars.synchronization_health_desc
FROM sys.dm_hadr_availability_replica_states AS ars
JOIN sys.availability_replicas               AS ar ON ar.replica_id = ars.replica_id;

SET NOEXEC OFF;

Every SQL Server support HA design ships with a failover runbook: verification before, a confirmation gate, the change, and validation after, as in the script alongside.