Don't just release new software and hope for the best. Contributor Jeremy Kadlec explains the importance of establishing a performance baseline in this three-part series. In part one he explained how to validate

    Requires Free Membership to View

a baseline. In part two, below, he covers top 10 performance monitor counters. In his next tip, he'll discuss how to capture a performance baseline with SQL Server Profiler.

Performance Monitor offers a simple means to collect macro-level performance statistics on a single SQL Server. If the SQL Server is reported by the users as experiencing an issue, Performance Monitor can be started to determine how the SQL Server is performing relative to the baseline. So, it is important to capture the baseline after each change is made to the system (hardware, software, and so on) or at least every three months to have relatively recent statistics. Below are 10 recommended Performance Monitor counters to help you capture a baseline and use as a comparison when you are researching a system issue.

Recommended Performance Monitor Baseline Counters
ID Counter Description Significance
1 CPU -- % processor time The percentage of the processor time that is used to process user or system transactions as opposed to the CPU sitting idly The higher the processor usage, the more the CPUs are being used to process user or system transactions
2 System -- Processor queue length The number of threads that are waiting for the CPUs The higher the queue rate, the more the threads are waiting on the CPUs to process the instructions on the CPU
3 Memory -- Pages/sec Pages read and written from memory The higher the memory usage, the more processes are thrashing in memory
4 Paging file -- % usage for _Total The percentage of all page files that are in use The higher the page-file usage, the more memory is needed to support the system in excess of the physical memory on the server
5 PhysicalDisk -- Avg. disk queue length The number of read and write requests that are queued for the physical disk subsystem The higher the average disk queue length, the more resources are waiting for earlier requests to finish prior to fulfilling their request
6 SQL Server: General statistics -- user connections The number of users on the system The lower the number of users indicates that the server is unable to support the system load
7 Network interface bytes Total/sec The bytes sent and received over a single network card Determine if excessive network traffic is occurring on a network card or if the system has issues isolated on the SQL Server with no network traffic
8 SQL Server: locks -- Lock waits/sec -- _Total The lock request waits per second Determine if excessive locking is limiting resources for users to access the system
9 System -- Threads The number of threads on the computer Determine if the number of threads greatly exceeds the typical baseline indicating more processes are occurring on the system
10 System -- Context Switches/sec The number of processes that are changing threads per second The higher the context switching, the more the server is stressed

Stay tuned for Jeremy Kadlec's next tip on performance baselining.

About the author: Jeremy Kadlec is the principal database engineer at Edgewood Solutions, a technology services company delivering professional services and product solutions for Microsoft SQL Server. He has authored numerous articles and delivers frequent presentations at regional SQL Server users groups and nationally at SQL PASS. Kadlec is the SearchSQLServer.com Performance Tuning expert. Ask him a question here.


More information from SearchSQLServer.com

  • Tip: Get an introduction to performance baselines in part one
  • Ask the Experts: Ask Jeremy Kadlec for answers to your performance tuning questions
  • Topic: Research performance and tuning best practices


  • This was first published in August 2005

    There are Comments. Add yours.

     
    TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

    REGISTER or login:

    Forgot Password?
    By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
    Sort by: OldestNewest

    Forgot Password?

    No problem! Submit your e-mail address below. We'll send you an email containing your password.

    Your password has been sent to:

    Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.