October 16, 2008 at 8:40 am
cost threshold is set to 5
Oraculum
October 16, 2008 at 8:45 am
If you do nothing else at all, I'd recommend bumping that up to something like 25 or 30. I've seen it set as high as 50 on servers that still saw a benefit from parallel processing. Parallelism can benefit some queries, but it comes at a fairly steep cost. Telling the optimizer that a query that it thinks might take more than 5 seconds can use parallelism leads to queries that the marshalling cost far outweighs the performance benefits.
I'm not saying that's your issue. I'm leaning towards statistics and some bad execution plans since this happened suddenly. But you're clearly hitting some issues with parallelism.
"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 16, 2008 at 9:02 am
some of my rs reports - cpu was normal all weekend and all day monday... then its starts to become eractic, the resting sql CPU is 10% which is unusual and total CPU is another 10% above it..
**There are alot of gaps in the 2nd report, as i had to stop idera dm to diagnose whether it was causing the problems on the server.
Oraculum
October 16, 2008 at 7:07 pm
oraculum (10/16/2008)
here are the results from the two querys...am wondering whether to restart sql agent....umm..
have also just noticed something has changed with my tempdb config (i have been off work for a few months!) did have 3 datafile with fixed initial size of 2.5 gb - this morning i noticed it was 8MB and growing all the time!
You've gotta fix that first... and make sure the same, ummmm... pork chop breath didn't make any other settings changes to TempDB.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 17, 2008 at 8:15 am
More questions:
1) how much RAM on the box?
2) How good (or poor) is your IO?
3) take a look in Perf Mon at Physical Disk: avg disk sec/read and avg disk sec/write for each of your data and log drives (including tempdb)
4) since tempdb shrunk, did the server experience a restart and you didn't have proper initial sizing in place?
5) what are your growth increments for tempdb, btw?
When you identify your worst performing queries (using DMV query mentioned previously), perhaps you can use the OPTION hint to restrict MAXDOP and see if that helps.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 23, 2010 at 8:03 am
Another pathway i have seen take a server to it's knees much in the same fashion had to do not with parallelism but with the Auto Update Statistics function running on your db('s.)
This would also potentially link to the SQL Agent component. I have been lucky (or unlucky) enough to do a lot with MaxDOP with great success as well as statistics and when to auto update and when not to.
The other thing to look at is if you have suddenly hit critical mass due to a code issue. Has the db been growing when not expected to? Is the db a temporary holding ground where the stats are in a constant state of update seeing as the data is being removed and added so frequently?
I like to be sure to look at windows perf counters as well as sql counter at all times. They can glean a lot of what else is going on. Physical disk queuing, CPU Queuing, Buffer Cache Hit Ratio, Page LIfe expectancy are key counters with many others being useful as well.
Viewing 6 posts - 31 through 35 (of 35 total)
You must be logged in to reply to this topic. Login to reply