Post reply

Perfmon Counters and their Impact on Server Performance

  • May 18, 2006 at 9:53 am

    #113448

    We are at the very early stages of assessing our SQL Servers to determine if we should move our data to a SAN.  EMC would like us to monitor the following perfmon objects.

    Add all counters and all instances for the following objects:

    • Physical Disk

    • Process

    • Processor

    • SQLServer:Access Methods

    • SQLServer:Buffer Manager

    • SQLServeratabases

    • SQLServer:General Statistics

    • SQLServer:Latches

    • SQLServer:Locks

    • SQLServer:Memory Manager

    • SQLServer:SQL Statistics

    The duration would be every 60 seconds for what appears to be only 2 hours (not nearly long enough in my opinion).  Should I be concerned about the load this will place on a server?  I typically do not add "ALL" counters and instances for an object when monitoring a server.

    Also, when monitoring the physical disk counters below is it necessary to divide the results by the number of physical disks in each RAID or are the results useful as is?

    Physical Disk\

      Average Disk sec/Read

      Average Disk sec/Write

      Average Disk Queue Length

      Disk Reads/sec

      Disk Writes/sec

      Disk Transfers/sec

    Thanks,  Dave

  • May 19, 2006 at 5:44 am

    #638946

    I think the impact on the SQL server from the monitoring should be fairly minimal.. there is impact though so you could also do the monitoring from another server and log the counter logs on that other server too rather than to the SQL server itself or else you use more SQL resources again.

    Personally though I have just done it on the SQL server itself because I really don't think it has much effect overall.

    As far as the other question there is some value to those ocunter on their own.. but when you select these counters then on the right side you can select what instance to monitor.  So for example it can monitor all disks (_Total) or you can select to monitor individual disks (C:, E:, etc..) - so you may have it do everything - get a full picture and also more of a breakdown in case you need it.  Ot maybe you do _Total and then if some numbers look high then you could do a further test with the drives broken out so you can see if particular one(s) are the issue.

    Oh- I also agree that 2 hours could be short.. if that were my only sample size then I would make sure to do it during typical or prefereably high usage hours if known.

    Have fun!

  • May 19, 2006 at 8:54 am

    #639023

    I've always monitored from a server other than my SQL server. Also, be sure you have sufficient disk space to hold the PERFMON log file. It can grow pretty large (although, every sixty seconds for two hours isn't a real long period of time) depending on the volume of activity.


    Terry

  • May 19, 2006 at 9:40 am

    #639044

    if you use another server it will impact network traffic. If you write to a table it will impact wherver the table is. If you're logging choose a fast drive - I usually used my backup drive.

    If you're moving to a SAN you'll need to this afterwards as well to make sure your SAN has been configured correctly. I have yet to see any evidence a SAN gives any performance benefit to a database system where the number of spindles / arrays  is the same as DAS.

    I've run many perfmon counters, had to to prove the SAN was at fault !! , I ran mine 24 hours a day - you're right the sampling is not sufficient, I'd say you need a month of 7 x 24 logs to get a true picture.

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • May 19, 2006 at 9:58 am

    #639049

    I spoke with EMC this morning and they agreed 2 hours was not enough.  We're going to run the trace for 1 week, although on some of our servers one week may not be long enough.  I would also like to include monthend processing in the monitoring.

    I also questioned why they wanted all counters and all instances.  Many of the counters I've found only useful if you've identified a bottleneck and want to get more details about the problem.  EMC had no problem with me reducing the number of counters being monitored.

    I've run perfmon directly on the server and remotely from my desktop.  I don't mind running on the server if I'm only gathering information on a small number of counters for a couple of hours, otherwise I prefer to run remotely.

    The perfmon request they made may be a bit premature since I've yet to perform a feasibility study regarding SQL Server to SAN.

    Thanks,  Dave

  • May 19, 2006 at 2:51 pm

    #639093

    EMC like many large organizations that sell products are just going through a process with you. Whether or not the individuals executing thast process are of 'stellar' quality is anothe issue. My advice is for you to have EMC register you for all of it's web-based elearning ASAP. This way you'll know if your ship is being steered correctly. Besides, EMC is an expensive option (Hitachi is much cheaper) so you'll want to maximize your time and dollars. Once you actually get things in-house the real configuration fun starts. The only one-size-fits-all advice I can provide on this is: use Meta-LUNs instead of LUNs. This will maximize the number of spindles handling I/O and minimize the number of spindles just spinning and doing nothing. Using just LUNs you'll have hot and cold spots of I/O activity on your SAN whereas with Meat-LUNs you will not.

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

  • May 23, 2006 at 3:06 pm

    #639606

    Quick question:  What tool or query are you going to use to analyze the performance counter results?

  • May 24, 2006 at 7:11 am

    #639755

    I'm not sure how they plan on analyzing the results.  I've only been asked to address the perfmon piece of the request.

    Dave

  • June 13, 2006 at 6:01 am

    #643329

    There is a specific counter that can isolate a SAN bottleneck from I/O bottlenecks on the server side. However the counter escapes my recollection but we spoke of it in the MS SQL performance tuning class. Does anyone know which counter this is.

    Kindest Regards,

    CJH Austin, TX

  • June 13, 2006 at 6:39 am

    #643345

    I think this is it and maybe this is the incorrect thread for these two posts.

     

    LogicalDisk|PhysicalDisk\Avg. Disk sec/Transfer

    Indicates how fast data is being moved (in seconds). Measures the average time of each data transfer, regardless of the number of bytes read or written. Shows the total time of the read or write, from the moment it leaves the Diskperf.sys driver to the moment it is complete. A high value for this counter might mean that the system is retrying requests due to lengthy queuing or, less commonly, disk failures. To analyze transfer data further, use Avg. Disk sec/Read and Avg. Disk sec/Write.
    Kindest Regards,

    CJH Austin, TX

  • June 13, 2006 at 6:39 am

    #643343

    I think this is it and maybe this is the incorrect thread for these two posts.

     

    LogicalDisk|PhysicalDisk\Avg. Disk sec/Transfer

    Indicates how fast data is being moved (in seconds). Measures the average time of each data transfer, regardless of the number of bytes read or written. Shows the total time of the read or write, from the moment it leaves the Diskperf.sys driver to the moment it is complete. A high value for this counter might mean that the system is retrying requests due to lengthy queuing or, less commonly, disk failures. To analyze transfer data further, use Avg. Disk sec/Read and Avg. Disk sec/Write.
    Kindest Regards,

    CJH Austin, TX

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

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