Performance Tuning Report – 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:

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.