How do I decide if current hardware configuration is good enough for SQL server ?

  • Hi,

    We have asp.net web application with SQL server 2005 on the same server.

    Server configuration is :

    Windows Server 2003 SP2

    Dual Core CPU, 3 GHz,

    3GB RAM

    We are seeing constant high CPU usage. I understand high CPU usage can be due to CPU intensive queries and missing indexes. If we optimize the queries - CPU utilization should come down, right ?

    Size of the SQL server database is around 12 GB. There are couple of other test and staging databases/applications on the same server.

    I wanted to check if this server configuration is good enough to handle the application and database OR in other words - how do I decide if I need hardware upgrade ?

    Thanks,

    Bhimraj

  • Just would like to add here - Memory:Pages/Sec counter value is constantly at 100 along with very high CPU utilization.

    Could that be only issue of badly written queries or hardware issue also ?

  • Would always reccomend that SQL Server is on its own box, that could be a physical or virtial server. In this case you need to investigate what is using the resources and try to move that off this server.

  • You need a much more thorough collection of data in order to determine the causes of the high cpu. Yeah, it could be SQL Server, but from what you've said so far, there's no precise indication. 3gb of memory is extremely small for most SQL Server installations. But, 12gb is a very small database, so you might be OK, but you have zero head room in the event that your IIS server or one of the test applications uses up all the memory.

    With no other information to go on, yes, you need a bigger machine.

    But, first, I'd pull all test operations off the production system. They shouldn't be there. After that, I'd gather a ton more metrics (check out my book if you want a listing) and determine if SQL Server was the issue and if so, what exactly. Then, you can determine if you need new hardware.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • rajg (5/31/2012)


    Just would like to add here - Memory:Pages/Sec counter value is constantly at 100 along with very high CPU utilization.

    Could that be only issue of badly written queries or hardware issue also ?

    At one company I was at, they went from a 4 CPU Standard Edition with 4 GB of ram to (at the time) a firebreathing new box with 16 CPU's, 32GB of Ram, and a brand new high speed San with fiber connections and the whole 9 yards.

    Everyone (well, everyone but me) was absolutely thrilled when some of the more painfull jobs dropped from 8 hour to 2 hours and 4 hours to 1 hour. They weren't so bloody happy when we reached the next "tipping point" after only a month of adding new customers and all of the jobs went back to their original times.

    Yes... there are some places where you need good hardware. Disks, memory, fiber channel, etc... but no matter how good the hardware is, you just can't get the performance improvement of actually writing good code.

    For example: That same company had a dupe-check job that would take 10 to 24 hours to usually fail. The run had to be done within a 24 hour period or we stood the chance of being fined by the PUC and a couple of other government agencies. It also only did 2/3rds the necessary work of dupe checking the last 60 days because it took too long to do a full 90 days. So, extrapolating a bit, the proc took 14 to 36 hours to usually fail. The code was supposed to run against 94 tables of about 4 million rows each all in different databases on a monthly basis and 4 tables on a daily basis. The daily runs were taking about 45 minutes and would also frequently fail.

    I took 2 days to analyze the problem with the help of someone who knew the data better than I. I took a day to "play" with the data to see what I could do in the form of "Proof of Principle" code. I checked the results with the people who knew the data. I spent 2 more days writing and testing the heck out of the code.

    The first time they ran it as a daily run, they reported back that it failed. When I asked what made them think so, they reported "It didn't take long enough. It only ran for 7 seconds and quit."

    After my urging, they checked the data and it had, indeed, run correctly in only 7 seconds. The Monthly run which normally took 10 to 24 hours to usually fail now ran in only 11 minutes.

    My point is, try... just try to get that kind of performance increase by buying hardware. Most of the time, it's just not possible for that to happen.

    BTW... the code I wrote is now about 4 years old. It hasn't failed yet. They have had, however, a hardware failure. 😛

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • whats ur most common wait type, it shud give u an idea of where you can focuss your energy. High CPU like mentioned above can be caused by a number of issues , however in the majority of the cases its fixed easily by tweaking the query or adding an index.

    Look for long running queries while your at it and whats ur waiting task count ?

    Jayanth Kurup[/url]

  • Jeff - Thanks for sharing your experience. I understood that hardware upgrade may or may not help but I have to get ride of slow queries first.

    @Grant - thanks for suggesting the book. I think I have to go through the book to understand more about different metrics to consider for identifying the performance bottlenecks.

    @Jayant - I am not sure where to find most common wait type or waiting task count. Is that in Activity Monitor ?

    Thanks all.

  • I have been monitoring some metrics and Pages/Sec value is very high constantly. I think Pages/sec value means number of pages read from Disk per sec and high value indicates that there could be memory issue, correct ?

    Please see attached screenshot for the metrics.

  • pages/sec is just an indication of load. You don't know if it's high or low until you get a baseline to compare it to.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • rajg (5/31/2012)


    Jeff - Thanks for sharing your experience. I understood that hardware upgrade may or may not help but I have to get ride of slow queries first.

    Sorry for the late reply but that's exactly what I was getting at.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 10 posts - 1 through 9 (of 9 total)

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