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

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

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

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

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

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

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

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!

Category: Reliability Item: Missing alerts Why should you enable SQL Server alerts? Enabling these Alerts can prevent a lot of problems: Potential to miss critical issues. Missing out on catching problems proactively. Increased chance of downtime. Which alerts should you include? It’s a best practice for DBAs to enable SQL Server event alerts for Severity

Category: Reliability Item: Backups compression settings What is The backup compression default option? It determines whether the server instance creates compressed backups by default. Backup compression is not available in all editions of SQL Server. Microsoft introduced in SQL Server 2008, and Standard Edition starting in SQL Server 2008R2. Why should you enable it? Backups could take less space and

Description: Problem: Deadlocking. This stored procedure is continuously involved in deadlocking. Solution: A new index. Scan operation can be tuned into becoming a seek (a lot more efficient). The total improvement looks like this: Technical Background: Most SQL Servers bottleneck on Disk access (or disk “reads”). It’s not CPU or RAM – which most customers

Description: Problem: This stored procedure runs very frequently (multiple times per minute) and consumes a lot of server resources (CPU, storage reads and duration). Change: Added indexes. BEFORE tuning: AFTER tuning: Here is what total improvement looks like: Technical Background: Most SQL Servers bottleneck on Disk access (or disk “reads”). It’s not CPU or RAM