SPONSORS:






User Tag List

Thanks Thanks:  0
Likes Likes:  0
Dislikes Dislikes:  0
Results 1 to 7 of 7
  1. #1
    Junior Member
    Join Date
    Jul 2003
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Quiz : Identify the bottleneck

    I ran Performance Monitor on the Database Server for an entire week, collecting counter data once every 60 seconds. Below are some of the results of the collected data:

    * % Process Time (for both CPUs) averages 55%, with occasional spikes of 100%
    * Processor Queue Length averages .8
    * % Disk Time (for drive D averages 61%
    Current Disk Queue Length (for drive D averages .77
    * Buffer Cache Hit Ratio averages 84%
    * Bytes Total/Second averages 3.4MBs

    The physical specifications of my Server are:
    * Two 700MHz Pentium III Xeon CPUs with 1MB cache each. There is room for two more CPUs to be added.
    * 1GB RAM, with room to add an additional 1GB of RAM.
    * Drive C: is an 18GB duplexed disk array that includes the operating system, the swap file, and the SQL Server binaries.
    * Drive D: is a RAID 5 disk array with four 18GB 10,000RPM hard drives. There is room to add two more 18GB hard drives to the array without having to use an external cabinet.
    * A single 100MBs NIC, with room for several more.

    Unfortunately, because of budget constraints, I am only able to upgrade one piece of hardware in my server.

    Identify the bottleneck and suggest me your choices...........


    Proxy Test

  2. #2
    Senior Member
    Join Date
    Jul 2002
    Location
    Palm Bay, FL USA
    Posts
    2,346
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Quiz : Identify the bottleneck

    1) so what is slow?
    2) What about memory usage?
    3) Do you even know that there IS a bottleneck on your DB server?

    There is no where near enough information for me to even determine IF there is a bottleneck here, let alone WHAT it is. Maybe I am missing something obvious?
    Scott Barber
    Chief Technologist, PerfTestPlus
    Executive Director, Association for Software Testing
    Co-Author, Performance Testing Guidance for Web Applications
    sbarber@perftestplus.com

    If you can see it in your mind...
    you will find it in your life.

  3. #3
    Moderator
    Join Date
    Sep 2001
    Location
    Boston, MA
    Posts
    4,348
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Quiz : Identify the bottleneck

    55% avg cpu seems a little high. that doesn't leave you with much overhead for large transaction spikes.
    Corey Goldberg
    Homepage: goldb.org
    Twitter: twitter.com/cgoldberg
    Google+: gplus.to/cgoldberg

  4. #4
    Senior Member
    Join Date
    Apr 2001
    Location
    N. Mankato, MN
    Posts
    319
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Quiz : Identify the bottleneck

    I would agree with RSBarber, there is a lot of information missing.

    The server specs that you listed are a really low end system compared to what we do for SQL servers.

    I would also agree with Corey most DB servers, the server processor is running a bit high. I generally add more processors once the CPU usage runs over 50%.

    Is the % Disk time using more time reading or writing?

    The disk Queue length looks fine...usually its 1.5 to 2 X the number of drives when you start seeing major disk issues.

    Also your cache/hit ratio looks a little low, but it isn't terrible, yet. Might be a slight memory issue (hard to say without memory stats)

    There is a lot of things you can do to help your SQL Server performance like running some traces look for long running queries, look at blocking, etc.

    I will probably re-visit this post in a little while with some links to look at. [img]images/icons/smile.gif[/img]

  5. #5
    Senior Member
    Join Date
    Apr 2001
    Location
    N. Mankato, MN
    Posts
    319
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Quiz : Identify the bottleneck

    As promised here are some good links to look at:

    http://www.sql-server-performance.com/
    http://www.tburke.net/info/reskittoo...rfcounters.htm
    http://msdn.microsoft.com/library/de...rcpctyplan.asp
    http://msdn.microsoft.com/library/de...l_ref_pcls.asp

    I think most of these articles state that a sustained 70 to 80% processor usage for 10 minutes is when the server should be replaced or have more CPUs added....however with a SQL Server I would say 50% is a good time to add more processors.

    Hope these help! [img]images/icons/wink.gif[/img]

  6. #6
    Moderator
    Join Date
    Aug 2000
    Location
    Vancouver, BC, Canada
    Posts
    1,189
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Quiz : Identify the bottleneck

    The best thing to do for SQLServer is to give it more memory. Your Buffer Cache Hit Ratio is not the highest. Increasing your memory will improve this and will also bring down your disk usage. (Make sure that you configure SQLServer in such a way that it takes the maximum amount of memory.) I am assuming that this is a dedicated machine.

    [% Disk Time (for drive D averages 61%]
    This is very high, especially as you realize that disk activity is something you should avoid as much as possible because it is the bottleneck in your system.

    Has your SQLServer been set up in such a fashion that log files are written to a different drive? (C) This can often speed up the database considerably. What was the disk activity on C, I bet you it was pretty low. So move the logfiles to C. (you could even consider breaking up tables and move them to another physical location)

    I agree with the previous posters about the processors, you do not have much headroom. So watch this one. I would consider extra processors for this anyway. 50% average over a day! is bad.

    What about optimizing the code? Moving code in stored procedures, eliminating dynamic sql is often a very effective measure to gain performance. Also making sure that appropriate indexes are available, no locking/lock waits and deadlocking is occurring.

    3.4 MBs = 27.2 Mbs (because perfmon gives you Bytes per second but network speed is expressed in bits per second) This is fairly high level of traffic. You could place another NIC, particularly if that NIC gets connected to another segment. So you have 2 different routes to get to the users.

    Also take a look again at the application. If the application is not built in a smart manner you can end up with lots of traffic for data that you have queried before. Example: Classic performance mistake is when developers keep on requesting code table contents eventhoough these contents have not changed are limited in size and could have stored in an array within the application after the first retrieval. This techniques makes both the client and the server perform a lot better.

    Also you could consider doing an update statistics or rebuilding the complete database.
    Your low buffer cache indicates to me that you have a database with a high mutation grade.
    Over time your database performance will degrade as your indexes become less and less effective. Talk to your DBA to see what can be done here.

    In my opinion you should always look at the usages (the apps) first before you just beef up the box. There is however a fine line. If your efforts to optimize are becoming more expensive than a machine update, it becomes hard to sell your optimizing activities.

    I hope this helps,
    Roland Stens

  7. #7
    Junior Member
    Join Date
    Jul 2003
    Location
    Houston, Texas
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Quiz : Identify the bottleneck

    I would like to request a bit of information. Is your Raid built with IDE or SCSI Drives? If you have a LOT of transactions and you are using IDE drives, then perhaps you may want to go to the budget devouring SCSI drives. Otherwise get yourself a huge chunk of memory an hope caching works.
    Kristofer A. Hoch
    Quality Assurance Engineer

 

 

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Search Engine Optimisation provided by DragonByte SEO v2.0.36 (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
Resources saved on this page: MySQL 9.38%
vBulletin Optimisation provided by vB Optimise v2.6.4 (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
User Alert System provided by Advanced User Tagging v3.2.8 (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
vBNominate (Lite) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
Feedback Buttons provided by Advanced Post Thanks / Like (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
Username Changing provided by Username Change (Free) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
BetaSoft Inc.
Digital Point modules: Sphinx-based search
All times are GMT -8. The time now is 01:49 PM.

Copyright BetaSoft Inc.