如何进行SQL性能优化?-使用动态管理视图和函数

在SQL Server 2005中,提供了动态管理视图和函数(Dynamic Management Views and Dynamic Management Functions),大大的方便了我们对系统运行情况的监控,故障诊断和性能优化。现在除了Debug以外,已经很少再对生产系统使用Profiler。

 

下面我会Step by step的介绍,如何使用DMV和DMF来诊断系统情况,介绍到的全部知识都来自于联机丛书(Books Online)。

 

Issue:在新上线一个系统后,我发现数据库服务器的CPU有所升高,达到20-30%,Peak time的时候甚至会达到50%。于是我打开性能监视器,发现SQL Logins/sec平均超过2000。那么,我希望知道,是哪些SQL语句调用次数如此频繁,找到了这些SQL语句之后,就可以进行有针对的优化。

Step1:首先我们看看有什么DMV是适用的。在联机丛书的索引中输入动态管理视图 [SQL Server],发现下面有个链接与执行有关的动态管理视图和函数,这里面有一个视图叫sys.dm_exec_query_stats,顾名思义,应该是对执行查询的统计信息。

Step2:看看sys.dm_exec_query_stats的描述和字段,这个对象是返回缓存查询计划的聚合性能统计信息。包含的字段有execution_count,表示计划自上次编译以来所执行的次数。OK,就是它了。

Step3:怎么用这个视图呢?那些列好复杂,一时没有头绪,往下找找看。有了!下面有个示例,按平均 CLR 时间返回有关前五个查询的信息。

 

SELECT TOP 5 creation_time, last_execution_time, total_clr_time,

    total_clr_time
/execution_count AS [Avg CLR Time], last_clr_time,

    execution_count,

    
SUBSTRING(st.text, (qs.statement_start_offset/2+ 1,

    ((
CASE statement_end_offset

        
WHEN -1 THEN DATALENGTH(st.text)

        
ELSE qs.statement_end_offset END

            
- qs.statement_start_offset)/2+ 1as statement_text

FROM sys.dm_exec_query_stats as qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st

ORDER BY total_clr_time/execution_count DESC;

GO

写不出来我总会改吧,照葫芦画瓢可轻松多了。

Step4: 我要做的是把排序条件的平均CLR时间改成平均每秒执行次数。看了一下字段列表,发现3个字段是有用的。creation_time 编译计划的时间。last_execution_time  上次执行计划的时间。execution_count 计划自上次编译以来所执行的次数。我考虑用(last_execution_time - creation_time ) /execution_count来得到平均每秒执行次数。修改后的SQL语句如下

SELECT TOP 5 creation_time, last_execution_time, 

execution_count 
/ datediff(second, creation_time, last_execution_time) AS [Execute Count Per Second],

    execution_count,

    
SUBSTRING(st.text, (qs.statement_start_offset/2+ 1,

    ((
CASE statement_end_offset

        
WHEN -1 THEN DATALENGTH(st.text)

        
ELSE qs.statement_end_offset END

            
- qs.statement_start_offset)/2+ 1as statement_text

FROM sys.dm_exec_query_stats as qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st

ORDER BY execution_count / datediff(second, creation_time, last_execution_time) DESC;

 Step5:执行后发现有点小问题,出现错误信息

Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.

错误信息说被零除,那么是有datediff(second, creation_time, last_execution_time)等于0的。OK,我们加点条件,标准的做法是判断datediff(second, creation_time, last_execution_time)

>0,我判断execution_count > 100,在这个地方也能够达到同样的效果。修改的结果如下:

 

SELECT TOP 5 creation_time, last_execution_time, 

execution_count 
/ datediff(second, creation_time, last_execution_time) AS [Execute Count Per Second],

    execution_count,

    
SUBSTRING(st.text, (qs.statement_start_offset/2+ 1,

    ((
CASE statement_end_offset

        
WHEN -1 THEN DATALENGTH(st.text)

        
ELSE qs.statement_end_offset END

            
- qs.statement_start_offset)/2+ 1as statement_text

FROM sys.dm_exec_query_stats as qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st

WHERE execution_count > 100

ORDER BY execution_count / datediff(second, creation_time, last_execution_time) DESC;

 

Step 6:再调整一下所需的字段,我对creation_time, last_execution_time其实没啥兴趣,我更关心这个SQL语句执行的时间,I/O方面的信息,看了下字段列表,有total_physical_reads 此计划自编译后在执行期间所执行的物理读取总次数,total_elapsed_time 完成此计划的执行所占用的总时间(微秒)。再除以execution_count就是平均值了。修改后的SQL语句如下:

 

SELECT     TOP 100 execution_count / datediff(second, creation_time, last_execution_time) AS [Execute Times Per Second], execution_count, 

total_logical_reads 
/execution_count AS [Avg Logical Reads],

total_elapsed_time 
/execution_count AS [Avg Elapsed Time],

SUBSTRING(st.text, (qs.statement_start_offset / 2+ 1, ((CASE statement_end_offset WHEN - 1 THEN DATALENGTH(st.textELSE qs.statement_end_offset END - qs.statement_start_offset) / 2+ 1AS statement_text

FROM         sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st

WHERE     execution_count > 100

ORDER BY 1 DESC;

 

Step7:OK,差不多能满足我的需求了,但是美中不足的是,我们对数据库的访问全是通过存储过程来实现的,要找到这段SQL语句出自哪个存储过程,虽然不是很麻烦,但是还是要动点手脚的,要是能在这个查询中显示出来就好了。 这个查询中用到了sys.dm_exec_sql_text,看看这里面有啥内容。在联机丛书的索引中输入sys.dm_exec_sql_text。Bingo!对于诸如存储过程、触发器或函数之类的数据库对象,SQL 句柄派生自数据库 ID、对象 ID 和对象编号。太好了,要的就是这个,有了ID就可以转换成名字了,object_name函数是早已熟悉的。最终的结果如下

在实际应用的时候,再根据情况稍做修改即可。希望各位读者从这篇文章学到的不仅是sys.dm_exec_query_stats ,更重要的是了解到使用Books Online的方法。

 

SELECT     TOP 100 execution_count / datediff(second, creation_time, last_execution_time) AS [Execute Count Per Second], execution_count,

total_logical_reads 
/execution_count AS [Avg Logical Reads],

total_elapsed_time 
/execution_count AS [Avg Elapsed Time],

db_name(st.dbid) as [database name],

object_name(st.objectid, st.dbid) as [object name],

SUBSTRING(st.text, (qs.statement_start_offset / 2+ 1, ((CASE statement_end_offset WHEN - 1 THEN DATALENGTH(st.textELSE qs.statement_end_offset END - qs.statement_start_offset) / 2+ 1AS statement_text

FROM         sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st

WHERE     execution_count > 100

ORDER BY 1 DESC;

本文转自:http://blog.myspace.cn/e/400261243.htm?type=0&comment=417931#417931

 

posted @ 2009-11-03 09:18  风影极光  阅读(1227)  评论(0编辑  收藏  举报