Creating a Performance Baseline

You'll often hear that you should monitor the performance of SQL Server. You may read a little about performance monitoring, and you may turn on a few counters or perform a query against a dynamic management view that you know about. But, you may still wonder "Are these numbers good or bad?"

To determine if something is bad, you need to know what it looks like when it is good. Sounds obvious doesn't it? By creating a performance baseline, you can learn what your numbers are when your system is performing well. A performance baseline includes a single performance chart that is accompanied by an interpretation of the results, based on your environment.

To establish your performance baseline against Windchill, you'll need to find a time when the performance of your SQL Server environment is considered normal. For example, no users are complaining about slow responses, no backups or large jobs are running, and no "special" processing is taking place. Once you find that time, you'll need to collect a range of Windows Performance Monitor (perfmon) counters, information from dynamic management views, and maybe even a small SQL Server Profiler trace. Then, you can use the results of your collection as the starting point for subsequent performance collections. How do the new numbers compare to the baseline numbers, when everything was fine? Did one counter go up or down? Did several numbers change? Having something to compare the current numbers with can help you identify the source of new performance bottlenecks.

What Should You Monitor?

The actual counters, dynamic management views, or SQL Server Profiler trace events that you should collect are based on your system setup. But, the counters that we list below are a good place to start. If you capture these counters, you should have enough information to determine if you are having a performance issue—and if you are having an issue, which area is the source.

Note: Many of the counters that we list below list a threshold. These threshold numbers are not written in stone, and your actual values may be different. It is important to note that a standard threshold number is a starting point—if your value is a little higher or a little lower, the values that you see during your performance baseline collection become your new thresholds.

Monitoring the Disk Subsystem

There are several methods to monitor the disk subsystem. Since the disk subsystem is getting more and more complex each year, we recommend that database administrators monitor the following Performance Monitor counters to understand the latency of their disk I/O requests.

Read more: PTC Windchill on SQL Server

Posted via email from jasper22's posterous