There Are Days When I Feel Like Giving Up on the Plan Cache and Query Store.

In theory, SQL Server performance monitoring is pretty simple:

1. Review the server’s top wait types
2. Find the queries causing those wait types
3. Fix those queries, or improve the way the server reacts to them (indexes, settings, etc.)

But in practice, step 2 is awful because:

• Apps send unparameterized strings to the database server
• Entity Framework users build queries with FromSqlRaw or string.Format()
• Entity Framework users write queries with .Contains, which builds an unparameterized IN list, even when they’re only looking for a single value (which got better in EF9)
• People write sloppy dynamic SQL that just concatenates values directly into the query string
• SaaS developers put each client in their own database, and plans aren’t reused across databases

So as a result, the plan cache and Query Store are damn near useless because every query that comes in is seen as a “new” query. I wrote about this back in 2018, and since then, the problem seems like it’s gotten continuously worse. I’ve been tracking client servers over the last couple of years, and these days, 1 in 3 servers I face has these issues. My lens might be distorted since maybe people who aren’t having this problem are all solving their performance issues with conventional tools like sp_BlitzCache and Query Store, but even if that’s true, there’s a problem bigger than human query troubleshooting.

Modern versions of SQL Server are increasingly reliant on properly parameterized queries. Tools like Automatic Plan Regression (aka Automatic Tuning), Adaptive Memory Grants, Adaptive Joins, CE Feedback, DOP Feedback, Parameter-Sensitive Plan Optimization, and Optional Parameter Plan Optimization all rely on proper parameterization so that they can adapt to the same query over time. If every query comes in wearing a disguise, these features just don’t work.

There’s a database-level switch that’s supposed to help: Forced Parameterization. Turn it on, and SQL Server and Azure SQL DB examine every incoming query, and if it isn’t fully parameterized, the literals are stripped out and replaced with variables. The problem is that it doesn’t work in a lot of situations:

• Partially parameterized queries – if a query has any parameters, Microsoft assumes the whole thing is parameterized, which is especially problematic for EF’s .Contains
• Literals in the SELECT list, like SELECT 1 AS ClientId, …  – which always blows me away when I see them, but strangely it’s a commonly used technique for reasons I will never understand
• Literals in HAVING, GROUP BY, and ORDER BY
• And more, as explained in this SQL Server 2008 R2 documentation page that has never been updated (Update May 9: the documentation has been updated!)

Even when Forced Parameterization does work, turning it on suddenly causes parameter sniffing emergencies. Queries that used to get their own hand-crafted plans suddenly get reusable plans, and while that’s great for performance monitoring, it’s not so great for end user performance in some scenarios. Your application might have 1,000 queries, and 990 of them might be just fine with reusable queries – but those 10 others represent 10 different parameter sniffing emergency situations that are going to strike out of nowhere, and keep striking if you don’t fix the queries for good.

I don’t have any answers.

It just feels like there are two kinds of shops:

• The ones who properly parameterize everything, and can leverage the plan cache and Query Store, but suffer from parameter sniffing emergencies. So they have good monitoring, but they need it, because they face performance emergencies from time to time.
• The ones who don’t parameterize at least some of their code, so the plan cache & Query Store are largely useless at best, misleading at worst – but they don’t have parameter sniffing emergencies. So they have bad monitoring, but … they don’t care as much.

Some days, I look at that latter group and say, I get it. Not all the time! Most of the time, I want the cool features built into modern versions of SQL Server, Azure SQL DB, and Intelligent Query Processing. But some days… some days, I want to embed literals into all my queries.