How We Boosted SQL Server Performance to Run 51 Times Faster

Duration improvement after tuning

41xQUERY #1

2xQUERY #2

8xQUERY #3

Tuning SQL Server to cut down on CPU time and disk read operations makes everything run smoother and faster, boosting both performance and reliability.

Problem

Three slow stored procedures significantly affected the SQL Server's performance.

Solution

Here is what we did to make these queries run much faster:

Query #1.SELECT that runs every 30 minutes:
Created index [IX_extra_6__INC] on [oehdraud_sql]

Query #2.UPDATE bins set bin_flag=1:
Created index IX__bin_flag__INC on [<removed>_ldbld_bins]

Query #3. Tuning procedure <removed>_DealerPortal_UpdateCustomerLevel:
Replace two temporary tables with Common Table Expressions (CTE)

Before vs. After

Query #1

[simple_chart type="bar" name="Query 1" labels="Duration (ms), CPU (ms)" data="164,83" label="Before" color="#e32a24" border="#000fff" data_1="4, 15" label_1="After" color_1="#2b2d42" options="plugins: {title: {display: true,text: 'Query #1, improvement before/after tuning', position:'top'}}"]

	Duration (ms)		CPU (ms)		Disk (8k page reads)
	Before	After	Before	After	Before	After
Run 1	203	4	93	16	943	601
Run 2	125	4	73	15	592	601
AVG	164	4	83	15	767	601

	Before	After	Improvement
Duration (ms)*	164	4	41x (or 4,100%)
CPU (ms)*	83	15	5,53x (or 553%)
Disk (number of reads)*	767	601	1,28x (or 128%)

Query #2

[simple_chart type="bar" name="Query 2" labels="Duration (ms), CPU (ms)" data="43,39" label="Before" color="#e32a24" border="#000fff" data_1="21, 15" label_1="After" color_1="#2b2d42" options="plugins: {title: {display: true,text: 'Query #2, improvement before/after tuning', position:'top'}}"]

	Duration (ms)		CPU (ms)		Disk (8k page reads)
	Before	After	Before	After	Before	After
Run 1	44	21	47	16	979	985
Run 2	49	27	47	15	1,003	985
Run 3	41	21	31	16	991	985
Run 4	39	17	31	16	1,012	985
AVG	43	21	39	15	996	985

	Before	After	Improvement
Duration*	43	21	2,05x (or 205%)
CPU*	39	15	2,6x (or 260%)
Disk*	996	985	1,01x (or 101%)

Query #3

[simple_chart type="bar" name="Query 3" labels="Duration (ms), CPU (ms)" data="1075,67" label="Before" color="#e32a24" border="#000fff" data_1="134, 10" label_1="After" color_1="#2b2d42" options="plugins: {title: {display: true,text: 'Query #3, improvement before/after tuning', position:'top'}}"]

	Duration (ms)		CPU (ms)		Disk (8k page reads)
	Before	After	Before	After	Before	After
Run 1	584	149	47	16	325	76
Run 2	1,290	180	31	16	325	130
Run 3	1,352	73	125	-	320	129
AVG	1,075	134	67	16	323	111

	Before	After	Improvement
Duration*	1,075	134	~8x (or 802%)
CPU*	67	16	~7x (or 670%)
Disk*	323	111	~3x (or 291%)

In a nutshell, by tweaking these three queries, we slashed the total execution time (Duration) by a whopping 51 times!

Final thoughts

When you tune SQL Server to use less CPU time, you're basically giving your server a break, letting it do more work with less effort.

This means your queries fly faster, and you can get more done at once without the server breaking a sweat.

Plus, it's a win for your budget too, because your hardware lasts longer and runs smoother.