使用sys.dm_exec_cached_plans监控存储过程性能

讨论了如何使用sys.dm_exec_query_stats动态管理视图(dmv )。本文将以SQL Server 2005为例，讨论如何利用dmv信息来判断tsql的性能优劣。在这篇文章中将继续我有关性能监视问题的讨论，并说明如何使用sys.dm_exec_cached_plans dmv来监视存储过程性能。

　　了解dmv统计

　　在我开始讨论如何收集存储过程性能的统计数据之前，我们先来回顾一些有关dmv统计的基本知识，如果你已经看过我前面关于dmvs的文章，你应该已经明白SQL Server如何收集和管理dmv信息，你可以跳过该节，从下一节开始看，如果没有，请继续往下读。

　　推出SQL Server 2005时，微软介绍了许多被称为dmvs的系统视图，让您可以探测SQL Server 的健康状况，诊断问题，或查看SQL Server实例的运行信息。

　　统计数据是在SQL Server运行的时候开始收集的，并且在SQL Server每次启动的时候，统计数据将会被重置。当你删除或者重新创建其组件时，某些dmv的统计数据也可以被重置，例如存储过程和表，而其它的dmv信息在运行dbcc命令时也可以被重置。

　　当你使用一个dmv时，你需要紧记SQL Server收集这些信息有多长时间了，以确定这些从dmv返回的数据到底有多少可用性。如果SQL Server只运行了很短的一段时间，你可能不想去使用一些dmv统计数据，因为他们并不是一个能够代表SQL Server实例可能遇到的真实工作负载的样本。另一方面，SQL Server只能维持一定量的信息，有些信息在进行SQL Server性能管理活动的时候可能丢失，所以如果SQL Server已经运行了相当长的一段时间，一些统计数据就有可能已被覆盖。

　　因此，任何时候你使用dmv，当你查看从SQL Server 2005的dmvs返回的相关资料时，请务必将以上的观点装在脑海中。只有当你确信从dmvs获得的信息是准确和完整的，你才能变更数据库或者应用程序代码。

　　使用次数

　　你想知道哪个存储过程执行得最频繁?你可以得到这个结果，但不是如你想像的那样直接去获取它，而只需要把从sys.dm_exec_sql_text表值动态管理函数( DMF )返回的日期加入sys.dm_exec_cached_plans dmv的信息中就可以做到。所以让我通过一些不同的查询来演示一下，以帮助您了解如何使用DMF和DMV获得存储过程的使用次数。

　　为了了解sys.dm_exec_cached_plans和sys.dm_exec_sql_text如何工作，请在您的服务器上运行下列代码，并查看输出：

SELECT usecounts, text, dbid, objectid FROM
   sys.dm_exec_cached_plans cp
   CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle)
WHERE objtype = 'Proc';

　　在这里，我在sys.dm_exec_cached_plans DMV中用CROSS APPLY操作将缓存的计划信息添加到sys.dm_exec_sql_text的输出中。只要DMF从dmv中返回plan_handle值，cross_apply操作将加入这一信息。当在你的机器上运行上述tsql，并查看输出时，你应该会注意到，对于文本列，会有多个行返回同一个值。为什么会发生这种情况?原因是，有时候多个计划存在于同一储存过程的程序缓存中。你可能也察觉到，那个函数看起来就像是扩展存储过程和汇编的CLR存储过程一样。因为某些原因，微软已决定把函数，扩展存储过程，以及CLR存储过程归为一个名为" Proc "的 对象类型 。我认为扩展存储过程和CLR存储过程归类为一个" Proc " 对象类型是有意义的 ，但它将函数也归为" Proc " 这个对象类型确实是没有意义的，所以，如果只要获得一个用户自定义存储过程的准确使用次数，我把上面的代码做如下修改就可以做到：

SELECT DB_NAME(dbid) AS [DB_NAME], 
       OBJECT_SCHEMA_NAME(objectid,dbid) AS [SCHEMA_NAME], 
       OBJECT_NAME(objectid,dbid)AS [OBJECT_NAME], 
       SUM(usecounts) AS [Use_Count], 
       dbid, 
       objectid  
FROM sys.dm_exec_cached_plans cp
   CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle)
WHERE objtype = 'Proc'
  AND UPPER(
-- remove white space first
            REPLACE( 
             REPLACE(
              REPLACE(
               REPLACE(
                REPLACE(
                 REPLACE(
                  REPLACE(text,'       ',' '),
                 '       ',' '),
                '      ',' '),
               '     ', ' '),
              '    ',' '),
             '   ',' '),
            '  ',' ')
           )
       LIKE '%CREATE PROC%'
GROUP BY dbid, objectid;

　　为了帮助您更好地理解本代码，让我解释一下我做的事情。为了消除函数，扩展存储过程和CLR存储过程，我在“text”列中搜索词组“CREATE PROC”，因为你可能在编写创建过程语句的时候，在“CREATE” and “PROC”的关键词之间留了多个空格，我用了一系列替换语句，以清除所有的额外空格，也有可能你编写创建过程语句的时候，使用了混合的大小写，所以我使用UPPER函数将文本统一转换成大写。

　　当你运行上面代码的时候，你可能会发现一些db_name列是空的。当dbid值是32767时，就会出现这种情况。因为数据库的ID号与系统数据库，即所谓的资源库是有联系的。这个资源库不是众所周知，但它却是存在于系统中的一个实际数据库，他的确存在，但你在SQL Server Management Studio中却看不到它。在你的数据文件目录下，有一个以字符串“mssqlsystemreource”开始命名的MDF和LDF文件，那就是资源数据库了。该资源库包含了所有编译过的系统存储过程和函数，所以为了彻底地找出所有可能的数据库名称，我已经修改了上面的代码，当dbid等于32767时，填写数据库中的名称列，下面是我的代码，由数据库为所有用户自定义的存储过程确定使用次数：

SELECT CASE when dbid = 32767 
            then 'Resource' 
            else DB_NAME(dbid) end [DB_NAME], 
       OBJECT_SCHEMA_NAME(objectid,dbid) AS [SCHEMA_NAME], 
       OBJECT_NAME(objectid,dbid)AS [OBJECT_NAME], 
       SUM(usecounts) AS [Use_Count], 
       dbid, 
       objectid  
FROM sys.dm_exec_cached_plans cp
   CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle)
WHERE objtype = 'Proc'
  AND UPPER(
-- remove white space first
            REPLACE( 
             REPLACE(
              REPLACE(
               REPLACE(
                REPLACE(
                 REPLACE(
                  REPLACE(text,'       ',' '),
                 '       ',' '),
                '      ',' '),
               '     ', ' '),
              '    ',' '),
             '   ',' '),
            '  ',' ')
            ) 
       LIKE '%CREATE PROC%'
GROUP BY dbid, objectid;

存储过程中其它与性能相关的查询

　　还有其它一些测量存储过程性能的较好的方法。采用使用次数是有意思的，但它并没有告诉你多少资源被使用或该储存过程运行需要花费多长时间，所以，下面我将介绍一个相应的性能监视的TSQL语句，以全面监控你的存储过程性能。

　　我喜欢通过一个存储过程中有多少逻辑I/O操作来衡量它的性能。使用逻辑 I / O是一个衡量存储过程执行效率的很好的方法，因为输入/输出通常是所有操作中费时最长的操作，如果你可以减少一定量的I / O操作，通常来说，你的程序性能将会得到大幅改善。这里是一个脚本，可以显示每一个存储过程执行了多少逻辑 I / O操作：

SELECT CASE when dbid = 32767 
            then 'Resource' 
            else DB_NAME(dbid) end [DB_NAME], 
       OBJECT_SCHEMA_NAME(objectid,dbid) AS [SCHEMA_NAME], 
       OBJECT_NAME(objectid,dbid)AS [OBJECT_NAME], 
       SUM(usecounts) AS [Use_Count], 
       SUM(total_logical_reads) AS [total_logical_reads],
       SUM(total_logical_reads) / SUM(usecounts) * 1.0 AS [avg_logical_reads],
       dbid, 
       objectid  
FROM sys.dm_exec_cached_plans cp
   CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle)
   JOIN 
   (SELECT SUM(total_logical_reads) AS [total_logical_reads],
           plan_handle  
      FROM sys.dm_exec_query_stats  
      GROUP BY plan_handle) qs
    ON cp.plan_handle = qs.plan_handle 
WHERE objtype = 'Proc'
  AND UPPER(
-- remove white space first
            REPLACE( 
             REPLACE(
              REPLACE(
               REPLACE(
                REPLACE(
                 REPLACE(
                  REPLACE(text,'       ',' '),
                 '       ',' '),
                '      ',' '),
               '     ', ' '),
              '    ',' '),
             '   ',' '),
            '  ',' ')
            ) 
       LIKE '%CREATE PROC%'
GROUP BY dbid, objectid
ORDER BY SUM(total_logical_reads) / SUM(usecounts) * 1.0 DESC;

　　我使用计算使用次数的查询并把它加入到一个归纳汇总的sys.dm_exec_query_stats dmv中，从而得到每一个存储过程逻辑读的代码。查看avg_logical_reads列，你可以了解到哪些存储过程的效率比较低。

　　另一个有意思的事情是衡量一个存储过程执行需要多长时间。所谓执行时间，是指从查询开始到查询结束的时间总量，以秒为单位。有些人也称之为耗费时间，或者说存储过程执行时用户的等待时间。下面是一个查询，用来计算SQL Server 2005中每个存储过程的平均耗费时间：

SELECT CASE when dbid = 32767 
            then 'Resource' 
            else DB_NAME(dbid) end [DB_NAME], 
       OBJECT_SCHEMA_NAME(objectid,dbid) AS [SCHEMA_NAME], 
       OBJECT_NAME(objectid,dbid)AS [OBJECT_NAME], 
       SUM(usecounts) AS [Use_Count],        
       SUM(total_elapsed_time) AS [total_elapsed_time],
       SUM(total_elapsed_time) / SUM(usecounts) * 1.0 AS [avg_elapsed_time],
       substring(convert(char(23),DATEADD(ms,sum(total_elapsed_time)/1000,0),121),12,23)  total_elapsed_time_ms,
       dbid, 
       objectid  
FROM sys.dm_exec_cached_plans cp
   CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle)  
   JOIN 
   (SELECT SUM(total_elapsed_time) AS [total_elapsed_time],
           plan_handle  
      FROM sys.dm_exec_query_stats  
      GROUP BY plan_handle) qs
    ON cp.plan_handle = qs.plan_handle 
WHERE objtype = 'Proc'
  AND UPPER(
-- remove white space first
            REPLACE( 
             REPLACE(
              REPLACE(
               REPLACE(
                REPLACE(
                 REPLACE(
                  REPLACE(text,'       ',' '),
                 '       ',' '),
                '      ',' '),
               '     ', ' '),
              '    ',' '),
             '   ',' '),
            '  ',' ')
            ) 
       LIKE '%CREATE PROC%'
GROUP BY dbid, objectid
ORDER BY SUM(total_elapsed_time) / SUM(usecounts) * 1.0 DESC;

　　结论

　　你应该监控你服务器上的所有代码。通过使用这篇文章中代码，你可以找出哪些存储过程使用了大量的资源。在一个开发环境中，当应用正在构建的时候，最适合进行这种监控。你可以使用该技术给你的程序员提供反馈：他们编写的存储过程到底效率如何。给你的程序员一些性能方面的统计数据，可以帮助他们了解怎样才能更好的运行代码，促进高性能设计方式，产生高效率代码。

文章出处：http://database.ctocio.com.cn/tips/161/7810661.shtml