\nThere's a newer version of SQL Server 2008 (Release Candidate 0) available on MSDN and Technet.\n<\/p>\n
\nThere's a couple of features that show up in RC0 that are performance related. One is a server setting 'optimize for ad hoc workloads'. This one tells SQL Server to save a "compiled plan stub" in the query cache for adhoc queries, rather than the query plan that's usually saved.\n<\/p>\n
\nYou can see these stubs as cacheobjtype 'Compiled Plan Stub' in sys.dm_exec_cached_plans. And you can see them because trying to resolve a plan_handle to a query plan (using sys.dm_exec_cached_plans or sys.dm_exec_query_stats) doesn't work for stubs, like it does for "real" plan handles. Saving a stub rather than the entire plan saves precious plan cache memory.\n<\/p>\n
\nHere's a little script to try it out.\n<\/p>\n
\nsp_configure 'show advanced options',1
\nreconfigure
\ngo
\nsp_configure 'optimize for ad hoc workloads',1
\nreconfigure
\ngo
\n— don't use on a production system, clears whole query cache
\nDBCC FREEPROCCACHE
\ngo
\nuse pubs
\ngo
\n— background plans to produce this DMV, note there is a stub for one of these plans
\nselect * from sys.dm_exec_cached_plans
\ngo
\n— not autoparameterized, stub produced
\nSELECT title_id, sum(qty) from sales
\ngroup by title_id
\nhaving sum(qty) = 30
\ngo
\n— you can see the stub
\nselect * from sys.dm_exec_cached_plans
\ngo
\n— autoparameterized, whole query plan for parameterized version
\nSELECT *
\nFROM titles
\nWHERE price = 19.99
\ngo\n<\/p>\n
\n— stub for the non-parameterized version, plan for the parameterized version
\nSELECT usecounts, cacheobjtype, objtype, [text]
\nFROM sys.dm_exec_cached_plans P
\n CROSS APPLY sys.dm_exec_sql_text(plan_handle)
\n WHERE [text] NOT LIKE '%dm_exec%'
\nORDER BY p.usecounts DESC
\n— query plan for stub query handle is not saved returns NULL
\nSELECT sql.text, p.query_plan
\nFROM sys.dm_exec_query_stats AS qs
\nCROSS APPLY sys.dm_exec_sql_text(sql_handle) sql
\nCROSS APPLY sys.dm_exec_query_plan(plan_handle) p
\nWHERE text NOT LIKE '%sys_dm_exec%' AND text NOT LIKE '%msparam_0%'
\nORDER BY qs.EXECUTION_COUNT DESC
\ngo\n<\/p>\n
\n— set it back, same experiment produces plans not stubs
\nsp_configure 'show advanced options',1
\nreconfigure
\ngo
\nsp_configure 'optimize for ad hoc workloads',0
\nreconfigure
\ngo<\/p>\n","protected":false},"excerpt":{"rendered":"
There's a newer version of SQL Server 2008 (Release Candidate 0) available on MSDN and Technet. There's a couple of features that show up in RC0 that are performance related. One is a server setting 'optimize for ad hoc workloads'. This one tells SQL Server to save a "compiled plan stub" in the query cache […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[19,29],"tags":[],"class_list":["post-690","post","type-post","status-publish","format-standard","hentry","category-performance","category-sql-server-2008"],"yoast_head":"\n