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 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
|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