Performance Tuning Report #8 – 9320x faster!

Total improvement: 932,058%

Not bad, huh?

These type of database speed improvements makes me smile! 🙂

 

Here is another SQL Server performance tuning report.

Before vs. After.

“Before” – shows numbers before we made any changes.

“After” – shows performance numbers after we made some changes in the database to improve slow sql calls.

 

This report shows a single query that runs a lot faster now.

And here is what SQL Profiler traces looked like Before making sql performance tuning changes vs. after:

02-sql-performance-number-before-tuning-changes-made

 

03-sql-performance-number-after-tuning-changes-made

 

Problem:

SELECT inside trigger (EHRHist<removed>Exam_AFTER_INSERT_UPDATE) is doing an index scan operation.

 

How did we do this?

Command: select top 1

@RecordExists = count(1)

from EHRHis<removed>Exam

where documentName = @DocumentName

and plansOfCare = @PlansOfCare

and encounterID != @EncounterId;

 

Change:  Added an index.

CREATE NONCLUSTERED INDEX IX__documentName__encounterID__INC

ON [dbo].[EHRHist<removed>] ([documentName],[encounterID])

INCLUDE ([plansOfCare])

WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = ON,

ignore_dup_key = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON,

ALLOW_PAGE_LOCKS  = OFF, FILLFACTOR = 100, data_compression = PAGE)

ON [INDEXES]

 

Agree? Disagree? Comment below.

Share this post

Share on facebook
Share on google
Share on twitter
Share on linkedin
Share on pinterest
Share on print
Share on email

Leave a Comment

Your email address will not be published. Required fields are marked *

4.8/5

42

TESTIMONIALS

FREE Scripts to Instantly
Improve SQL Server Performance

3 FREE Scripts to Instantly
Improve SQL Server Performance

Learn how to increase Performance of SQL Server INSTANTLY with 3 free scripts.