Nancy Lytle
nancy.lytle at gmail.com
Mon Sep 28 16:15:28 CDT 2009
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 > > > > > > > > > > > > > > > > > > > > > > > > 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 > > > > > _______________________________________________ > dba-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com >