Microsoft SQL database metrics
Site24x7 Microsoft SQL Server database monitoring offers real-time performance tracking and instant alerts for each of your Microsoft SQL Server databases. Ensure high availability and optimal performance with customizable alerts.
Get real-time insights into critical performance indicators like database state, database size, transactions, top data and log files, recent backup details, slow and expensive queries, and top tables by size. This will help manage and optimize your SQL Server database instances effectively.
Supported database metrics
| Metrics | Description | Unit |
|---|---|---|
| DB State | The state of the database, which indicates if the database is ONLINE, RESTORING, RECOVERING, RECOVERY PENDING, SUSPECT, EMERGENCY, or OFFLINE | int |
| Active Transactions | The number of active transactions for the database | Count |
| Log File Size (GB) | The cumulative size of all the log files in the database | GB |
| Last Backup | The time of last backup of database in dd/mm/yyyy format | Date |
| Log File Used Size | The cumulative used size of all the log files in the database | GB |
| Transactions/Sec | The number of transactions started for the database | Count per second |
| Database details | ||
| Database Name | The name of the database, which is unique within an instance of SQL Server | Text |
| Owner Name | The owner name of the database | Text |
| Creation Date | The date the database was created or renamed. For tempdb, this value changes every time the server restarts. Note that the creation date does not change when a rename operation is performed on Azure SQL Database. | Date and time |
| Database Mode | The mode of the database, which can be READ_ONLY or READ_WRITE | Text |
| Recovery Model | The recovery model for the database, which can be FULL, BULK-LOGGED, or SIMPLE | Text |
| Collation | The collation for the database, which acts as the default collation in the database | Text |
| Compatibility Level | The compatibility level setting for the database, which can be NULL or any one of the following: 90 = SQL Server 2005 (9.x) 100 = SQL Server 2008 (10.0.x) 110 = SQL Server 2012 (11.x) 120 = SQL Server 2014 (12.x) 130 = SQL Server 2016 (13.x) 140 = SQL Server 2017 (14.x) 150 = SQL Server 2019 (15.x) 160 = SQL Server 2022 (16.x) |
Number |
| Page Verification | The setting of the PAGE VERIFY option, which can be ONE, TORN_PAGE_DETECTION, or CHECKSUM | Text |
| Always On | This states whether the database has participated in SQL Always On or not. | Text |
| Mirroring | This states whether the database has participated in SQL DB Mirroring or not. | Text |
| Database performance | ||
| Transactions/Sec | The number of transactions started for the database | Count per second |
| Log Cache Hit Ratio | The percentage of log cache reads that were satisfied from the log cache | Percentage |
| Bulk Copy Rows/Sec | The number of rows bulk copied per second | Rows per second |
| Repl. Trans. Rate | The number of transactions per second that are read out of the transaction log of the publication database and delivered to the distribution database | Count per second |
| Replication Pending Xacts | The number of transactions in the transaction log of the publication database marked for replication, but not yet delivered to the distribution database | Count |
| Write Transactions/Sec | The number of transactions written to the database and committed in the last second | Count per second |
| Total Number of Connections | The total number of connections made to the database | Count |
| Active Sessions | The number of sessions running at that moment | Number |
| Idle Sessions | The number of sessions that are in the sleeping state | Number |
| Tables by size | ||
| Table Name | The name of the table | Text |
| Schema | The name of the schema | Text |
| Row Count | The approximate number of rows in this table | Count |
| Total Space | The total space occupied by this table | Bytes |
| Used Space | The used space of this table | Bytes |
| Free Space | The free space of this table | Bytes |
| Data File Size | The cumulative size of all the data files in the database | GB |
| Log File Size | The cumulative size of all the log files in the database | GB |
| Log Growths and Shrinks | The total number of log growths and shrinks for this database | Count |
| Database performance | ||
| Data File Size | The cumulative size of all the data files in the database | GB |
| Log File Size (GB) | The cumulative size of all the log files in the database | GB |
| Log File Used Size (GB) | The cumulative used size of all the log files in the database | GB |
| Used Log Space Percentage | The used percent of log space for the database | Percentage |
| Free Log Space Percentage | The free percent of log space for the database | Percentage |
| Log Cache Hit Ratio | The percentage of log cache reads that were satisfied from the log cache | Percentage |
| Number of MDF data files present | The number of Master Data Files (.mdf) present | Count |
| Number of NDF data files present | The number of Secondary Data Files (.ndf) present | Count |
| Number of LDF log files present | The number of Log Files (.ldf) present | Count |
| Top data files by disk | ||
| Physical Name | The operating system file name | Text |
| File ID | The ID of the file within the database. The primary file_id is always 1. | Number |
| Auto Growth | This indicates the status of the growth: 0 = File is fixed size, and will not grow>0 = File will grow automatically |
Text |
| Disk Size | The size occupied by the disk file of the database | Bytes |
| Used Space | The used space of the file of the database | Bytes |
| Available Space | The available space of the file of the database | Bytes |
| Top log files by disk | ||
| Physical Name | The operating system file name | Text |
| File ID | The ID of the file within the database. The primary file_id is always 1 | Number |
| Auto Growth | This indicates the status of the growth: 0 = File is fixed size, and will not grow>0 = File will grow automatically |
Text |
| Disk Size | The size occupied by the disk file of the database | Bytes |
| Used Space | The used space of the file of the database | Bytes |
| Available Space | The available space of the file of the database | Bytes |
| Top data files by IO | ||
| Physical Name | The operating system file name | Text |
| File ID | The ID of the file within the database. The primary file_id is always 1. | Number |
| Average Read Latency | The average read latency of the database in milliseconds | ms |
| Total IO | The total of the number of writes to the disk and the number of reads from the disk | Count |
| Read Count | The number of read IOs | Count |
| Write Count | The number of write IOs | Count |
| Total IO Bytes | The total of the number of bytes written to the disk and the number of bytes read from the disk | Bytes |
| Bytes Read | The number of bytes read from the disk | Bytes |
| Bytes Written | The number of bytes written to the disk | Bytes |
| Top log files by IO | ||
| Physical Name | The operating system file name | Text |
| File ID | The ID of the file within the database. The primary file_id is always 1 | Number |
| Average Read Latency | The average read latency of the database in milliseconds | ms |
| Total IO | The total of the number of writes to the disk and the number of reads from the disk | Count |
| Read Count | The number of read IOs | Count |
| Write Count | The number of write IOs | Count |
| Total IO Bytes | The total of the number of bytes written to the disk and the number of bytes read from the disk | Bytes |
| Bytes Read | The number of bytes read from the disk | Bytes |
| Bytes Written | The number of bytes written to the disk | Bytes |
Expensive queries: CPU-intensive queries, long running queries, and IO-intensive queries
| Query string | The text of the SQL query | Text |
| Count | The number of times the plan has been executed since it was last compiled | Count |
| Average CPU Time | The average amount of CPU time that was consumed by the executions of this plan since it was compiled | Milliseconds |
| Maximum CPU Time | The maximum CPU time consumed during a single execution | Milliseconds |
| Average Execution Time | The average elapsed time for the completed executions of this plan | Milliseconds |
| Maximum Execution Time | The maximum elapsed time for completed executions of this plan | Milliseconds |
| Average Blocked Time | The average of the difference between the total elapsed time and the total worker time | Milliseconds |
| Average Logical Reads | The average number of logical reads performed by the executions of this plan since it was compiled | Count |
| Average Logical Writes | The average number of logical writes performed by the executions of this plan since it was compiled | Count |
| Average Physical Reads | The average number of physical reads performed by the executions of this plan since it was compiled | Count |
| Average Logical IO | The average I/O of the total number of logical reads and writes performed by the executions of this plan since it was compiled | Count |
| Average Rows | The average number of rows returned by the query | Count |
| Session ID | The SQL Server session ID | Number |
| Username | The login username | Text |
| Program | The name of the application program | Text |
| Query String | The text of the SQL query | Text |
| Database Name | The database being used by the process | Text |
| Status | The status of the process ID | Text |
| Blocking Session ID | The ID of the session that is blocking the request | Number |
| CPU Time | The CPU time that is used by the request | Milliseconds |
| Memory Usage | The number of pages in the procedure cache that are currently allocated to this process | Count |
| Wait Time | The current wait time to execute the query | Milliseconds |
| Lock Stats by Request Type | The current status of the request and the number of locks based on the request status. The possible values include Granted, Convert, Wait, Low Priority Convert, Low Priority Wait, or Abort Blockers | Count |
| Lock Stats by Lock Mode | The lock mode of the request and the number of locks based on the lock mode. The possible values include Exclusive, Intent, Schema, Shared, Update, and Bulk Operation | Count |
| Backup Type | The type of the backup type, which can be NULL or the following: D = Database I = Differential database L = Log F = File or file group G = Differential file P = Partial Q = Differential partial |
Text |
| Backup Start Date | The date and time the backup operation started. This can be NULL | Date and time |
| Backup Finish date | The date and time the backup operation finished. This can be NULL | Date and time |
| Physical Device Name | The physical name of the backup device. It can be NULL. This field is shared between the backup and restore process. It may contain the original backup destination path or the original restore source path depending on whether backup or restore occurred first on a server for a database | Text |
| Compatibility Level | The compatibility level setting for the database, which can be NULL or any one of the following: 90 = SQL Server 2005 (9.x) 100 = SQL Server 2008 (10.0.x) 110 = SQL Server 2012 (11.x) 120 = SQL Server 2014 (12.x) 130 = SQL Server 2016 (13.x) 140 = SQL Server 2017 (14.x) 150 = SQL Server 2019 (15.x) 160 = SQL Server 2022 (16.x) |
Number |
| Device Type | The type of the backup device, which can be NULL or any of the following: 2 = Disk 5 = Tape 7 = Virtual device 9 = Azure Storage 105 = A permanent backup device All permanent device names and device numbers can be found in sys.backup_devices |
Text |
Related articles
-
On this page
- Overview
- Data and log files
- Expensive queries
- Sessions and locks
- Backups