Before vs. After Perf Tuning Report #8 – 913,262% speed improvement

Before vs. After Perf Tuning Report #8 – 913,262% speed improvement

Total improvement: 913,262%

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.

sql-server-performance-tuning-report-8

 

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