October 23, 2012 at 4:05 am
Hi All
On my SQL Server instance, I have +- 26000 Adhoc plans and +-23000 of those plans have only been used one.
This insance hosts 8 databases, where can I start looking to see which database(s) these plans are being generated for?
Any other troubleshooting steps I can take for this?
Thanks
October 23, 2012 at 5:35 am
There is a setting called 'Optimize for Ad Hoc Workload' that could help you out. Instead of storing a plan each time a query is run, the first time it's run a plan stub is stored. This radically reduces the amount of wasted space in the cache.
"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
October 25, 2012 at 5:25 am
Grant Fritchey (10/23/2012)
There is a setting called 'Optimize for Ad Hoc Workload' that could help you out. Instead of storing a plan each time a query is run, the first time it's run a plan stub is stored. This radically reduces the amount of wasted space in the cache.
Thanks
What do you think about the database setting "parameterization forced"?
Thanks
October 26, 2012 at 8:04 am
SQLSACT (10/25/2012)
Grant Fritchey (10/23/2012)
There is a setting called 'Optimize for Ad Hoc Workload' that could help you out. Instead of storing a plan each time a query is run, the first time it's run a plan stub is stored. This radically reduces the amount of wasted space in the cache.Thanks
What do you think about the database setting "parameterization forced"?
Thanks
That is a sledge hammer approach that can make some things faster/better but can totally screw the pooch on others. Test and see if it is right for your workloads.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
October 26, 2012 at 8:17 am
SQLSACT (10/25/2012)
Grant Fritchey (10/23/2012)
There is a setting called 'Optimize for Ad Hoc Workload' that could help you out. Instead of storing a plan each time a query is run, the first time it's run a plan stub is stored. This radically reduces the amount of wasted space in the cache.Thanks
What do you think about the database setting "parameterization forced"?
Thanks
Completely missed this until Kevin answered it.
I agree with Kevin. I've seldom seen it help. I haven't seen it hurt though, yet. Again, I'm with Kevin, test it and see.
"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
October 26, 2012 at 10:27 am
Thanks Guys
October 26, 2012 at 10:28 am
Grant Fritchey (10/26/2012)
SQLSACT (10/25/2012)
Grant Fritchey (10/23/2012)
There is a setting called 'Optimize for Ad Hoc Workload' that could help you out. Instead of storing a plan each time a query is run, the first time it's run a plan stub is stored. This radically reduces the amount of wasted space in the cache.Thanks
What do you think about the database setting "parameterization forced"?
Thanks
Completely missed this until Kevin answered it.
I agree with Kevin. I've seldom seen it help. I haven't seen it hurt though, yet. Again, I'm with Kevin, test it and see.
If I go the route of changing the setting to Forced Parameterization, how can I get an idea of which database all these single use plans come from?
Thanks
October 26, 2012 at 10:38 am
sys.dm_exec_query_plan ( plan_handle ) has a dbid field.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
October 26, 2012 at 11:30 am
TheSQLGuru (10/26/2012)
sys.dm_exec_query_plan ( plan_handle ) has a dbid field.
That column shows null for adhoc and prepared statements
Guess I'll have to do some serious digging..
Thanks
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply