For each SQL Server instance that is installed, Performance Monitor/System Monitor will have a number of SQL Server “specific performance objects added to it, each with a number of associated counters. Because you can now have multiple SQL Server instances on a single machine, each will have separate monitoring objects. You certainly wouldn't want to mix monitoring values across multiple instances. You have already seen a few of these as you were monitoring each major component of network, processors, memory, and disk systems. Table 37.5 shows the complete list of the installed SQL Server performance objects.
Table 37.5. SQL Server Performance Objects
The following sections will look at some of the most relevant performance objects and counters.
MSSQL:Cache Manager Object
For finding information about the operation of SQL Server's caches, the MSSQL:Cache Manager object holds a number of useful counters that measure such things as data cache, procedure, and trigger cache operations.
These cache counters allow you to watch how each of the caches is used and what each one's upper limit is. These useful counters help indicate whether additional physical memory would benefit SQL Server:
You can display each of these counters for specific cache instances, ranging from ad hoc SQL plans to procedure plans and trigger plans.
A few related cache counters provide more of an overview on the cache operations: MSSQL:Memory Manager:SQL Cache Memory and MSSQL:Memory Manager:Optimizer Memory.
The MSSQL:Buffer Manager object also contains a counter that pertains to the operation of the Read Ahead Manager: Readahead Pages/sec. The information returned by this counter will indicate how much work is done populating the page cache due to sequential scans of data. This might indicate the need to optimize certain queries, add more physical memory, or even consider pinning a table into the cache.
Monitoring SQL Server's Disk Activity
In the section "Monitoring the Disk System" earlier in this chapter, you saw how to monitor disk activity. Here, we will examine what SQL Server's contribution is to all of this disk activity. Disk activity can be categorized into reads and writes .
SQL Server carries out writes to the disk for the following processes:
Logging is a constant occurrence in any database that allows modifications, and SQL Server attempts to optimize this process by batching a number of writes together. To watch how much work is done on behalf of the database logs, examine the MSSQL:Databases:Log Bytes Flushed and MSSQL:Databases:Log Flushes/sec counters. The first tells you the quantity of the work, and the second tells you the frequency.
The third kind of write occurs to make space within the page cache. This is carried out by the Lazy Writer process, which we can track with the counter MSSQL:Buffer Manager:Lazy Writes.
It is easy to monitor the amount of reading SQL Server is doing using the counter MSSQL:Buffer Manager:Page Reads. All read and write counter values are combined server-level values.
One of the necessary areas of performance degradation is locking. However, you need to ensure that the correct types of locks are issued and that the worst kind of lock, a blocking lock, is kept to a minimum. A blocking lock, as its name implies, prevents other users from continuing their own work. An easy way to identify the level of blocking locks is to use the counter MSSQL:Memory Manager:Lock Blocks. If this counter indicates a value greater than 0 on a frequent basis, you need to examine the queries being executed or even revisit the database design.
Even though you cannot always trace performance problems directly to the number of users connected, it is a good idea to occasionally monitor how this number fluctuates. It is fairly easy to trace one particular user that is causing a massive performance problem.
The leverage point here is to see the current number of user connections with MSSQL: General Statistics User Connections counter in conjunction with other objects and counters. It is easy to say that the disk subsystem is a bottleneck, but how many users is SQL Server supporting at the time?
Another area of memory used by SQL Server exclusively is the procedure cache, and corresponding to the procedure cache is a large number of counters that provide insight on its utilization.
The procedure cache maintains pointers to the procedure buffer, which is where the executable from the stored procedures is actually kept. You can separately monitor the amount of memory used by the procedure buffers and cache.
For the procedure buffers, you can track how many are currently in use with MSSQL:Cache Manager:Object Counts:Procedure Plans. SQL Server also maintains a separate set of counters for the parts of the cache that are active as opposed to only in use. You can also track the total size of the procedure cache using the MSSQL:Cache Manager:Cache Pages:Procedure Plans counter, which is in 8KB pages. This counter value will fluctuate with the execution of each new stored procedure and other server activity.
You can extend the range of information that the Performance Monitor displays by creating up to 10 of your own counters. These user-defined counters appear under the MSSQL:User Settable:Query object, which contains the 10 counters as instances, starting with User Counter 1. You define your own counters by calling stored procedures with the names sp_user_counter1 through sp_user_counter10 , which are found in the master database.
These counters work differently than they did under previous versions and require you to call the stored procedures to update the information they return to the Performance Monitor. To make any real use of these stored procedures, you now need to call them within a loop or as part of a job that is scheduled on some recurring basis.
Using these counters allows you to monitor any information you want, whether it is system, database, or even object specific. The only restriction is that the stored procedure can only take a single integer value argument.
The following sample procedure sets the average connection time for all user connections. Processes that have a kernel ID ( kpid ) of 0 are system ones: checkpoint, Lazy Writer, and so on:
DECLARE @value INT SELECT @value = AVG( DATEDIFF( mi, login_time, GETDATE())) FROM master..sysprocesses WHERE kpid > 0 EXEC sp_user_counter1 @value
You could further extend this information by creating additional user procedures for returning the minimum and maximum times connected, as well as database usage. Your only limitation is that you can monitor only 10 pieces of information at one time.