Arggh! Quad Xeon only using one processor for SQL Server - why?
-
I know I have seen this referred to before - I can't find it now, so I'm hoping one of you has better recollection than I do.
We have a Windows 2000 box running SQL Server 2000. The box has Quad Xeon processors (4 processors w/ hyperthreading). We recently noticed that this server was on service pack 1, and had our hosting provider upgrade us to SP 3a. Today, we've been seeing heavy activity, and the box has been horribly unresponsive. However, when we look at task manager, it appears that one CPU will handle the load for a while, then it will drop off and another one will max out. In the past (and on a sister box), we have seen SQL Server occupy all 8 processors quite effectively.
A vague memory tells me that this is a commmon symptom of a misconfiguration of some sort that could have happened when the hosting provider applied SP3a. However, for the life of me, I cannot find a reference to this anywhere.
Anybody recognize this problem, and recall the solution?
Thanks in advance,
R David Francis -
Have you checked all the available processors in the processors configuration item?
-
Yes, I have done that - "Use all available processors" is selected, as it should be. Thanks for the suggestion, though.
That may actually be the problem/solution I'm thinking of, but it doesn't seem like it.
R David Francis -
exec sp_configure
and check that 'max degree of parallelism' is either zero or 4
Ensure NT fibres turned off.
Other than that, no idead.
Rob.
-
RD, did you find out what was wrong?
-
I have not found out what was wrong. A number of people have suggested that the problem was a single bad query (since many queries do not allow for parallelism to kick in, this could explain the problem).
The box hasn't been busy enough to clearly state that SQL Server is continuing to only use one processor at a time or that it is acting "normal" now and spreading load out across the processors.
I'll try to keep you all posted....
R David Francis -
Thank you RD, definitely keep us posted. Thank you.
-
Not a solution but you can test for parallelism by querying as:
Select * from reasonablylargetable option (maxdop X)
where X is the number of processors you would like used. If you specify a query which is likely to work the processors harder that would be better.
Just watch the processor utilisation as you vary X.
DB
The systems fine with no users loggged in. Can we keep it that way ?br> -
As the originator of this thread, I thought i should bring it to some closure.
We have not had a repeat of the symptoms we saw that day. I have seen some indication that the server is properly distributing its load since then (though I'll be happiest when we next get the chance to do some load testing).
From the suggestions of others, it's fairly clear that my memory of a known problem matching my scenario (recent service pack update causes SQL Server to only use one processor) was a mental short circuit of some sort.
We are writing this one off to a runaway query (and, presumably, an ad hoc one, as this has not repeated).
Thanks for all your assistance!
R David Francis
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply