使用sql waits阻塞对整体性能的影响
sql server 2000提供了76种等待类型来提供等待报告。sql server 2005提供了多余100个等待类型来跟踪应用程序性能。任何时间1个用户连接在等待时，sql server会累加等待时间。例如应用程序请求资源例如i/o，锁或内存，可以等待资源直到可用。这些等待信息可以跨所有连接将被汇总和分类，所以性能配置可以从给定的负载获得。因此，sql等待类型从应用程序负载或用户观点识别和分类用户（或线程）等待。
这个查询列出了在sql server中前10位的等待。这些等待时累积的，但是你可以使用dbcc sqlperf ([sys.dm_os_wait_stats], clear)重置这个计数器。
select top 10 *
from sys.dm_os_wait_stats
order by wait_time_ms desc下列是输出，要注意几个关键点：
◆一些等待是正常的例如后台线程的等待，例如lazy writer组件。
◆一些会话为获取共享锁等待很长时间
◆信号等待是在一个工作线程获取对资源访问到它被拿到cpu上调度执行这段时间。长时间的信号等待也许意味着很高的cpu争用。
wait_type waiting_tasks_count wait_time_ms max_wait_time_ms signal_wait_time_ms
------------------ -------------------- -------------------- -------------------- -------
lazywriter_sleep 415088 415048437 1812 156
sqltrace_buffer_flush 103762 415044000 4000 0
lck_m_s 6 25016812 23240921 0
writelog 7413 86843 187 406
logmgr_reserve_append 82 82000 1000 0
sleep_bpool_flush 4948 28687 31 15
lck_m_x 1 20000 20000 0
pageiolatch_sh 871 11718 140 15
pageiolatch_up 755 9484 187 0
io_completion 636 7031 203 0为了分析等待状态，你需要获取数据，用于以后分析。附录b提供了2个示例的存储过程。
◆track_waitstats.收集数据渴望的采样数量和采样的时间间隔。这里有一个调用的示例
exec dbo.track_waitstats @num_samples=6
,@delay_interval=30
,@delay_type='s'
,@truncate_history='y'
,@clear_waitstats='y'◆get_waitstats.分析前面步骤收集到的数据。这有一个调用的示例。
exec [dbo].[get_waitstats_2005]
◆spid运行，需要当前不可用的资源。因为资源不可用，在t0时，它移动到资源等待列表。
◆信号指出资源是可用的，所以spid在t1时间移动到可运行队列。
◆spid等候运行状态直到t2，同样的cpu通过可运行队列处理按顺序到达等待。
你可以使用这些存储过程分析资源等待和信号等待，使用这些信息隔离资源争用。
图5 显示了示例的报告。
图5：等待统计分析报告
图5中现实的等待状态分析报告预示了由于阻塞(lck_m_s)和内存分配(resource_semaphore)的性能问题。特定的55%的所有等待是等待共享锁，而43%是由于内存请求。阻塞每个对象的分析将是识别主要的争用点。
监视索引的使用
其他方面的查询性能与dml查询，查询删除，插入和修改数据相关。在指定表上定义更多索引，在需要数据修改时就需要更多的资源。由于锁结合持续事务，时间长的修改操作可以损害并发性。因此在应用程序中使用那个索引就变得非常重要。你能计算出是否在数据库架构上有大量从未使用过的索引存在。
sql server 2005提供了新的sys.dm_db_index_usage_stats动态管理视图显示哪些索引是使用的，和是否他们被用于用户查询或仅用于系统操作。每次执行查询，在这个视图中的列将根据用于执行查询的查询计划将会增加。当sql server启动并运行，数据就被收集了。这个dmv中的数据只是保存在内存中的，没有持久化。所以当sql server实例关闭，数据将会丢失。你可以周期性的获取这个表，并将数据保存用于以后分析。
|||在索引操作被分为用户类型和系统类型。用户类型引用select和insert/delete/update操作。系统类型操作是类似于dbcc这样的命令或ddl命令或是update statistics。每种类别的语句列被区分为：
◆依靠索引的seek操作 (user_seeks or system_seeks)
◆依靠索引的lookup操作(user_lookups or system_lookups)
◆依靠索引的scan操作(user_scans or system_scans)
◆依靠索引的update操作(user_updates or system_updates)
每种访问索引都会记路最后一次访问的时间戳。一个索引本身通过3列识别，database_id，object_id和index_id。然而，index_id=0代表是一个堆表，index_id=1代表时集束索引，反之index_id>1但表是非集束索引。
一个整天运行的数据库应用程序，从sys.dm_db_index_usage_stats中得到的索引访问信息列表将增长。
下列是在sql server 2005使规则和任务的定义：
◆seek: 识别用于访问数据的b树结构数量。不论b树结构只是访问每级只有少量页面来获取一个数据行，还是是表中使用半索引页面读取如几个g数据或百万行的数据。所以我们希望在这个类别有更多的累计。
◆scan: 识别不使用b树索引获取数据的数据表数量。没有任何索引定义的表属于这种情况。有索引定义但是在执行语句查询时并没有使用这些说印的表也属于这种情况。
◆lookup: 识别在一个集束索引通过’seeking’在一个非集束索引上查询数据，2个索引都定义在同一张表上。这种场景描述在sql server 2000中的书签查询。它表现了这样一个场景，非集束索引被用于访问表，并且非集束索引没有覆盖查询的列和索引列没有在where子句定义，sql server将使用非集束索引列的user_seeks值加上使用集束索引列的user_lookups值。这个值能变得很高如果多个非集束索引在这个表上定义。如果依靠集束索引的user_seeks值高，user_lookups的数量也会很高，加上一部分user_seeks也是很高，应该通过将非集束索引大量的高于集束索引。
|||下列dmv查询可以被用于获取在所有数据库中所有对象上关于索引使用信息。
select object_id, index_id, user_seeks, user_scans, user_lookups
from sys.dm_db_index_usage_stats
order by object_id, index_id你能看到下列结果：
object_id index_id user_seeks user_scans user_lookups
------------ ------------- -------------- -------------- -----------------
521690298 1 0 251 123
521690298 2 123 0 0在这种情况有251次查询的执行直接访问数据层表而不使用索引。有123次查询的执行通过使用非集束索引访问表，但是没有覆盖查询选择列表或在where子句指定列，因为我们看到了123次在集束索引的lookup访问。
最有趣的类别着眼于‘user type statement’类型。使用方法指出在‘system category’可以被看作为存在索引的结果。如果索引不存在，它不会更新统计，也不需要检查一致性。因此分析需要着眼于4列显示独立语句的使用或分析用户应用程序。
为了获取从上次sql server启动以来，关于指定表没有使用的索引信息，这种查询将在数据库上下文中执行。
select i.name
from sys.indexes i
where i.object_id=object_id('<table_name>') and
i.index_id not in (select s.index_id
from sys.dm_db_index_usage_stats s
where s.object_id=i.object_id and
i.index_id=s.index_id and
database_id = <dbid> )所有没有被使用的索引仍可以通过下列语句获取信息：
select object_name(object_id), i.name
from sys.indexes i
where i.index_id not in (select s.index_id
from sys.dm_db_index_usage_stats s
where s.object_id=i.object_id and
i.index_id=s.index_id and
database_id = <dbid> )
order by object_name(object_id) asc在这种情况下，表名称和索引名称根据表明排序。
.动态管理视图的真正目的是在长时间运行时观察索引的使用情况。它可以提供视图的快照或查询结果集，将其存储，然后每天比较相应的改变。如果你能识别特殊的索引数月没有使用或者在很长时间没有使用，你可以最终从数据库中删除他们。
总结
更多信息请见：http://www.microsoft.com/sql/
附录a: dbcc memorystatus 描述
这有一些使用dbcc memorystatus命令的信息。可是，一些信息也可以使用动态管理视图(dmvs)获取。
sql server 2000 dbcc memorystatus在http://support.microsoft.com/?id=271624中描述
sql server 2005 dbcc memorystatus 在http://support.microsoft.com/?id=907877中描述
附录b: 阻塞脚本
附录提供在本白皮书中引用的存储过程源代码列表。你可以根据你的需求修改或裁减这些存储过程。
sp_block
create proc dbo.sp_block (@spid bigint=null)
as
-- this stored procedure is provided "as is" with no warranties, and
-- confers no rights.
-- use of included script samples are subject to the terms specified
at -- http://www.microsoft.com/info/cpyright.htm
--
-- t. davidson
-- this proc reports blocks
--1. optional parameter @spid
--
select
t1.resource_type,
'database'=db_name(resource_database_id),
'blk object' = t1.resource_associated_entity_id,
t1.request_mode,
t1.request_session_id,
t2.blocking_session_id
from
sys.dm_tran_locks as t1,
sys.dm_os_waiting_tasks as t2
where
t1.lock_owner_address = t2.resource_address and
t1.request_session_id = isnull(@spid,t1.request_session_id)分析操作的索引统计
|||这套存储过程可以用于分析索引的使用。
get_indexstats
create proc dbo.get_indexstats
(@dbid smallint=-1
,@top varchar(100)=null
,@columns varchar(500)=null
,@order varchar(100)='lock waits'
,@threshold varchar(500)=null)
as
--
-- this stored procedure is provided "as is" with no warranties,
and confers no rights.
-- use of included script samples are subject to the terms specified
at http://www.microsoft.com/info/cpyright.htm
--
-- t. davidson
-- this proc analyzes index statistics including accesses, overhead,
-- locks, blocks, and waits
--
-- instructions: order of execution is as follows:
--(1) truncate indexstats with init_indexstats
--(2) take initial index snapshot using insert_indexstats
--(3) run workload
--(4) take final index snapshot using insert_indexstats
--(5) analyze with get_indexstats
-- @dbid limits analysis to a database
-- @top allows you to specify top n
-- @columns is used to specify what columns from
-- sys.dm_db_index_operational_stats will be included in the report
-- for example, @columns='scans,lookups,waits' will include columns
-- containing these keywords
-- @order used to order results
-- @threshold used to add a threshold,
-- example: @threshold='[block %] > 5' only include if blocking is over 5%
--
------ definition of some computed columns returned
-- [blk %] = percentage of locks that cause blocks e.g. blk% =
100 * lock waits / locks
-- [index usage] = range_scan_count + singleton_lookup_count + leaf_insert_count
-- [nonleaf index overhead]=nonleaf_insert_count +
nonleaf_delete_count + nonleaf_update_count
-- [avg row lock wait ms]=row_lock_wait_in_ms/row_lock_wait_count
-- [avg page lock wait ms]=page_lock_wait_in_ms/page_lock_wait_count
-- [avg page latch wait ms]=page_latch_wait_in_ms/page_latch_wait_count
-- [avg pageio latch wait ms]=page_io_latch_wait_in_ms/page_io_latch_wait_count
-----------------------------------------------------------------------------------------
--- case 1 - only one snapshot of sys.dm_db_operational_index_stats was stored in
--- indexstats. this is an error - return errormsg to user
--- case 2 - beginning snapshot taken, however some objects were not referenced
--- at the time of the beginning snapshot. thus, they will not be in the initial
--- snapshot of sys.dm_db_operational_index_stats, use 0 for starting values.
--- print info msg for informational purposes.
--- case 3 - beginning and ending snapshots, beginning values for all objects and indexes
--- this should be the normal case, especially if sql server is up a long time
-----------------------------------------------------------------------------------------
set nocount on
declare @orderby varchar(100), @where_dbid_is varchar(100), @temp varchar(500),
@threshold_temptab varchar(500)
declare @cmd varchar(max),@col_stmt varchar(500),@addcol varchar(500)
declare @begintime datetime, @endtime datetime, @duration datetime, @mincount int,
@maxcount int
select @begintime = min(now), @endtime = max(now) from indexstats
if @begintime = @endtime
begin
print 'error: indexstats contains only
1 snapshot of sys.dm_db_index_operational_stats'
print 'order of execution is as follows: '
print '(1) truncate indexstats with init_indexstats'
print '(2) take initial index snapshot using insert_indexstats'
print '(3) run workload'
print '(4) take final index snapshot using insert_indexstats'
print '(5) analyze with get_indexstats'
return -99
end
select @mincount = count(*) from indexstats where now = @begintime
select @maxcount = count(*) from indexstats where now = @endtime
if @mincount < @maxcount
begin
print 'infomsg1: sys.dm_db_index_operational_stats only contains entries
for objects referenced since last sql re-cycle'
print 'infomsg2: any newly referenced objects and indexes captured in
the ending snapshot will use 0 as a beginning value'
end
select @top = case
when @top is null then ''
else lower(@top)
end,
@where_dbid_is = case (@dbid)
when -1 then ''
else ' and i1.database_id = ' + cast(@dbid as varchar(10))
end,
--- thresholding requires a temp table
@threshold_temptab = case
when @threshold is null then ''
else ' select * from #t where ' + @threshold
end
--- thresholding requires temp table, add 'into #t' to select statement
select @temp = case (@threshold_temptab)
when '' then ''
else ' into #t '
end
select @orderby=case(@order)
when 'leaf inserts' then 'order by [' + @order + ']'
when 'leaf deletes' then 'order by [' + @order + ']'
when 'leaf updates' then 'order by [' + @order + ']'
when 'nonleaf inserts' then 'order by [' + @order + ']'
when 'nonleaf deletes' then 'order by [' + @order + ']'
when 'nonleaf updates' then 'order by [' + @order + ']'
when 'nonleaf index overhead' then 'order by [' + @order + ']'
when 'leaf allocations' then 'order by [' + @order + ']'
when 'nonleaf allocations' then 'order by [' + @order + ']'
when 'allocations' then 'order by [' + @order + ']'
when 'leaf page merges' then 'order by [' + @order + ']'
when 'nonleaf page merges' then 'order by [' + @order + ']'
when 'range scans' then 'order by [' + @order + ']'
when 'singleton lookups' then 'order by [' + @order + ']'
when 'index usage' then 'order by [' + @order + ']'
when 'row locks' then 'order by [' + @order + ']'
when 'row lock waits' then 'order by [' + @order + ']'
when 'block %' then 'order by [' + @order + ']'
when 'row lock wait ms' then 'order by [' + @order + ']'
when 'avg row lock wait ms' then 'order by [' + @order + ']'
when 'page locks' then 'order by [' + @order + ']'
when 'page lock waits' then 'order by [' + @order + ']'
when 'page lock wait ms' then 'order by [' + @order + ']'
when 'avg page lock wait ms' then 'order by [' + @order + ']'
when 'index lock promotion attempts' then 'order by [' + @order + ']'
when 'index lock promotions' then 'order by [' + @order + ']'
when 'page latch waits' then 'order by [' + @order + ']'
when 'page latch wait ms' then 'order by [' + @order + ']'
when 'pageio latch waits' then 'order by [' + @order + ']'
when 'pageio latch wait ms' then 'order by [' + @order + ']'
else ''
end
if @orderby <> '' select @orderby = @orderby + ' desc'
select
'start time'[email protected],
'end time'[email protected],
'duration (hh:mm:ss:ms)'=convert(varchar(50),
@[email protected],14),
'report'=case (@dbid)
when -1 then 'all databases'
else db_name(@dbid)
end +
case
when @top = '' then ''
when @top is null then ''
when @top = 'none' then ''
else ', ' + @top
end +
case
when @columns = '' then ''
when @columns is null then ''
when @columns = 'none' then ''
else ', include only columns containing ' + @columns
end +
case(@orderby)
when '' then ''
when null then ''
when 'none' then ''
else ', ' + @orderby
end +
case
when @threshold = '' then ''
when @threshold is null then ''
when @threshold = 'none' then ''
else ', threshold on ' + @threshold
end
select @cmd = ' select i2.database_id, i2.object_id, i2.index_id, i2.partition_number '
select @cmd = @cmd +' , begintime=case min(i1.now) when max(i2.now)
then null else min(i1.now) end '
select @cmd = @cmd +' , endtime=max(i2.now) '
select @cmd = @cmd +' into #i '
select @cmd = @cmd +' from indexstats i2 '
select @cmd = @cmd +' full outer join '
select @cmd = @cmd +' indexstats i1 '
select @cmd = @cmd +' on i1.database_id = i2.database_id '
select @cmd = @cmd +' and i1.object_id = i2.object_id '
select @cmd = @cmd +' and i1.index_id = i2.index_id '
select @cmd = @cmd +' and i1.partition_number = i2.partition_number '
select @cmd = @cmd +' where i1.now >= ''' + convert(varchar(100),@begintime, 109) + ''''
select @cmd = @cmd +' and i2.now = ''' + convert(varchar(100),@endtime, 109) + ''''
select @cmd = @cmd + ' ' + @where_dbid_is + ' '
select @cmd = @cmd + ' group by i2.database_id, i2.object_id, i2.index_id,
i2.partition_number '
select @cmd = @cmd + ' select ' + @top + ' i.database_id,
db_name=db_name(i.database_id), object=isnull(object_name(i.object_id),
i.object_id), indid=i.index_id, part_no=i.partition_number '
exec dbo.add_column
@[email protected] out,
@[email protected],
@col_stmt=' ,[leaf inserts]=i2.leaf_insert_count -
isnull(i1.leaf_insert_count,0)'
select @cmd = @cmd [email protected]
exec dbo.add_column
@[email protected] out,
@[email protected],@col_stmt=' ,
[leaf deletes]=i2.leaf_delete_count –
isnull(i1.leaf_delete_count,0)'
select @cmd = @cmd + @addcol
exec dbo.add_column
@[email protected] out,
@[email protected],
@col_stmt=' ,[leaf updates]=i2.leaf_update_count -
isnull(i1.leaf_update_count,0)'
select @cmd = @cmd + @addcol
exec dbo.add_column
@[email protected] out,
@[email protected],
@col_stmt=' ,[nonleaf inserts]=i2.nonleaf_insert_count -
isnull(i1.nonleaf_insert_count,0)'
select @cmd = @cmd + @addcol
exec dbo.add_column
@[email protected] out,
@[email protected],
@col_stmt=' ,[nonleaf deletes]=i2.nonleaf_delete_count -
isnull(i1.nonleaf_delete_count,0)'
select @cmd = @cmd + @addcol
exec dbo.add_column
@[email protected] out,
@[email protected],
@col_stmt=' ,[nonleaf updates]=i2.nonleaf_update_count -
isnull(i1.nonleaf_update_count,0)'
select @cmd = @cmd + @addcol
exec dbo.add_column
@[email protected] out,
@[email protected],
@col_stmt=' ,[nonleaf index overhead]=(i2.nonleaf_insert_count –
isnull(i1.nonleaf_insert_count,0)) + (i2.nonleaf_delete_count -
isnull(i1.nonleaf_delete_count,0)) + (i2.nonleaf_update_count -
isnull(i1.nonleaf_update_count,0))'
select @cmd = @cmd + @addcol
exec dbo.add_column
@[email protected] out,
@[email protected],
@col_stmt=' ,[leaf allocations]=i2.leaf_allocation_count -
isnull(i1.leaf_allocation_count,0)'
select @cmd = @cmd + @addcol
exec dbo.add_column
@[email protected] out,
@[email protected],
@col_stmt=' ,[nonleaf allocations]=i2.nonleaf_allocation_count -
isnull(i1.nonleaf_allocation_count,0)'
select @cmd = @cmd [email protected]
exec dbo.add_column
@[email protected] out,
@[email protected],
@col_stmt=' ,[allocations]=(i2.leaf_allocation_count -
isnull(i1.leaf_allocation_count,0)) + (i2.nonleaf_allocation_count -
isnull(i1.nonleaf_allocation_count,0))'
select @cmd = @cmd [email protected]
exec dbo.add_column
@[email protected] out,
@[email protected],
@col_stmt=' ,[leaf page merges]=i2.leaf_page_merge_count -
isnull(i1.leaf_page_merge_count,0)'
select @cmd = @cmd + @addcol
exec dbo.add_column
@[email protected] out,
@[email protected],
@col_stmt=' ,[nonleaf page merges]=i2.nonleaf_page_merge_count -
isnull(i1.nonleaf_page_merge_count,0)'
select @cmd = @cmd + @addcol
exec dbo.add_column
@[email protected] out,
@[email protected],
@col_stmt=' ,[range scans]=i2.range_scan_count - isnull(i1.range_scan_count,0)'
select @cmd = @cmd + @addcol
exec dbo.add_column
@[email protected] out,
@cols_containing= @columns,
@col_stmt=' ,[singleton lookups]=i2.singleton_lookup_count -
isnull(i1.singleton_lookup_count,0)'
select @cmd = @cmd [email protected]
exec dbo.add_column
@[email protected] out,
@[email protected],
@col_stmt=' ,[index usage]=(i2.range_scan_count -
isnull(i1.range_scan_count,0)) + (i2.singleton_lookup_count -
isnull(i1.singleton_lookup_count,0)) + (i2.leaf_insert_count -
isnull(i1.leaf_insert_count,0))'
select @cmd = @cmd + @addcol
exec dbo.add_column
@[email protected] out,
@[email protected],
@col_stmt=' ,[row locks]=i2.row_lock_count - isnull(i1.row_lock_count,0)'
select @cmd = @cmd + @addcol
exec dbo.add_column
@[email protected] out,
@[email protected],
@col_stmt=' ,[row lock waits]=i2.row_lock_wait_count -
isnull(i1.row_lock_wait_count,0)'
select @cmd = @cmd + @addcol
exec dbo.add_column
@[email protected] out,
@[email protected],
@col_stmt=' ,[block %]=cast (100.0 * (i2.row_lock_wait_count -
isnull(i1.row_lock_wait_count,0)) / (1 + i2.row_lock_count -
isnull(i1.row_lock_count,0)) as numeric(5,2))'
select @cmd = @cmd + @addcol
exec dbo.add_column
@[email protected] out,
@[email protected],
@col_stmt=' ,[row lock wait ms]=i2.row_lock_wait_in_ms -
isnull(i1.row_lock_wait_in_ms,0)'
select @cmd = @cmd + @addcol
exec dbo.add_column
@[email protected] out,
@[email protected],
@col_stmt=' ,[avg row lock wait ms]=cast ((1.0*(i2.row_lock_wait_in_ms -
isnull(i1.row_lock_wait_in_ms,0)))/(1 + i2.row_lock_wait_count -
isnull(i1.row_lock_wait_count,0)) as numeric(20,1))'
select @cmd = @cmd [email protected]
exec dbo.add_column
@[email protected] out,
@[email protected],
@col_stmt=' ,[page locks]=i2.page_lock_count - isnull(i1.page_lock_count,0)'
select @cmd = @cmd [email protected]
exec dbo.add_column
@[email protected] out,
@[email protected],
@col_stmt=' ,[page lock waits]=i2.page_lock_wait_count -
isnull(i1.page_lock_wait_count,0)'
select @cmd = @cmd [email protected]
exec dbo.add_column
@[email protected] out,
@[email protected],
@col_stmt=' ,[page lock wait ms]=i2.page_lock_wait_in_ms -
isnull(i1.page_lock_wait_in_ms,0)'
select @cmd = @cmd [email protected]
exec dbo.add_column
@[email protected] out,
@[email protected],
@col_stmt=' ,[avg page lock wait ms]=cast ((1.0*(i2.page_lock_wait_in_ms -
isnull(i1.page_lock_wait_in_ms,0)))/(1 + i2.page_lock_wait_count -
isnull(i1.page_lock_wait_count,0)) as numeric(20,1))'
select @cmd = @cmd [email protected]
exec dbo.add_column
@[email protected] out,
@[email protected],
@col_stmt=' ,[index lock promotion attempts]=i2.index_lock_promotion_attempt_count -
isnull(i1.index_lock_promotion_attempt_count,0)'
select @cmd = @cmd [email protected]
exec dbo.add_column
@[email protected] out,
@[email protected],
@col_stmt=' ,[index lock promotions]=i2.index_lock_promotion_count -
isnull(i1.index_lock_promotion_count,0)'
select @cmd = @cmd [email protected]
exec dbo.add_column
@[email protected] out,
@[email protected],
@col_stmt=' ,[page latch waits]=i2.page_latch_wait_count -
isnull(i1.page_latch_wait_count,0)'
select @cmd = @cmd [email protected]ol
exec dbo.add_column
@[email protected] out,
@[email protected],
@col_stmt=' ,[page latch wait ms]=i2.page_latch_wait_in_ms -
isnull(i1.page_latch_wait_in_ms,0)'
select @cmd = @cmd [email protected]
exec dbo.add_column
@[email protected] out,
@[email protected],
@col_stmt=' ,[avg page latch wait ms]=cast ((1.0*(i2.page_latch_wait_in_ms -
isnull(i1.page_latch_wait_in_ms,0)))/(1 + i2.page_latch_wait_count -
isnull(i1.page_latch_wait_count,0)) as numeric(20,1))'
select @cmd = @cmd [email protected]
exec dbo.add_column
@[email protected] out,
@[email protected],
@col_stmt=' ,[pageio latch waits]=i2.page_io_latch_wait_count -
isnull(i1.page_latch_wait_count,0)'
select @cmd = @cmd [email protected]
exec dbo.add_column
@[email protected] out,
@[email protected],
@col_stmt=' ,[pageio latch wait ms]=i2.page_io_latch_wait_in_ms -
isnull(i1.page_latch_wait_in_ms,0)'
select @cmd = @cmd [email protected]
exec dbo.add_column
@[email protected] out,
@[email protected],
@col_stmt=' ,[avg pageio latch wait ms]=cast ((1.0*(i2.page_io_latch_wait_in_ms -
isnull(i1.page_io_latch_wait_in_ms,0)))/(1 + i2.page_io_latch_wait_count -
isnull(i1.page_io_latch_wait_count,0)) as numeric(20,1))'
select @cmd = @cmd [email protected]
select @cmd = @cmd + @temp
select @cmd = @cmd + ' from #i i '
select @cmd = @cmd + ' left join indexstats i1 on i.begintime =
i1.now and i.database_id = i1.database_id and i.object_id =
i1.object_id and i.index_id = i1.index_id and i.partition_number =
i1.partition_number '
select @cmd = @cmd + ' left join indexstats i2 on i.endtime =
i2.now and i.database_id = i2.database_id and i.object_id =
i2.object_id and i.index_id = i2.index_id and i.partition_number =
i2.partition_number '
select @cmd = @cmd + ' ' + @orderby + ' '
select @cmd = @cmd + @threshold_temptab
exec ( @cmd )
goinsert_indexstats
|||
create proc insert_indexstats (@dbid smallint=null,
@objid int=null,
@indid int=null,
@partitionid int=null)
as
--
-- this stored procedure is provided "as is" with no warranties,
and confers no rights.
-- use of included script samples are subject to the terms specified
at http://www.microsoft.com/info/cpyright.htm
-- this stored procedure stores a snapshot of sys.dm_db_index_operational_stats
into the table indexstas
-- for later analysis by the stored procedure get_indexstats. please note
that the indexstats table has an additional
-- column to store the timestamp when the snapshot is taken
--
-- t. davidson
-- snapshot sys.dm_db_index_operational_stats
--
declare @now datetime
select @now = getdate()
insert into indexstats
(database_id
,object_id
,index_id
,partition_number
,leaf_insert_count
,leaf_delete_count
,leaf_update_count
,leaf_ghost_count
,nonleaf_insert_count
,nonleaf_delete_count
,nonleaf_update_count
,leaf_allocation_count
,nonleaf_allocation_count
,leaf_page_merge_count
,nonleaf_page_merge_count
,range_scan_count
,singleton_lookup_count
,forwarded_fetch_count
,lob_fetch_in_pages
,lob_fetch_in_bytes
,lob_orphan_create_count
,lob_orphan_insert_count
,row_overflow_fetch_in_pages
,row_overflow_fetch_in_bytes
,column_value_push_off_row_count
,column_value_pull_in_row_count
,row_lock_count
,row_lock_wait_count
,row_lock_wait_in_ms
,page_lock_count
,page_lock_wait_count
,page_lock_wait_in_ms
,index_lock_promotion_attempt_count
,index_lock_promotion_count
,page_latch_wait_count
,page_latch_wait_in_ms
,page_io_latch_wait_count
,page_io_latch_wait_in_ms,
now)
select database_id
,object_id
,index_id
,partition_number
,leaf_insert_count
,leaf_delete_count
,leaf_update_count
,leaf_ghost_count
,nonleaf_insert_count
,nonleaf_delete_count
,nonleaf_update_count
,leaf_allocation_count
,nonleaf_allocation_count
,leaf_page_merge_count
,nonleaf_page_merge_count
,range_scan_count
,singleton_lookup_count
,forwarded_fetch_count
,lob_fetch_in_pages
,lob_fetch_in_bytes
,lob_orphan_create_count
,lob_orphan_insert_count
,row_overflow_fetch_in_pages
,row_overflow_fetch_in_bytes
,column_value_push_off_row_count
,column_value_pull_in_row_count
,row_lock_count
,row_lock_wait_count
,row_lock_wait_in_ms
,page_lock_count
,page_lock_wait_count
,page_lock_wait_in_ms
,index_lock_promotion_attempt_count
,index_lock_promotion_count
,page_latch_wait_count
,page_latch_wait_in_ms
,page_io_latch_wait_count
,page_io_latch_wait_in_ms
,@now
from sys.dm_db_index_operational_stats(@dbid,@objid,@indid,@partitionid)
goinit_index_operational_stats
|||
create proc dbo.init_index_operational_stats
as
--
-- this stored procedure is provided "as is" with no warranties, and
-- confers no rights.
-- use of included script samples are subject to the terms specified at
-- http://www.microsoft.com/info/cpyright.htm
--
-- t. davidson
--
-- create indexstats table if it doesn't exist, otherwise truncate
--
set nocount on
if not exists (select 1 from dbo.sysobjects where
id=object_id(n'[dbo].[indexstats]') and
objectproperty(id, n'isusertable') = 1)
create table dbo.indexstats (
database_id smallint not null
,object_id int not null
,index_id int not null
,partition_number int not null
,leaf_insert_count bigint not null
,leaf_delete_count bigint not null
,leaf_update_count bigint not null
,leaf_ghost_count bigint not null
,nonleaf_insert_count bigint not null
,nonleaf_delete_count bigint not null
,nonleaf_update_count bigint not null
,leaf_allocation_count bigint not null
,nonleaf_allocation_count bigint not null
,leaf_page_merge_count bigint not null
,nonleaf_page_merge_count bigint not null
,range_scan_count bigint not null
,singleton_lookup_count bigint not null
,forwarded_fetch_count bigint not null
,lob_fetch_in_pages bigint not null
,lob_fetch_in_bytes bigint not null
,lob_orphan_create_count bigint not null
,lob_orphan_insert_count bigint not null
,row_overflow_fetch_in_pages bigint not null
,row_overflow_fetch_in_bytes bigint not null
,column_value_push_off_row_count bigint not null
,column_value_pull_in_row_count bigint not null
,row_lock_count bigint not null
,row_lock_wait_count bigint not null
,row_lock_wait_in_ms bigint not null
,page_lock_count bigint not null
,page_lock_wait_count bigint not null
,page_lock_wait_in_ms bigint not null
,index_lock_promotion_attempt_count bigint not null
,index_lock_promotion_count bigint not null
,page_latch_wait_count bigint not null
,page_latch_wait_in_ms bigint not null
,page_io_latch_wait_count bigint not null
,page_io_latch_wait_in_ms bigint not null
,now datetime default getdate())
else truncate table dbo.indexstats
goadd_column
|||create proc dbo.add_column (
@add_stmt varchar(500) output,
@find varchar(100)=null,
@cols_containing varchar(500)=null,
@col_stmt varchar(max))
as
--
-- this stored procedure is provided "as is" with no warranties, and
-- confers no rights.
-- use of included script samples are subject to the terms specified at
-- http://www.microsoft.com/info/cpyright.htm
--
-- t. davidson
-- @add_stmt is the result passed back to the caller
-- @find is a keyword from @cols_containing
-- @cols_containing is the list of keywords to include in the report
-- @col_stmt is the statement that will be compared with @find.
-- if @col_stmt contains @find, include this statement.
-- set @add_stmt = @col_stmt
--
declare @length int, @strindex int, @eos bit
if @cols_containing is null
begin
select @[email protected]_stmt
return
end
select @add_stmt = '', @eos = 0
while @add_stmt is not null and @eos = 0
@dbid=-1,
select @strindex = charindex(',',@cols_containing)
if @strindex = 0
select @find = @cols_containing, @eos = 1
else
begin
select @find = substring(@cols_containing,1,@strindex-1)
select @cols_containing =
substring(@cols_containing,
@strindex+1,
datalength(@cols_containing) - @strindex)
end
select @add_stmt=case
--when @cols_containing is null then null
when charindex(@find,@col_stmt) > 0 then null
else ''
end
end
--- null indicates this statement is to be passed back through out parm @add_stmt
if @add_stmt is null select @[email protected]_stmt
go等待状态
这套存储过程可以在sql server中分析锁。
track_waitstats_2005
create proc [dbo].[track_waitstats_2005] (
@num_samples int=10,
@delay_interval int=1,
@delay_type nvarchar(10)='minutes',
@truncate_history nvarchar(1)='n',
@clear_waitstats nvarchar(1)='y')
as
--
-- this stored procedure is provided "as is" with no warranties, and confers no rights.
-- use of included script samples are subject to the terms specified
at http://www.microsoft.com/info/cpyright.htm
--
-- t. davidson
-- @num_samples is the number of times to capture waitstats, default is 10 times
-- default delay interval is 1 minute
-- delaynum is the delay interval - can be minutes or seconds
-- delaytype specifies whether the delay interval is minutes or seconds
-- create waitstats table if it doesn't exist, otherwise truncate
-- revision: 4/19/05
--- (1) added object owner qualifier
--- (2) optional parameters to truncate history and clear waitstats
set nocount on
if not exists (select 1
from sys.objects
where object_id = object_id ( n'[dbo].[waitstats]') and
objectproperty(object_id, n'isusertable') = 1)
create table [dbo].[waitstats]
([wait_type] nvarchar(60) not null,
[waiting_tasks_count] bigint not null,
[wait_time_ms] bigint not null,
[max_wait_time_ms] bigint not null,
[signal_wait_time_ms] bigint not null,
now datetime not null default getdate())
if lower(@truncate_history) not in (n'y',n'n')
begin
raiserror ('valid @truncate_history values are ''y'' or ''n''',16,1) with nowait
end
if lower(@clear_waitstats) not in (n'y',n'n')
begin
raiserror ('valid @clear_waitstats values are ''y'' or ''n''',16,1) with nowait
end
if lower(@truncate_history) = n'y'
truncate table dbo.waitstats
if lower (@clear_waitstats) = n'y'
-- clear out waitstats
dbcc sqlperf ([sys.dm_os_wait_stats],clear) with no_infomsgs
declare @i int,
@delay varchar(8),
@dt varchar(3),
@now datetime,
@totalwait numeric(20,1),
@endtime datetime,
@begintime datetime,
@hr int,
@min int,
@sec int
select @i = 1
select @dt = case lower(@delay_type)
when n'minutes' then 'm'
when n'minute' then 'm'
when n'min' then 'm'
when n'mi' then 'm'
when n'n' then 'm'
when n'm' then 'm'
when n'seconds' then 's'
when n'second' then 's'
when n'sec' then 's'
when n'ss' then 's'
when n's' then 's'
else @delay_type
end
if @dt not in ('s','m')
begin
raiserror ('delay type must be either ''seconds'' or ''minutes''',16,1) with nowait
return
end
if @dt = 's'
begin
select @sec = @delay_interval % 60, @min = cast((@delay_interval / 60) as int),
@hr = cast((@min / 60) as int)
end
if @dt = 'm'
begin
select @sec = 0, @min = @delay_interval % 60, @hr =
cast((@delay_interval / 60) as int)
end
select @delay= right('0'+ convert(varchar(2),@hr),2) + ':' +
+ right('0'+convert(varchar(2),@min),2) + ':' +
+ right('0'+convert(varchar(2),@sec),2)
if @hr > 23 or @min > 59 or @sec > 59
begin
select 'delay interval and type: ' + convert (varchar(10),@delay_interval) + ','
+ @delay_type + ' converts to ' + @delay
raiserror ('hh:mm:ss delay time cannot > 23:59:59',16,1) with nowait
return
end
while (@i <= @num_samples)
begin
select @now = getdate()
insert into [dbo].[waitstats] (
[wait_type],
[waiting_tasks_count],
[wait_time_ms],
[max_wait_time_ms],
[signal_wait_time_ms],
now)
select
[wait_type],
[waiting_tasks_count],
[wait_time_ms],
[max_wait_time_ms],
[signal_wait_time_ms],
@now
from sys.dm_os_wait_stats
insert into [dbo].[waitstats] (
[wait_type],
[waiting_tasks_count],
[wait_time_ms],
[max_wait_time_ms],
[signal_wait_time_ms],
now)
select
'total',
sum([waiting_tasks_count]),
sum([wait_time_ms]),
0,
sum([signal_wait_time_ms]),
@now
from [dbo].[waitstats]
where now = @now
select @i = @i + 1
waitfor delay @delay
end
--- create waitstats report
execute dbo.get_waitstats_2005
go
exec dbo.track_waitstats @num_samples=6
,@delay_interval=30
,@delay_type='s'
,@truncate_history='y'
,@clear_waitstats='y'get_waitstats_2005
新闻热点
疑难解答
