March 14, 2014 at 10:16 am
Hi Everybody
I am aware of the importance of 'Wait stats' as a general starting point for performance trouble shooting in sql server, and so to that end I run a job every night that loads the wait stats into a table.
The 2 questions I have are
1. Is this the best method from a time scale point of view or would I be better doing this at the end of a week instead of every night ?
2. The query I run to collect the wait stats is scheduled at the end of the working day, so would it be collecting waits 'as is at the time of collection' . In other words because there would be not the same load on the server at that time when the job runs, is it therefore a true representation of any pain points I may have.
The query I use is listed below....
INSERT INTO database_admin.dbo.WaitStats
( [WaitType]
)
VALUES ( 'Wait Statistics for ' + CAST(GETDATE() AS NVARCHAR(19))
);
INSERT INTO database_admin.dbo.WaitStats
( [CaptureDate] ,
[WaitType] ,
[Wait_S] ,
[Resource_S] ,
[Signal_S] ,
[WaitCount] ,
[Percentage] ,
[AvgWait_S] ,
[AvgRes_S] ,
[AvgSig_S]
)
EXEC
( '
WITH [Waits] AS
(SELECT
[wait_type],
[wait_time_ms] / 1000.0 AS [WaitS],
([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS],
[signal_wait_time_ms] / 1000.0 AS [SignalS],
[waiting_tasks_count] AS [WaitCount],
100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage],
ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum]
FROM sys.dm_os_wait_stats
WHERE [wait_type] NOT IN (
N''CLR_SEMAPHORE'', N''LAZYWRITER_SLEEP'',
N''RESOURCE_QUEUE'', N''SQLTRACE_BUFFER_FLUSH'',
N''SLEEP_TASK'', N''SLEEP_SYSTEMTASK'',
N''WAITFOR'', N''HADR_FILESTREAM_IOMGR_IOCOMPLETION'',
N''CHECKPOINT_QUEUE'', N''REQUEST_FOR_DEADLOCK_SEARCH'',
N''XE_TIMER_EVENT'', N''XE_DISPATCHER_JOIN'',
N''LOGMGR_QUEUE'', N''FT_IFTS_SCHEDULER_IDLE_WAIT'',
N''BROKER_TASK_STOP'', N''CLR_MANUAL_EVENT'',
N''CLR_AUTO_EVENT'', N''DISPATCHER_QUEUE_SEMAPHORE'',
N''TRACEWRITE'', N''XE_DISPATCHER_WAIT'',
N''BROKER_TO_FLUSH'', N''BROKER_EVENTHANDLER'',
N''FT_IFTSHC_MUTEX'', N''SQLTRACE_INCREMENTAL_FLUSH_SLEEP'',
N''DIRTY_PAGE_POLL'', N''ONDEMAND_TASK_QUEUE'')
)
SELECT
GETDATE(),
[W1].[wait_type] AS [WaitType],
CAST ([W1].[WaitS] AS DECIMAL(14, 2)) AS [Wait_S],
CAST ([W1].[ResourceS] AS DECIMAL(14, 2)) AS [Resource_S],
CAST ([W1].[SignalS] AS DECIMAL(14, 2)) AS [Signal_S],
[W1].[WaitCount] AS [WaitCount],
CAST ([W1].[Percentage] AS DECIMAL(4, 2)) AS [Percentage],
CAST (([W1].[WaitS] / [W1].[WaitCount]) AS DECIMAL (14, 4)) AS [AvgWait_S],
CAST (([W1].[ResourceS] / [W1].[WaitCount]) AS DECIMAL (14, 4)) AS [AvgRes_S],
CAST (([W1].[SignalS] / [W1].[WaitCount]) AS DECIMAL (14, 4)) AS [AvgSig_S]
FROM [Waits] AS [W1]
INNER JOIN [Waits] AS [W2]
ON [W2].[RowNum] <= [W1].[RowNum]
GROUP BY [W1].[RowNum], [W1].[wait_type], [W1].[WaitS],
[W1].[ResourceS], [W1].[SignalS], [W1].[WaitCount], [W1].[Percentage]
HAVING SUM ([W2].[Percentage]) - [W1].[Percentage] < 95;'
);
GO
March 14, 2014 at 11:18 am
No it's won't be of any good use as you are doing this Off-Hours .. You should try to get metrics during the busy part of the day to have a meaningful representation.
--
SQLBuddy
March 14, 2014 at 12:19 pm
sqlbuddy123 (3/14/2014)
No it's won't be of any good use as you are doing this Off-Hours .. You should try to get metrics during the busy part of the day to have a meaningful representation.--
SQLBuddy
I disagree since the wait stats are cumulative since the last restart of SQL Server you will get the delta between each day. This will help you identify changes by day, week, month, etc. The down side will be that you are at the day level which means that you may have DW loads or reporting sprocs running "off hours" that may skew the results from the "business" hours. You may want to take a snapshot of the wait stats right before you busy hours and then again after business hours, but it is up to you to determine what you want to capture and report on.
March 14, 2014 at 12:58 pm
I was just answering to 2 Qns. Should have mentioned the approach too.
Keith Tate (3/14/2014)
You may want to take a snapshot of the wait stats right before you busy hours and then again after business hours, but it is up to you to determine what you want to capture and report on.
I disagree. This would yield skewed results as the busy hours needn't extend towards the after business hours. The delta should span the busy hours window.
--
SQLBuddy
March 14, 2014 at 1:40 pm
Pearl I'm using the exact same code in a scheduled job every day;the job captures the current wait stats, then resets them with the dbcc perf command;
i'm thinking that over time, that will give me a baseline so i can have a better feel of my waits as well.
Lowell
March 14, 2014 at 4:37 pm
Lowell - should i be resetting the wait stats dmv's at the start of the day ? I don't think I currently take that step !
The job runs right at the end of the day before any dataloads- so the server is just pretty idle at the point the query runs to gather the wait stats - are they the days wait stats though or just the wait stats at that exact moment ie nothing running on the server.
I was also under the impression that wait stats are indeed culmaltive - so I'm thinking the job is gathering the stats since the last server restart.
Thanks:-)
March 15, 2014 at 4:16 am
Since the wait stats are cumulative, it doesn't matter what time of day you capture them as long as you capture them the same time every day. Then you just have to be able to query to see the differences from day to day, today to last week or last month, etc. This is a fine approach.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 15, 2014 at 5:15 am
Grant - thanks for clearing that up.
Thanks everybody !!:-)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply