October 18, 2011 at 1:14 pm
Hi,
I don't think I'm ready to post a full request for advice on troubleshooting a performance issue we're having, but I did run one DMV query and am curious to know whether anyone here thinks it really does point in the direction of examining CXPACKET waits. I know there is a whole page with instructions on how to post performance problems, but at this point I'm not ready for that, and I'm not asking for more than an initial review of this DMV result before I investigate further.
This page
http://www.mssqltips.com/sqlservertip/1949/sql-server-sysdmoswaitstats-dmv-queries/
has this query which neatly summarizes the percentage contribution of various wait types.
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',
'CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT')
) -- 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
INNER 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 < 95; -- percentage threshold;
My query gave the results in the attached Excel file (Build: Microsoft SQL Server 2005 - 9.00.3080.00 (X64)). CXPACKET was by far the biggest percentage (almost 71%):
wait_typewait_time_spctrunning_pct
CXPACKET3170462.1170.9270.92
I'd be grateful for any advice on how to proceed, including whether or not to use this result as a troubleshooting guide, and, if so, how I might go about using it for that purpose.
Thanks,
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
October 18, 2011 at 1:21 pm
CXPacket waits mean that queries are running in parallel. That's all.
If you have high CXPacket waits for a query, the trick is to find what the one thread that doesn't have a CXPacket wait is waiting on.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 19, 2011 at 6:15 am
What are the 2nd & 3rd highest wait types. Unless you're sure that parallelism is actually a problem on your system (probably evidenced by CPU binding), this is not necessarily any issue at all.
"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
October 19, 2011 at 7:17 am
Grant Fritchey (10/19/2011)
What are the 2nd & 3rd highest wait types. Unless you're sure that parallelism is actually a problem on your system (probably evidenced by CPU binding), this is not necessarily any issue at all.
Thanks,
The second and third wait types are as follows (10.38% and 5.36%):
BROKER_TASK_STOP464081.310.3881.3
ASYNC_NETWORK_IO239710.135.3686.66
The gap between first and second is quite large -- not sure if that is significant.
Thanks again,
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
October 19, 2011 at 10:39 am
Broker task is a system wait that should be ignored.
Async network IO typically means that the client app is being slow in consuming the resultset.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 19, 2011 at 12:11 pm
Sorry, I was slow getting back here. Gail's answering everything. Just out of curiosity, what is the cost threshold for parallelism on your server?
"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
October 20, 2011 at 6:31 am
Grant Fritchey (10/19/2011)
Sorry, I was slow getting back here. Gail's answering everything. Just out of curiosity, what is the cost threshold for parallelism on your server?
This is what he meant for you to run 😉 :
Whatever you do, don't change any settings, just post back the script results.
SELECT
optz.counter
, optz.occurrence
, CONVERT(DECIMAL(18,2), optz.value) AS AvgValue
, conf.name
, conf.value
, conf.value_in_use
, Uptime.DaysUptime AS [Days Server UPTIME & Last Stats Reset]
, CASE WHEN Uptime.DaysUptime < 45 THEN 'You may not have very meaningful stats because of a recent restart' ELSE NULL END AS [Stats Warning]
, CASE WHEN optz.value < conf.value THEN 'Cost threshold for parallelism might be too low' ELSE NULL END AS [Cost Threshold Warning]
, CASE WHEN conf.value_in_use <> conf.value THEN 'Server needs to be restarted for the setting to take effect' ELSE NULL END [Restart Warning]
FROM
sys.dm_exec_query_optimizer_info optz
CROSS JOIN sys.configurations conf
OUTER APPLY(
SELECT
CONVERT(DECIMAL(18 , 2) , DATEDIFF(hh , create_date , GETDATE()) / 24.0) AS DaysUptime
FROM
sys.databases
WHERE
name = 'tempdb'
) Uptime
WHERE
optz.counter = 'final cost'
AND conf.name = 'cost threshold for parallelism'
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply