Help with pinned CPU

  • Ripple

    Right there with Babe

    Points: 777

    We have a single SQL Server instance with four databases. About 80 concurrent users.

    Our CPU gets pinned (100% utilized) when all users make queries at the same time. The DML is 95% select statements, using stored procedures on top of views. Disk activity is very low, with most reads comming from cache. Procedure run time is fast with one user, less than a second. 10% of queries are returning a single image.

    DL 360, with 2 CPUs and 1GB memory.

    DB1 20MB

    DB2 30 MB

    DB3 300 MB

    DB4 150 MB

    Any help or direction would be appreciated.

    - Brendan

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 715095

    Can you post some code?

    Steve Jones

    steve@dkranch.net

  • Andy Warren

    SSC Guru

    Points: 119675

    Is SQL set to use all 4 processors? Is the OS seeing them? Doesnt hurt to check! Have you set any of the affinity/parallel options? What version and service pack of OS and SQL are you using?

    Andy

  • Wesley Brown

    SSChampion

    Points: 12035

    or the name of the application? Don't forget the DL 360 doesn't take xeon procs in it ether. I wouldn't expect wonders from the little 1U unit. We passed them up for heavy DB loads. Also, how many users and have you run any profiling on the SP's yet?

    Wes

    http://www.sqlserverio.com
    http://www.cactuss.org
    http://www.salssa.org

  • David Benoit

    SSC-Dedicated

    Points: 34562

    Brendan,

    How much memory is being utilized with the 80 concurrent users? You may want to troubleshoot this further by looking at the user issue further. You touched on the fact that the processes work great with one user so, that would be where I would start to look.

    One other thing you did not mention was what OS you were using and what SQL Server version and Service Pack you were using. Also, is there any other applications running on this box or is it strictly dedicated to SQL Server?

    David

    David

    @SQLTentmaker

    “He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

  • ckempste

    SSCoach

    Points: 17983

    Hi

    Crank out perfmon and start adding counters. Run this for a few hours every minute. You will be amazed how much information this can give you.

    80 concurrent users is a lot of people. I believe your problem is the server is paging heavily, you will probably find high values with %priviliged time and spikes in disk queue lengths. How much "free" RAM do you have? if its 10 to 15Mb then SQL is just keeping enough avail for the rest of the system.

    If you are tights on memory, reduce the MAX from dynamic to fixed around 100Mb free) and see what happens to overall performance. It will take sometime for SQL to warm up to the fixed alloc of memory.

    Cheers

    Chris


    Chris Kempster
    www.chriskempster.com
    Author of "SQL Server Backup, Recovery & Troubleshooting"
    Author of "SQL Server 2k for the Oracle DBA"

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply