Post reply

DB Performance Degradation - IO Bottleneck

  • July 17, 2009 at 8:55 am

    #207095

    Hi Everyone

    We have a heavily used database which is about 8GB in Size and we are having some serious performance issues with it for a while now.

    I have carried out some basic maintenance on this database towards improving performance. That includes;

    1. Re-building indexes, Re-organising indexes, Updating statistics, Truncating log files etc.

    2. Avoiding auto-growth on this database as this database is quite heavy used during the day time.

    3. Moved transaction log and data file on seperate physical drive.

    4. I have also run index tuenning wizard using the workload taken from busy period of the day and applied necessary recommendation after carefully reviewing them.

    In terms of hardware, we had this database stored on SAN and because of IO bottleneck we decided to move it to the stand alone dedicated server to improve resource availability.

    The stand-alone server has; (Is this a potential problem??)

    1. 4GB of RAM.

    2. Disks are 10k disks.

    3. running on standalone server.

    We are in the process of infrastructure upgrade and the hardware problem will soon be solved. However, in the mean time, i am trying to squeese the last bit of performance out of sql server by applying highest standards.

    After doing some initial investigation, i have figured out that there are five major tables and they are all about 1.5 gig in size. I am also thinking about spliting data files to keep this tables on seperate physical drives to improve IO performance. However, i don't think i need to go down that route yet to improve performance of a 8GB database or do i?????????

    I am running out of my options here really. can anyone throw some ideas please?

    Thanks a lot in advance.

    Regards,

    Vivek

    Vivek Shukla - MCTS SQL Server 2008

  • July 17, 2009 at 9:32 am

    #1026258

    What version of SQL? 64/32?

    I have to admit an 8GB db isn't all that big..

    While you may have an IO bottleneck now I'm wondering if the hardware just highlighted your problem. Take a look at those workloads yo used for the index tuning wizard and see what sprocs are called most often and how long they take, spend some time tuning those, they will have the greatest impact moving forward. Knowing that you have an IO bottleneck is a starting point, look for cleaner ways to accomplish your goals in your code..

    CEWII

  • July 17, 2009 at 9:58 am

    #1026286

    We are SQL 2000 32-bit.

    Also, after my initial look at this 3rd party product database i have found that the database has been poorly designed. I don't think it is normalised very well either.

    I have been studying last few weeks trace files and highlighted all highly used sprocs and queries. Most of this queries are using five main tables in the database with different search and sort criteria.

    I can't really do to much about the way these queries are written as this is a third party designed database. The most i could do is do some index tuenning on them.

    Vivek Shukla - MCTS SQL Server 2008

  • July 17, 2009 at 10:02 am

    #1026290

    While the index tuning wizard does an ok job, performance tuning is as much an art as it is a science, the single greatest advantage we have is that mush tween our ears..

    I would take those queries and see what their query plan is and see if there are any indexes that might make sense to you. It may be that you can't do anything, if thats true then you will have some ammunition to explain why this tools should be retired..

    CEWII

  • July 17, 2009 at 10:11 am

    #1026300

    I absolutely concur with what you are saying.

    Along with running performance tuenning, I have also had a close look at this queries for possible index creation and continueing to do so.

    I have created few indexes which i thought would potentially improve perforamnce but at the same time i don't want to flud database with too many indexes which can have counter effect.

    Also, these tables as i said in my first post are about 1.5 gig in size and creating index on them again would use more space and can potentially lead to more io and performance degradation.

    I think i'll have to trade carefully here.

    Vivek Shukla - MCTS SQL Server 2008

  • July 17, 2009 at 10:22 am

    #1026306

    Useful indexes will reduce IO load, not increase it. Trick is to find useful indexes.

    Take a look at these two articles, they may help you in identifying the problematic queries.

    http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

    http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 17, 2009 at 1:10 pm

    #1026395

    RE: Your trace findings.

    Are there stored procs that are running slowly with a high number of reads and CPU?

    If so, how many? If it's just a few, you can try optimizing them. If the database design is poor, the stored proc t-sql is probably not very efficient either.

    Dave MasonSeminole County, FL

  • July 17, 2009 at 1:23 pm

    #1026401

    I agree that finding useful indexes is the key, you seem to be worried about space, 8GB is really nothing. You incur costs for indexes on the write, not the read. You need to understand HOW the queries are using the indexes, and that is why you need to look at query plans, that will tell you much.

    CEWII

  • July 18, 2009 at 8:33 am

    #1026556

    I would determine what your IOPS is on the system. Permon physical disk read sec and disk write sec.

    Then determine what RAID Group this is 1, 5, 10

    1DISK_READ_SEC + (2* DISK_WRITE_SEC)

    5DISK_READ_SEC + (4* DISK_WRITE_SEC)

    10DISK READ SEC + (2 * DISK_WRITE_SEC)

    Then determine what drives your TEMPDB , DATA, LOGS are on what disk and what SAN partition and how many IOPS that can handle. You SAN guy can help or SAN vendor. Even send them your stats they do analysis aswell.

    Once you got these figures you cand determine if your IOPS is too much for the SAN Disks.

    Index is good but if you are handling way too many iops for your disk then this will not fix problem.

    Also need to determine the speed it takes the disks to handle the requests.

  • July 20, 2009 at 3:22 am

    #1026824

    Thanks a lot everyone for your replies.

    It seems like most of the replies are suggesting that the potential cause of degradation in performance seems to be poorly performing queries, missing indexes.

    I would also like to follow tracey's suggestion of calculating IOPS and consulting my SAN administrator or SAN Vendor and see if we are trying to put more work load on it than what it can handle. I think this could be our problem as we are using SAN with 10K disks in them and they quite heavily used.

    I am following all of your suggestions and as soon as i have an answer, i should let you all know.

    Thanks a lot again for you help.

    Vivek

    Vivek Shukla - MCTS SQL Server 2008

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

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