[dba-SQLServer] HELP, server completely unresponsive

jwcolby jwcolby at colbyconsulting.com
Mon Sep 28 14:53:54 CDT 2009


What is MAXDOP?

I have already told SQL Server to only use 12 gigs and 3 processors.

John W. Colby
www.ColbyConsulting.com


Nancy Lytle wrote:
> 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
>>
>  		 	   		  
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
> 
> 



More information about the dba-SQLServer mailing list