SQL Server Performance Engineering

Context & Problem

In an enterprise core transaction and inventory system, customer service dashboards and automated verification routines began experiencing critical timeouts. Search and ledger reconciliation queries executed against multi-million row transactional tables were taking between 6 and 12 seconds to complete, routinely hitting command timeout thresholds (30 seconds) during peak hours.

The degradation caused ripple effects across the entire platform:

• High CPU spikes on the primary database cluster.
• Escalating deadlock rates on transactional update queries due to shared read locks held during prolonged table scans.
• Degraded end-user application responsiveness.

Investigation & Root Cause Analysis

Using SQL Server Extended Events, Dynamic Management Views (DMVs such as sys.dm_exec_query_stats and sys.dm_exec_query_plan), and SQL Server Profiler, I analyzed the graphical execution plans of the offending queries.

The investigation uncovered three primary architectural defects:

1. Non-SARGable Search Predicates:

• Queries filtered columns wrapped inside scalar functions (such as WHERE DATEADD(d, 0, DATEDIFF(d, 0, CreatedAt)) = @TargetDate and WHERE UPPER(TenantCode) = @Code).
• Applying functions directly to table columns forced the query optimizer to perform a full clustered index scan across 15+ million rows rather than leveraging existing index seeks.

2. Implicit Datatype Conversion (Type Mismatch):

• The application framework passed NVARCHAR parameters to VARCHAR columns, resulting in implicit CONVERT_IMPLICIT(nvarchar(50), [ColumnName]) operations in the execution plan, silently invalidating index usage.

3. Bookmark Lookups (Key Lookups) on High-Volume Selects:

• Existing non-clustered indexes satisfied the filter conditions, but because the queries requested 6 additional status and metadata columns, the query engine executed hundreds of thousands of Key Lookups back into the Clustered Index for each execution. At scale, the optimizer determined a full table scan was cheaper than random I/O lookups.

Implementation & Optimization Strategy

1. Refactoring Queries for SARGability:

• Rewrote date filtering predicates to use explicit half-open range bounds:

WHERE CreatedAt >= @StartOfDay AND CreatedAt < @EndOfDay.

• Ensured database collation rules handled case-insensitivity naturally, removing column-wrapping UPPER() calls and allowing direct index traversal.

2. Parameter Type Alignment:

• Audited the C# data access layer to ensure ADO.NET and EF Core parameter declarations strictly matched the underlying database column types (SqlDbType.VarChar with explicit string lengths), completely eliminating implicit type conversion warnings.

3. Composite Covering Indexes with INCLUDE Clauses:

• Rather than creating wide clustered indexes or proliferating redundant indexes, I designed focused composite indexes ordering keys by selectivity (TenantId, Status, CreatedAt), paired with INCLUDE (Amount, ReferenceNumber, UpdatedAt) to satisfy query projections entirely in the leaf level of the non-clustered index.
• This eliminated 100% of the Key Lookups, transforming millions of random I/O reads into a compact sequential b-tree seek.

Results & Verified Impact

• Latency Reduction: Query execution times plummeted from an average of 8.4 seconds down to 115 milliseconds under peak concurrent production load.
• I/O Reduction: Logical reads dropped from over 1,200,000 page reads per invocation down to approximately 3,800 reads, drastically freeing database buffer memory.
• System Stability: Concurrency deadlocks dropped to near zero because queries completed in milliseconds, relinquishing read locks almost instantaneously.