3

I am investigating on the SQL Server behaviour with the “Optimize for Ad hoc Workloads” option set to “True”; in particular, I am trying to understand which information we have in cache after the first execution of an Ad Hoc query and consequently the Stub is put in cache.

I noticed that as soon the query is executed the first time and the Stub is in cache, we are able to get query statistics through sys.dm_exec_query_stats, but only for a limited period of time. On a production environment (SQL Server 2019 (RTM)) it seems that sys.dm_exec_query_stats maintains this information for less than one hour.

Here it is the query I ran:

select
    sysdatetime() as [SysDateTime],
    case when qs.sql_handle is not null then 'Query Stats are here' end as [Query Stats are here],
    count(*) as Cnt,
    min (qs.last_execution_time) as MinLastExecTime    
from sys.dm_exec_cached_plans cp
left join sys.dm_exec_query_stats qs on qs.plan_handle = cp.plan_handle
where cp.objtype = 'Adhoc' and cp.cacheobjtype = 'Compiled Plan Stub' 
group by case when qs.sql_handle is not null then 'Query Stats are here' end
go

And this is the result:

enter image description here

I am not able to find a good explanation. Is there anyone that can help?

Improve this question
2
  • Not really duplicated, but it helped. It seems that with "Optimize for Adhoc workload" set to TRUE, for Adhoc queries NOT qualified for Simple Parameterization, in cache there is ONLY a Compiled Plan Stub / Adhoc WITH an entry in sys.dm_exec_query_stats. This explains my doubt.
    – Alessandro Mortola
    Commented Apr 12, 2023 at 4:56
  • Yes, the linked Q & A covers the root of the misunderstanding. There are very few exact duplicates, but questions with essentially the same answer happen quite frequently, as here.
    – Paul White ♦
    Commented Apr 12, 2023 at 5:18

0

Reset to default

Browse other questions tagged or ask your own question.