本文主要向大家介绍了SQLServer数据库：寻找数据库访问的性能瓶颈，通过具体的内容向大家展示，希望能对大家学习SQLServer数据库有所帮助。

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

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

需要考虑两个因素：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小时以内的查询也进行排除，避免数据干扰。

本文由职坐标整理并发布，了解更多内容，请关注职坐标SQL Server数据库频道！