December 2, 2013 at 2:56 am
We have a few databases that run almost exclusively non-parametrized queries. A significant part of these queries is repeated often for data refresh. Each will have its own execution plan that is used a lot of times. But another part is one-time or executed very rarely. However these one-time queries are largely identical but too complex to be 'parametrized simply'. They have the same execution plan though. Since OFAHW is enabled, only a stub of these plans will be stored. This blurs the query statistics a bit since these queries and the plan stub are aged out of the cache more rapidly.
We are looking at the option of using Forced Parametrization. But I am wondering what the best resources to look at are. Does sys.dm_exec_query_stats not show the complete information because of the stub plans that are removed because of OFAHW? I am of course very reluctant to disable OFAHW just to be able to analyze possible effect of FP. Alternatively we are also looking at template plan guides.
Does anyone have some hints in which direction to look?
December 2, 2013 at 3:47 am
Forced Parameterization has a very limited set of cases where it will be applied. It's only slightly more likely to be used by the optimizer than simple parameterization. Check the Books Online for the details. You could enable it as a test in your situation to see if you'll see any benefit. I wouldn't turn off Optmize for Ad Hoc when I turned that on. I'd leave both on under most circumstances.
If you're only ever seeing plan stubs, then the plans are not identical and reusable. Otherwise, the second time the same plan came up, you'd see a plan stored in cache instead of a plan stub. So, I'm pretty certain you are not quite seeing the circumstances you describe.
Also, it sounds like these queries are coming in as one giant batch instead of a series of discrete statements. That's also likely to lead to a lack of plan reuse since the entire batch is compiled together, not each individual plan.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 2, 2013 at 4:30 am
Thanks for the reply.
I thought that the text of a query was part of the key of a plan. These queries have hardcoded values and show as such via dm_exec_query_text (so not parametrized). The query plan hash is the same but the handle is different in dm_exec_query_stats.
In general we see many different plan handles for queries that have the same plan hash value (in dm_exec_query_stats). Besides knowing that the workload is almost exclusively with hardcoded values this led us to consider forced parametrization.
December 2, 2013 at 4:35 am
Yeah, the text is part of the issue and forced parameterization could help some. But it's use is pretty limited. Again, I'd turn it on to see if it can help. It certainly won't hurt.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 2, 2013 at 5:01 am
OFAHW creates a plan-stub the first time an ad-hoc query is issued, the next time that same query is issued, SQL creates a full execution plan and removes the stub.
Are you facing any performance issues that's steering you to change your parameterization options?
December 2, 2013 at 5:31 am
No there are no specific performance issues that trigger the discussion. We have recently been analyzing performance from a broad perspective where we have done some changes mainly concerning indexes and query tuning. The effects were already very positive.
FP is just another aspect that we want to get clear. Our company has a series of products all using SQL Server with very different types of databases. So another goal of such an endeavour is to educate ourselves in the various aspects of performance tuning for SQL Server.
December 3, 2013 at 12:27 am
I've always maintained that if it's not broken, don't fix it. This is obviously besides routine maintenance.
However, as Grant said - Turn in on, monitor it and take it from there.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply