locked
Performance monitoring RRS feed

  • Question

  • Hi

    We have a sql server 2005 db 64 bit - Linux with 10 instances running on it. The server has 12 GB of ram. Around 40 web applications access this database and at the moment the performance is good.  we have a plan to increase the load by 20% by this week. what are the parameters do I need to check to find if the current database would withstand the additional load. thanks in advance

    cheers
    APVN
    Wednesday, May 13, 2009 1:52 PM

Answers

  • Hi,

    May I suggest counting the number of blocks and locks that occur? Also, you may want to also verify your disk access speed is good. With additional concurrency you might start to run into some I/O contention. This article is a good one for reviewing disk access: http://msdn.microsoft.com/en-us/library/dd758814(loband).aspx.

    Also, you may want to make sure that your memory management is set for each instance to only take a certain amount. If each instance is set to take the max then they may canabilize resources from one another. 

    Cheers,
    Bob

    • Marked as answer by APVN Wednesday, May 13, 2009 2:18 PM
    Wednesday, May 13, 2009 1:57 PM
  • The simple answer is you may not be able to know if you can withstand the additional load.  In some cases a perfectly happy server, can begin to hit problems with a 1-2 increase in connections if those added connections create blocking and locking that can't be overcome immediately.  Places to watch to know if you are at the critical tipping point as the load increases are your standard SQL Server Performance Counters.

    Microsoft released a tool on Codeplex that makes parsing your counter logs a breeze and provides good information on where you might be hitting bottlenecks or could potentially have problems:

    Performance Analysis of Log s (PAL) Tool

    I use it for most of my analysis at this point because it lists the counters you should collect in the SQL Template, and it has preestablished, but configurable thresholds for where problems might be occuring.



    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    • Marked as answer by APVN Wednesday, May 13, 2009 2:44 PM
    Wednesday, May 13, 2009 2:17 PM

All replies

  • Hi,

    May I suggest counting the number of blocks and locks that occur? Also, you may want to also verify your disk access speed is good. With additional concurrency you might start to run into some I/O contention. This article is a good one for reviewing disk access: http://msdn.microsoft.com/en-us/library/dd758814(loband).aspx.

    Also, you may want to make sure that your memory management is set for each instance to only take a certain amount. If each instance is set to take the max then they may canabilize resources from one another. 

    Cheers,
    Bob

    • Marked as answer by APVN Wednesday, May 13, 2009 2:18 PM
    Wednesday, May 13, 2009 1:57 PM
  • The simple answer is you may not be able to know if you can withstand the additional load.  In some cases a perfectly happy server, can begin to hit problems with a 1-2 increase in connections if those added connections create blocking and locking that can't be overcome immediately.  Places to watch to know if you are at the critical tipping point as the load increases are your standard SQL Server Performance Counters.

    Microsoft released a tool on Codeplex that makes parsing your counter logs a breeze and provides good information on where you might be hitting bottlenecks or could potentially have problems:

    Performance Analysis of Log s (PAL) Tool

    I use it for most of my analysis at this point because it lists the counters you should collect in the SQL Template, and it has preestablished, but configurable thresholds for where problems might be occuring.



    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    • Marked as answer by APVN Wednesday, May 13, 2009 2:44 PM
    Wednesday, May 13, 2009 2:17 PM
  • Thanks Bob.  I have 12gb of ram and have set max server memory of 9 GB. Is that you have mentioned of setting up each instance a certain memory? sorry I am new to sqlserver. thanks
    Wednesday, May 13, 2009 2:27 PM