4.0 Useful Queries on DMV’s to understand Plan Cache Behavior

Forced parameterization not effecient

Cine — Fri, 13 Apr 2007 13:03:44 GMT

We have forced parameterization enabled since we were having big problems with almost identical queries there were not simple enough for the simple parameterization to work. But we are still getting a ton of plans that are not being parameterized

select count(*) from sys.dm_exec_cached_plans cp

select count(*) from sys.dm_Exec_Cached_plans where cacheobjtype = 'Compiled Plan'

select count(*) from sys.dm_Exec_Cached_plans where cacheobjtype = 'Compiled Plan' and objtype = 'Adhoc'

select count(*) from sys.dm_Exec_Cached_plans where cacheobjtype = 'Compiled Plan' and objtype = 'Prepared'

Returns 23283, 23163, 21879 and 1245 respectively.

Any clue as to why it does this? I can see many of the queries should be very simple to parameterize.

Another questions is, why is the dbid of the sys.dm_exec_cached_plans always null. It makes it very hard to track down things when you have multiple db on the same machine.

re: 4.0 Useful Queries on DMV’s to understand Plan Cache Behavior

sangeethashekar — Fri, 13 Apr 2007 23:18:24 GMT

Thank you for bringing this to our attention. I would be happy to work with you to learn more about the nature of queries in your workload/application and investigate why forced parameterization does not work for you. Please send me email at sshekar@microsoft.com

Reset cached plan - how to and what to?

SQL Server Performance and Tuning - (SSQA) — Mon, 14 May 2007 12:55:24 GMT

I don't believe you would need to reset the cache on a production server, it is not a best practice to

More TokenAndPermUserStore

Grumpy Old DBA — Thu, 19 Jul 2007 13:10:37 GMT

I was interested in Jasper’s post http://sqlblogcasts.com/blogs/sqldbatips/archive/2007/07/18/troubleshooting-performance-issues-with-tokenandpermuserstore-in-sql2005.aspx?CommentPosted=true#commentmessage