SQL Server Consultants in Atlanta, GA

What we found. A 16-core Always On pair under load, with parallelism left at defaults, memory and tempdb misconfigured, no consistent maintenance, and several queries doing far more work than they needed. The CPU pressure looked like a hardware ceiling, but it was configuration and query cost.

What we changed (illustrative):

-- Round 1: instance configuration and maintenance
EXEC sys.sp_configure 'cost threshold for parallelism', 50;   -- was 5
EXEC sys.sp_configure 'max degree of parallelism', 8;         -- was 0
EXEC sys.sp_configure 'optimize for ad hoc workloads', 1;
RECONFIGURE;
-- right-size min/max memory, add tempdb data files to match the
-- scheduler count, and add CHECKDB + index/statistics maintenance jobs

-- Round 2: modernize the optimizer, then tune the top queries by CPU
ALTER DATABASE erp_prod SET COMPATIBILITY_LEVEL = 160;        -- SQL Server 2022
-- query store on; parameter-sensitive plans (PSP) and intelligent
-- query processing (IQP) now active across the hot workload

With the workload tuned, the production load fit comfortably on 4 cores per server, so we right-sized both VMs from 16 cores to 4 and dropped from SQL Server Enterprise licensing on 32 cores to 8.

What we found. The firm's busiest query filtered a high-frequency timekeeping change-log table on a status flag and a date, but no index supported that pattern. Every run did a full clustered-index scan, about 13.2 million logical reads, roughly 200 times a day. Three more queries on the workflow and print-job tables had the same gaps: "unique" non-clustered indexes acting as slow secondary lookups, and missing INCLUDE columns forcing key lookups.

What we changed (object names generalized for privacy):

-- 1. Cover the hot change-log query (filters on status + date)
CREATE NONCLUSTERED INDEX IX_TimecardChangeLog_status_date
    ON dbo.TimecardChangeLog (changeStatus, changeDate);

-- 2. Convert the workflow process-item "unique" non-clustered
--    indexes into clustered primary keys (stop the heap lookups)
ALTER TABLE dbo.WorkflowProcessItem
    ADD CONSTRAINT PK_WorkflowProcessItem PRIMARY KEY CLUSTERED (processItemId);

-- 3. Cover the workflow-step query so it stops doing key lookups
CREATE NONCLUSTERED INDEX IX_WorkflowItemStep_state
    ON dbo.WorkflowItemStep (stepState) INCLUDE (itemId, updatedDate);

-- 4. Covering index for the print-job notification query
CREATE NONCLUSTERED INDEX IX_PrintJob_notif_result
    ON dbo.PrintJob (hasNotification, resultCode) INCLUDE (userId);

The effect. The flagship query turned a 13,262,425-read scan into a 58-read seek: 60,315 ms down to 1 ms. Across roughly 200 executions a day, that returned about 201 minutes of server time. The three related queries on the same workflow fell just as sharply.

What we found. Three queries against the Always On primary ran tens of thousands of times a day and were doing enormous scans: one read 5.5 billion pages over the window, another 1.9 billion. The missing indexes were also driving lock contention; deadlocks on the primary had climbed to 131.

What we changed. Three covering non-clustered indexes on the primary replica, one per hot query, each sized to turn a scan into a seek, keyed on the query's filter columns and INCLUDE-ing the columns it returns:

-- One covering index per hot query: key on the filter columns,
-- INCLUDE the output columns, so each query seeks instead of scanning.
CREATE NONCLUSTERED INDEX IX_q1_covering ON dbo.[hot_table_1] (/* filter cols */) INCLUDE (/* output cols */);
CREATE NONCLUSTERED INDEX IX_q2_covering ON dbo.[hot_table_2] (/* filter cols */) INCLUDE (/* output cols */);
CREATE NONCLUSTERED INDEX IX_q3_covering ON dbo.[hot_table_3] (/* filter cols */) INCLUDE (/* output cols */);

The effect, per query:

Query 1   reads     5,500,000,000  ->  56,000,000  (~98x)
Query 2   duration      282.71 ms  ->  0.29 ms     (~975x)
Query 3   duration         3.73 s  ->  11.04 ms    (~338x)

Combined, the three returned about 11 hours of daily SQL processing on the same hardware, and the lock pressure behind the deadlocks eased as the scans disappeared.