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:

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.

Mark Varnas

Mark Varnas

I love making performance tuning SQL Servers fast and making them more stable. And I channel that obsession into our SQL Managed Services and new content here. When I'm not writing about SQL, I spend time outside hiking, skiing, mountain biking, or trying a new recipe.

Leave a Reply

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

Call Us Now

Or, Let Us Know How We Can Help

Tight On Time?

Schedule A Call: