要对程序进行优化,首先要能找哪里需要优化。

对于网站来说,数据库访问的性能是最先要考虑的因素。但是一个网站,每天都要进行成千上万,甚至还要多得多次的数据库查询,怎么找到应该优化哪条查询语句呢?

需要考虑两个因素:1)某条数据库查询语句的查询频率,2)以及每次查询花费的时间。

如果一条查询,即使比较慢,但是一天仅仅用到一两次,那么对它的优化也不是很迫切。另一条查询,即使已经很快了,但是如果使用的频率特别高,那么哪怕几毫秒,也是值得花力气优化的。

在SQL Server中,为了在巨大数量的数据库查询中定位最需要优化的语句,有一个系统视图 sys.dm_exec_query_stats,就会非常有用。

在SQL Server中进行下面这个查询:

-- TOP 20  queries (by CPU)
SELECT TOP (20)
    [Total CPU (sec)] = total_worker_time * 0.000001,
    [Total Elapsed Time (sec)] = total_elapsed_time * 0.000001,
    [Execution Count] = execution_count,
    [Average CPU (sec)] = total_worker_time * 0.000001 / execution_count ,
    [DB Name] = DB_NAME(ST.dbid),
    [Object Name] = OBJECT_NAME(ST.objectid, ST.dbid),
    [Query Text] = 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] = qp.query_plan
FROM sys.dm_exec_query_stats QS
CROSS APPLY sys.dm_exec_sql_text([sql_handle]) ST
CROSS APPLY sys.dm_exec_query_plan ([plan_handle]) QP
ORDER BY total_worker_time DESC 

可以得到下面这样的结果,列出了花费 CPU 时间最多的查询。具体来说,包括下面几列:

  • [Total CPU (sec)] 总 CPU 时间
  • [Total Elapsed Time (sec)] 总实际时间
  • [Execution Count] 查询执行次数
  • [Average CPU (sec)] 平均 CPU 时间
  • [DB Name] 数据库名称
  • [Object Name] 查询对象名称,
  • [Query Text] 查询的 SQL 文本
  • [Query Plan] 查询的执行计划

寻找数据库访问性能瓶颈

这些SQL查询,就是首先应该重点考虑优化的地方!!

这里比较值得注意的有两点

  • CPU Time 和 Elapsed Time的区别:前者就是CPU进行计算的时间,后者是真正实际使用的时间,比包括其他一些等候的时间等等,但是 Elapsed Time 并不是总大于 CPU 时间,对于多核 CPU,CPU 时间常常大于 Elapsed Time,因为计算 CPU 时间的时候会把各个核各自花费的时间累加起来。
  • 数据库名称:这个查询时针对整个数据库引擎实例的,因此里面的各个数据库的查询都混在一起,遗憾的是,通过系统中的 dm_exec_sql_text 获取数据库ID的时候,对动态查询无效,所以表里只能返回 NULL。可以本文后面的参考文章。

基于上面的说明,我又做了一个更能体现性能指标的查询:

SELECT execution_count, creation_time,
    [Execution/Hour] = execution_count * 3600.0 / DATEDIFF(s, creation_time, GETDATE()),
    [CPU/Execution] = total_worker_time * 0.000001 / execution_count,
    [CPU/Hour] = total_worker_time * 0.0036 / DATEDIFF(s, creation_time, GETDATE()), 
    [DB Name] = DB_NAME(ST.dbid),
    [Query Text] = 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] = qp.query_plan
FROM sys.dm_exec_query_stats QS
CROSS APPLY sys.dm_exec_sql_text([sql_handle]) ST
CROSS APPLY sys.dm_exec_query_plan ([plan_handle]) QP
WHERE execution_count * 3600.0 / DATEDIFF(s, creation_time, GETDATE()) > 1.0 
    and DATEDIFF(s, creation_time, GETDATE()) >3600
Order by [CPU/Hour] desc

这里可以列出各个查询语句的三个性能指标:

  • 平均每小时中该查询被执行的次数
  • 平均每次执行该查询花费的 CPU 时间(秒)
  • 平均每小时中执行该查询花费的总CPU时间(秒)

这三个指标去寻找执行的次数又多又慢的查询,非常有效!第一个指标反映一个查询的频度,第二个指标反映这个查询的速度,第三个等于前两个相乘,反映考虑频度权重的速度。

此外,在这个查询中,对于平均每小时执行不了1次的查询进行排除,以及刚刚编译1小时以内的查询也进行排除,避免数据干扰。

希望这篇文章对您有所帮助!

参考资料:

(total_elapsed_time < total_cpu_time) Vs. uses_parallelism
http://michaeljswart.com/2011/12/cxpacket-whats-that-and-whats-next

sys.dm_exec_query_stats (Transact-SQL)
http://msdn.microsoft.com/zh-cn/library/ms189741.aspx

sys.dm_exec_sql_text DBID column NULL for dynamic SQL
http://connect.microsoft.com/SQLServer/feedback/details/374600/sys-dm-exec-query-stats-dbid-column-null-for-dynamic-sql