October 23, 2011 at 7:50 am
Hi ,
i just upgarded my sql 2000 to sql server 2008 x64 ent edition cluster and have found some DMVs script can help me to understand the type of waits i have on SQL 2008 to measuer the sql perfromance after upgarde.. but after review all waits type "which seems fine (i am not suer)" i'd like to share it with you in case i have to pay more attention to some numbers....
here is the script i use
WITH Waits AS
(
SELECT
wait_type,
wait_time_ms /1000. AS wait_time_s,
100. * wait_time_ms / SUM(wait_time_ms)OVER()AS pct,
ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn
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')
)
--filter out additional irrelevant waits
SELECT
W1.wait_type,
CAST(W1.wait_time_s AS DECIMAL(12,2))AS wait_time_s,
CAST(W1.pct AS DECIMAL(12,2))AS pct,
CAST(SUM(W2.pct)AS DECIMAL(12,2))AS running_pct
FROM Waits AS W1
JOIN Waits AS W2
ON W2.rn<= W1.rn
GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct
HAVING SUM(W2.pct)-W1.pct < 90-- percentage threshold
ORDER BY W1.rn;
and here is the result ...
ONDEMAND_TASK_QUEUE 1364504.26 23.90 23.90
BACKUPIO 1187464.89 20.79 44.69
BACKUPTHREAD 1155758.78 20.24 64.93
OLEDB 5 02883.75 8.81 73.74
TRACEWRITE 377205.73 6.61 80.34
LCK_M_U 339362.33 5.94 86.28
CXPACKET 261092.40 4.57 90.86
First Column "wait_type"
Seconde Column "wait_time_s"
third column "pct"
fourth column "running_pct"
would be really appriciated if anyone can support me to analysis above values
October 24, 2011 at 7:23 am
Take a look at this URL and you can see if there is anything to be worried about. So far I do not see anything outwardly to be concerned about.
-Roy
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply