Today I am sharing another MS SQL Performance tuning report from recent tuning.
Improvements after Red9 SQL Server tuning
After making a couple of small tweaks to the SQL Server stored procedure we were able to get few improvements
- Run time duration improved by: 638%
- CPU improved by: 378%
- Disk improved by: 312%
Overall stored procedure improvement: 13x faster!
The green block above shows the SQL Server Stored Procedure performance AFTER tuning.
What changes made this query run faster?
- Removed SQL function from where clause (subquery);
- Removed unused columns from a temporary tables;
- Replaced two temporary tables with CTE;
Why should you avoid SQL Server functions in the WHERE clause for Performance?
When functions are used in the WHERE clause this forces SQL Server to do a table scan or index scan instead of doing an index seek if there is an index that can be used.
