MS SQL Server Summary attributes

The MS SQL Server Summary attribute group contains attributes that you can use to monitor summary information for SQL servers.

Cache Average Free Scan The average number of buffers scanned by the LazyWriter when the LazyWriter searches the data cache for an unused buffer to replenish the free buffer pool. If Microsoft SQL Server must read a large number of buffers to find a free one, server performance might degrade. A low value indicates optimal performance. The value format is an integer.

Cache Hit Ratio The current ratio of data cache hits to total requests. Use this attribute to check the effectiveness of the data cache. For optimal performance, the returned value must be approximately 95% or greater. The value format is an integer.

Cache Maximum Free Page Scan The maximum value for the number of buffers scanned by the LazyWriter when the LazyWriter searches the data cache for an unused buffer to replenish the free buffer pool. Use this attribute to check the effectiveness of the data cache. The value format is an integer.

Checkpoint Pages per Sec The number of pages flushed to disk per second by a checkpoint or other operation that require all dirty pages to be flushed.

CPU Idle Delta The difference in the CPU Idle between the current and the previous samples.

CPU Idle Sec The time (in seconds) that the SQL Server has been idle since it was last started. The value is in seconds even though the column name would imply milliseconds.

CPU Percent Idle The percentage of time that a database server has been idle during the current monitoring interval. Use this attribute to gauge how much of the CPU resource the database server uses so that you can allocate resources more efficiently. Also, you can use this attribute to determine how resource-intensive certain operations are. The value format is an integer.

CPU Usage Delta The difference in the CPU Usage between the current and the previous samples.

CPU Usage Sec The time (in seconds) that the CPU has spent working since the SQL Server was last started. The value is in seconds even though the column name would imply milliseconds.

Current® Interval The number of seconds that have elapsed between the previous sample and the current sample. The value format is an integer; for example, 90. A sample contains the data that IBM® Tivoli® Monitoring for Microsoft SQL Server collects about each SQL server. New data becomes available if a new interval has occurred and data has been refreshed at the CMS hub.

Data Cache Size The number of kilobytes (KB) allocated for the data cache memory. The SQL server uses the data cache to store data and index pages. The value format is an integer; for example, 1000. The cache is sometimes referred to as the buffer cache.

Host Name The name of the computer on which the SQL Server is running. The value format is an alphanumeric string with a maximum of 64 characters, for example, Voyager. This attribute is not available for use in situations or for historical data collection.

Hub Timestamp The time when this data was inserted at the hub. This attribute is not available for use in situations. Otherwise, this attribute is available to use like any other attribute, for example it is available for reports, queries, and workspaces.

Lazy Writes per Sec The number of buffers written per second by the buffer manager lazy writer. The lazy writer is a system process that flushes out batches of dirty, aged buffers and make them available to user processes. The lazy writer eliminates the need to perform frequent checkpoints to create available buffers.

Mixed Page Allocations Per Sec The number of pages that are allocated from mixed extents per second. These pages can be used for storing the Index Allocation Map (IAM) pages. The first eight pages from mixed extents are allocated to an allocation unit.

Originnode The key for the table view in the format serverid:hostname:MSS. This attribute is not available for use in situations. Otherwise, this attribute is available to use like any other attribute, for example it is available for reports, queries, and workspaces.

Page Life Expectancy The duration (in seconds) for which an SQL Server block or page is stored in the memory.

Page Reads per Sec The number of physical database page reads that are issued per second. This statistic displays the total number of physical page reads across all databases. Because physical IO is expensive, you might be able to minimize the cost, either by using a larger data cache, intelligent indexes, and more efficient queries, or by changing the database design.

Page Writes per Sec The number of physical database page writes issued.

Percent Max Locks The percentage of locks on resources of the maximum number of locks allowed by the SQL server. The value format is a percentage with two decimal places allowed; for example, 10.00. Setting a higher limit for the maximum number of locks does not impair performance. If your operations exceed the number of available locks, you can increase this limit.

Procedure Cache Size The number of kilobytes (KB) allocated for the procedure cache. The SQL server uses the procedure cache to compile queries and store procedures that are compiled. The value format is an integer; for example, 1000.

Readahead Pages per Sec The number of pages read in anticipation of use.

Repl Distribution Delivered Cmds per Sec The number of distribution commands delivered per second to the Subscriber.

Repl Distribution Delivered Trans per Sec The number of distribution transactions delivered per second to the Subscriber.

Repl Distribution Delivery Latency The distribution latency (in MS), the time it takes for transactions to be delivered to the Distributor and applied at the Subscriber.

Repl Logreader Delivered Cmds per Sec The number of Log Reader Agent commands delivered per second to the Distributor.

Repl Logreader Delivered Trans per Sec The number of Log Reader Agent transactions delivered per second to the Distributor.

Repl Logreader Delivery Latency The current amount of time (in MS) elapsed from when transactions are applied at the Publisher to when they are delivered to the Distributor.

Repl Merge Conflicts per Sec The number of conflicts per second that occurred in the Publisher or Subscriber upload and download. If the value is not zero, the value might require notifying the losing side, overriding the conflict, and so on.

Repl Merge Downloaded Changes per Sec The number of rows merged (inserted, updated, and deleted) per second from the Publisher to the Subscriber.

Repl Merge Uploaded Changes per Sec The number of rows merged (inserted, updated, and deleted) per second from the Subscriber to the Publisher.

Server The name of the SQL server. The value format is an alphanumeric string with a maximum of 30 characters; for example, CFS_SVR5.

Server CPU Pct IO The percentage of time used for I/O operations during the current monitoring interval. Use this attribute to gauge how much of the CPU resource the database server uses for I/O so you can allocate resources more efficiently. You also can use this attribute to determine how I/O resource-intensive certain operations are. The value format is an integer.

Server CPU Percent The percentage of CPU time the SQL server process is using on the host. The value format is a percentage with two decimal places allowed; for example, 20.00.

Server Type The type of SQL server. The value format is an alphanumeric string; for example, SQL Server.

Server Version The version of the SQL Server. The value format is the version in the format w.x.y.z; for example, 10.0.2531.0.

SQLServerAgent Failed Jobs Reports any jobs run by the SQLServerAgent service that have failed in the last monitoring interval. The first time you retrieve this attribute, it returns 0. The next time you retrieve it, it returns the total number of SQLServerAgent failed jobs found in the system history tables since the first run. These jobs include replication and user-defined jobs, such as maintenance or backup tasks. Use this attribute to alert you when scheduled tasks have failed. The value format is an integer.

Time Since Startup The number of minutes that have elapsed since the SQL server was started. The value format is an integer; for example, 360.

Total Blocking Lock Requests The total number of current locks blocking other processes. A blocking lock must be released before other processes requesting competing locks can progress. (For more information on locks, see the Microsoft SQL Server Books online.) Use this attribute to show the number of blocking locks active during server activity. This attribute can indicate that processes are being held up through lock contention rather than hardware performance issues. The value format is an integer.

Total Locks Remaining The total number of locks that can still be taken out. The maximum number of locks is configurable. In Microsoft SQL Server Version 8.0, you can have the number of locks configured automatically. Use this attribute to show the number of locks active during server activity. The value format is an integer.

Total OS CPU Percent The percentage of CPU time being used by all processes on the host. The value format is a percentage with two decimal places allowed; for example, 40.00.

Total Server Memory The total amount of dynamic memory (in KB) that the server is using currently.