February 6, 2012 at 8:06 am
I have a virtual sql server with 8 procs for a OLTP having some deadlock and blocking issues. THis was migrated from an older physical box that had quad procs and did not have these issues. Don't see any glaring cpu issues, trying to work with code staff on some of their stuff. MAXDOP is now set to 1 on the new box, anyone seen a return on increasing this setting to say "4". Memory and SSD disks do not seem to be an issue.
February 6, 2012 at 10:32 am
Most of the time the cause for deadlocks/blocking is within the code. The concurrency issue can be solved after a review of the code and maintaining transaction/isolation based sessions.
February 6, 2012 at 10:44 am
Preaching to the choir,
February 6, 2012 at 11:25 am
I'd do a more thorough comparison between this box and the old one to detrmine what all the differences are.
As far as MAXDOP goes, I prefer to leave it set to 0 (default, access to all CPUs) and modify the cost threhsold for parallelism from the default of 5 up to something reasonable like 40.
After that, look at wait stats in general and the waits on this query in particular to understand what's causing it to slow down.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
February 6, 2012 at 11:35 am
Beyond what Grant says, MS recommends setting maxdop to 8 on servers with more than 8 CPUs, because the cost of spreading the job out will be more than the benefit of parallel processing.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 6, 2012 at 11:53 am
GSquared (2/6/2012)
Beyond what Grant says, MS recommends setting maxdop to 8 on servers with more than 8 CPUs, because the cost of spreading the job out will be more than the benefit of parallel processing.
Makes sense. I don't think I've managed a server with more than 8. We had them that large at my last company but they were hosting a whole slew of VMs that were running with 2.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
February 6, 2012 at 12:26 pm
Thanks this is a VM box, I have been getting the error below (not sure if related)
Date 2/6/2012 10:30:40 AM
Log SQL Server (Current - 2/6/2012 8:05:00 AM)
Source spid91
Message
The client was unable to reuse a session with SPID 91, which had been reset for connection pooling. The failure ID is 29. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.
THis seems to be an different issue
February 6, 2012 at 12:37 pm
Yeah, totally different. When I've run into that one it's usually been a configuration issue. Either I'm not running the right SP on the server, or the developers are running an old version of ADO.NET. Connection pooling issues are a real pain, but completely unrelated to deadlocks and parallelism.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
February 8, 2012 at 8:05 am
Probably not a deadlock or maxdop issue. If a shared connection suffers an error that closes the SPID (16 or higher, if I'm not mistaken), then anything else trying to use that connection will suffer the kind of error you got.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 8, 2012 at 8:18 am
GSquared (2/8/2012)
If a shared connection suffers an error that closes the SPID (16 or higher, if I'm not mistaken)
20 or higher. Below that they terminate the statement or the batch, but not the connection.
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
February 10, 2012 at 6:52 am
GilaMonster (2/8/2012)
GSquared (2/8/2012)
If a shared connection suffers an error that closes the SPID (16 or higher, if I'm not mistaken)20 or higher. Below that they terminate the statement or the batch, but not the connection.
Thanks. Couldn't remember and didn't have time to look it up.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply