I need to search for people by their DisplayName, so I’ve created an index on that:
CREATE INDEX IX_DisplayName ON dbo.Users(DisplayName);
And now I’m going to search for a couple of different people – me, and the lady in the meat dress – and then examine what’s in my plan cache:
SELECT * FROM StackOverflow.dbo.Users WHERE DisplayName = 'Brent Ozar';
SELECT * FROM StackOverflow.dbo.Users WHERE DisplayName = 'Lady Gaga';
Here’s the results:
Hey, whaddya know, Lady Gaga uses Stack Overflow too! We’re practically meant for each other.
But let’s zoom in a little on that last result set, the output of sp_BlitzCache:
SQL Server built and cached two query plans.
This has a few interesting problems:
- It built an execution plan for each one of them – which meant the query took a little longer to finish
- It cached each execution plan separately – meaning it takes up more memory
- Each plan could be different – in cases where the different name has a different number of estimated rows, SQL Server might choose to use (or avoid) an index
With just 2 queries, who cares? But if your app is sending in the same query thousands of times, each with different parameters, this can add up to more CPU time, more memory used for caching plans, and less memory used for caching data.
Our tools warn you about this in a few different ways:
- sp_Blitz warns you that you have a large number of plans for a single query, and that it’s time to dig deeper by looking at the plan cache
- sp_BlitzCache shows a warning for queries that have multiple plans (indicating that the query you’re looking at might just be a bad one out of several)
- SQL ConstantCare® suggests forced parameterization when we see that you have a ton of these over time, and can’t keep plans in the cache as a result
You could fix this by changing the application so that it uses parameterized SQL instead of strings. Run this query to tell your developers which queries are involved:
st.text AS QueryText,
qp.query_plan AS QueryPlan
FROM ( SELECT query_hash,
COUNT(DISTINCT(query_hash)) AS DistinctPlanCount,
COUNT(query_hash) AS PlanCount
GROUP BY query_hash
) AS q
JOIN sys.dm_exec_query_stats qs ON q.query_hash = qs.query_hash
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE PlanCount > 1
ORDER BY q.PlanCount DESC, q.query_hash;
And then when they say, “Sorry, we can’t fix those,” keep reading.
Optimize for Ad Hoc does not fix this.
When turned on, this server-level setting tells SQL Server to expect a lot of different queries that will never be seen again. That means:
- SQL Server still compiles every string, every time it sees it
- Just now it doesn’t cache that query’s plan until it sees the query a second time (which it almost never will, because you’re sending in a different string every time)
So your CPU is still high – you’ve just saved some memory, but not a lot. This isn’t a full fix.
Forced Parameterization fixes this.
If you right-click on a database, click Properties, Options, and scroll to the Miscellaneous section, you’ll see Parameterization. The default is Simple, but you can also choose Forced.
Setting it to Forced takes effect instantly, doesn’t require a restart, and then has a different behavior.
If I run the same queries again, here’s the new output:
SQL Server takes a little more time with each incoming query, turns the literals into variables, and then checks to see if there’s an execution plan already compiled for it. That means:
- Faster query runtime because we can skip compiling a full plan for it
- Less memory wasted on duplicate plans being cached in memory
- Easier to spot performance issues because now the same query is grouped together easier in tools like sp_BlitzCache
Things to know about Forced Parameterization:
- It’s set at the database level, and needs to be set in the database where users are running queries.
- It could theoretically be a performance drag – if all of your queries had literals, but they really were totally different queries, this could slow things down. I only recommend using this tool to fix a problem, not to proactively prevent a problem.
- When enabled, plans do get reused – which means you may suddenly have parameter sniffing issues that you didn’t have before (because before, every query got its own hand-crafted plan.)
So when should you use Forced Parameterization?
- When our tools are alerting you about a high number of plans for a single query (like, say, 10,000 or more)
- You can’t fix that query to be parameterized
- You want to reduce CPU usage and increase memory available to cache data
- You’re comfortable troubleshooting parameter sniffing issues that may arise with that query