SQL Server Consultants in Chicago, IL

What the review found. The analytics query filtered a very large table with no supporting index, so every one of its ~7,481 daily runs scanned the table end to end, about 789 million reads each pass.

What we changed (identifiers generalized for privacy):

-- Analytics query scanned a huge table: ~789M reads, 6.63s per run, ~7,481x/day.
CREATE NONCLUSTERED INDEX IX_analytics_covering
    ON dbo.[analytics_fact] (/* filter cols */) INCLUDE (/* output cols */);
-- rollback: DROP INDEX script provided

The covering index turned the scan into a seek: reads fell from about 789 million to roughly 22,000, and the run dropped from 6.63 seconds to under a millisecond.

What the review found. The December cutover left configuration drift across the migrated instances and a cluster of recurring errors that tripped environment-wide alerts, which buried any genuine failover warning and left the primary running below the headroom it had.

What we changed (identifiers generalized for privacy):

-- Post-migration estate: config drift + recurring errors buried real failover health.
-- Checked AG replica sync + failover readiness, then re-baselined the alert rules.
SELECT ar.replica_server_name, ars.role_desc,
       ars.synchronization_health_desc, ars.operational_state_desc
FROM sys.dm_hadr_availability_replica_states AS ars
JOIN sys.availability_replicas AS ar ON ars.replica_id = ar.replica_id;
-- alert thresholds reset to real conditions; prior config captured for rollback

With the replicas confirmed healthy and the alert rules reset to fire on real conditions, SQL errors fell about 95% and environment-wide alerts about 92% month over month, while the primary took on 26.8% more work.

What we uncovered. Concurrent sessions were contending on the same hot query, and a weak plan made each one hold locks longer than it needed to, which cascaded into deadlocks under load about 2,206 times a day.

What we changed (identifiers generalized for privacy):

-- Reworked the hot query + indexing so sessions seek instead of scanning,
-- shortening how long each holds locks and breaking the deadlock cycle.
CREATE NONCLUSTERED INDEX IX_hotpath_covering
    ON dbo.[order_lines] (/* filter cols */) INCLUDE (/* output cols */);
-- query rewritten to trim lock duration; rollback scripts provided

With locks held only briefly and the plan seeking instead of scanning, deadlocks dropped 92% month over month and the query handed back about 20 minutes of SQL time a day.