Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with HTTPS or Subversion.

Download ZIP
branch: master
Fetching contributors…

Cannot retrieve contributors at this time

198 lines (180 sloc) 8.399 kb
/************************************************************************************************
Server Wait Statistics
By Wesley D. Brown
Date 06/27/2011
**Description**
This procedure pulls back server wait statistics so you can analyze possible bottlenecks.
Functions:
**End Discription**
**Change Log**
Bug Fix:
**End Change Log**
************************************************************************************************/
/************************************************************************************************
DROP TABLE dbo.ServerWaits
DROP TABLE dbo.ServerWaitsHistory
************************************************************************************************/
IF NOT EXISTS (SELECT *
FROM dbo.sysobjects
WHERE id = Object_id(N'[dbo].[ServerWaits]')
AND Objectproperty(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[ServerWaits]
(
[RecordID] [INT] IDENTITY(1, 1) NOT NULL,
[ServerName] [VARCHAR] (255) NOT NULL,
[WaitType] [NVARCHAR](60),
[WaitingTasksCount] [BIGINT],
[WaitingTasksCountFromStart] [BIGINT],
[WaitTimeInMilliseconds] [BIGINT],
[WaitTimeInMillisecondsFromStart] [BIGINT],
[MaximumWaitTimeMilliseconds] [BIGINT],
[MaximumWaitTimeMillisecondsFromStart] [BIGINT],
[SignalWaitTimeMilliseconds] [BIGINT],
[SignalWaitTimeMillisecondsFromStart] [BIGINT],
[RecordedDateTime] [DATETIME] NULL,
[IntervalInMilliseconds] [BIGINT] NULL,
[FirstMeasureFromStart] [BIT] NULL
)
END
ELSE
BEGIN
PRINT 'Table ServerWaits already exists'
END
IF NOT EXISTS (SELECT *
FROM dbo.sysobjects
WHERE id = Object_id(N'[dbo].[ServerWaitsHistory]')
AND Objectproperty(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[ServerWaitsHistory]
(
[RecordID] [INT] NOT NULL,
[ServerName] [VARCHAR] (255) NOT NULL,
[WaitType] [NVARCHAR](60),
[WaitingTasksCount] [BIGINT],
[WaitingTasksCountFromStart] [BIGINT],
[WaitTimeInMilliseconds] [BIGINT],
[WaitTimeInMillisecondsFromStart] [BIGINT],
[MaximumWaitTimeMilliseconds] [BIGINT],
[MaximumWaitTimeMillisecondsFromStart] [BIGINT],
[SignalWaitTimeMilliseconds] [BIGINT],
[SignalWaitTimeMillisecondsFromStart] [BIGINT],
[RecordedDateTime] [DATETIME] NULL,
[IntervalInMilliseconds] [BIGINT] NULL,
[FirstMeasureFromStart] [BIT] NULL
)
END
ELSE
BEGIN
PRINT 'Table ServerWaitsHistory already exists'
END
IF EXISTS (SELECT *
FROM dbo.sysobjects
WHERE id = Object_id('[dbo].[GatherServerWaits]')
AND Objectproperty(id, 'isprocedure') = 1)
BEGIN
DROP PROCEDURE gatherserverwaits
END
GO
/*
Uses snapshot of waits to determine what's waiting longest
Some help with wait_type can be found at
http://msdn.microsoft.com/en-us/library/ms179984(v=SQL.105).aspx
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q244455
Best reference found to date at
http://blogs.msdn.com/b/jimmymay/archive/2009/04/26/wait-stats-introductory-references.aspx
http://support.microsoft.com/kb/822101
http://blogs.msdn.com/b/psssql/archive/2009/11/03/the-sql-server-wait-type-repository.aspx
http://sqldev.net/misc/WaitTypes.htm
*/
CREATE PROCEDURE GatherServerWaits (@Duration DATETIME = '01:00:00',
@IntervalInSeconds INT = 120,
@showall BIGINT = 1)
AS
SET nocount ON
DECLARE @StopTime DATETIME,
@LastRecordedDateTime DATETIME,
@CurrentDateTime DATETIME,
@ErrorNumber INT,
@NumberOfRows INT,
@ErrorMessageText NVARCHAR(4000),
@CurrentServerName VARCHAR(255),
@DifferenceInMilliSeconds BIGINT
SELECT @CurrentServerName = CAST(Serverproperty('servername') AS VARCHAR(255))
SET @DifferenceInMilliSeconds = Datediff(ms, CONVERT(DATETIME, '00:00:00', 8),
@Duration)
SELECT @StopTime = Dateadd(ms, @DifferenceInMilliSeconds, Getdate())
IF EXISTS (SELECT 1
FROM dbo.serverwaits)
BEGIN
IF EXISTS (SELECT *
FROM dbo.sysobjects
WHERE id = Object_id(N'[dbo].[ServerWaits]')
AND Objectproperty(id, N'IsTable') = 1)
BEGIN
INSERT INTO dbo.serverwaitshistory
SELECT *
FROM serverwaits;
TRUNCATE TABLE dbo.serverwaits;
END
END
WHILE Getdate() <= @StopTime
BEGIN
SELECT @LastRecordedDateTime = @CurrentDateTime
SELECT @CurrentDateTime = Getdate()
INSERT INTO dbo.serverwaits
(servername,
waittype,
waitingtaskscount,
waitingtaskscountfromstart,
waittimeinmilliseconds,
waittimeinmillisecondsfromstart,
maximumwaittimemilliseconds,
maximumwaittimemillisecondsfromstart,
signalwaittimemilliseconds,
signalwaittimemillisecondsfromstart,
recordeddatetime,
intervalinmilliseconds,
firstmeasurefromstart)
SELECT @CurrentServerName,
ws.wait_type,
ws.waiting_tasks_count - mws.WaitingTasksCountFromStart AS waitingtaskscount,
ws.waiting_tasks_count AS signalwaittimemillisecondsfromstart,
ws.wait_time_ms - mws.WaitTimeInMillisecondsFromStart AS waittimeinmilliseconds,
ws.wait_time_ms AS signalwaittimemillisecondsfromstart,
ws.max_wait_time_ms - mws.MaximumWaitTimeMillisecondsFromStart AS maximumwaittimemilliseconds,
ws.max_wait_time_ms AS signalwaittimemillisecondsfromstart,
ws.signal_wait_time_ms - mws.SignalWaitTimeMillisecondsFromStart AS signalwaittimemilliseconds,
ws.signal_wait_time_ms AS signalwaittimemillisecondsfromstart,
@CurrentDateTime,
CASE
WHEN @LastRecordedDateTime IS NULL THEN NULL
ELSE Datediff(ms, mws.recordeddatetime, @CurrentDateTime)
END AS intervalinmilliseconds,
CASE
WHEN @LastRecordedDateTime IS NULL THEN 1
ELSE 0
END AS firstmeasurefromstart
FROM sys.dm_os_wait_stats ws
LEFT OUTER JOIN dbo.ServerWaits mws
ON ws.wait_type = mws.WaitType
WHERE ( @LastRecordedDateTime IS NULL
OR mws.RecordedDateTime = @LastRecordedDateTime )
-- AND ( ws.wait_time_ms - mws.waittimeinmilliseconds ) >= @showall
SELECT @ErrorNumber = @@ERROR,
@NumberOfRows = @@ROWCOUNT
IF @ErrorNumber != 0
BEGIN
SET @ErrorMessageText = 'Error ' + CONVERT(VARCHAR(10),
@ErrorNumber
)
+
' failed to insert server waits data!'
RAISERROR (@ErrorMessageText,
16,
1) WITH LOG
RETURN @ErrorNumber
END
WAITFOR delay @IntervalInSeconds
END
GO
Jump to Line
Something went wrong with that request. Please try again.