Duration improvement after tuning

41x

QUERY #1

QUERY #2

QUERY #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

	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%)

*The numbers are an average of multiple runs of the query: SELECT oeh.user_def_field…

Query #2

	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%)

*The numbers are an average of multiple runs of the query: UPDATE bins set bin_flag=1…

Query #3

	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%)

*The numbers are an average of multiple runs of the query: [redacted]_DealerPortal_UpdateCustomerLevel

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.

Discover what clients are saying about Red9

Red9 has incredible expertise both in SQL migration and performance tuning.

The biggest benefit has been performance gains and tuning associated with migrating to AWS and a newer version of SQL Server with Always On clustering. Red9 was integral to this process. The deep knowledge of MSSQL and combined experience of Red9 have been a huge asset during a difficult migration. Red9 found inefficient indexes and performance bottlenecks that improved latency by over 400%.

Rich Staats
Cloud Engineer
MetalToad

Always willing to go an extra mile

Working with Red9 DBAs has been a pleasure. They are great team players and have an expert knowledge of SQL Server database administration. And are always willing to go the extra mile to get the project done.

Evelyn A.
Sr. Database Administrator

Boosts server health and efficiency for enhanced customer satisfaction

Since adding Red9 to the reporting and DataWarehousing team, Red9 has done a good job coming up to speed on our environments and helping ensure we continue to meet our customer's needs. Red9 has taken ownership of our servers ensuring they remain healthy by monitoring and tuning inefficient queries.

Andrew F.
Datawarehousing Manager

See more testimonials

How We Boosted SQL Server Performance to Run 51 Times Faster