1 Microsoft SQL Server Metrics

This chapter provides descriptions for all Microsoft SQL Server metric categories, and tables list and describe associated metrics for each category. The tables also provide user actions if any of the metrics for a particular category support user actions. Shaded rows represent key columns for a particular category.

Configuration Metrics

Configuration metrics consist of the following categories:

SQL Server Configuration
Registry Setting Configuration
Database Setting Configuration

SQL Server Configuration Metrics

The metrics in this category represent a SQL Server installation. The metrics contain details of the product and version of the SQL Server instance.

Table Name — MGMT_EMX_MSSQL_SQLSERVER
View Name — MGMT_EMX_MSSQL_SQLSERVER_VIEW

Default Collection Interval — Every 24 hours

Table 1-1 SQL Server Configuration Metrics

Metric	Description
Name	Name of the SQL Server.
Clustered	Whether the server belongs to a cluster.
Package	Product installed. 0 — Unknown 1 — Office 0 — Unknown 0 — Unknown 0 — Unknown
Product	Installed product.
Version	Installed version.
Operatingsystem	Operating system on which the installation is done.

Registry Setting Configuration Metrics

The metrics in this category contain the installation and run-time parameters of the SQL Server stored in the registry.

Table Name — MGMT_EMX_MSSQL_REGSETTING
View Name — MGMT_EMX_MSSQL_REGSETTING_VIEW

Default Collection Interval — Every 24 hours

Table 1-2 Registry Setting Configuration Metrics

Metric	Description
Setting ID (key column)	Instance of the SQL Server.
Agent Log File	Path and file name for the Agent log.
Backup Directory	Location of the backup files directory.
Case Sensitive	Comparison method for multi-byte character data is either case-sensitive or not.
Error Log Path	Operating system path and file name to be used for the SQL Server error log.
Master DB Path	The full path and file name of the operating system file containing the master database.
NT Event Logging	Whether the SQL Server uses the Windows NT application log. If TRUE, the SQL Server sends all events to the Windows NT application log and the SQL Server error log. If FALSE, the SQL Server sends events only to the SQL Server error log.
Number of Processors	Number of CPUs available to the SQL Server on the server.
Perf Mon Mode	Operating system path and file name to be used for the SQL Server error log. Windows NT Performance Monitor polling behavior when the monitor is launched. 0 — Continuous 1 — On demand
Registered Organization	Company name supplied by the installer.
Registered Owner	User name supplied by the installer.
Replication Installed	TRUE when components supporting replication are installed.
RPC Encrypt	Whether RPC encryption is enabled.
SNMP	Whether Simple Network Management Protocol (SNMP) is installed on an instance of the SQL Server.
SNMP Current Version	Version of Simple Management Protocol (SNMP) currently installed on an instance of the SQL Server.
Sort Order	Character set used and ordering applied.
SQL Data Root	Default operating system directory implementing storage for SQL Server system user-defined databases.
TCP Port	TCP/IP Sockets Net-Libraries port number on an instance of the SQL Server.

Database Setting Configuration Metrics

The metrics in this category contain the settings for a database. These settings control the access to and the behavior of the database.

Table Name — MGMT_EMX_MSSQL_DBSETTING
View Name — MGMT_EMX_MSSQL_DBSETTING_VIEW

Default Collection Interval — Every 24 hours

Table 1-3 Database Setting Configuration Metrics

Metric	Description
Setting ID (key column)	Database name.
SQL ServerName	Name of the SQL Server.
Offline	Whether the database is online. Also, whether the database is unavailable, or is being made unavailable, for use by authorized users.
Recovery Type	Whether the comparison method for multi-byte character data is case-sensitive or not. Type of recovery model that a database will use: Value — 0 Description — Simple Explanation — The database can be recovered only to the last full database backup or last differential backup. Value — 1 Description — Bulk Logged Explanation — Logging for all SELECT INTO, CREATE INDEX, and bulk loading data operations is minimal and therefore requires less log space. In exchange for better performance and less log space usage, the risk of exposure to loss is greater than with full recovery. Value — 2 Description — Full Explanation — Database backups and transaction log backups provide full recoverability from media failure. All operations are fully logged, including bulk operations such as SELECT INTO, CREATE INDEX, and bulk loading data. Value — 3 Description — Unknown Explanation — The recovery type is not known.
AutoClose	Whether the database is closed and its resources are freed when no user connection accesses the database.
AutoCreateStat	Whether the optimizer directs automatic creation of supporting data statistics as required.
AutoShrink	Whether operating system files maintaining table and index data are evaluated for downward resizing when the server periodically checks for unused space.
AutoUpdateStat	Whether the optimizer directs the automatic rebuilding of statistics.
CursorCloseOnCommit	Whether cursors are closed when a transaction is completed.
DataSpaceUsage	Amount of space in use and reserved for use of data in megabytes.
IndexSpaceUsage	Amount of space for the index in megabytes.
DBOUseOnly	Whether only users with the database ownership privilege can access the database.
SingleUser	Whether only one user can access the database at a given time.
ReadOnly	Whether the database is read-only.
DefaultCursor	Whether cursors declared in a batch are created with local scope.
SelectIntoBulkCopy	Whether non-logged operations are allowed.
TruncateLogOnChekpoint	Whether the SQL Server removes log entries referencing committed transactions when activity on the databases forces a dirty page write.

Access Methods Metrics

The metrics in this category search through and measure the allocation of SQL Server database objects, such as the number of index searches or number of pages that are allocated to indexes and data.

For Microsoft SQL Server 2000

Default Collection Interval — Every 30 minutes

Table 1-4 Access Methods Metrics

Metric	Description
Access Method Counter Name (key column)	Performance metric name. See Table 1–5.
Access Method Counter Value	Performance metric value.

The Access Method Counter Name key column contains several metrics. Table 1–5 provides a list of these metrics and a description for each.

Table 1-5 Access Method Counter Name Metrics

Metric	Description
Extents Deallocations/sec	Number of extents deallocated per second from database objects used for storing index or data records.
Extents Allocated/sec	Number of extents allocated per second to database objects used for storing index or data records.
Forwarded Records/sec	Number of records per second fetched through forwarded record pointers.
FreeSpace Page Fetches/sec	Number of pages returned per second by free space scans used to satisfy requests to insert record fragments.
FreeSpace Scans/sec	Number of scans per second that were initiated to search for free space in which to insert a new record fragment.
Full Scans/sec	Number of unrestricted full scans per second, which can be either base-table or full-index scans.
Index Searches/sec	Number of index searches per second. These are used to start range scans and single index record fetches and to reposition an index.
Mixed Page Allocations/sec	Number of pages allocated per second from mixed extents. These are used for storing the first eight pages that are allocated to an index or table.
Page Deallocations/sec	Number of pages deallocated per second from database objects used for storing index or data records.
Page Splits/sec	Number of page splits per second that occur because of overflowing index pages.
Pages Allocated/sec	Number of pages allocated per second to database objects used for storing index or data records.
Probe Scans/sec	Number of probe scans per second. These are used to directly find rows in an index or base table.
Range Scans/sec	Number of qualified range scans through indexes per second.
Scan Point Revalidations/sec	Number of times per second that the scan point had to be revalidated to continue the scan.
Skipped Ghosted Records/sec	Number of ghosted records per second skipped during scans.
Table Lock Escalations/sec	Number of times locks on a table were escalated.
Workfiles Created/sec	Number of workfiles created per second.
Worktables Created/sec	Number of work tables created per second.
Worktables from Cache Base	Denominator ("base") of a fraction that the performance counter Worktables from Cache ratio represents.
Worktables from Cache Ratio	Percentage of work tables created where the initial pages were immediately available in the work table cache.

For Microsoft SQL Server 2005

Default Collection Interval — Every 30 minutes

Table 1-6 Access Method Counter Name Metrics

Metric	Description
Extents Deallocations/sec	Number of extents deallocated per second from database objects used for storing index or data records.
Extents Allocated/sec	Number of extents allocated per second to database objects used for storing index or data records.
Forwarded Records/sec	Number of records per second fetched through forwarded record pointers.
FreeSpace Page Fetches/sec	Number of pages returned per second by free space scans used to satisfy requests to insert record fragments.
FreeSpace Scans/sec	Number of scans per second that were initiated to search for free space in which to insert a new record fragment.
Full Scans/sec	Number of unrestricted full scans per second, which can be either base-table or full-index scans.
Index Searches/sec	Number of index searches per second. These are used to start range scans and single index record fetches and to reposition an index.
Mixed Page Allocations/sec	Number of pages allocated per second from mixed extents. These are used for storing the first eight pages that are allocated to an index or table.
Page Deallocations/sec	Number of pages deallocated per second from database objects used for storing index or data records.
Page Splits/sec	Number of page splits per second that occur because of overflowing index pages.
Pages Allocated/sec	Number of pages allocated per second to database objects used for storing index or data records.
Probe Scans/sec	Number of probe scans per second. These are used to directly find rows in an index or base table.
Range Scans/sec	Number of qualified range scans through indexes per second.
Scan Point Revalidations/sec	Number of times per second that the scan point had to be revalidated to continue the scan.
Skipped Ghosted Records/sec	Number of ghosted records per second skipped during scans.
Table Lock Escalations/sec	Number of times locks on a table were escalated.
Workfiles Created/sec	Number of workfiles created per second.
Worktables Created/sec	Number of work tables created per second.
Worktables from Cache Base	Denominator ("base") of a fraction that the performance counter Worktables from Cache ratio represents.
Worktables from Cache Ratio	Percentage of work tables created where the initial pages were immediately available in the work table cache.

Agent Status Metrics

The metrics in this category provide information regarding the current status of the Agent.

Default Collection Interval — Every 5 minutes

Table 1-7 Agent Status Metrics

Metric	Description and User Action
Process ID	Process ID of the Sqlserver Agent process.
Server name	Name of the Sqlserver instance.
Software Home	Path of the Sqlserver process.
Sqlserver Agent Status	Status of the Sqlserver Agent process. When the status is not running, the SQL server Agent must be started.

Buffer Manager Metrics

The Buffer Manager object provides counters to monitor how Microsoft SQL Server uses:

Memory to store data pages, internal data structures, and the procedure cache.
Counters to monitor the physical I/O as the SQL Server reads database pages from, and writes database pages to, the disk.

For Microsoft SQL Server 2000

Default Collection Interval — Every 15 minutes

Table 1-8 Buffer Manager Metrics

Metric	Description
Buffer Manager Counter Name (key column)	Performance metric name. See Table 1–9.
Buffer Manager Counter Value	Performance metric value.

The Buffer Manager Counter Name key column contains several metrics. Table 1–9 provides a list of these metrics and a description for each.

Table 1-9 Buffer Manager Counter Name Metrics

Metric	Description
Buffer Cache Hit Ratio	Percentage of pages found in the buffer cache without having to read from disk. The ratio is the total number of cache hits divided by the total number of cache lookups since the SQL Server was started. After a long period of time, the ratio does not change very much. Because reading from the cache is much less expensive than reading from disk, this ratio should be high. Generally, you can increase the buffer cache hit ratio by increasing the amount of memory available to the SQL Server.
Buffer Cache Hit Ratio Base	Denominator ("base") of a fraction that the performance counter Buffer Cache Hit Ratio represents.
Checkpoint Pages/sec	Number of pages flushed to disk per second by a checkpoint or other operations that cause all dirty pages to be flushed to disk.
Database Pages	Total number of database pages.
Free List Stalls/sec	Number of requests that had to wait for a free page.
Free Pages	Total number of pages on all free lists.
Lazy Writes/sec	Number of buffers written per second by the buffer manager's lazy writer. The lazy writer is a system process that flushes out batches of dirty, aged buffers (buffers that contain changes that must be written back to disk before the buffer can be reused for a different page) and make them available to user processes. The lazy writer eliminates the need to perform frequent checkpoints in order to create available buffers.
Page Lookups/sec	Number of requests to find a page in the buffer pool.
Page Reads/sec	Number of physical database page reads issued per second. This statistic displays the total number of physical page reads across all databases. Because physical I/O is expensive, you may be able to minimize the cost by using a larger data cache, intelligent indexes, more efficient queries, or by changing the database design.
Page Writes/sec	Number of database page writes issued per second. Page writes are generally expensive. Reducing page-write activity is important for optimal tuning. One way to do this is to ensure that you do not run out of free buffers in the free buffer pool. If you do, page writes will occur while waiting for an unused cache buffer to flush.
Procedure Cache Pages	Number of pages used to store compiled queries.
Readahead Pages/sec	Number of pages read in anticipation of use.
Reserved Pages	Number of buffer pool reserved pages.
Stolen Pages	Number of pages used for miscellaneous server purposes (including procedure cache).
Target Pages	Ideal number of pages in the buffer pool.
Total Pages	Number of pages in the buffer pool (includes database, free, and stolen pages).

For Microsoft SQL Server 2005

Default Collection Interval — Every 15 minutes

Table 1-10 Buffer Manager Counter Name Metrics

Metric	Description
Buffer Cache Hit Ratio	Percentage of pages found in the buffer cache without having to read from disk. The ratio is the total number of cache hits divided by the total number of cache lookups since the SQL Server was started. After a long period of time, the ratio does not change very much. Because reading from the cache is much less expensive than reading from disk, this ratio should be high. Generally, you can increase the buffer cache hit ratio by increasing the amount of memory available to the SQL Server.
Buffer Cache Hit Ratio Base	Denominator ("base") of a fraction that the performance counter Buffer Cache Hit Ratio represents.
Checkpoint Pages/sec	Number of pages flushed to disk per second by a checkpoint or other operations that cause all dirty pages to be flushed to disk.
Database Pages	Total number of database pages.
Free List Stalls/sec	Number of requests that had to wait for a free page.
Free Pages	Total number of pages on all free lists.
Lazy Writes/sec	Number of buffers written per second by the buffer manager's lazy writer. The lazy writer is a system process that flushes out batches of dirty, aged buffers (buffers that contain changes that must be written back to disk before the buffer can be reused for a different page) and make them available to user processes. The lazy writer eliminates the need to perform frequent checkpoints in order to create available buffers.
Page Lookups/sec	Number of requests to find a page in the buffer pool.
Page Reads/sec	Number of physical database page reads issued per second. This statistic displays the total number of physical page reads across all databases. Because physical I/O is expensive, you may be able to minimize the cost by using a larger data cache, intelligent indexes, more efficient queries, or by changing the database design.
Page Writes/sec	Number of database page writes issued per second. Page writes are generally expensive. Reducing page-write activity is important for optimal tuning. One way to do this is to ensure that you do not run out of free buffers in the free buffer pool. If you do, page writes will occur while waiting for an unused cache buffer to flush.
Procedure Cache Pages	Number of pages used to store compiled queries.
Readahead Pages/sec	Number of pages read in anticipation of use.
Reserved Pages	Number of buffer pool reserved pages.
Stolen Pages	Number of pages used for miscellaneous server purposes (including procedure cache).
Target Pages	Ideal number of pages in the buffer pool.
Total Pages	Number of pages in the buffer pool (includes database, free, and stolen pages).

Cache Manager Metrics

The Cache Manager object provides counters to monitor how the Microsoft SQL Server uses memory to store objects such as stored procedures, ad hoc and prepared Transact-SQL statements, and triggers. Multiple instances of the Cache Manager object can be monitored at the same time, with each instance representing a different type of plan to monitor.

For Microsoft SQL Server 2000

Default Collection Interval — Every 15 minutes

Table 1-11 Cache Manager Metrics

Metric	Description
Cache Manager Counter Name (key column)	Performance metric name. See Table 1–12.
Cache Manager Instance Name (key column)	Instance for the Cache Manager counter name.
Cache Manager Counter Value	Performance metric value.

The Cache Manager Counter Name key column contains several metrics. Table 1–12 provides a list of these metrics and a description for each.

Table 1-12 Cache Manager Counter Name Metrics

Metric	Description
Cache Hit Ratio	Percentage of pages found in the cache without having to read from disk. The ratio is the total number of cache hits divided by the total number of cache lookups since the SQL Server was started. After a long period of time, the ratio does not change very much. Because reading from the cache is less expensive than reading from disk, this ratio should be high. Generally, you can increase the cache hit ratio by increasing the amount of memory available to the SQL Server.
Cache Hit Ratio Base	Denominator ("base") of a fraction that the performance counter Cache Hit Ratio represents.
Cache Pages	Number of pages used by objects in the cache. After a long period of time, the count does not change very much.
Cache Object Counts	Number of objects found in the cache. After a long period of time, the count does not change very much.
Cache Use Counts/sec	Number of times per second that each type of object in the cache has been used. The higher this value is, the better. After a long period of time, the count does not change very much.

For Microsoft SQL Server 2005

Default Collection Interval — Every 15 minutes

Table 1-13 Cache Manager Counter Name Metrics

Metric	Description
Name (key column)	Instance for the Cache Manager counter name.
Cache Hit Ratio	Percentage of pages found in the cache without having to read from disk. The ratio is the total number of cache hits divided by the total number of cache lookups since the SQL Server was started. After a long period of time, the ratio does not change very much. Because reading from the cache is less expensive than reading from disk, this ratio should be high. Generally, you can increase the cache hit ratio by increasing the amount of memory available to the SQL Server.
Cache Hit Ratio Base	Denominator ("base") of a fraction that the performance counter Cache Hit Ratio represents.
Cache Pages	Number of pages used by objects in the cache. After a long period of time, the count does not change very much.
Cache Object Counts	Number of objects found in the cache. After a long period of time, the count does not change very much.
Cache Use Counts/sec	Number of times per second that each type of object in the cache has been used. The higher this value is, the better. After a long period of time, the count does not change very much.

Database Metrics

The MSSQL_Database class represents a SQL Server database. Each SQL Server installation can contain one or more databases.

Default Collection Interval — Every 15 minutes

Table 1-14 Database Metrics

Metric	Description and User Action
SQL Server Name (key column)	SQL Server name, which is the instance ID specified in the database functional template.
Name (key column)	Database name.
Create Date	Time and date the database was created.
Database File Path	Primary location of the database files.
Database Status	Status of the database: 0 — Normal 32 — Loading 192 — Recovering 256 — Suspect 512 — Offline 1024 — Standby 32768 — Emergency Mode
Size (MB)	Total size of the database in megabytes. Allocate more space to the database if this metric decreases beyond the critical threshold.
Space Available %	Percentage of space that is available. Allocate more space to the database if this metric decreases beyond the critical threshold.
Space Available (KB)	Unused space in kilobytes. Allocate more space to the database if this metric decreases beyond the critical threshold.
Version	Version of Microsoft SQL Server used to create the referenced database.

Database Backup Metrics

The metrics in this category provide detailed backup information for all databases.

Default Collection Interval — Every 30 minutes

Table 1-15 Database Backup Metrics

Metric	Description
Database_name (key column)	Name of the database.
Name (key column)	Backup name.
Backup Finish Time	Time the backup finished.
Backup Start Time	Time the backup started.
File Location	Physical location of the files.
File Logical Name	Logical name of the files.
File Size	Size of the file.
File Type	D signifies a data file, and L signifies a log file.
Physical Device Name	Name of the physical device.
Unique Backup Set ID	Unique Backup Set UID for the backup.

Database Information Metrics

The metrics in this category report information about a specified database or all databases.

Default Collection Interval — Every 30 minutes

Table 1-16 Database Information Metrics

Metric	Description
Database_name (key column)	Name of the database.
Compatibility Level	Compatibility level of the database.
Database ID	Unique ID of the database.
Database Owner	Owner of the database.
Database Size	Current size of the database.
Date Created	Creation date of the database.
Status	Status of the database.

Database Job Metrics

The metrics in this category return information about jobs that are used by the SQLServerAgent service to perform automated activities in Microsoft SQL Server.

Default Collection Interval — Every 15 minutes

Table 1-17 Database Job Metrics

Metric	Description and User Action
job_id (key column)	Job identification number.
Computer Used to Send Network Messages	Name of the user or computer used when sending network messages.
Computer Used to Send Pages	Name of the user or computer used when sending a page.
Current Execution Status	0 — Returns only jobs that are not idle or suspended 1 — Executing 2 — Waiting for thread 3 — Between retries 4 — Idle 5 — Suspended 7 — Performing completion actions
Current Execution Steps in the Job	Current job execution step.
Current Retry Attempt	If the job is running and the step has been retried, this is the current retry attempt.
Delete Job Event	Bitmask indicating under what circumstances the job should be deleted when a job completes. Possible values are the same as for notify_level_eventlog.
Description	Description for the job.
Email of Operator	Email name of the operator to notify.
Enabled	Indicates whether the job is enabled to be executed.
ID of Next Run Schedule	Identification number of the next run schedule.
Job Category	The category to which the job belongs.
Job Creation Date	Date the job was created.
Job Modification Date	Date the job was last modified.
Job Owner	The owner of the job.
Job Type	1 — Local job 2 — Multiserver job 0 — Job has no target servers
Job Version Number	Version of the job, which is automatically updated each time the job is modified.
Last Run Date (mm-dd-yyyy)	Date the job last started executing.
Last Run Outcome	Outcome of the job the last time it ran: 0 — Failed 1 — Succeeded 3 — Canceled 5 — Unknown
Last Run Time (hh:mm:ss)	Time the job last started executing.
Name	Name of the job.
Next Run Date (mm-dd-yyyy)	Date the job is next scheduled to run.
Next Run Time (hh:mm:ss)	Time the job is next scheduled to run.
Notify Level Email	Bitmask indicating under what circumstances a notification email should be sent when a job completes. Possible values are the same as for notify_level_eventlog.
Notify Level Event Log	Bitmask indicating under what circumstances a notification event should be logged to the Microsoft Windows NT application log. Possible values are as follows: 0 — Never 1 — When a job succeeds 2 — When the job fails 3 — Whenever the job completes (regardless of the job outcome)
Notify Level Net Send	Bitmask indicating under what circumstances a network message should be sent when a job completes. Possible values are the same as for notify_level_eventlog.
Notify Level Page	Bitmask indicating under what circumstances a page should be sent when a job completes. Possible values are the same as for notify_level_eventlog.
Number of Job Schedules	Number of job schedules the job has.
Number of Job Steps	Number of job steps the job has.
Number of Target Servers	Number of target servers the job has.
Originating Server	Name of the server from which the job originated.
Start Step ID	ID of the step in the job where execution should begin.

Database Lock Metrics

The metrics in this category report information about locks.

Default Collection Interval — Every 15 minutes

Table 1-18 Database Lock Metrics

Metric	Description
spid (key column)	Server process ID of the current user process.
dbid (key column)	Database identification number requesting a lock.
ObjId (key column)	Object identification number of the object requesting a lock.
IndID (key column)	The index identification number.
Mode	Lock mode: Shared (S) Update (U) Exclusive (X) Intent Schema Bulk update (BU) RangeS_S — Shared range, shared resource lock; serializable range scan. RangeS_U — Shared range, update resource lock; serializable update scan. RangeI_N — Insert range, null resource lock. Used to test ranges before inserting a new key into an index. RangeX_X — Exclusive range, exclusive resource lock. Used when updating a key in a range.
Resource	Lock resource that corresponds to the value in syslockinfo.restext: RID, KEY, PAG, EXT, TAB, and DB
Status	The current status of the lock: GRANT, WAIT, and CNVT

Database Parameter Metrics

The Databases object in Microsoft SQL Server provides counters to monitor:

Bulk copy operations.
Backup and restore throughput.
Transaction log activities.

Monitoring transactions and the transaction log determine how much user activity is occurring in the database and how full the transaction log is becoming. The amount of user activity can determine the performance of the database and affect log size, locking, and replication. Monitoring low-level log activity to gauge user activity and resource usage can help you identify performance bottlenecks.

Default Collection Interval — Every 24 hours

Table 1-19 Database Parameter Metrics

Metric	Description and User Action
Name (key column)	Name of the Database Configuration parameter.
Current Value	Current value of the Database Configuration parameter.
Description	Text description of the configuration value.
Dynamic Reconfigure	Whether the parameter can be dynamically reconfigured. If TRUE, a modification to the value is immediately effective. If FALSE, modifications are visible only after the SQL Server service has been stopped and restarted.
ID	Parameter name.
Maximum Value	Upper bound for a configuration value.
Minimum Value	Lower bound for a configuration value.
Running Value	Value for the configuration option (value in syscurconfigs.value).

Table 1-20 ID Description Mapping

Metric	Description
101	Recovery interval.
102	Allow updates.
103	User Connections.
106	Locks.
107	Open objects
109	Fill factor.
115	Nested triggers.
117	Remote access.
124	Default language.
125	Language in cache.
502	Max async I/O.
503	Max worker threads.
505	Network packet size.
518	Show advanced option.
542	Remote proc trans.
543	Remote conn timeout.
1110	Time slice.
1123	Default sort order ID.
1124	Unicode local ID.
1125	Unicode comparison style.
1126	Language neutral.
1127	Two-digit year cutoff.
1505	Index create mem.
1514	Spin Counter.
1517	Priority boost.
1519	Remote login timeout.
1520	Remote query timeout.
1531	Cursor threshold.
1532	Set working set size.
1533	Resource timeout.
1534	User Options.
1535	Processor affinity mask.
1536	Max text repl size.
1537	Media retention.
1538	Cost threshold for parallelism.
1539	Max degree of parallelism.
1540	Min memory per query.
1541	Query wait.
1542	VLM size.
1543	Min memory.
1544	Max memory.
1545	Query max time.
1546	Lightweight pooling.

Database Performance Metrics

The Databases object in Microsoft SQL Server provides counters to monitor:

Bulk copy operations.
Backup and restore throughput.
Transaction log activities.

For Microsoft SQL Server 2000

Default Collection Interval — Every 15 minutes

Table 1-21 Database Performance Metrics

Metric	Description
Database Performance Counter Name (key column)	Performance metric name. See Table 1–22.
Database Performance Instance Name (key column)	Instance for the Database Performance Counter Name
Database Performance Counter Value	Performance metric value.

The Database Performance Counter Name key column contains several metrics. Table 1–22 provides a list of these metrics and a description for each.

Table 1-22 Database Performance Counter Name Metrics

Metric	Description
Active Transactions	Number of active transactions for the database.
Backup/Restore Throughput/sec	Read/write throughput for backup and restore operations of a database per second. For example, you can measure how the performance of the database backup operation changes when more backup devices are used in parallel or when faster devices are used. Throughput of a database backup or restore operation allows you to determine the progress and performance of your backup and restore operations.
Bulk Copy Rows/sec	Number of rows bulk-copied per second.
Bulk Copy Throughput/sec	Amount of data bulk-copied in kilobytes per second.
Data File(s) Size (KB)	Cumulative size (in kilobytes) of all the data files in the database including any automatic growth. Monitoring this counter is useful, for example, for determining the correct size of tempdb.
DBCC Logical Scan Bytes/sec	Number of logical read scan bytes per second for database consistency checker (DBCC) statements.
Log Bytes Flushed/sec	Total number of log bytes flushed.
Log Cache Hit Ratio	Percentage of log cache reads satisfied from the log cache.
Log Cache Reads/sec	Reads performed per second through the log manager cache.
Log File(s) Size	Cumulative size in kilobytes of all the transaction log files in the database.
Log File(s) Used Size (KB)	The cumulative used size of all the log files in the database.
Log Flush Wait Time	Total wait time in milliseconds to flush the log.
Log Flush Waits/sec	Number of commits per second waiting for the log flush.
Log Flushes/sec	Number of log flushes per second.
Log Growths	Total number of times the transaction log for the database has expanded.
Log Shrinks	Total number of times the transaction log for the database has contracted.
Log Truncations	Total number of times the transaction log for the database has truncated.
Percent Log Used	Percentage of space in the log that is in use.
Repl. Pending Xacts	Number of transactions in the transaction log of the publication database marked for replication, but not yet delivered to the distribution database.
Repl. Trans. Rate	Number of transactions per second read out of the transaction log of the publication database and delivered to the distribution database.
Shrink Data Movement Bytes/sec	Amount of data being moved per second by autoshrink operations, DBCC SHRINKDATABASE, or DBCC SHRINKFILE statements.
Transactions/sec	Number of transactions started for the database per second.

For Microsoft SQL Server 2005

Default Collection Interval — Every 15 minutes

Table 1-23 Database Performance Counter Name Metrics

Metric	Description
Name (key column)	Instance for the Database Performance Counter Name
Active Transactions	Number of active transactions for the database.
Backup/Restore Throughput/sec	Read/write throughput for backup and restore operations of a database per second. For example, you can measure how the performance of the database backup operation changes when more backup devices are used in parallel or when faster devices are used. Throughput of a database backup or restore operation allows you to determine the progress and performance of your backup and restore operations.
Bulk Copy Rows/sec	Number of rows bulk-copied per second.
Bulk Copy Throughput/sec	Amount of data bulk-copied in kilobytes per second.
Data File(s) Size (KB)	Cumulative size (in kilobytes) of all the data files in the database including any automatic growth. Monitoring this counter is useful, for example, for determining the correct size of tempdb.
DBCC Logical Scan Bytes/sec	Number of logical read scan bytes per second for database consistency checker (DBCC) statements.
Log Bytes Flushed/sec	Total number of log bytes flushed.
Log Cache Hit Ratio	Percentage of log cache reads satisfied from the log cache.
Log Cache Reads/sec	Reads performed per second through the log manager cache.
Log File(s) Size	Cumulative size in kilobytes of all the transaction log files in the database.
Log File(s) Used Size (KB)	The cumulative used size of all the log files in the database.
Log Flush Wait Time	Total wait time in milliseconds to flush the log.
Log Flush Waits/sec	Number of commits per second waiting for the log flush.
Log Flushes/sec	Number of log flushes per second.
Log Growths	Total number of times the transaction log for the database has expanded.
Log Shrinks	Total number of times the transaction log for the database has contracted.
Log Truncations	Total number of times the transaction log for the database has truncated.
Percent Log Used	Percentage of space in the log that is in use.
Repl. Pending Xacts	Number of transactions in the transaction log of the publication database marked for replication, but not yet delivered to the distribution database.
Repl. Trans. Rate	Number of transactions per second read out of the transaction log of the publication database and delivered to the distribution database.
Shrink Data Movement Bytes/sec	Amount of data being moved per second by autoshrink operations, DBCC SHRINKDATABASE, or DBCC SHRINKFILE statements.
Transactions/sec	Number of transactions started for the database per second.

Database Session Metrics

The metrics in this category provide information about current Microsoft SQL Server users and processes. The information returned can be filtered to return only the processes that are not idle.

Default Collection Interval — Every 15 minutes

Table 1-24 Database Session Metrics

Metric	Description
spid (key column)	System process ID.
ecid (key column)	Execution context ID of a given thread associated with a specific SPID. ECID = {0, 1, 2, 3, ...n}, where 0 always represents the main or parent thread, and {1, 2, 3, ...n} represent the sub-threads.
Block	System process ID for the blocking process, if one exists. Otherwise, this column is zero. When a transaction associated with a given SPIF is blocked by an orphan distributed transaction, this column returns a -2 for the blocking orphan transaction.
Command	SQL Server command (Transact-SQL statement, SQL Server internal engine process, and so forth) executing for the process.
Database Name	Database used by the process.
Host Name	Host or computer name for each process.
Login Name	Login name associated with the particular process.
Status	Process status.

Error Log Entry Metrics

The MSSQL_ErrorLogEntry class represents the entries in a SQL Service error log.

For Microsoft SQL Server Database 2000

Default Collection Interval — Every 30 minutes

Table 1-25 Error Log Entry Metrics

Metric	Description
ArchiveID (key column)	Archive number of the log the entry is stored in. The active log has number 0.
EntryID (key column)	Number of the entry within a log. Each entry in a log is successively numbered.
Text	Text message for the corresponding error log entry.

For Microsoft SQL Server Database 2005

Default Collection Interval — Every 60 minutes

Table 1-26 Database Performance Counter Name Metrics

Metric	Description
Type of Record (key column)	Specifies the type of event. This is an enumerated string
Record Number (key column)	Identifies the event within the Windows logfile (for example, NT Eventlog logfile). This is specific to the logfile and is used together with the logfile name to uniquely identify an instance of this class.
Event Log Entry	Name of Windows logfile (for example, NT Eventlog logfile). This is used together with the RecordNumber to uniquely identify an instance of this class.
Source	Variable-length null-terminated string specifying the name of the source (application, service, driver, subsystem) that generated the entry. It is used together with the EventIdentifier to uniquely identify an NT event.
Event Code	This property has the value of the lower 16-bits of the EventIdentifier property. It is present to match the value displayed in the NT Event Viewer. Two events from the same source may have the same value for this property but may have different severity and EventIdentifier values
Event Identifier	Identifies the event. This is specific to the source that generated the event log entry, and is used, together with SourceName, to uniquely identify an NT event type.
Date-Time	Date and time of event generation.
Event Severity	Indicates the severity of the event. Two events from the same source may have may have different severity and EventIdentifier values.
Category	Specifies a subcategory for this event. This subcategory is source specific.
User	User name of the logged on user when the event ocurred. If the user name cannot be determined this will be NULL.
Event Message	Event message as it appears in the NT Eventlog. This is a standard message with zero or more insertion strings supplied by the source of the NT event. The insertion strings are inserted into the standard message in a predefined format. If there are no insertion strings or there is a problem inserting the insertion strings, only the standard message will be present in this field.

General Statistics Metrics

The General Statistics object in Microsoft SQL Server provides counters to monitor general server-wide activity, such as the number of current connections and the number of users connecting and disconnecting per second from computers running an instance of SQL Server. This can be useful when you are working on large online transaction processing (OLTP) systems where many clients connect and disconnect from an instance of SQL Server.

For Microsoft SQL Server 2000

Default Collection Interval — Every 30 minutes

Table 1-27 General Statistics Metrics

Metric	Description
General Statistics Counter Name (key column)	Performance metric name. See Table 1–28.
General Statistics Counter Value	Performance metric value.

The General Statistics Counter Name key column contains several metrics. Table 1–28 provides a list of these metrics and a description for each.

Table 1-28 General Statistics Counter Name Metrics

Metric	Description
Logins/sec	Total number of logins started per second.
Logouts/sec	Total number of logout operations started per second.
User Connections	Number of user connections. Because each user connection consumes some memory, configuring overly high numbers of user connections could affect throughput. User connections should be set to the maximum expected number of concurrent users.

For Microsoft SQL Server 2005

Default Collection Interval — Every 30 minutes

Table 1-29 General Statistics Counter Name Metrics

Metric	Description
Logins/sec	Total number of logins started per second.
Logouts/sec	Total number of logout operations started per second.
User Connections	Number of user connections. Because each user connection consumes some memory, configuring overly high numbers of user connections could affect throughput. User connections should be set to the maximum expected number of concurrent users.

Integrated Security Setting Metrics

Note:

This metric is supported for SQLServer Database 2000 targets but not for SQLServer Database 2005 targets.

The MSSQL_IntegratedSecuritySetting class represents the security settings of a SQL Server installation. This setting affects all login connections to the SQL Server regardless of the login authentication type.

Default Collection Interval — Every 24 hours

Table 1-30 Integrated Security Setting Metrics

Metric	Description and User Action
SettingID (key column)	SQL Server name.
Audit Level	Indicates the current audit level security setting. Possible values are shown below. You can change the value for the desired auditing level. 0 Description — None Explanation — Do not log authentication attempts. 1 Description — Audit Login Success Explanation — Log successful authentication. 2 Description — Audit Login Failure Explanation — Log failed authentication. 3 Description — Audit All Explanation — Log all authentication attempts regardless of success or failure.
Impersonate Client	Indicates the current audit level security setting as shown Security context for non-administrative users executing xp_cmdshell. If TRUE, xp_cmdshell runs in the security context of the client connection. If FALSE, xp_cmdshell runs in the security context of the SQL Server Agent.
Security Mode	Indicates the current security mode. Possible values are shown below. You can change the value for the desired security mode. 0 Description — None Explanation — Do not log authentication attempts. 1 Description — Audit Login Success Explanation — Log successful authentication. 2 Description — Audit Login Failure Explanation — Log failed authentication. 3 Description — Audit All Explanation — Log all authentication attempts regardless of success or failure.

Last Database Backup Metrics

The metrics in this category provide the last backup information for all databases.

Default Collection Interval — Every 30 minutes

Table 1-31 Last Database Backup Metrics

Metric	Description
database_name (key column)	Name of the database.
Days Since Last Backup	Number of days since the last backup of the database.
Last Backup Date	Date when the last backup of the database was initiated.

Latches Metrics

The Latches object in the Microsoft SQL Server provides counters to monitor internal SQL Server resource locks called latches. Monitoring the latches to determine user activity and resource usage can help you identify performance bottlenecks.

For Microsoft SQL Server 2000

Default Collection Interval — Every 15 minutes

Table 1-32 Latches Metrics

Metric	Description
Latch Counter Name (key column)	Performance metric name. See Table 1–33
Latch Counter Value	Performance metric value.

The Latch Counter Name key column contains several metrics. Table 1–33 provides a list of these metrics and a description for each.

Table 1-33 Latch Counter Name Metrics

Metric	Description
Average Latch Wait Time (ms)	Average latch wait time in milliseconds for latch requests that had to wait.
Average Latch Wait Time Base	Denominator ("base") of a fraction that the performance counter Average Latch Wait Time represents.
Latch Waits/sec	Number of latch requests that could not be granted immediately and had to wait before being granted.
Total Latch Wait Time	Total latch wait time in milliseconds for latch requests that had to wait in the last second.

For Microsoft SQL Server 2005

Default Collection Interval — Every 15 minutes

Table 1-34 Latch Counter Name Metrics

Metric	Description
Average Latch Wait Time (ms)	Average latch wait time in milliseconds for latch requests that had to wait.
Average Latch Wait Time Base	Denominator ("base") of a fraction that the performance counter Average Latch Wait Time represents.
Latch Waits/sec	Number of latch requests that could not be granted immediately and had to wait before being granted.
Total Latch Wait Time	Total latch wait time in milliseconds for latch requests that had to wait in the last second.

Login Metrics

The MSSQL_Login class represents the login authentication records present in a SQL Server installation.

Default Collection Interval — Every 5 minutes

Table 1-35 Login Metrics

Metric

Description

Name (key column)

User name.

SQL ServerName (key column)

SQL Server name.

Type

0 — Other NT user authentication 1 — NT group 2 — SQL server authentication

MSSQL Database File Metrics

The MSSQL_DatabaseFile class is an extension to the CIM_DataFile class. It contains properties that are relevant to an operating system file that is also a file storing SQL Server database data.

Default Collection Interval — Every 30 minutes

Table 1-36 MSSQL Database File Metrics

Metric	Description
Name (key column)	User name.
SQL ServerName (key column)	SQL Server name.
Database Name (key column)	Name of the database.
FileGroup Name (key column)	Name of the File Group.
Database File Path	Complete path of the database file.
File Growth	Growth increment of the operating system file that stores table, index, or log data. When FileGrowthType is in megabytes, the FileGrowth value represents the number of megabytes of disk space to allocate for incremental file growth. When FileGrowthType is percent, the value represents a percentage and must be in the range from 1 through 100.
File Growth Type	Method of incremental allocation applied when an operating system file is extended. 0 — Megabyte 1 — Percent 99 — Invalid
Maximum Size	Upper limit for the size of an operating system file containing table and index data, or maintaining a database transaction log.
Primary File	Whether the database file is the one that maintains the database-specific system tables. A SQL Server database can have only one primary file.
Space Available in MB	Amount of disk resources, in megabytes, allocated and unused in operating system files.

MSSQL File Group Metrics

The MSSQL_FileGroup class represents the groups of operating system files that store a database. A SQL Server filegroup categorizes the operating system files containing data from a single SQL Server database to simplify database administration tasks, such as a backup. A filegroup cannot contain the operating system files of more than one database, though a single database can contain more than one filegroup.

Default Collection Interval — Every 30 hours

Table 1-37 MSSQL File Group Metrics

Metric	Description
DatabaseName (key column)	Name of the database.
Name (key column)	FileGroup name.
SQL ServerName (key column)	Name of the SQL Server.
Default	Whether the filegroup is the default filegroup during table or index creation.
Read Only	Whether the filegroup is read only.
Size	Total size of the file group in megabytes.
Type	Filegroup type. A database is created on exactly one filegroup named PRIMARY. This is the primary filegroup. After database creation, you can add a filegroup to the database, called a user-defined file group. 0 — User-defined 8 — On read-only media 16 — Primary

MSSQL Transaction Log Metrics

Note:

This metric is supported for SQLServer Database 2000 targets but not for SQLServer Database 2005 targets.

The MSSQL_TransactionLog class represents the transaction log of a Microsoft SQL Server database. A SQL Server transaction log maintains a record of modifications to the operating system files containing the data of an SQL Server database. The transaction log provides data recovery assistance if a system failure occurs, and a SQL Server database has at least one operating system file that stores transaction log records. A transaction log can be written to more than one operating system file. Each SQL Server database maintains its own transaction log, and the operating system file or files that store log records cannot be shared with another database.

Default Collection Interval — Every 30 minutes

Table 1-38 MSSQL Transaction Log Metrics

Metric	Description
Name (key column)	FileGroup name.
SQL ServerName (key column)	Name of the SQL Server.
Initial Size	Initial size of the database.
Last Backup	Time of the last backup.
Space Available	Space available in the database.

Memory Manager Metrics

The Memory Manager object in Microsoft SQL Server provides counters that enable you to monitor overall server memory usage to gauge user activity and resource usage. This can help you identify performance bottlenecks.

For Microsoft SQL Server 2000

Default Collection Interval — Every 15 minutes

Table 1-39 Memory Manager Metrics

Metric	Description
Memory Manager Counter Name (key column)	Performance metric name. See Table 1–40.
Memory Manager Counter Value	Performance metric value.

The Memory Manager Counter Name key column contains several metrics. Table 1–40 provides a list of these metrics and a description for each.

Table 1-40 Memory Manager Counter Name Metrics

Metric	Description
Connection Memory (KB)	Total amount of dynamic memory the server is using for maintaining connections.
Granted Workspace Memory (KB)	Total amount of memory currently granted to executing processes such as hash, sort, bulk copy, and index creation operations.
Lock Memory (KB)	Total amount of dynamic memory the server is using for locks.
Lock Blocks Allocated	Current number of allocated lock blocks. At server startup, the number of allocated lock blocks plus the number of allocated lock owner blocks depends on the SQL Server Locks configuration option. If more lock blocks are needed, the value increases.
Lock Owner Blocks Allocated	Current number of allocated lock owner blocks. At server startup, the number of allocated lock owner blocks plus the number of allocated lock blocks depends on the SQL Server Locks configuration option. If more lock owner blocks are needed, the value increases dynamically.
Lock Blocks	Current number of lock blocks in use on the server (refreshed periodically). A lock block represents an individual locked resource, such as a table, page, or row.
Lock Owner Blocks	Number of lock owner blocks currently in use on the server (refreshed periodically). A lock owner block represents the ownership of a lock on an object by an individual thread. Therefore, if three threads each have a shared (S) lock on a page, there will be three lock owner blocks.
Maximum Workspace Memory (KB)	Maximum amount of memory available for executing processes such as hash, sort, bulk copy, and index creation operations.
Memory Grants Outstanding	Total number of processes per second that have successfully acquired a workspace memory grant.
Memory Grants Pending	Total number of processes per second waiting for a workspace memory grant.
Optimizer Memory (KB)	Total amount of dynamic memory the server is using for query optimization.
SQL Cache Memory (KB)	Total amount of dynamic memory the server is using for the dynamic SQL cache.
Target Server Memory (KB)	Total amount of dynamic memory the server is willing to consume.
Total Server Memory (KB)	The memory allocated to the SQL Server.

For Microsoft SQL Server 2005

Default Collection Interval — Every 15 minutes

Table 1-41 Memory Manager Counter Name Metrics

Metric	Description
Connection Memory (KB)	Total amount of dynamic memory the server is using for maintaining connections.
Granted Workspace Memory (KB)	Total amount of memory currently granted to executing processes such as hash, sort, bulk copy, and index creation operations.
Lock Memory (KB)	Total amount of dynamic memory the server is using for locks.
Lock Blocks Allocated	Current number of allocated lock blocks. At server startup, the number of allocated lock blocks plus the number of allocated lock owner blocks depends on the SQL Server Locks configuration option. If more lock blocks are needed, the value increases.
Lock Owner Blocks Allocated	Current number of allocated lock owner blocks. At server startup, the number of allocated lock owner blocks plus the number of allocated lock blocks depends on the SQL Server Locks configuration option. If more lock owner blocks are needed, the value increases dynamically.
Lock Blocks	Current number of lock blocks in use on the server (refreshed periodically). A lock block represents an individual locked resource, such as a table, page, or row.
Lock Owner Blocks	Number of lock owner blocks currently in use on the server (refreshed periodically). A lock owner block represents the ownership of a lock on an object by an individual thread. Therefore, if three threads each have a shared (S) lock on a page, there will be three lock owner blocks.
Maximum Workspace Memory (KB)	Maximum amount of memory available for executing processes such as hash, sort, bulk copy, and index creation operations.
Memory Grants Outstanding	Total number of processes per second that have successfully acquired a workspace memory grant.
Memory Grants Pending	Total number of processes per second waiting for a workspace memory grant.
Optimizer Memory (KB)	Total amount of dynamic memory the server is using for query optimization.
SQL Cache Memory (KB)	Total amount of dynamic memory the server is using for the dynamic SQL cache.
Target Server Memory (KB)	Total amount of dynamic memory the server is willing to consume.
Total Server Memory (KB)	The memory allocated to the SQL Server.

Memory Statistics Metrics

The metrics in this category provide information about various memory-related performance issues.

Default Collection Interval — Every 15 minutes

Table 1-42 Memory Statistics Metrics

Metric	Description and User Action
SQL ServerName (key column)	Name of the SQL Server.
Average Latch Wait Time	Average latch wait time in milliseconds for latch requests that had to wait. If this number is high, your server might have resource limitations.
Buffer Cache Hit Ratio	Percentage of pages found in the buffer cache without having to read from disk. The ratio is the total number of cache hits divided by the total number of cache lookups since the SQL Server was started. After a long period of time, the ratio does not change very much. Because reading from the cache is much less expensive than reading from disk, this ratio should be high. Generally, you can increase the buffer cache hit ratio by increasing the amount of memory available to the SQL Server.
Cache Hit Ratio	Percentage of pages found in the cache without needing to read from disk. The ratio is the total number of cache hits divided by the total number of cache lookups since the SQL Server was started. After a long period of time, the ratio does not change very much. Because reading from the cache is less expensive than reading from disk, this ratio should be high. The higher this value is, the better. Generally, you can increase the cache hit ratio by increasing the amount of memory available to the SQL Server.
Log Flush Wait Time	Log cache is very important, because it rolls back a transaction before it is committed if the circumstances warrant. But after a transaction is complete (and no longer can be rolled back), this log cache is immediately flushed to the physical log file. This is a normal procedure. SELECT queries that do not modify data do not create transactions and do not produce log flushes. Essentially, a log flush occurs when data is written from the log cache to the physical log file. Therefore, a log flush occurs every time a transaction completes, and the number of log flushes that occur are related to the number of transactions performed by the SQL Server. One way to troubleshoot the disk I/O bottleneck is to capture the Log Flushes/sec counter data and see how busy this mechanism is. If the server experiences a lot of transactions, it will also experience a lot of log flushes, so the value you see for this counter can vary from server to server, depending on how busy it is with action-type queries that create transactions. Try to identify situations where the number of log flushes per second seems to be significantly higher than the expected number of transactions that you think should be running on a server.
Total Lock Wait Time	Total wait time in milliseconds for locks in the last second. If the value is high, your server has high resource contention.

Processor Metrics

The Win32_Processor class represents a device that is capable of interpreting a sequence of machine instructions on a Win32 computer system. On a multiprocessor machine, one instance of this class exists for each processor.

Default Collection Interval — Every 15 minutes

Table 1-43 Processor Metrics

Metric	Description and User Action
CPU Status (key column)	Device ID for the device.
CPU Status	Status of the CPU.
Load Percentage	Usage of the CPU. If the value increases above the critical threshold, this indicates a possible risk to the processor.

Response Metrics

This metrics category provide information about the response of the target Sqlserver Instance.

Default Collection Interval — Every 5 minutes

Table 1-44 Response Metrics

Metric	Description and User Action
Process ID	Process ID of the Sqlserver process.
Server Name	Name of the instance of the Sqlserver.
Software Home	Path of the Sqlserver process.
Sqlserver Status	Status of the Sqlserver process. When the status is not Running, the SQL Server must be started.

SQL Server Locks Metrics

The Locks object in the Microsoft SQL Server provides information about SQL Server locks on individual resource types. Locks are held on SQL Server resources, such as rows read or modified during a transaction, to prevent concurrent use of resources by multiple transactions. For example, if an exclusive (X) lock is held on a row within a table by a transaction, no other transaction can modify that row until the lock is released. Minimizing locks increases concurrency, which can improve performance. Multiple instances of the Locks object can be monitored at the same time, with each instance representing a lock on a resource type.

For Microsoft SQL Server 2000

Default Collection Interval — Every 15 minutes

Table 1-45 SQL Server Locks Metrics

Metric	Description
SQL Server Locks Counter Name (key column)	Performance metric name. See Table 1–46.
SQL Server Locks Instance Name (key column)	Instance for the SQL Server Locks Counter Name.
SQL Server Locks Counter Value	Performance metric value.

The SQL Server Locks Counter Name key column contains several metrics. Table 1–46 provides a list of these metrics and a description for each.

Table 1-46 SQL Server Locks Counter Name Metrics

Metric	Description
Average Wait Time (ms)	Average amount of wait time in milliseconds for each lock request that resulted in a wait.
Average Wait Time Base	Denominator ("base") of a fraction that the performance counter Average Wait Time ratio represents.
Lock Requests/sec	Number of new locks and lock conversions per second requested from the lock manager.
Lock Timeouts/sec	Number of lock requests per second that timed out, including internal requests for NOWAIT locks.
Lock Waits/sec	Number of lock requests per second that could not be satisfied immediately and required the caller to wait.
Lock Wait Time (ms)	Total wait time in milliseconds for locks in the last second.
Number of Deadlocks/sec	Number of lock requests per second that resulted in a deadlock.

For Microsoft SQL Server 2005

Default Collection Interval — Every 15 minutes

Table 1-47 SQL Server Locks Counter Name Metrics

Metric	Description
Name (key column)	Instance for the SQL Server Locks Counter Name.
Average Wait Time (ms)	Average amount of wait time in milliseconds for each lock request that resulted in a wait.
Average Wait Time Base	Denominator ("base") of a fraction that the performance counter Average Wait Time ratio represents.
Lock Requests/sec	Number of new locks and lock conversions per second requested from the lock manager.
Lock Timeouts/sec	Number of lock requests per second that timed out, including internal requests for NOWAIT locks.
Lock Waits/sec	Number of lock requests per second that could not be satisfied immediately and required the caller to wait.
Lock Wait Time (ms)	Total wait time in milliseconds for locks in the last second.
Number of Deadlocks/sec	Number of lock requests per second that resulted in a deadlock.

SQL Server Process Metrics

The MSSQL_Process class represents SQL Server processes. Note that these are not the same as an operating system's notion of a process. These are the processes identified by the SQL Server and assigned a SQL Server process ID by the SQL Server.

Default Collection Interval — Every 15 minutes

Table 1-48 SQL Server Process Metrics

Metric	Description and User Action
SQL ServerName (key column)	SQL Server name.
CPU Time (key column)	Process ID.
Blocked Process ID	ID of a process being blocked by the process.
CPU Time	Cumulative CPU usage time of the process.
Client Name	Name of the client application.
Command	Abbreviated indicator of the current command. When no command is current, it has a value of AWAITING COMMAND.
Creation Date	Time that the process began executing.
Database Name	Database currently being used by the process.
Execution State	Current operating condition of the process. Possible values are as shown: 0 — Unknown 1 — Other 2 — Ready 3 — Running 4 — Blocked 5 — Suspended Blocked 6 — Suspended Ready
Host Name	Name of the client workstation that started the SQL Server process.
Kernel Mode Time	Time in kernel mode in milliseconds. If this information is not available, a value of 0 should be used.
Login	Login used by the process to connect to the SQL Server.
Memory Usage	Number of pages in the procedure cache that are currently allocated to this process. A negative number indicates that the process is freeing memory allocated by another process.
Priority	If a priority is not defined for a process, a value of 0 should be used.
State	Whether the process is running or sleeping.
Termination Date	Time that the process was stopped or terminated.
User Mode Time	Number of milliseconds in user mode. If this information is not available, a value of 0 should be used.
Working Set Size	Amount of memory in bytes that a process needs to execute efficiently for an operating system that uses page-based memory management. If a sufficient amount of memory is unavailable (< working set size), thrashing occurs. If this information is not known, NULL or 0 should be entered. If this data is provided, it could be monitored to understand a process' changing memory requirements as execution proceeds.

SQL Server Role Metrics

The MSSQL_Role class represents a database role or a SQL Server role. Roles establish groups of users with similar security attributes. Permissions can be granted by role, simplifying security planning and administration.

Default Collection Interval — Every 30 minutes

Table 1-49 SQL Server Role Metrics

Metric	Description
Name	Role name.
SQL ServerName	Name of the SQL Server.
Full Name	Descriptive title for the role.

SQL Statistics Metrics

The SQL Statistics object in the Microsoft SQL Server provides counters to monitor compilation and the type of requests sent to an instance of the SQL Server. Monitoring the number of query compilations and recompilations and the number of batches received by an instance of the SQL Server indicates how quickly the SQL Server is processing user queries and how effectively the query optimizer is processing the queries.

For Microsoft SQL Server 2000

Default Collection Interval — Every 10 minutes

Table 1-50 SQL Statistics Metrics

Metric	Description
SQL Statistics Counter Name (key column)	Performance metric name. See Table 1–51.
SQL Statistics Counter Value (key column)	Performance metric value.

The SQL Statistics Counter Name key column contains several metrics. Table 1–51 provides a list of these metrics and a description for each.

Table 1-51 SQL Statistics Counter Name Metrics

Metric	Description
Auto-Param Attempts/sec	Number of auto-parameterization attempts per second. Total should be the sum of the failed, safe, and unsafe auto-parameterizations. Auto-parameterization occurs when the SQL Server attempts to reuse a cached plan for a previously executed query that is similar as the current query, but not exactly the same. For more information, see "Auto-parameterization" in the Microsoft SQL Server Introduction.
Batch Requests/sec	Number of Transact-SQL command batches received per second. This statistic is affected by all constraints (such as I/O, number of users, cache size, complexity of requests, and so forth). High batch requests mean good throughput. For more information, see "Batch Processing" in the Microsoft SQL Server Introduction.
Safe Auto-Params/sec	Number of safe auto-parameterization attempts per second.
SQL Compilations/sec	Number of SQL compilations per second. Indicates the number of times the compile code path is entered. Includes compiles due to recompiles. After SQL Server user activity is stable, this value should reach a steady state.
SQL Recompilations/sec	Number of SQL recompiles per second. Counts the number of times recompiles are triggered. Generally, the number of recompiles should be low.
Unsafe Auto-Params/sec	Number of unsafe auto-parameterization attempts per second. The table has characteristics that prevent the cached plan from being shared. These are designated as unsafe. The fewer of these that occur the better.

For Microsoft SQL Server 2005

Default Collection Interval — Every 10 minutes

Table 1-52 SQL Statistics Counter Name Metrics

Metric	Description
Auto-Param Attempts/sec	Number of auto-parameterization attempts per second. Total should be the sum of the failed, safe, and unsafe auto-parameterizations. Auto-parameterization occurs when the SQL Server attempts to reuse a cached plan for a previously executed query that is similar as the current query, but not exactly the same. For more information, see "Auto-parameterization" in the Microsoft SQL Server Introduction.
Batch Requests/sec	Number of Transact-SQL command batches received per second. This statistic is affected by all constraints (such as I/O, number of users, cache size, complexity of requests, and so forth). High batch requests mean good throughput. For more information, see "Batch Processing" in the Microsoft SQL Server Introduction.
Safe Auto-Params/sec	Number of safe auto-parameterization attempts per second.
SQL Compilations/sec	Number of SQL compilations per second. Indicates the number of times the compile code path is entered. Includes compiles due to recompiles. After SQL Server user activity is stable, this value should reach a steady state.
SQL Recompilations/sec	Number of SQL recompiles per second. Counts the number of times recompiles are triggered. Generally, the number of recompiles should be low.
Unsafe Auto-Params/sec	Number of unsafe auto-parameterization attempts per second. The table has characteristics that prevent the cached plan from being shared. These are designated as unsafe. The fewer of these that occur the better.

Server Alerts Metrics

The metrics in this category provide details of alerts from the server.

Default Collection Interval — Every 15 minutes

Table 1-53 Server Alerts Metrics

Metric	Description
ID (key column)	System-assigned unique integer identifier.
Category Name	Name of the alert category.
Count Reset Date	Date the occurrence_count was last set.
Count Reset Time	Time the occurrence_count was last reset.
Database Name	Database in which the error must occur for the alert to appear. If the database name is NULL, the alert appears regardless of where the error occurred.
Delay Between Responses	Seconds of waiting between responses to the alert.
Enabled	Status of whether the alert is currently enabled (1) or not (0). An unenabled alert is not sent.
Event Category ID	Reserved.
Event Description Keyword	Description of the SQL Server error in the Windows NT application log that must be like the supplied sequence of characters.
Event ID	Reserved.
Event Source	Source of the event. It is always MSSQLServer for Microsoft SQL Server version 7.0.
Flags	If type is 2, this column shows the definition of the performance condition; otherwise, the column is NULL.
Has Notification	Nonzero if one or more operators are notified for this alert. The value is one or more of the following values (ORed together): 1 — Has email notification 2 — Has pager notification 4 — Has netsend notification
Include Event Description	Whether the description of the SQL Server error from the Microsoft Windows NT application log should be included as part of the notification message.
Job ID	Job identification number.
Job Name	An on-demand job to be executed in response to an alert.
Last Occurrence Date	Date the alert last occurred.
Last Occurrence Time	Time the alert last occurred.
Last Response Date	Date the SQLServerAgent service last responded to the alert.
Last Response Time	Time the SQLServerAgent service last responded to the alert.
Message ID	Message error number that defines the alert. (This usually corresponds to an error number in the sysmessages table.) If severity is used to define the alert, message_id is 0 or NULL.
Name	Alert name, such as Demo: Full msdb log.
Notification Message	Optional additional message sent to the operator as part of the email or pager notification.
Occurrence Count	Number of times the alert occurred.
Performance Condition	Reserved. Will always be '[uncategorized]' for SQL Server 7.0.
Severity	Severity level (from 9 through 25, 110, 120, 130, or 140) that defines the alert.
Type	1 — SQL Server event alert 2 — SQL Server performance alert

Server Statistics Metrics

The metrics in this category provide information about various server-related performance issues.

Default Collection Interval — Every 15 minutes

Table 1-54 Server Statistics Metrics

Metric	Description
CPU Busy Ratio	CPU utilization.
CPU ms	CPU busy time in milliseconds.
Errors / sec.	Packet error rate in seconds.
IDLE ms	CPU idle time in milliseconds.
IO ms	IO busy time in milliseconds.
Max Connections	Maximum number of connections.
Open Transactions	Total number of transactions.
Packet Error Ratio	The ratio of erroneous packets received to the number of packets received.
Packets Errors	Number of packet errors.
Packets Received	Number of received packets.
Packets Sent	Number of sent packets.
Reads / sec.	Packet read rate in seconds.
Total Errors	Total number of errors.
Total Reads	Total number of reads.
Total Writes	Total number of writes.
Writes / sec.	Packet write rate in seconds.

User Metrics

The User object exposes the attributes of a single Microsoft SQL Server database user.

Default Collection Interval — Every 24 hours

Table 1-55 User Metrics

Metric	Description and User Action
DatabaseName (key column)	Name of the database.
Name (key column)	User name.
SQL ServerName (key column)	Name of the SQL Server.
Status	The status property is a string indicating the current status of the object. Various operational and non-operational statuses can be defined. Operational statuses are OK, Degraded, and Pred Fail. Pred Fail indicates that an element may be functioning properly but predicting a failure in the near future.
System Object	The SystemObject property indicates whether the object is owned by Microsoft. A value of True indicates that the object implementation is owned by Microsoft.