Context
100 customer advisors use widgets that query an SQL view. This view performs many calculations and consumes a lot of CPU. The database CPU quickly reaches 100%.
Challenge
The CPU load on the database is such that it risks blocking other applications. At the end of the day, the query almost no longer responds.
Technologies used
DBMS: MS SQL Server 2016 EE
Methodology and implementation
Radical optimization using SQL Server 2016 In-Memory OLTP tables:
**Diagnosis** - Complex query with numerous calculations - View queried 100+ times per second during peak hours - Parallel execution plans saturating all CPUs - Impact on other database applications
**In-Memory OLTP Solution** 1. Creation of an In-Memory table for cache 2. Replacement of view with natively compiled stored procedure 3. Refresh job every 5 minutes 4. Exploitation of native compiler to optimize T-SQL code
**Technical advantages** - Data stored in memory (no disk I/O) - Natively compiled code (no T-SQL interpretation) - No locks/latches on In-Memory tables - Ultra-fast single-thread execution
The performance gain is spectacular: from 10 seconds to less than 1 millisecond!
Results
Once the natively compiled stored procedure is used instead of the SQL view query, the database CPU drops to 20%. The procedure returns data in less than 1 ms instead of 10s. It's 10,000 times faster!
Visualizations
Nighttime CPU load analysis
Cache refresh job schedule
Cache refresh step details
Cache impact on CPU usage