1

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.

0

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.

  • 2
    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? – hot2use Jan 28 at 9:59
  • The result is: AdHoc_Plan_MB: 5011.804687500 Total_Cache_MB: 5237.062500000 AdHoc%: 95.698775 – Commander Jan 28 at 10:01
  • 1
    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. – Learning_DBAdmin Jan 28 at 10:40
  • 4
    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. – Tibor Karaszi Jan 28 at 11:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.