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]
