Potts2002
asked on
SQL 2005 Multi processor utilisation. How to check and enable all processors and prcoesse are being used by SQL 2005
Hi We have a Dell Poweredge 2950 with Dual Quad core processors. We have installed SQL 2005 and would like to check to ensure that all the processors and processes are bring used by SQL. How can we check this and if not how can we enable SQL to access the additional processing power.
Thanks
Potts2002
Thanks
Potts2002
ASKER
Hi Imran
I have checked that and it lists processors 0 - 7 and all are ticked. Presumably that signifies that SQL is using all the processors!!
Is there anything else we need to check?
Is there any utility/process we can run which will indicated how fast SQL is running?
I have checked that and it lists processors 0 - 7 and all are ticked. Presumably that signifies that SQL is using all the processors!!
Is there anything else we need to check?
Is there any utility/process we can run which will indicated how fast SQL is running?
it that is the database server than you need to monitor the following performance counters
Performance Audit Checklist
Counter Name Bottleneck if Counter
Memory: Pages/sec Average less than 20
Memory: Available Bytes Average <5MB
Physical Disk: % Disk time Greater then 55 for 10 Min period
Physical Disk: Avg. Disk Queue Length Greater than equal 2 for 10 Min Period
Processor: % Processor Time exceeds 80% for continuous periods
System: Processor Queue Length exceeds 2 per CPU for continuous periods
SQL Server Buffer: Buffer Cache Hit Ratio should exceed 90%, and ideally be over 99%
SQL Server General: User Connections connections exceeds the number of available worker threads, then SQL Server will begin to share worker threads
Performance Audit Checklist
Counter Name Bottleneck if Counter
Memory: Pages/sec Average less than 20
Memory: Available Bytes Average <5MB
Physical Disk: % Disk time Greater then 55 for 10 Min period
Physical Disk: Avg. Disk Queue Length Greater than equal 2 for 10 Min Period
Processor: % Processor Time exceeds 80% for continuous periods
System: Processor Queue Length exceeds 2 per CPU for continuous periods
SQL Server Buffer: Buffer Cache Hit Ratio should exceed 90%, and ideally be over 99%
SQL Server General: User Connections connections exceeds the number of available worker threads, then SQL Server will begin to share worker threads
ASKER
Hi Thanks Imran.
We have checked all the monitors and everything is OK.
We have a particular app which is running slow but is also not challenging the performance of the server can you suggest any other config areas to check?
Thanks
Potts2002
We have checked all the monitors and everything is OK.
We have a particular app which is running slow but is also not challenging the performance of the server can you suggest any other config areas to check?
Thanks
Potts2002
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
check processors