[dba-SQLServer] HELP, server completely unresponsive

What is your MAXDOP set to?  I found that setting mine a little lower than expected actually helped.

We have 2 processors, it was set to use all.  This would cause some queries to just suck the life out of it sometimes so I cut it back to 1 and it runs much better.

Nancy Lytle 
N_Lytle at terpalum.umd.edu











 EMAILING FOR THE GREATER GOOD
Join me


 
> Date: Mon, 28 Sep 2009 11:19:09 -0400
> From: jwcolby at colbyconsulting.com
> To: dba-sqlserver at databaseadvisors.com; dba-vb at databaseadvisors.com; accessd at databaseadvisors.com
> Subject: Re: [dba-SQLServer] HELP, server completely unresponsive
> 
> Well I thought that assigning three processors to the SQL Server software was the answer, and it in 
> fact did make a difference in some cases, but there is still something happening that "locks up" the 
> server. IO started a long running update query going this morning and immediately afterwards I was 
> able to move around, look at other stuff. Then I went away to do other things. When I came back it 
> was "locked up" - with the Management studio application full screen, and the cursor immovable. Of 
> course I thought it was locked up, and I have always believed that it was literally never going to 
> come back.
> 
> I just happened to to an Alt-Tab, then went off to my lap top to do other stuff. When I came back I 
> was at Windows Explorer, not Management Studio. So the machine is in fact responding, but 
> glacially. The mouse cursor is locked up, cannot be moved. I use a KVM switch, and when I move to 
> another machine the cursor is moving just fine, when I move back to this machine it is locked up. 
> However the cursor changed from the insertion pointer icon to the arrow icon when i switched from 
> Management Studio to Windows Explorer, so again the machine is definitely not unresponsive, it is 
> just responding glacially.
> 
> This time I have SQL Server assigned three processors, and I was observing that SQL Server pegged 
> three of the processors when it began processing the query so I am comfortable that it in fact is 
> correctly using just three processors.
> 
> So what is happening that would so completely freeze up the server that the cursor won't even move?
> 
> Weird.
> 
> Has anyone else out there ever experienced this? Found a solution?
> 
> John W. Colby
> www.ColbyConsulting.com
> 
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>