动态监视存储过程的执行和动态sql上的CPU错误

Question

我正在开发一个脚本来监视存储过程的执行和cpu利用率，但是，动态运行下面的脚本它不起作用。它直接在SQL Server Mgmt Studio中工作。有人能发现需要改变的地方吗?

错误信息是:

156号电话，第15层，第1州，第43行
关键字'proc'附近语法错误。

脚本如下:

DECLARE @SQL NVARCHAR(MAX)
--SELECT @SQL ='SELECT * FROM OPENQUERY('+ QUOTENAME('servername')    + ','''+ '
SELECT @SQL = 'SELECT dbID
                      ,dbname
                      ,StoredProcedure
                      ,last_execution_time
                      ,total_cpu_time
                      ,executionCounts
                      ,Total_Logical_Reads
                      ,Total_Logical_Writes 
               FROM OPENQUERY('+ QUOTENAME('servername') + ','''+ '
SET FMTONLY OFF   
SET NOCOUNT ON
SET DATEFORMAT DMY
SET DEADLOCK_PRIORITY LOW
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
;with r0 as 
(
   SELECT 
      st.dbID
      ,StoredProcedure = OBJECT_NAME(st.objectid,st.dbid)
      ,last_execution_time = MAX(QS.last_execution_time)
      ,total_cpu_time = sum(qs.total_worker_time) 
      ,executionCounts = sum(cp.usecounts)
      ,Total_Logical_Reads=sum(qs.Total_Logical_Reads)
      ,Total_Logical_Writes=sum(qs.Total_Logical_Writes)
   FROM 
      sys.dm_exec_query_stats qs 
   INNER JOIN 
      sys.dm_exec_cached_plans cp ON cp.plan_handle = qs.plan_handle
   CROSS APPLY 
      sys.dm_exec_sql_text(cp.plan_handle) st
   WHERE
      cp.objtype = ''proc''--> most of our problems are related to stored procedures 
      --  removing this filter would increase the cost of the query in the monitored servers
      AND OBJECT_NAME(st.objectid, st.dbid) IS NOT NULL
 GROUP BY
     st.dbid, st.objectid
)
SELECT 
    r0.dbID
    ,DBName = CASE WHEN r0.dbid = 32767 
                     THEN ''Resource''
                     ELSE COALESCE(DB_NAME(r0.dbid), ''No3 Name'') -- keep the coalesce for the database name to be used in a primary key constraint
              END 
    ,r0.StoredProcedure
    ,r0.last_execution_time
    ,r0.total_cpu_time
    ,r0.executionCounts
,r0.Total_Logical_Reads
,r0.Total_Logical_Writes
FROM  
    r0
ORDER BY 
    total_cpu_time DESC' + ''')'
--print @sql
EXEC SP_EXECUTESQL @SQL

Answer 1

这是动态SQL的乐趣之一。

你在另一个字符串中嵌套一个字符串…

在第43行字符串字面量'proc'周围添加另一组' ':

:cp.objtype = ''proc''

就变成:cp.objtype = ''''proc''''

一个很好的测试是注释掉你的EXEC和取消注释PRINT行。然后运行它将字符串打印到SSMS中的窗口。然后将输出复制到一个新窗口…

注意'proc'和'resource '以及'No3 Name'都被转义了。您必须在所有这些文字中添加额外的'才能使其正常工作。