SQL Server 2005性能排錯(cuò)（5）

2024-08-31 00:50:07

字體：大中小

供稿：網(wǎng)友

　　使用sql waits阻塞對(duì)整體性能的影響

　　sql server 2000提供了76種等待類型來(lái)提供等待報(bào)告。sql server 2005提供了多余100個(gè)等待類型來(lái)跟蹤應(yīng)用程序性能。任何時(shí)間1個(gè)用戶連接在等待時(shí)，sql server會(huì)累加等待時(shí)間。例如應(yīng)用程序請(qǐng)求資源例如i/o，鎖或內(nèi)存，可以等待資源直到可用。這些等待信息可以跨所有連接將被匯總和分類，所以性能配置可以從給定的負(fù)載獲得。因此，sql等待類型從應(yīng)用程序負(fù)載或用戶觀點(diǎn)識(shí)別和分類用戶（或線程）等待。

　　這個(gè)查詢列出了在sql server中前10位的等待。這些等待時(shí)累積的，但是你可以使用dbcc sqlperf ([sys.dm_os_wait_stats], clear)重置這個(gè)計(jì)數(shù)器。

select top 10 *
from sys.dm_os_wait_stats
order by wait_time_ms desc

　　下列是輸出，要注意幾個(gè)關(guān)鍵點(diǎn)：

　　◆一些等待是正常的例如后臺(tái)線程的等待，例如lazy writer組件。

　　◆一些會(huì)話為獲取共享鎖等待很長(zhǎng)時(shí)間

　　◆信號(hào)等待是在一個(gè)工作線程獲取對(duì)資源訪問到它被拿到cpu上調(diào)度執(zhí)行這段時(shí)間。長(zhǎng)時(shí)間的信號(hào)等待也許意味著很高的cpu爭(zhēng)用。

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

　　為了分析等待狀態(tài)，你需要獲取數(shù)據(jù)，用于以后分析。附錄b提供了2個(gè)示例的存儲(chǔ)過程。

本文來(lái)源于網(wǎng)頁(yè)設(shè)計(jì)愛好者web開發(fā)社區(qū)http://www.html.org.cn收集整理，歡迎訪問。

|||

　　◆track_waitstats.收集數(shù)據(jù)渴望的采樣數(shù)量和采樣的時(shí)間間隔。這里有一個(gè)調(diào)用的示例

exec dbo.track_waitstats @num_samples=6
,@delay_interval=30
,@delay_type='s'
,@truncate_history='y'
,@clear_waitstats='y'

　　◆get_waitstats.分析前面步驟收集到的數(shù)據(jù)。這有一個(gè)調(diào)用的示例。

　　exec [dbo].[get_waitstats_2005]

　　◆spid運(yùn)行，需要當(dāng)前不可用的資源。因?yàn)橘Y源不可用，在t0時(shí)，它移動(dòng)到資源等待列表。

　　◆信號(hào)指出資源是可用的，所以spid在t1時(shí)間移動(dòng)到可運(yùn)行隊(duì)列。

　　◆spid等候運(yùn)行狀態(tài)直到t2，同樣的cpu通過可運(yùn)行隊(duì)列處理按順序到達(dá)等待。

　　你可以使用這些存儲(chǔ)過程分析資源等待和信號(hào)等待，使用這些信息隔離資源爭(zhēng)用。

　　圖5 顯示了示例的報(bào)告。

　　圖5：等待統(tǒng)計(jì)分析報(bào)告

　　圖5中現(xiàn)實(shí)的等待狀態(tài)分析報(bào)告預(yù)示了由于阻塞(lck_m_s)和內(nèi)存分配(resource_semaphore)的性能問題。特定的55%的所有等待是等待共享鎖，而43%是由于內(nèi)存請(qǐng)求。阻塞每個(gè)對(duì)象的分析將是識(shí)別主要的爭(zhēng)用點(diǎn)。

　　監(jiān)視索引的使用

　　其他方面的查詢性能與dml查詢，查詢刪除，插入和修改數(shù)據(jù)相關(guān)。在指定表上定義更多索引，在需要數(shù)據(jù)修改時(shí)就需要更多的資源。由于鎖結(jié)合持續(xù)事務(wù)，時(shí)間長(zhǎng)的修改操作可以損害并發(fā)性。因此在應(yīng)用程序中使用那個(gè)索引就變得非常重要。你能計(jì)算出是否在數(shù)據(jù)庫(kù)架構(gòu)上有大量從未使用過的索引存在。

　　sql server 2005提供了新的sys.dm_db_index_usage_stats動(dòng)態(tài)管理視圖顯示哪些索引是使用的，和是否他們被用于用戶查詢或僅用于系統(tǒng)操作。每次執(zhí)行查詢，在這個(gè)視圖中的列將根據(jù)用于執(zhí)行查詢的查詢計(jì)劃將會(huì)增加。當(dāng)sql server啟動(dòng)并運(yùn)行，數(shù)據(jù)就被收集了。這個(gè)dmv中的數(shù)據(jù)只是保存在內(nèi)存中的，沒有持久化。所以當(dāng)sql server實(shí)例關(guān)閉，數(shù)據(jù)將會(huì)丟失。你可以周期性的獲取這個(gè)表，并將數(shù)據(jù)保存用于以后分析。

|||

　　在索引操作被分為用戶類型和系統(tǒng)類型。用戶類型引用select和insert/delete/update操作。系統(tǒng)類型操作是類似于dbcc這樣的命令或ddl命令或是update statistics。每種類別的語(yǔ)句列被區(qū)分為：

　　◆依靠索引的seek操作 (user_seeks or system_seeks)

　　◆依靠索引的lookup操作(user_lookups or system_lookups)

　　◆依靠索引的scan操作(user_scans or system_scans)

　　◆依靠索引的update操作(user_updates or system_updates)

　　每種訪問索引都會(huì)記路最后一次訪問的時(shí)間戳。一個(gè)索引本身通過3列識(shí)別，database_id，object_id和index_id。然而，index_id=0代表是一個(gè)堆表，index_id=1代表時(shí)集束索引，反之index_id>1但表是非集束索引。

　　一個(gè)整天運(yùn)行的數(shù)據(jù)庫(kù)應(yīng)用程序，從sys.dm_db_index_usage_stats中得到的索引訪問信息列表將增長(zhǎng)。

　　下列是在sql server 2005使規(guī)則和任務(wù)的定義：

　　◆seek: 識(shí)別用于訪問數(shù)據(jù)的b樹結(jié)構(gòu)數(shù)量。不論b樹結(jié)構(gòu)只是訪問每級(jí)只有少量頁(yè)面來(lái)獲取一個(gè)數(shù)據(jù)行，還是是表中使用半索引頁(yè)面讀取如幾個(gè)g數(shù)據(jù)或百萬(wàn)行的數(shù)據(jù)。所以我們希望在這個(gè)類別有更多的累計(jì)。

　　◆scan: 識(shí)別不使用b樹索引獲取數(shù)據(jù)的數(shù)據(jù)表數(shù)量。沒有任何索引定義的表屬于這種情況。有索引定義但是在執(zhí)行語(yǔ)句查詢時(shí)并沒有使用這些說印的表也屬于這種情況。

　　◆lookup: 識(shí)別在一個(gè)集束索引通過’seeking’在一個(gè)非集束索引上查詢數(shù)據(jù)，2個(gè)索引都定義在同一張表上。這種場(chǎng)景描述在sql server 2000中的書簽查詢。它表現(xiàn)了這樣一個(gè)場(chǎng)景，非集束索引被用于訪問表，并且非集束索引沒有覆蓋查詢的列和索引列沒有在where子句定義，sql server將使用非集束索引列的user_seeks值加上使用集束索引列的user_lookups值。這個(gè)值能變得很高如果多個(gè)非集束索引在這個(gè)表上定義。如果依靠集束索引的user_seeks值高，user_lookups的數(shù)量也會(huì)很高，加上一部分user_seeks也是很高，應(yīng)該通過將非集束索引大量的高于集束索引。

|||

　　下列dmv查詢可以被用于獲取在所有數(shù)據(jù)庫(kù)中所有對(duì)象上關(guān)于索引使用信息。

select object_id, index_id, user_seeks, user_scans, user_lookups
from sys.dm_db_index_usage_stats
order by object_id, index_id

　　你能看到下列結(jié)果：

object_id　　　 index_id　　user_seeks　　user_scans　　user_lookups
------------　　　------------- -------------- --------------　-----------------
521690298　　　　 1　　　　　　　　　0　　　　　　　　 251　　　　　　　　 123
521690298　　　　 2　　　　　　　　123　　　　　　　　 0　　　　　　　　　　 0

　　在這種情況有251次查詢的執(zhí)行直接訪問數(shù)據(jù)層表而不使用索引。有123次查詢的執(zhí)行通過使用非集束索引訪問表，但是沒有覆蓋查詢選擇列表或在where子句指定列，因?yàn)槲覀兛吹搅?23次在集束索引的lookup訪問。

　　最有趣的類別著眼于‘user type statement’類型。使用方法指出在‘system category’可以被看作為存在索引的結(jié)果。如果索引不存在，它不會(huì)更新統(tǒng)計(jì)，也不需要檢查一致性。因此分析需要著眼于4列顯示獨(dú)立語(yǔ)句的使用或分析用戶應(yīng)用程序。

　　為了獲取從上次sql server啟動(dòng)以來(lái)，關(guān)于指定表沒有使用的索引信息，這種查詢將在數(shù)據(jù)庫(kù)上下文中執(zhí)行。

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> )

　　所有沒有被使用的索引仍可以通過下列語(yǔ)句獲取信息：

|||

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

　　在這種情況下，表名稱和索引名稱根據(jù)表明排序。

　　.動(dòng)態(tài)管理視圖的真正目的是在長(zhǎng)時(shí)間運(yùn)行時(shí)觀察索引的使用情況。它可以提供視圖的快照或查詢結(jié)果集，將其存儲(chǔ)，然后每天比較相應(yīng)的改變。如果你能識(shí)別特殊的索引數(shù)月沒有使用或者在很長(zhǎng)時(shí)間沒有使用，你可以最終從數(shù)據(jù)庫(kù)中刪除他們。

　　總結(jié)

　　更多信息請(qǐng)見：http://www.microsoft.com/sql/

　　附錄a: dbcc memorystatus 描述

　　這有一些使用dbcc memorystatus命令的信息。可是，一些信息也可以使用動(dòng)態(tài)管理視圖(dmvs)獲取。

　　sql server 2000 dbcc memorystatus在http://support.microsoft.com/?id=271624中描述

　　sql server 2005 dbcc memorystatus 在http://support.microsoft.com/?id=907877中描述

　　附錄b: 阻塞腳本

　　附錄提供在本白皮書中引用的存儲(chǔ)過程源代碼列表。你可以根據(jù)你的需求修改或裁減這些存儲(chǔ)過程。

　　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)

　　分析操作的索引統(tǒng)計(jì)

|||

　　這套存儲(chǔ)過程可以用于分析索引的使用。

　　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 )
go

　　insert_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)
go

　　init_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
go

　　add_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

　　等待狀態(tài)

網(wǎng)站運(yùn)營(yíng)seo文章大全

提供全面的站長(zhǎng)運(yùn)營(yíng)經(jīng)驗(yàn)及seo技術(shù)!

|||

　　這套存儲(chǔ)過程可以在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'