通过SQL Server 2005的过程缓存监测查询性能

核心提示：在SQL Server 2005，对存储过程的重新编译可以只针对存储过程内的执行语句，通过SQL Server 2005的过程缓存监测查询性能，也就是说，在 SQL Server 2005中对数据库引擎进行重新编译，任何时候，都可以使用该查询来查看数据库缓存可供重复使用的语句的情况，只需重新编译批处理或存储过程中的少量

在SQL Server 2005，对存储过程的重新编译可以只针对存储过程内的执行语句，也就是说，在 SQL Server 2005中对数据库引擎进行重新编译，只需重新编译批处理或存储过程中的少量语句即可。这明显不同于SQL Server 2000，后者重新编译存储过程时，会编译整个存储过程或批处理。SQL Server 2005语句级重新编译功能使其他语句能够在不需要的时候跳过重新编译进程。

在SQL Server 2005中，在执行语句之前，数据库引擎会编译执行计划，以便找出运行该语句的最佳途径。SQL Server 2005会尽可能的重复利用已存在的原始计划，达到提高查询速度和效率的目的，并把该计划存储在过程缓存中。数据库引擎会持续维护这块缓存，并在有需要的时候会对该计划进行重新优化，如利用、插入或删除。你可以使用动态管理视图(DMV)来查询该执行计划缓存，根据数据库中出现的语句的使用频率和性能还能收集到大量信息。

本文简单的介绍如何查看检测数据库服务器上执行语句用到的执行计划，并对已经存储在过程缓存中的语句执行计划的信息进行解析，使我们可以了解最常用的查询执行性能情况，以便如何在必要时对其进行调整优化。

我们将使用sys.dm_exec_query_stats动态管理视图来查看过程缓存的语句执行计划。它包含了缓存中所有语句的聚集数据，包括语句执行的次数、语句执行耗时长短、由语句引起的最大读取量等等;同时还包括针对正在运行的执行计划和SQL语句等数据的散列。通过对查询脚本稍做修改，我们甚至还能查到存储过程的名称及其所在的数据库。

运行下面的查询脚本，会返回指定数据库系统中根据从过程缓存执行指令次数从多到少排列的前一百位执行计划，这可以帮助我们了解SQL Server在过程缓存中保留了哪些存储过程。

在该查询中，使用了sys.dm_exec_query_stats动态管理视图，CROSS APPLY运算符，以及sys.dm_exec_sql_text和sys.dm_exec_query_plan两个函数来查找缓存中的指令和正在使用的执行计划。注意，对于本例中的查询必须在运行在SQL Server 2005 Service Pack 2上，因为OBJECT_NAME函数功能有可能存在些许的不同。如果运行的不是SQL Server 2005 Service Pack 2，去掉OBJECT_NAME函数的第二个参数即可。

以下是引用片段：
　　SELECT　TOP 100
　　　　 qs.execution_count,
　　　　 DatabaseName = DB_NAME(qp.dbid),
　　　　 ObjectName = OBJECT_NAME(qp.objectid,qp.dbid),
　　　　 StatementDefinition =
　　　　　　　　SUBSTRING (
　　　　　　　　　　　　st.text,
　　　　　　　　　　　　(
　　　　　　　　　　　　　　　　qs.statement_start_offset / 2
　　　　　　　　　　　　) + 1,
　　　　　　　　 (
　　　　　　　　　　　　　　　　　　　 (
　　　　　　　　　　　　　　　　　　　　　　　 CASE qs.statement_end_offset
　　　　　　　　　　　　 WHEN -1 THEN DATALENGTH(st.text)
　　　　　　　　　　　　 ELSE qs.statement_end_offset
　　　　　　　　　　　　　　　　　　　　　　　 END - qs.statement_start_offset
　　　　　　　　　　　　　　　　　　　 ) / 2
　　　　　　　　　　　　　　　　) + 1
　　　　　　　　),
　　　　 query_plan,
　　　　 st.text, total_elapsed_time
FROM　　sys.dm_exec_query_stats AS qs
　　　　 CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
　　　　 CROSS APPLY sys.dm_exec_query_plan (qs.plan_handle) qp
WHERE
　　 st.encrypted = 0
ORDER BY qs.execution_count DESC

如果要查找过程缓存中执行语句用的XML执行计划，点击query_plan栏中的链接，就会创建一个可供查看的XML文档。此XML文档的好处就是可以将其存为.sqlplan文件，然后就可以在SQL Server 2005 Management Studio中打开用来查看图形执行计划。

本文所举的例子简单却非常好用。任何时候，都可以使用该查询来查看数据库缓存可供重复使用的语句的情况，根据查询到的信息制定优化决策。