high level, why your queries are waiting and thus giving suboptimal performance.
Running the SQL script shown here will identify the top 10 causes of waiting on your server instance.
Use SSRS/Crystal Reports or .NET to create a nice dashboard showing this data in a friendly manner this will help you visualize the top offender(s) wait type(s).
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
WITH Waits AS
(SELECT TOP
10
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 (
'CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE', 'SLEEP_TASK',
'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH',
'WAITFOR', 'LOGMGR_QUEUE',
'CHECKPOINT_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH',
'XE_TIMER_EVENT',
'BROKER_TO_FLUSH',
'BROKER_TASK_STOP', 'CLR_MANUAL_EVENT', 'CLR_AUTO_EVENT', 'DISPATCHER_QUEUE_SEMAPHORE',
'FT_IFTS_SCHEDULER_IDLE_WAIT',
'XE_DISPATCHER_WAIT',
'XE_DISPATCHER_JOIN',
'BROKER_EVENTHANDLER',
'TRACEWRITE', 'FT_IFTSHC_MUTEX', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
'BROKER_RECEIVE_WAITFOR',
'ONDEMAND_TASK_QUEUE',
'DBMIRROR_EVENTS_QUEUE',
'DBMIRRORING_CMD', 'BROKER_TRANSMITTER', 'SQLTRACE_WAIT_ENTRIES',
'SLEEP_BPOOL_FLUSH', 'SQLTRACE_LOCK')
)
SELECT TOP 10
W1.wait_type AS WaitType,
CAST (W1.Percentage AS DECIMAL(4, 2)) AS Percentage
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;
No comments:
Post a Comment