SQL Server 中的即席查詢記憶體消耗

我有一個 SQL Server 2012，其中只有一 (1) 個數據庫。當我生成伺服器儀表板報告時，它顯示 50% 的 CPU 使用率與我的數據庫相關，其餘的與即席查詢相關聯。

這些即席查詢在哪裡？它們與我的數據庫有關嗎？為什麼這兩個參數是分開的？如何找到並優化它們？

任何意見將不勝感激。

您能否在查詢下方執行並發布結果？這將提供 Adhoc 計劃的百分比、總記憶體計劃和 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

您還可以檢查以下查詢以獲取臨時查詢的級別：

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

您還可以在查詢下方執行以檢查查詢級別的詳細資訊：

 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

不用說，對這些主題進行更多搜尋，您會看到大量結果。

引用自：https://dba.stackexchange.com/questions/228215