How does SQL Server's Performance Dashboard categorize a query as "ad-hoc" or otherwise?

Question

I'm looking at the CPU usage in sql server's server performance dashboard report.

I have 10 dbs on the server.

So the pie chart shows me 85% for adhoc queries. Whereas only 15% is split amongst all dbs.

What does 85% adhoc mean? Don't these queries belong to those 10 dbs?

Sample image:

Answer 1

It means queries not in a stored procedure. Profiler or Extended Events is your friend for stuff like this. These tools allow you to capture the query used for the reports.

      select total_worker_time
      ,       case when db_name(dbid) is null then 'Adhoc Queries' else  db_name(dbid) end as db_name
      ,       dbid
      ,       1 as state
      ,       1 as msg
      from sys.dm_exec_query_stats s1
      cross apply sys.dm_exec_sql_text(sql_handle) as  s2

The docs claim that dbid

For ad hoc and prepared SQL statements, the ID of the database where the statements were compiled.

sys.dm_exec_sql_text

But that doesn't match the observed behavior of the system (doc pr here). In the old days, and when that report was written having a null dbid meant that the query wasn't part of a stored procedure. And that appears to still be the case from my testing. Ad-hoc batches, client prepared statements, and dynamic SQL in stored procedures all appear with dbid of null.

Only static SQL in stored procedures had the dbid populated.

Looks like the change mentioned in the docs was made to sys.dm_exec_query_plan, but not to sys.dm_exec_sql_text. So this query really should be

  select total_worker_time
  ,       case when db_name(p.dbid) is null then 'Adhoc Queries' else  db_name(p.dbid) end as db_name
  ,       p.dbid  
  ,       1 as state
  ,       1 as msg
  from sys.dm_exec_query_stats s1
  cross apply sys.dm_exec_query_plan(s1.plan_handle) p 

So a reasonable estimate of CPU per database would be

select case when db_name(p.dbid) is null then 'Unknown' else  db_name(p.dbid) end as db_name,
       sum(qs.total_worker_time) worker_time
from sys.dm_exec_query_stats qs
cross apply sys.dm_exec_query_plan(qs.plan_handle) p 
group by p.dbid
order by worker_time desc