[dba-SQLServer] HELP, server completely unresponsive

jwcolby jwcolby at colbyconsulting.com
Mon Sep 28 20:05:27 CDT 2009


I'll give that a try.

Thanks!

John W. Colby
www.ColbyConsulting.com


Nancy Lytle wrote:
> It is the Degree of Parallelism you are telling the SQL Server to use for queries, essentially, it is a setting.  It can be set using T-SQL or you can set it in SSMS under Properties, I think Advanced (I'm not at my server right now) and maybe you should throttle it down to 2, and see how that goes.  Of course you can check it out in BOL, but I know how tight you time is, I was given the tip by a someone else and it did work a charm on some of our servers (I manage 1900 servers of various DB sizes, all the same DB though and all with the same hardware/software config) that had the larger DB sizes.  Test it out, see how it goes.  It sounds counter intuitive, but it prevents one query from taking control of all of SQL Server and holding everything else hostage.
> 
> Nancy Lytle 
> N_Lytle at terpalum.umd.edu
> 
> 
> 
> 
> 
> 
> 
> 
> 
>  EMAILING FOR THE GREATER GOOD
> Join me
>  
>> Date: Mon, 28 Sep 2009 15:53:54 -0400
>> From: jwcolby at colbyconsulting.com
>> To: dba-sqlserver at databaseadvisors.com
>> Subject: Re: [dba-SQLServer] HELP, server completely unresponsive
>>
>> 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




More information about the dba-SQLServer mailing list