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 consist of the following categories:
SQL Server Configuration
Registry Setting Configuration
Database Setting Configuration
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 |
|---|---|
|
Server 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 String |
Installed version. |
|
Operatingsystem Details |
Operating system on which the installation is done. |
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 |
|---|---|
|
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. |
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 |
|---|---|
|
Database Name (key column) |
Database name. |
|
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. |
|
AutoCreateStatistics |
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. |
|
AutoUpdateStatistics |
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. |
|
DB Owner UseOnly |
Whether only users with the database ownership privilege can access the database. |
|
SingleUser Mode |
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. |
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.
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. |
The metrics in this category provide information regarding the current status of the Agent.
Default Collection Interval — Every 5 minutes
Table 1-6 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. |
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.
Default Collection Interval — Every 15 minutes
Table 1-7 Buffer Manager Metrics
| Metric | Description |
|---|---|
|
Buffer Manager Counter Name (key column) |
Performance metric name. See Table 1–8. |
|
Buffer Manager Counter Value |
Performance metric value. |
The Buffer Manager Counter Name key column contains several metrics. Table 1–8 provides a list of these metrics and a description for each.
Table 1-8 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). |
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.
Default Collection Interval — Every 15 minutes
Table 1-9 Cache Manager Metrics
| Metric | Description |
|---|---|
|
Cache Manager Counter Name (key column) |
Performance metric name. See Table 1–10. |
|
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–10 provides a list of these metrics and a description for each.
Table 1-10 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. |
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
| Metric | Description and User Action |
|---|---|
|
Database 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 |
|
Database Size (MB) |
Total size of the database in megabytes. Allocate more space to the database if this metric decreases beyond the critical threshold. |
|
Database Space Available % |
Percentage of space that is available. Allocate more space to the database if this metric decreases beyond the critical threshold. |
|
Space Available (MB) |
Unused space in megabytes. 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. |
The metrics in this category provide detailed backup information for all databases.
Default Collection Interval — in real time
Table 1-12 Database Backup Metrics
| Metric | Description |
|---|---|
|
Backup Set ID (key column) |
Unique backup set identification number that identifies the backup set. |
|
Media Set ID (key column) |
Unique media set identification number that identifies the media set containing the backup set. |
|
Family Sequence Number (key column) |
Position of this media family in the media set. |
|
File Number (key column) |
File identification number unique within a database. |
|
Backup Set Name |
Name of the backup set. Can be NULL |
|
Backup Set Description |
Description of the backup set. Can be NULL. |
|
Username |
Name of the user performing the backup operation. Can be NULL. |
|
Start Date |
Date and time the backup operation started. Can be NULL. |
|
Finish Date |
Date and time the backup operation finished. Can be NULL. |
|
Size of Backup (bytes) |
Size of the backup set, in bytes. Can be NULL. |
|
Database Name |
Name of the database involved in the backup operation. Can be NULL. |
|
Server Name |
Name of the server running the SQL Server backup operation. Can be NULL. |
|
Machine Name |
Name of the computer running SQL Server. Can be NULL. |
|
Media Set Name |
Name of the media set. Can be NULL. |
|
Media Set Device Name |
Physical name of the backup device. Can be NULL. |
|
Physical Block Size (Bytes) |
Physical block size used to write the media family. Can be NULL. |
|
File Group Name |
Name of the filegroup containing a backed up database file. Can be NULL. |
|
File Group Physical Name |
Remainder of the physical (operating system) file name. Can be NULL. |
|
Backup Set Expiration Date |
Date and time the backup set expires. Can be NULL. |
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 2 hours
Table 1-13 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. |
The metrics in this category report information about locks.
Default Collection Interval — Every 15 minutes
Table 1-14 Database Lock Metrics
| Metric | Description |
|---|---|
|
Server Process Identifier (key column) |
Server process ID of the current user process. |
|
Database Identifier (key column) |
Database identification number requesting a lock. |
|
Object Identifier (key column) |
Object identification number of the object requesting a lock. |
|
Index Identifier (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 |
|
Lock Request Status |
The current status of the lock: GRANT, WAIT, and CNVT |
|
Type |
The lock type:
|
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-15 Database Parameter Metrics
| Metric | Description and User Action |
|---|---|
|
Parameter 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-16 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. |
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 — Will upload data when alert raised
Table 1-17 Database Performance Metrics
| Metric | Description |
|---|---|
|
Database Performance Counter Name (key column) |
Performance metric name. See Table 1–18. |
|
Database Performance Instance Name (key column) |
Instance for the Database Performance Counter Name |
|
Database Performance Server Locks Counter Value |
Performance metric value. |
The Database Performance Counter Name key column contains several metrics. Table 1–18 provides a list of these metrics and a description for each.
Table 1-18 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. |
The MSSQL_ErrorLogEntry class represents the entries in a SQL Service error log.
Default Collection Interval — Metric is disabled. Will upload data when alert is raised.
Table 1-19 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. |
|
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. |
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.
Default Collection Interval — Every 30 minutes
Table 1-20 General Statistics Metrics
| Metric | Description |
|---|---|
|
General Statistics Counter Name (key column) |
Performance metric name. See Table 1–21. |
|
General Statistics Counter Value |
Performance metric value. |
The General Statistics Counter Name key column contains several metrics. Table 1–21 provides a list of these metrics and a description for each.
Table 1-21 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. |
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-22 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. |
The metrics in this category provide the last backup information for all databases.
Default Collection Interval — Every 24 hours
The MSSQL_Login class represents the login authentication records present in a SQL Server installation.
Default Collection Interval — in real time
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-25 MSSQL Database File Metrics
| Metric | Description |
|---|---|
|
Database File Name (key column) |
User 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. |
|
Database File Space Available (%) |
Percentage of space available of the database file. |
|
Size (MB) |
Current size of the database file. |
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 — in real time
Table 1-26 MSSQL File Group Metrics
| Metric | Description |
|---|---|
|
DatabaseName (key column) |
Name of the database. |
|
Filegroup Name (key column) |
FileGroup name. |
|
Default |
Whether the filegroup is the default filegroup during table or index creation. |
|
Read Only |
Whether the filegroup is read only. |
|
Total Size of the File Group (in MB) |
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 |
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-27 MSSQL Transaction Log Metrics
| Metric | Description |
|---|---|
|
Database Name (key column) |
FileGroup name. |
|
Size |
Initial size of the database. |
|
Last Backup |
Time of the last backup. |
|
Transaction Log Space Available (in MB) |
Space available in the database. |
|
Transaction Log Space Available (%) |
Percentage of space available in the database. |
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.
Default Collection Interval — Every 15 minutes
Table 1-28 Memory Manager Metrics
| Metric | Description |
|---|---|
|
Memory Manager Counter Name (key column) |
Performance metric name. See Table 1–29. |
|
Memory Manager Counter Value |
Performance metric value. |
The Memory Manager Counter Name key column contains several metrics. Table 1–29 provides a list of these metrics and a description for each.
Table 1-29 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. |
The metrics in this category provide information about various memory-related performance issues.
Default Collection Interval — Every 15 minutes
Table 1-30 Memory Statistics Metrics
| Metric | Description and User Action |
|---|---|
|
Average Latch Wait Time (ms) |
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 (ms) |
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 (ms) |
Total wait time in milliseconds for locks in the last second. If the value is high, your server has high resource contention. |
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
This metrics category provide information about the response of the target SQL Server Instance.
Default Collection Interval — Every 5 minutes
| Metric | Description and User Action |
|---|---|
|
Process ID |
Process ID of the SQL Server process. |
|
Server Name |
Name of the instance of the SQL Server. |
|
Software Home |
Path of the SQL Server process. |
|
SQL Server Status |
Status of the SQL Server process. When the status is not Running, the SQL Server must be started. |
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.
Default Collection Interval — Every 15 minutes
Table 1-33 SQL Server Locks Metrics
| Metric | Description |
|---|---|
|
SQL Server Locks Counter Name (key column) |
Performance metric name. See Table 1–34. |
|
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–34 provides a list of these metrics and a description for each.
Table 1-34 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. |
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-35 SQL Server Process Metrics
| Metric | Description and User Action |
|---|---|
|
Process Handle (key column) |
Process ID. |
|
Blocked Process ID |
ID of a process being blocked by the process. |
|
CPU Time (ms) |
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. |
|
Login |
Login used by the process to connect to the SQL Server. |
|
Memory Usage (pages) |
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. |
|
Process State |
Whether the process is running or sleeping. |
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 — Real Time
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.
Default Collection Interval — Every 10 minutes
Table 1-37 SQL Statistics Metrics
| Metric | Description |
|---|---|
|
SQL Statistics Counter Name (key column) |
Performance metric name. See Table 1–38. |
|
SQL Statistics Counter Value (key column) |
Performance metric value. |
The SQL Statistics Counter Name key column contains several metrics. Table 1–38 provides a list of these metrics and a description for each.
Table 1-38 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. |
The metrics in this category provide information about various server-related performance issues.
Default Collection Interval — Every 15 minutes
Table 1-39 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. |
The User object exposes the attributes of a single Microsoft SQL Server database user.
Default Collection Interval — Real Time
| Metric | Description and User Action |
|---|---|
|
DatabaseName (key column) |
Name of the database. |
|
Name (key column) |
User name. |
|
User 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. |
The metrics in this category provide details about the windows cluster.
The metrics in this category provide details about windows cluster nodes.
The metrics in this category provide details about windows cluster resources.
The metrics in this category provide details about the cluster name and network.
The metrics in this category provide details about the cluster resource and type.
The metrics in this category provide details about the cluster resource and group.
The metrics in this category provide details about the cluster active group and the node.
The metrics in this category provide details about the cluster resource group and cluster name.
The metrics in this category provide details about the cluster active resource and node.
The metrics in this category provide details about the cluster quorom resource and cluster name.
The metrics in this category provide details about the cluster resource and owner node.
The metrics in this category provide details about the cluster resource and cluster name.
The metrics in this category provide details about the cluster resource group and preferred node.
The metrics in this category provide information of the defragment level of Microsoft SQL Server 2005. Currently, this metric is not applicable for Microsoft SQL Server 2000. To retrieve data for this metric, DMV "sys.dm_db_index_physical_stats" is queried for each database (for all objects, indices and partitions). The scan level mode to obtain the statistics is DEFAULT or NULL (i.e., equivalent to LIMITED), which is the fastest mode and scans the smallest number of pages.
Evaluation and Collection Frequency — Every SUN
Table 1-54 Cluster Resource Group and Preferred Node Metrics
| Metric | Description |
|---|---|
|
Allocation Unit type Description |
Description of the allocation unit type:
The LOB_DATA allocation unit contains the data that is stored in columns of type text, ntext, image, varchar(max), nvarchar(max), varbinary(max), and xml. For more information, see Data Types (Transact-SQL). The ROW_OVERFLOW_DATA allocation unit contains the data that is stored in columns of type varchar(n), nvarchar(n), varbinary(n), and sql_variant that have been pushed off-row. For more information, see Row-Overflow Data Exceeding 8 KB. |
|
Number of Index levels |
1 = Heap, or LOB_DATA or ROW_OVERFLOW_DATA allocation unit. |
|
Current Level of Index |
Zero for index leaf levels, heaps, and LOB_DATA or ROW_OVERFLOW_DATA allocation units. Greater than zero for nonleaf index levels. index_level will be the highest at the root level of an index. The nonleaf levels of indexes are only processed when mode = DETAILED. |
|
Average Fragmentation in Percent |
Logical fragmentation for indexes, or extent fragmentation for heaps in the IN_ROW_DATA allocation unit. The value is measured as a percentage and takes into account multiple files. For definitions of logical and extent fragmentation, see Remarks. Zero for LOB_DATA and ROW_OVERFLOW_DATA allocation units. NULL for heaps when mode = SAMPLED. Upload Frequency - After every sample. Alert Text - Average fragmentation for %indexid% is %avg_fragmentation_in_percent%%% where Database ID is %database_id%, Object ID is %object_id% ,Index ID is %index_id%,Partition Number is %partition_number% and Index type Description is %index_type_desc%. It has crossed warning (%warning_threshold%%%) or critical (%critical_threshold%%%) threshold. Multiple Thresholds - For this metric you can set different warning and critical threshold values for each unique combination of " Database ID ", " Object ID ", " Index ID ", " Partition Number ", and " Index type Description " objects. If warning or critical threshold values are currently set for any unique combination of " Database ID ", " Object ID ", " Index ID ", " Partition Number ", and " Index type Description " objects, those thresholds can be viewed on the Metric Detail page for this metric. To specify or change warning or critical threshold values for each unique combination of "Database ID", "Object ID", "Index ID", "Partition Number", and "Index type Description" objects, use the Edit Thresholds page. |
|
Number of fragments in the leaf level |
Number of fragments in the leaf level of an IN_ROW_DATA allocation unit. For more information about fragments, see Remarks. NULL for nonleaf levels of an index, and LOB_DATA or ROW_OVERFLOW_DATA allocation units. NULL for heaps when mode = SAMPLED. |
|
Average number of pages in one fragment |
Average number of pages in one fragment in the leaf level of an IN_ROW_DATA allocation unit. NULL for nonleaf levels of an index, and LOB_DATA or ROW_OVERFLOW_DATA allocation units. NULL for heaps when mode = SAMPLED. |
|
Total number of index or data pages |
For an index, the total number of index pages in the current level of the b-tree in the IN_ROW_DATA allocation unit. For a heap, the total number of data pages in the IN_ROW_DATA allocation unit. For LOB_DATA or ROW_OVERFLOW_DATA allocation units, total number of pages in the allocation unit. |
|
Average(%) of available data storage space used |
Average percentage of available data storage space used in all pages. For an index, average applies to the current level of the b-tree in the IN_ROW_DATA allocation unit. For a heap, the average of all data pages in the IN_ROW_DATA allocation unit. For LOB_DATA or ROW_OVERFLOW DATA allocation units, the average of all pages in the allocation unit. NULL when mode = LIMITED. Upload Frequency - After every sample. Alert Text - Average percentage of available data storage space used in all pages for %indexid% is %avg_page_space_used_in_percent%%% where Database ID is %database_id%, Object ID is %object_id% , Partition Number is %partition_number% and Index type Description is %index_type_desc%.It has fallen below warning (%warning_threshold%%%) or critical (%critical_threshold%%%) threshold. Multiple Thresholds - For this metric you can set different warning and critical threshold values for each unique combiation of "Database ID", "Object ID", "Index ID", "Partition Number", and "Index type Description " objects. If warning or critical threshold values are currently set for any unique combination of "Database ID", "Object ID", "Index ID", "Partition Number", and "Index type Description" objects, those thresholds can be viewed on the Metric Detail page for this metric. To specify or change warning or critical threshold values for each unique combination of "Database ID", " Object ID ", " Index ID ", "Partition Number", and "Index type Description" objects, use the Edit Thresholds page. |
|
Total number of records |
Total number of records. For an index, total number of records applies to the current level of the b-tree in the IN_ROW_DATA allocation unit. For a heap, the total number of records in the IN_ROW_DATA allocation unit. Note: For a heap, the number of records returned from this function might not match the number of rows that are returned by running a SELECT COUNT(*) against the heap. This is because a row may contain multiple records. For example, under some update situations, a single heap row may have a forwarding record and a forwarded record as a result of the update operation. Also, most large LOB rows are split into multiple records in LOB_DATA storage. For LOB_DATA or ROW_OVERFLOW_DATA allocation units, the total number of records in the complete allocation unit. NULL when mode = LIMITED. |
|
Number of ghost records ready for removal |
Number of ghost records ready for removal by the ghost cleanup task in the allocation unit. Zero for nonleaf levels of an index in the IN_ROW_DATA allocation unit. NULL when mode = LIMITED. |
|
Number of ghost records retained in an allocation unit |
Number of ghost records retained by an outstanding snapshot isolation transaction in an allocation unit. Zero for nonleaf levels of an index in the IN_ROW_DATA allocation unit. NULL when mode = LIMITED. |
|
Minimum record size in bytes |
For an index, minimum record size applies to the current level of the b-tree in the IN_ROW_DATA allocation unit. For a heap, the minimum record size in the IN_ROW_DATA allocation unit. For LOB_DATA or ROW_OVERFLOW_DATA allocation units, the minimum record size in the complete allocation unit. NULL when mode = LIMITED. |
|
Maximum record size in bytes |
For an index, the maximum record size applies to the current level of the b-tree in the IN_ROW_DATA allocation unit. For a heap, the maximum record size in the IN_ROW_DATA allocation unit. For LOB_DATA or ROW_OVERFLOW_DATA allocation units, the maximum record size in the complete allocation unit. NULL when mode = LIMITED. |
|
Average record size in bytes |
For an index, the average record size applies to the current level of the b-tree in the IN_ROW_DATA allocation unit. For a heap, the average record size in the IN_ROW_DATA allocation unit. For LOB_DATA or ROW_OVERFLOW_DATA allocation units, the average record size in the complete allocation unit. NULL when mode = LIMITED. |
|
Number of records in a heap that have forward pointers |
Number of records in a heap that have forward pointers to another data location. (This state occurs during an update, when there is not enough room to store the new row in the original location.) NULL for any allocation unit other than the IN_ROW_DATA allocation units for a heap. NULL for heaps when mode = LIMITED. |