Sql Server2005性能

 

Sql Server2005性能诊断

enjoyasp.net sql server 1 Comment 发表评论

引用：http://technet.microsoft.com/zh-cn/library/cc966540(en-us).aspx
Sql Server速度变慢问题主要由三个方面引起，可从这三个方面入手分析问题
  1，资源瓶颈：CPU、I/O、内存等。
  2，临时表瓶颈：tempdb各个数据库共用，资源紧张时会引起性能降低。
  3，用户比较耗时的sql查询。
 
一，CPU瓶颈分析。瓶颈症状：打开perfmon, 监视 processor:% Processor Time若长期>80%，或者任务管理器，说明可能是CPU原因。
1，分析是否是查询语句引起
 这里涉及一个动态视图：sys.dm_exec_query_stats，返回每条查询语句运行的时间
关键栏位有：
 1）sql_handle:表示包含查询的批查询或存储过程的标记,若sql_handle相同，代表查询在同个批处理或同一个存储过程内。可将此值传入    sys.dm_exec_sql_text中获取查询语句。同个存储过程或批处理语句的sql_handle相同，如何分辨是哪个查询语句得到了执行，
可用statement_start_offset、statement_end_offset来查出此查询语句的真容。
2）statement_start_offset：查询在其批查询或持久化对象文本中的开始位置
3）statement_end_offset：查询在其批查询或持久化对象文本中的结束位置
4）execution_count： 计划自上次编译以来所执行的次数。
5）total_worker_time：此计划自编译以来执行所用的 CPU 时间总量（以微秒为单位报告，但仅精确到毫秒）。
6）last_execution_time：上次开始执行计划的时间。
故分析耗CPU语句方法可得出：
 
SELECT TOP 50
    qs.total_worker_time/qs.execution_count/1000. as [平均消耗CPU 时间(ms)],
    total_worker_time/1000 AS [总消耗CPU 时间(ms)],execution_count [运行次数],
    SUBSTRING(qt.text,qs.statement_start_offset/2+1,
        (case when qs.statement_end_offset = -1
        then DATALENGTH(qt.text)
        else qs.statement_end_offset end -qs.statement_start_offset)/2 + 1)
    as [查询语句], qt.text [所在存储过程],
    qt.dbid, dbname=db_name(qt.dbid),
    qt.objectid,object_name(qt.objectid,qt.dbid) ObjectName
 
FROM sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt
--WHERE qs.last_execution_time >='2011-08-28 10:00' 限定时间
ORDER BY
        [平均消耗CPU 时间(ms)] DESC
 
注：测试sys.dm_exec_query_stats 时可先清除缓存DBCC FREEPROCCACHE 再执行查询语句分析。
 
2，分析是否是重新编译引起，重新编译比较费时。
1）perfmon:
 
    SQL Server: SQL Statistics: Batch Requests/sec :每秒钟接收的请求数
 
    SQL Server: SQL Statistics: SQL Compilations/sec：每秒钟的编译数
 
    SQL Server: SQL Statistics: SQL Recompilations/sec：每秒钟的重新编译数
 
2）profile:SP:Recompile / SQL:StmtRecompile.
3）sys.dm_exec_query_stats 有一个栏位plan_generation_num，计划编译次数，可用此来分析最常编译的计划。
select top 25
    plan_generation_num,
    SUBSTRING(qt.text,qs.statement_start_offset/2+1,
        (case when qs.statement_end_offset = -1
        then DATALENGTH(qt.text)
        else qs.statement_end_offset end -qs.statement_start_offset)/2 + 1)
        as stmt_executing,
    qt.text,
    execution_count,
    sql_handle,
    dbid,
    db_name(dbid) DBName,
    objectid,
    object_name(objectid,dbid) ObjectName
from sys.dm_exec_query_stats as qs
    Cross apply sys.dm_exec_sql_text(sql_handle) qt
where plan_generation_num >1
--AND qs.last_execution_time >='2011-08-28 10:00' 限定时间
order by plan_generation_num desc
 
 
 
二，内存瓶颈分析。瓶颈症状：打开perfmon, 监视 Paging File: %Usage长期>80%。Paging File:Usage: 分页空间使用百分率
 
三，I/O瓶颈分析：症状：计数器PhysicalDisk:%Disk Time >80% , Avg.Disk Queue Length:>2
1，%Disk Time :所选磁盘驱动器忙于为读或写入请求提供服务所用的时间的百分比。
Avg. Disk Queue Length 指读取和写入请求(为所选磁盘在实例间隔中列队的)的平均数。
不过，若出现上述情况，也可以是内存不足引起。
 
2，sys.dm_exec_query_stats 有栏位total_logical_writes：此计划自编译后在执行期间所执行的逻辑写入总次数。total_logical_reads：此计划自编译后在执行期间所执行的逻辑写入总次数。
故查询最耗I/O的语句为：
 
select top 50
(total_logical_reads/execution_count) as [平均逻辑读取次数],
(total_logical_writes/execution_count) as [平均逻辑写入次数],
(total_physical_reads/execution_count) as [平均对象读取次数],
 Execution_count 运行次数,
substring(qt.text,r.statement_start_offset/2+1,
(case when r.statement_end_offset = -1
then datalength(qt.text)
else r.statement_end_offset end - r.statement_start_offset)/2+1) [运行语法]
from sys.dm_exec_query_stats  as r
    cross apply sys.dm_exec_sql_text(r.sql_handle) as qt
--WHERE r.last_execution_time >='2011-08-28 10:00' 限定时间
order by
 (total_logical_reads + total_logical_writes) Desc
 
四，tempdb瓶颈。空间资源耗尽引起。
查询：
Select
    SUM (user_object_reserved_page_count)*8 as user_objects_kb,
    SUM (internal_object_reserved_page_count)*8 as internal_objects_kb,
    SUM (version_store_reserved_page_count)*8  as version_store_kb,
    SUM (unallocated_extent_page_count)*8 as freespace_kb
From sys.dm_db_file_space_usage
Where database_id = 2
其中：freespace_kb  要足够大才可。