Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Plan Cache - Adhoc - Usecounts 1 Expand / Collapse
Author
Message
Posted Tuesday, October 23, 2012 4:05 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, September 22, 2014 4:37 AM
Points: 1,379, Visits: 2,688
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
Post #1375940
Posted Tuesday, October 23, 2012 5:35 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 9:27 PM
Points: 13,776, Visits: 28,178
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
The Scary DBA
Author of: SQL Server Query Performance Tuning
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1375974
Posted Thursday, October 25, 2012 5:25 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, September 22, 2014 4:37 AM
Points: 1,379, Visits: 2,688
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
Post #1376929
Posted Friday, October 26, 2012 8:04 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 1:11 PM
Points: 4,368, Visits: 6,208
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 at GMail
Post #1377602
Posted Friday, October 26, 2012 8:17 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 9:27 PM
Points: 13,776, Visits: 28,178
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
The Scary DBA
Author of: SQL Server Query Performance Tuning
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1377614
Posted Friday, October 26, 2012 10:27 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, September 22, 2014 4:37 AM
Points: 1,379, Visits: 2,688
Thanks Guys
Post #1377720
Posted Friday, October 26, 2012 10:28 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, September 22, 2014 4:37 AM
Points: 1,379, Visits: 2,688
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
Post #1377721
Posted Friday, October 26, 2012 10:38 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 1:11 PM
Points: 4,368, Visits: 6,208
sys.dm_exec_query_plan ( plan_handle ) has a dbid field.

Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1377730
Posted Friday, October 26, 2012 11:30 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, September 22, 2014 4:37 AM
Points: 1,379, Visits: 2,688
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
Post #1377760
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse