I’m teaming up with Erik Darling to teach you SQL Server Performance Tuning in two days at the PASS Data Community Summit in Seattle.
Erik and I are co-teaching both days of training to give you a strong strategic background on the internals you need to know, along with critical tactical performance tuning techniques. Join us to level up your perf tuning skills!
I’m a huge fan of SQL Server’s Query Store feature. Query Store collects query execution plans and aggregate query performance metrics, including wait stats. Having Query Store enabled makes troubleshooting performance issues such as bad parameter sniffing, much, much easier. Because Query Store is integrated into SQL Server itself, it also can catch query plans in a lightweight way that an external monitoring system will often miss.
When performance matters, it’s important to ensure that you’re managing Query Store so that Query Store cleanup does not run during high volume times. Query Store cleanup could slow your workload down significantly.
Last November, a puzzle was really bothering me. Some queries from an application were timing out frequently after running for 30 seconds, but they were halfway invisible in the SQL Server.
It took me more than half hour to figure out how to start an XEvents trace on a read-scale out instance of Azure SQL Managed Instance. It’s hard to monitor read scale-out instances, so tracing is desirable! I started with a simple trace of sql_statement_completed. Hopefully this saves other folks some time.
If you use readable secondaries in Availability Groups or Read-Scale out instances in Azure SQL Managed Instance, you may have queries fail repeatedly if there is a glitch and statistics are not successfully ‘refreshed’ on the secondary replica. Those queries may keep failing until you manually intervene.
It’s unclear if Microsoft will ever fix this. There is a well established support deflection article which documents the issue and provides ‘workarounds’.
Whether or not you use Azure SQL Managed Instance, you will likely be asked for an opinion on it eventually if you’re a SQL Server person.
While the architecture is documented, it can be a bit of a long read and some of the gotchas are spread out over different pages – so I’m drawing up the architecture of each service tier along with notable implications for the design on performance and cost. Here’s the scoop on General Purpose.
One feature I’ve not appreciated enough in the past in SQL Server is Resource Governor.
Resource Governor allows you to fix problems with Memory Grants in a simple way, as Erik Darling recommends. It also lets you classify sessions into groups and limit the maximum number of simultaneous requests, and /or limit the degree of parallelism if you need more CPU for other workloads. While this will slow down the queries you classify into that group, this can be super useful, especially if you’re already using something like Snapshot isolation to prevent blocking or you are using a read-only replica.
In Azure SQL Managed Instance, you get to use Resource Governor, even in the General Purpose tier. This is awesome.
Just make sure you execute commands in the context of the master database, or you’ll get error 40510: Statement 'ALTER RESOURCE GOVERNOR' is not supported in this version of SQL Server.
It is supported! Just USE master; before running your commands.
Copyright (c) 2025, Catalyze SQL, LLC; all rights reserved. Opinions expressed on this site are solely those of Kendra Little of Catalyze SQL, LLC. Content policy: Short excerpts of blog posts (3 sentences) may be republished, but longer excerpts and artwork cannot be shared without explicit permission.