by Andrew J. » Sun, 02 Mar 2008 10:34:11
urprisingly that doesn't look too bad. The links I provided whould get you
atarted on looking into performance issues in general but here are a couple
sps that you can use to view the wait stats in 2000. The first puts a
snapshot into a table. If you call it the first time with a parameter of 1
it will clear out the wait stats counters but that is optional. You need to
take at least 2 snapshots for the report sp to work. Take a snap shot with
the sp then wait an hour and execute it again. Then run the 2nd sp to get a
listing of the waits. One of the links I posted before (
http://sqldev.net/misc/WaitTypes.htm ) will tell you what hey mean.
CREATE PROCEDURE [dbo].[gather_waitstats]
@Clear TINYINT = 0
AS
SET NOCOUNT ON
IF OBJECT_ID('[dbo].[VWaitStats]') IS NULL
BEGIN
CREATE TABLE [dbo].[VWaitStats] (
[wait type] VARCHAR(80),
[requests] NUMERIC(20,1),
[wait time] NUMERIC (20,1),
[signal wait time] NUMERIC(20,1),
[CaptureTime] DATETIME DEFAULT GETDATE()
)
END
IF @Clear = 1
BEGIN
TRUNCATE TABLE [dbo].[VWaitStats]
-- Clear out the waitstats
DBCC SQLPERF (WAITSTATS,CLEAR)
END
INSERT INTO [dbo].[VWaitStats] ([wait type], [requests], [wait time],[signal
wait time])
EXEC ('DBCC SQLPERF(WAITSTATS)')
GO
-------------
CREATE PROCEDURE [dbo].[report_waitstats]
@WithOLEDB TINYINT = 0
AS
SET NOCOUNT ON
DECLARE @Samples INT, @Delay VARCHAR(9), @Now DATETIME, @BeginTime DATETIME,
@EndTime DATETIME
DECLARE @TotalWait NUMERIC(20,1), @FirstSample DATETIME, @SecondSample
DATETIME, @Interval INT
DECLARE @ExcludedWait NUMERIC(20,1)
SET @FirstSample = (SELECT TOP 1 [CaptureTime]
FROM [dbo].[VWaitStats]
ORDER BY [CaptureTime] )
SET @SecondSample = (SELECT TOP 1 [CaptureTime]
FROM [dbo].[VWaitStats]
WHERE [CaptureTime] > @FirstSample
ORDER BY [CaptureTime])
SELECT @Now = MAX(CaptureTime), @BeginTime = MIN(CaptureTime), @EndTime =
MAX(CaptureTime)
FROM [dbo].[VWaitStats]
WHERE [wait type] = 'Total'
SET @Interval = DATEDIFF(mi,@FirstSample,@SecondSample)
SET @Samples = (DATEDIFF(mi,@BeginTime,@EndTime) / @Interval) + 1
SET @Delay = RIGHT('0' + CAST(@Interval / 60 AS VARCHAR(2)),2) + ':' +
RIGHT('0' + CAST(@Interval % 60 AS VARCHAR(2)),2) + ':00'
--- subtract waitfor, sleep, and resource_queue FROM Total
IF @WithOLEDB = 0
BEGIN
SELECT @TotalWait = SUM([wait time]) + 1 FROM [dbo].[VWaitStats]
WHERE [wait type] not in
('WAITFOR','SLEEP','RESOURCE_QUEUE','Total', '***total***','OLEDB') and
CaptureTime = @Now
SELECT @ExcludedWait = SUM([wait time]) FROM [dbo].[VWaitStats]
WHERE [wait type] in ('WAITFOR','SLEEP','RESOURCE_QUEUE','OLEDB')
and CaptureTime = @Now
END
ELSE
BEGIN
SELECT @TotalWait = SUM([wait time]) + 1 FROM [dbo].[VWaitStats]
WHERE [wait type] not in
('WAITFOR','SLEEP','RESOURCE_QUEUE','Total', '***total***') and CaptureTime
= @Now
SELECT @ExcludedWait = SUM([wait time]) FROM [dbo].[VWaitStats]
WHERE [wait type] in ('WAITFOR','SLEEP','RESOURCE_QUEUE') and
CaptureTime = @Now
END
PRINT 'Started at: ' + CONVERT(VARCHAR(20),@BeginTime,20) + ' Ended at:
' +
CONVERT(VARCHAR(20),@EndTime,20)
PRINT ' '
PRINT 'Duration: ' + CONVERT(VARCHAR(10), DATEDIFF(mi,@BeginTime,@EndTime))
+ ' Minutes'
PRINT ' '
PRINT 'Numer of Sa
