May 20, 2009 at 1:26 am
Hi,
I'm experiencing an weird situation. One of our SQL Servers shows 2 CPU's being far more busy then the other 6. AFAI can see SQL Server is the apl using CPU, but I am uncertain on what to use to investigate further. Googling the net I cannot find as specific relation between such behaveour and SQL Server.
Any1 having experience with this?
We are running SQL2005 ED SP2 on W2003 with all SP's and patches.
Greetz,
Hans Brouwer
May 20, 2009 at 2:19 am
Hi Hans
Did you check the activity monitor for some jobs with higher load? Are there some special tasks/statements which cause the high load?
Did you check the count of CPUs configured for SQL Server?
Greets
Flo
May 20, 2009 at 2:28 am
Florian,
I checked all these items and no, there was nothing indicating why this behaveour occurred. SQL Server can use all 8 CPU's.
Tnx for answering.
Greetz,
Hans Brouwer
May 20, 2009 at 3:55 am
Off course SQLServer will only use multiple cpus if possible and needed.
If someone issues a query stating 'option (maxdop 2)' it will only use max 2 cpus to resolve that query.
Queries that cannot be parallelized, will only use a single cpu.
If a query can generate a parallel plan, sqlserver will also evaluate how many parallel threads it will launch.
use of e.g. temp tables (single tempdb data file) can influence sqlservers parallelism choices.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 20, 2009 at 6:13 am
Hi ALZDBA,
I considered some of the things you mentioned. The strange thing is, these 2 CPU are constantly (much) higher then the other cpu's. They follow the same frequencywave as the other cpu's, but then at a lot higher rate(Compare:max cpu2=23, cpu3&7 are at 53%...)
I have forced paralellism in a testquery and saw no deviation from this higher used wave on the 2 cpu's.
We're planning a reboot and then have a look at this behaveour.
Greetz,
Hans Brouwer
May 20, 2009 at 6:39 am
There are some other options :
from within sqlserver these settings can influence the number of cpus a query can use:
/* show the current settings */
exec sp_configure 'affinity mask' ;
exec sp_configure 'max degree of parallelism' ;
Is WSRM (Windows system resource manager) installed ?
- Is it configured to manage processor availability (guaranteed % cpu) ?
- does wsrm restrict any cpus ?
Is this a dedicated sqlserver box ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 21, 2009 at 2:26 am
Hi Alz,
The server is a dedicated SQLServer box. Can't check on WSRM, it's a long holidayweekend here in the Netherlands. I'll check monday.
Greetz,
Hans Brouwer
June 3, 2009 at 6:49 am
Sorry for the delay.
There is no WSRM in use; max degree of parallelism is set to 0, meaning it will use all 8 CPU's.
We're checking things out and hopefully monday I have some more info.
Tnx.
Greetz,
Hans Brouwer
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply