Adhoc queries memory consumption in SQL Server

Question

I have a SQL Server 2012 and it has just one (1) database in it. When I generate a server dashboard report it shows that 50% of CPU usage is related to my database and the rest is associated to Adhoc queries.

Where are these adhoc queries? Are they related to my database? Why are these two parameters separated? How can I find and optimize them?

Any comments would be appreciated.

Answer 1

Could you please run below query and post the result? This would provide the percentage of Adhoc Plan, Total cache plan and percentage of Adhoc:

SELECT AdHoc_Plan_MB, Total_Cache_MB,
 AdHoc_Plan_MB*100.0 / Total_Cache_MB AS 'AdHoc %'
FROM (
SELECT SUM(convert(bigint,(CASE
 WHEN objtype = 'adhoc'
 THEN size_in_bytes
 ELSE 0 END))) / 1048576.0 AdHoc_Plan_MB,
 SUM(convert(bigint, size_in_bytes)) / 1048576.0 Total_Cache_MB
FROM sys.dm_exec_cached_plans)A

You may check below query also to get level of adhoc queries:

SELECT S.CacheType, S.Avg_Use, S.Avg_Multi_Use,
 S.Total_Plan_3orMore_Use, S.Total_Plan_2_Use, S.Total_Plan_1_Use, S.Total_Plan,
 CAST( (S.Total_Plan_1_Use * 1.0 / S.Total_Plan) as Decimal(18,2) )[Pct_Plan_1_Use],
 S.Total_MB_1_Use, S.Total_MB,
 CAST( (S.Total_MB_1_Use * 1.0 / S.Total_MB ) as Decimal(18,2) )[Pct_MB_1_Use]
 FROM
 (
 SELECT CP.objtype[CacheType],
 COUNT(*)[Total_Plan],
 SUM(CASE WHEN CP.usecounts > 2 THEN 1 ELSE 0 END)[Total_Plan_3orMore_Use],
 SUM(CASE WHEN CP.usecounts = 2 THEN 1 ELSE 0 END)[Total_Plan_2_Use],
 SUM(CASE WHEN CP.usecounts = 1 THEN 1 ELSE 0 END)[Total_Plan_1_Use],
 CAST((SUM(CP.size_in_bytes * 1.0) / 1024 / 1024) as Decimal(12,2) )[Total_MB],
 CAST((SUM(CASE WHEN CP.usecounts = 1 THEN (CP.size_in_bytes * 1.0) ELSE 0 END)
 / 1024 / 1024) as Decimal(18,2) )[Total_MB_1_Use],
 CAST(AVG(CP.usecounts * 1.0) as Decimal(12,2))[Avg_Use],
 CAST(AVG(CASE WHEN CP.usecounts > 1 THEN (CP.usecounts * 1.0)
 ELSE NULL END) as Decimal(12,2))[Avg_Multi_Use]
 FROM sys.dm_exec_cached_plans as CP
 GROUP BY CP.objtype
 ) AS S
 ORDER BY S.CacheType

You may also run below query to check query level details:

 SELECT DISTINCT 
 QCP.objtype
 ,MultipleQ.PlanCount
 ,qStat.query_hash
 ,sText.text AS QueryText
FROM ( 
 SELECT query_hash,
 COUNT(query_hash) AS PlanCount
 FROM sys.dm_exec_query_stats
 GROUP BY query_hash
 ) AS MultipleQ
INNER JOIN sys.dm_exec_query_stats qStat ON MultipleQ.query_hash = qStat.query_hash
INNER JOIN sys.dm_exec_cached_plans QCP
 ON QCP.plan_handle = qStat.plan_handle
CROSS APPLY sys.dm_exec_sql_text(qStat.sql_handle) AS sText
CROSS APPLY sys.dm_exec_query_plan(qStat.plan_handle) AS qp
WHERE PlanCount > 1
ORDER BY MultipleQ.PlanCount DESC

Needless to say, do a bit more search on these topics and you will see tonnes of result.

• John K. N.: Your script provides a summary of MB used for each type of plan. The original question is about CPU usage. How does your query relate to the original question?
• Commander: The result is: AdHoc_Plan_MB: 5011.804687500 Total_Cache_MB: 5237.062500000 AdHoc%: 95.698775
• Learning_DBAdmin: Adhoc queries are not specific to one user database, its a server wide result. As per query results - 95.6% of the queries are adhoc. In case, you have issue with performance, you may try enabling "Optimize for adhoc workloads" but, please check more details, I have added one more query for detailed level. @hot2use As far as CPU Usage is concerned, if you look at the server dashboard report, you would see at bottom "CPU Usage and IO Performed chart shows the cumulative share of all objects by database". So, its pertaining to databases only.
• Tibor Karaszi: Just a note that "optimize for ad-hoc workloads" will very likely save on memory (memory can be used for better things), it will not reduce CPU-usage. There is nothing special about ad-hoc queries related to CPU usage compared to other type of queries. Well, if they are a bit complex, you spend CPU on generating plans over and over again. But apart from that, you need to find the offenders and work with those queries. If you have a high cpu usage because of plan generation, then look at the app and see why it generates ad-hoc queries un the first place.