[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


SQL 2005 Multi processor utilisation. How to check and enable all processors and prcoesse are being used by SQL 2005

Posted on 2007-07-25
Medium Priority
Last Modified: 2013-12-10
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.


Question by:Potts2002
  • 3
  • 2
LVL 28

Expert Comment

ID: 19564213
Right click sql server in management studio click properties
check processors

Author Comment

ID: 19564247
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?
LVL 28

Expert Comment

ID: 19564270
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

Author Comment

ID: 19564813
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?


LVL 28

Accepted Solution

imran_fast earned 2000 total points
ID: 19565105
How much memory you have allocated for your sql server by default it takes only 4 GB if you want to increase that then look for this article

also run sql profiler to grab long running quries and then use sql engine tuning advisor to look for suggestions.

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction: When experiencing some peculiar problem with the functioning of your PC, how many times has it happened that you look for a solution and even google can’t help? It could be that you are one of the only few people on earth who ma…
pc, laptop  monitor connection configurations
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Loops Section Overview

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question