What is the Query Store? The SQL Server Query Store is a feature introduced in SQL Server 2016. It provides you with insight on query plan choice and performance against a specific database. Why should I enable it? This information helps in identifying performance issues even after SQL Server restart or upgrade. All data that SQL Server … Read more

Description: Problem: Stored procedure takes 38 seconds to execute. It times out on the application front-end. It was performing multiple scans and reading too much data. Solution:  The Stored procedure re-written. Also, added a second select with a UNION ALL, this was necessary to divide the WHERE clause selectivity. Other notes: Result set for both … Read more

Description: Problem: This stored procedure showed up in the top resource-consuming report. It was putting too much stress on the CPU. Solution: Added two new indexes. Why does disk improvement matter for stored procedure speed? It’s simple. The less you access the disk; the more disk capacity is left over. It works just like a … Read more

Description: Problem: Select command inside trigger (EHRHist<removed>_AFTER_INSERT_UPDATE) doing an index scan. Solution: Added an index. Command: Change: Before: After: Technical Background: Most SQL Servers bottleneck is on Disk access (or disk “reads”). It’s not CPU or RAM – which most customers often suspect first. And that makes much sense. Here is why. Inefficient queries scan … Read more

Description: Problem: Slowness in the biling process. Solution: SQL code refactoring.   Time to run stored proc before tuning: 34s Time to run stored proc after tuning: 4s Technical Background: Most SQL Servers bottleneck is on Disk access (or disk “reads”). It’s not CPU or RAM – which most customers often suspect first. And that … Read more

Description: Problem: Slow procedure killing SQL Server performance Change: To improve the stored procedure performance, we created four views. Also, we made changes to the stored proc code, removing few tables from EXISTS operator that were part of the LEFT JOIN. Technical Background: Most SQL Servers bottleneck is on Disk access (or disk “reads”). It’s … Read more

Description: Problem: Slow stored procedure Notes: After making couple of small tweaks to the SQL Server stored procedure we were able to get few improvements: Run time duration improved by: 10,788% CPU improved by: 10,903% Disk improved by: 46,180% Overall stored procedure improvement: 67,871% What exactly was changed? -Split a subquery in four parts using … Read more

Problem: Slow stored procedure 75,336% improvement in stored procedure performance! Notes: Improvements achieved: • CPU from 18,922 to 1,249 • Reads from 12,850,589 to 18,532 • Duration from 30,682 to 685 What was changed? A SELECT with four JOINS became two SELECTs using a UNION. It’s not 1 thousand times faster. But it is close! … Read more

FREE Scripts to Instantly
Improve SQL Server Performance

3 FREE Scripts to Instantly
Improve SQL Server Performance

Learn how to increase Performance of SQL Server INSTANTLY with 3 free scripts.