October 15, 2009 at 12:20 pm
Hello everyone, hope we are all having a good day.
Ran accross something interesting today.
My OLTP system currently has parrallelism turn on. On our brand-spanking-new server, that has been up and running since 10/1, I see the following information.
CXPacket total wait time (sec) = 7,086,376.171
Now if I do some very simple math, this works out to
7,086,376.171/60 = 118,106.25 minutes
118,106.25/ 60 = 1968.5 hours
1968.5/24 = 82 days
Now, what I question, is how can a server, which has been up since 10/1 have a total wait time of 82 days, almost 3 months worth, when today is only 10/15.
Thanks,
Fraggle.
October 15, 2009 at 3:12 pm
How many processors do you have on this server?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
October 15, 2009 at 3:14 pm
The ol' magic 8 ball.
October 15, 2009 at 3:32 pm
Fraggle-805517 (10/15/2009)
The ol' magic 8 ball.
So 8 processor cores? No hyperthreading?
7,086,376.171/8 = 885422.02 -- wait time is cumulative over all processors, so divide by 8 to get actual time. Though technically max 7 out of 8 threads could have CXPacket waits at a time.
885,422.02/60 = 14757.03 minutes
14757.03/ 60 = 245.95 hours
245.95/24 = 10.25 days
Still there's something really bad going on if you have CXPacket waits that high
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 15, 2009 at 3:34 pm
Any thoughts on how to go about figuring out what is causing those? Pretty sure that turning off the parallelism option isn't going to solve anything, even though I think it should be off for an OLTP system.
Fraggle
October 15, 2009 at 3:40 pm
Fraggle-805517 (10/15/2009)
Any thoughts on how to go about figuring out what is causing those?
Sub-optimal queries
Poor row estimates by the optimiser.
Use sys.dm_exec_requests to look for queries prone to CXPacket waits and try and optimise them
Pretty sure that turning off the parallelism option isn't going to solve anything, even though I think it should be off for an OLTP system.
It will. CXPacket is a parallel skew wait. It occurs when a query parallels and one some threads finish before others. The early finishers have to wait for the others to catch up. That wait is a CXPacket wait.
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 15, 2009 at 3:43 pm
Sorry Gila, I mistyped. Obviously, the CXPacket will decrease if I change the setting and turn it off. However, then it will go else where. So I am just trading off one thing for another.
I will take a look at the dmv. Didn't realized it shows cxpackets.
Thanks,
Fraggle.
October 15, 2009 at 3:51 pm
Fraggle-805517 (10/15/2009)
Sorry Gila, I mistyped. Obviously, the CXPacket will decrease if I change the setting and turn it off. However, then it will go else where. So I am just trading off one thing for another.I will take a look at the dmv. Didn't realized it shows cxpackets.
Thanks,
Fraggle.
Thank you Gail...
If you have high cxpacket waits, as you know one of the options is to disable parallelism. Since you obviously don't want to do that (why? - is your system an OLAP/reporting system), you can modify the settings to reduce the opportunities for SQL Server to select a parallel plan.
You can decrease the max degree of parallelism setting from default of 0 to 4 (or even 2) and increase the cost threshold for parallelism. Some people increase that to 50, others to 75 and still others leave it alone. I would start by reducing the number of CPU's available and see if that helps. If it does not help enough, then I would start increasing the cost threshold and monitoring to see if that makes a large difference.
And finally, follow up on Gail's advice and work on optimizing the queries before making any configuration changes.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
October 15, 2009 at 3:57 pm
Actually, I would love nothing more than to shut off the parallelism in the system (heck in all of our systems). However, convincing my manager of that is a completely different issue. I believe his line was, "Why would MS ship it with a default of on, if it should be off for an OLTP system?"
Outside of saying MS can be idiodic at time, I couldn't give a valid argument as I am new to this type of issue.
I actually found the issue because we are in the middle of optimizing a number of queries, but this partical system is vastely higher is CXPacket waits than or other system. I am pretty sure, as this server is setup the same as the other servers that it is query issues. Trying to find them is the first part of the puzzle.
Of course it doesn't help in finding the issue when the database is offsite and all of the procs are encrypted. Making life fun for sure.
Fraggle
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply