Start with obtaining a performance baseline. You monitor the server over time so that you can determine Server average performance, identify peak usage, determine the time required for backup and restore activities, and so on. This gives you a baseline from which you can judge the server against to determine if you have a performance bottleneck.
Hardware bottlenecks
Most hardware bottlenecks can be attributed to disk throughput, memory usage, processor usage, or network bandwidth. Usually, the best tools you can use to identify hardware bottlenecks are System Monitor and counter logs. You can also get useful information from the Windows Event Viewer logs.
One problem can often mask another. You might suspect hard disk performance as the cause of degraded query performance. However, when you check the hard disk performance, you find the hard disk performing well but under a less-than-expected load. It could be that the problem actually lies with the network and that the bottleneck is in getting the data to and from the hard disk.
You can sometimes relieve bottlenecks by rescheduling resource-intensive SQL Server activities so that there’s less of a load on the server.
SQL Server bottlenecks
Some performance problems might appear to be hardware bottlenecks initially, but actually have a different cause. SQL Server bottlenecks often look like hardware problems until you collect performance data. Common SQL Server bottlenecks include Locking conflicts, Resource contention, Excessive user connections and activity, and tempdb activity.
The SQL Server Activity Monitor and SQL Server Profiler, along with Windows System Monitor or Performance Monitor, are the primary tools you use to monitor SQL Server performance.
One area you should check is the performance of queries. If you have queries that use excessive server resources, overall performance suffers. SQL Profiler and the Database Engine Tuning Advisor can both help you gather information about queries.
Monitoring processor use
High processor utilization can be an indication that:
- You need a processor upgrade (faster, better processor).
- You need an additional processor.
- Processor affinity isn’t configured correctly.
- You have a poorly designed application.
By monitoring processor activity over time, you can determine if you have a processor bottleneck and often identify the activities placing an excessive load on the processor. Counters that you should monitor include those in the following table.
| Object | Counter | Description |
| Processor | % Processor Time | Value should be consistently less than 90%. A consistent value of 90% or more indicates a processor bottleneck. Monitor this for each processor in a multiprocessor system and for total processor time (all processors). |
| Processor | % Privileged Time | Amount of time that’s spent processing operating system commands. This value should be relatively low in comparison to the % Processor Time. High values here and in Physical Disk counters can indicate that you need a faster disk subsystem. |
| Processor | % User Time | Time spent on user processes (applications), which includes SQL Server. If this value is relatively low in comparison to the % Processor Time, the problem could be a server process. Determine the process that’s loading the processor. |
| System | % Total Processor Time | Average processor time for all processors in a multiprocessor system. There’s typically no reason to monitor this in a single processor system. |
| System | Context Switches/sec | The rate at which processors are switched between threads. A value of 8,000 with a % Processor Time of 90 or more indicates that you should enable SQL Server fiber mode (Windows NT fibers) scheduling. |
| System | Processor Queue Length | The number of threads waiting for processor time. A consistent value of 2 or more can indicate a processor bottleneck. This value is system-wide, not per processor. |
If you observe a significant difference in the “%Processor Time” values for the processors in a multiple processor system, it could indicate that SQL Server isn’t configured to use all available processors. If SQL Server is the only server application running on the computer, then all processors should be available to SQL Server. You can configure processor affinity through the Server Properties on the Processors page.
In most cases, a processor bottleneck indicates you need to either upgrade to a faster processor or to multiple processors. In some cases, you can reduce the processor load by fine-tuning your application.
Monitoring disk use
SQL Server is a disk-intensive application. Because of this, the disk subsystem is often a bottleneck to system performance. Disk subsystem components include the hard disk drives and controllers as well as CD-ROM drives and tape drives if used. Typically, the counters you need to monitor include those described in the following table.
| Object | Counter | Description |
| PhysicalDisk | % Disk Time | Should be consistently less than 90%. In RAID subsystems, the value can range higher than 100%, and you need to rely primarily on disk queue values. |
| PhysicalDisk | Avg. Disk Queue Length | Should be less than or equal to two times the number of spindles. |
| PhysicalDisk | Current Disk Queue Length | Number of disk requests waiting at any one time. Helps determine which activities are generating the most disk activity. |
| Memory | Page Faults/sec | Determine whether the disk activity is being generated by SQL Server or because of excessive paging. |
| SQLServer: Buffer Manager | Page Reads/sec | Determine the amount of disk activity being generated by SQL Server. |
You determine the percentage of the activity being generated by SQL Server by comparing SQL Server read/write activity with disk read/write activity. If the hard disk is configured with a single partition, use physical disk read/sec and write/sec counters. If the hard disk is configured as multiple partitions, you should use the logical disk read/sec and write/sec counters for those logical drives employed by SQL Server. Disk bottlenecks often windhampharmacy.com/depakote require either installation of faster disk subsystems or multiple hard disks and disk controllers to allow split reads and writes.
Monitoring memory use
There are two general categories of memory counters that you should monitor:
- Potential low-memory conditions in system memory.
- SQL Server memory usage so you can determine whether or not SQL Server is causing the problem.
The following table describes counters that monitor memory use.
| Object | Counter | Description |
| Memory | Available Bytes | Amount of memory available for processes and should be consistently higher than 5,000 KB. |
| Memory | Pages/sec | Should never climb consistently above 0. |
| Process | Page Faults/sec | A high value indicates excessive paging, which can result in both memory and the hard disk becoming performance bottlenecks. You must determine if the problem is being caused by SQL Server or by another process running on the system. |
| Process | Working Set | Monitor this value for each instance of SQL Server running on the system to determine the amount of system memory being used by SQL Server. This value should be greater than 5,000 KB at all times. |
| SQLServer: Buffer Manager | Buffer Cache Hit Ratio | For most applications, this value should be 90 or higher, indicating that most data requirements are being serviced from the cache. A lower value can indicate a memory bottleneck or a poorly designed application. |
| SQLServer: Buffer Manager | Total Pages | A low value here can indicate a need to install additional memory. Typically, a low number here is accompanied by a low Buffer Cache Hit Ratio. |
| SQLServer: Memory Manager | Total Server Memory (KB) | If this value is relatively high in comparison to total system memory, it’s a good indicator that you should install more memory. |
If at all possible, you should avoid running other server applications on a system that’s supporting SQL Server. If this isn’t possible and if you can’t monitor memory used by the other application directly, you must infer its memory usage by comparing system memory and SQL Server memory counters. In most situations, the preferred configuration is to let SQL Server manage memory use automatically.
]]>