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 — Uploads data when an alert is raised
Table 1-32 Database Performance Metrics
| Metric | Description |
|---|---|
|
Database Performance Counter Name (key column) |
Performance metric name. See **INTERNAL XREF ERROR**. |
|
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. **INTERNAL XREF ERROR** provides a list of these metrics and a description for each.
Table 1-33 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. |