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
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
    LVL 28

    Expert Comment

    Right click sql server in management studio click properties
    check processors

    Author Comment

    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

    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

    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

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    Great sound, comfort and fit, excellent build quality, versatility, compatibility. These are just some of the many reasons for choosing a headset from Sennheiser.
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    761 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

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now