数据库(性能)管理及脚本总结

在确定查询性能方面有三个有用的测量：实耗时间、I/O和CPU

 

一. 找到实耗时间的三种方法:

1. 查看SQL查询视窗的右下角显示的时间;

2. SELECT语句之前3. 循环,大型脚本或存储过程要找出哪一部分运行时间最长, 可以用以下语句:

declare @X int; declare @start datetime; set @X=1; set @start=getdate(); while @X<10000 set @X=@X+1; print 'Time for first loop in ms: ' + cast(DateDiff(ms,@start,getdate()) as char(10)); set @start=getdate(); while @X<100000 set @X=@X+1; print 'Time for second loop in ms: ' + cast(DateDiff(ms,@start,getdate()) as char(10));

 

二. 测量CPU时间的三个方法;

1. 执行“SET STATISTICS TIME ON”语句;

2. 这个方法会受SQL Server上运行的其它东西影响, 如果你有一个具有较少活动的单独系统，那么它仍然是一个测量CPU消耗的方法。

declare @X int; declare @cpu_start int; set @X=1; set @cpu_start=@@cpu_busy; while @X<10000 set @X=@X+1; print 'CPU cost for loop1 in ms: ' + cast((@@cpu_busy - @cpu_start)*@@timeticks/1000 as char); set @cpu_start=@@cpu_busy; while @X<100000 set @X=@X+1; print 'CPU cost for loop2 in ms: ' + cast((@@cpu_busy - @cpu_start)*@@timeticks/1000 as char);

3. 一个更精确测量CPU使用的方法是使用SQL Server profiler.

 

三. I/O

当你调整你的查询时，你要将执行以生成一个结果集的逻辑I/O和物理I/O操作的数目降低到最小.

1. 使用“SET STATISTICS IO ON”语句;

除去因为你的查询已经存在于缓冲器缓存中而在页面请求时会发生的I/O计数差异，你可以在运行每一个测试之前执行“DBCC DROPCLEANBUFFER”命令; 这将使你的查询运行时具有一个干净的缓冲池而不必停止和重启SQL server。

2. 使用SQL Server profiler.

 

四. 脚本

1. 找出开销最大的前 50个查询

SELECT TOP 50 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1, ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(qt.TEXT) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)+1), qs.execution_count, qs.total_logical_reads, qs.last_logical_reads, qs.total_logical_writes, qs.last_logical_writes, qs.total_worker_time, qs.last_worker_time, qs.total_elapsed_time/1000000 total_elapsed_time_in_S, qs.last_elapsed_time/1000000 last_elapsed_time_in_S, qs.last_execution_time, qp.query_plan FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp -- ORDER BY qs.total_logical_reads DESC -- logical reads -- ORDER BY qs.total_logical_writes DESC -- logical writes -- ORDER BY qs.last_worker_time DESC -- ORDER BY qs.total_worker_time DESC -- CPU time

 

2. 显示每一个存储过程执行了多少逻辑 I / O操作来衡量它的性能.

    查看avg_logical_reads列，你可以了解到哪些存储过程的效率比较低。

(dbid=32767, 表示系统数据库, 即资源数据库)

微软把函数，扩展存储过程，以及CLR存储过程归为一个名为" Proc "的 对象类型, 有时候多个计划存在于同一储存过程的程序缓存中.

因此需要做where部分的处理.

SELECT CASE when dbid = 32767 then 'Resource' else DB_NAME(dbid) end [DB_NAME], OBJECT_SCHEMA_NAME(objectid,dbid) AS [SCHEMA_NAME], OBJECT_NAME(objectid,dbid)AS [OBJECT_NAME], SUM(usecounts) AS [Use_Count], SUM(total_logical_reads) AS [total_logical_reads], SUM(total_logical_reads) / SUM(usecounts) * 1.0 AS [avg_logical_reads], dbid, objectid FROM sys.dm_exec_cached_plans cp CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) JOIN (SELECT SUM(total_logical_reads) AS [total_logical_reads], plan_handle FROM sys.dm_exec_query_stats GROUP BY plan_handle) qs ON cp.plan_handle = qs.plan_handle WHERE 对象类型 = 'Proc' AND UPPER( -- remove white space first REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE(text,' ',' '), ' ',' '), ' ',' '), ' ', ' '), ' ',' '), ' ',' '), ' ',' ') ) LIKE '%CREATE PROC%' GROUP BY dbid, objectid ORDER BY SUM(total_logical_reads) / SUM(usecounts) * 1.0 DESC;

 

3. 计算每个存储过程平均耗费时间, 或者说执行时, 用户的等待时间

SELECT CASE when dbid = 32767 then 'Resource' else DB_NAME(dbid) end [DB_NAME], OBJECT_SCHEMA_NAME(objectid,dbid) AS [SCHEMA_NAME], OBJECT_NAME(objectid,dbid)AS [OBJECT_NAME], SUM(usecounts) AS [Use_Count], SUM(total_elapsed_time) AS [total_elapsed_time], SUM(total_elapsed_time) / SUM(usecounts) * 1.0 AS [avg_elapsed_time], substring(convert(char(23),DATEADD(ms,sum(total_elapsed_time)/1000,0),121),12,23) total_elapsed_time_ms, dbid, objectid FROM sys.dm_exec_cached_plans cp CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) JOIN (SELECT SUM(total_elapsed_time) AS [total_elapsed_time], plan_handle FROM sys.dm_exec_query_stats GROUP BY plan_handle) qs ON cp.plan_handle = qs.plan_handle WHERE objtype = 'Proc' AND UPPER( -- remove white space first REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE(text,' ',' '), ' ',' '), ' ',' '), ' ', ' '), ' ',' '), ' ',' '), ' ',' ') ) LIKE '%CREATE PROC%' GROUP BY dbid, objectid ORDER BY SUM(total_elapsed_time) / SUM(usecounts) * 1.0 DESC;

 

4.查看索引体积

select segment_name, bytes, blocks, extents from user_segments where segment_name='IDX_T_CMP2'