Arggh! Quad Xeon only using one processor for SQL Server - why?
-
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 8 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply