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-30 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-31 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. |