SQL Performance Monitoring and Bottlenecks
Bottlenecks occur when hardware resources cannot keep up with the demands of the software. For example, when a software process, or combination of processes, wants more I/O from a disk than the disk can physically deliver, a bottleneck occurs at the disk. When the CPU subsystem becomes too saturated and processes are waiting, a bottleneck has occurred.
Bottlenecks are usually fixed in one of two ways. The first is to identify the limiting hardware and increase its capabilities. In other words, get a faster hard drive or increase the speed of the CPU. The second way is to make the software processes utilize the hardware more efficiently. This could be done by putting an index on a table so that either the disk I/O necessary to service a query is reduced, or the CPU units necessary to process a join are lessened.
The following are five key areas to monitor when tracking server performance and identifying bottlenecks. Each bottleneck candidate will have varied performance monitoring objects and counters to consider.
- Memory Usage: Refers to the amount of memory SQL Server needs, relative to itself and to the operating system memory. If SQL Server has enough memory, but the operating system is starved for memory, such that it has to frequently swap through the pagefile to disk, overall performance will suffer dramatically.
- CPU: High CPU utilization rates indicate the CPU subsystem is underpowered. Solutions could be upgrading the CPU or increasing the number of processors.
- Disk I/O performance: Failure of the disk or disk controller to satisfy read or write requirements in a timely manner impacts performance.
- User connections: Improperly setting the number of user connections could rob memory otherwise available to SQL Server.
- Blocking Locks: One process keeps another process from accessing or updating data. This is particularly noticeable to users and is the cause of some of your most server performance problems from a user perspective.
Memory Tuning: The Operating System and SQL Server
Start your intensive analysis of memory by looking at two counters,
- Memory: Available Bytes
- Memory: Pages Faults/sec
The Available Bytes counter tells how much memory is available for use by processes.
The Pages Faults/sec counter tells us the number of hard page faults, pages which have to be retrieved from the hard disk since they are not in working memory. It also includes the number of pages written to the hard disk to free space in the working set to support a hard page fault.
A low number for Available Bytes indicates that there may not be enough memory available; or processes, including SQL Server, may not be releasing memory. A high number of Pages Faults/sec indicate excessive paging. Taking a more in-depth look at individual instances of Process:Page Faults/sec, to see if the SQL Server process, for example, has excessive paging, may be necessary. A low rate of Pages Faults/sec (commonly 5-10 per second) is normal, as the operating system will continue to do some house keeping on the working set.
Starting with SQL Server 7.0, memory is auto-tuning by default. In general, though, you want to give SQL as much dedicated memory as possible. This is mostly dependent on what other application may be running on the server. By using the sp_configure stored procedure, you can set the values to MIN SERVER MEMORY and MAX SERVER MEMORY to dedicated values.
If SQL Server is the only application on the server, set MIN SERVER MEMORY and MAX SERVER MEMORY to the same value. If SQL Server co-exists with one or more applications, lower the MIN SERVER MEMORY setting to count for the memory demands of the other application(s). If the other application fails to start in a timely manner, it may be because SQL Server has been operating at, or near the MAX SERVER MEMORY setting, and is slow in releasing memory to the new, and now starved, application. In this instance, lower the value of MAX SERVER MEMORY. Obviously, MAX SERVER MEMORY always needs to be greater than, or equal to, MIN SERVER MEMORY.
NOTE: If you have installed and are running the Full-Text Search support (Microsoft Search service, also known as MSSearch), then you must set the max server memory option manually in order to leave enough memory for the MSSearch service to run. Microsoft supplies a handy formula here: Total Virtual Memory (SQL Server MAX + Virtual Memory for Other Processes) = 1.5 * Server Physical Memory.