SOS Help pls: SQL Server 7 use only 1 proc from 8

[balage0]

Dear Listmembers,

I have a big problem now. I have an 8 processor server, but SQL Server Service now use only 1 from 8 processor.

There was nothing changed at the last couple of days, just a server reboot on the night because of a cold backup.

Do you have any idea why it use only 1 proc, and how can I resolve this without a server reboot in daytime.

Many Thanks,

Balazs

 

[mrdenny]

How are you seeing that it's only using one processor? What is telling you this?

Denny

Between the ESP=ON and the RUM (Read Users Mind) upgrade, I'm ready to go.

 

[balage0]

Thanks Denny,
so, when I run an sql query via Microsoft excel using odbc, then one of the processors go up and running on 90%, and only the sqlserver process use this 90%. But the other 7 processor is on 5-10% of processor usage.

However I have a test server, that is copied from the Live one 1 months ago. ruuning this report on this server, then all the 4 processor is used equivalent. All is working, not just one.

I checked the SQL server settings, and all the processors is enabled to use...

do you have any idea?

Many thanks
Balage

 

[mrdenny]

Sounds like the query is only using a single processor.

Run sp_configure and check the following:
"cost threshold for parallelism"
"max degree of parallelism"

also when the query is being run, run sp_who2 ?? (where ?? is the spid of the query). This will tell you how many process threads the query is using.

Ods are, your cost settings are set so that the server doesn't think that the server needs to use parallelism.

Put the query in Query Analyser and get the Execution Plan and see what the cost is. It will also tell you where the Parallelism is being used. The server could decide that it doesn't need to use parallelism based on indexes, data location on the drive, speed of the drives, etc.

Denny

--Anything is possible. All it takes is a little research. (Me)