SQL Server 2005性能排错(5)
2007-05-15 09:28:24 来源:WEB开发网核心提示: 这套存储过程可以在SQL Server中分析锁。track_waitstats_2005CREATE proc [dbo].[track_waitstats_2005] (@num_samples int=10,@delay_interval int=1,@delay_type nvarc
这套存储过程可以在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
- ››sql server自动生成批量执行SQL脚本的批处理
- ››sql server 2008亿万数据性能优化
- ››SQL Server 2008清空数据库日志方法
- ››sqlserver安装和简单的使用
- ››SQL Sever 2008 R2 数据库管理
- ››SQL SERVER无法安装成功,sqlstp.log文件提示[未发...
- ››Sql Server中通过父记录查找出所有关联的子记录
- ››SqlServer触发器、存储过程和函数
- ››SQL Server 中的事务(含义,属性,管理)
- ››Sqlite数据库插入和读取图片数据
- ››Sql server 2005拒绝了对对象 'xx表' (数...
- ››Sql server 2005拒绝了对对象 'xx表' (数...
更多精彩
赞助商链接