SQL Server and host machine metrics
Published 14 February 2023
SQL Server metrics
Counters
SqlServer:SqlStatistics
Batch requests/sec
Number of T-SQL batch requests received by SQL Server per second, averaged since last collection time.
SQL Compilations/sec
SQL compilations per second; each data point shows the average value since the previous data point.
SQL Compilations/batch
Calculated: SQL compilations per batch requests received by SQL Server.
SqlServer:AccessMethods
Full scans/sec
Measures the number of base table or full index scans occurring per second.
Page splits/sec
The number of page splits, and new page allocations, in a SQL Server table per second.
Page splits/batch request
Calculated metric: the number of page splits per second in a SQL Server table divided by the number of T-SQL command batch requests received per second.
SqlServer:GeneralStatistics
User connections
The total number of user connections to a SQL Server instance at a given time.
SqlServer:Latches and SqlServer:Locks
Avg. Latch wait time (ms)
The average wait time, in milliseconds, that latch requests have to wait before being executed.
Lock timeouts/sec
The number of locks per second that timed out.
Lock waits/sec
The number of locks per second that could not be satisfied immediately and had to wait for resources.
SqlServer:BufferManager
Buffer cache hit ratio (%)
The percentage of pages that were found in the buffer pool without having to read from disk.
Buffer page life expectancy
Indicates the average number of seconds a page will stay in the buffer pool without being referenced.
Server Properties
Sample output for SQL Server instances:
Version: | |
---|---|
Edition: | Standard Edition (64-bit) |
Collation: | SQL_Latin1_General_CP1_CI_AS |
Sample output for Azure SQL Server databases:
Collation: | SQL_Latin1_General_CP1_CI_AS |
---|---|
Creation Date: | 4/24/2019 3:45:50 AM |
Compatibility Level: | 140 |
State: | ONLINE |
Page Verify: | CHECKSUM |
Read Only: | False |
Query Store: | True |
Auto Shrink: | False |
Auto Create Stats: | True |
Auto Update Stats: | True |
Permissions
Shows Windows, Active Directory and SQL membership of the following fixed server-level roles: sysadmin, serveradmin and securityadmin.
These roles have powerful server-wide capabilities and permissions, and membership should be strictly controlled. Assign more specific permissions whenever possible.
Host machine metrics
(Not available for cloud-hosted databases)
Host machine counters
Memory:
Network Utilization (%) for each network adapter.
The percentage of total bandwidth usage to help identify whether your network adapter is at saturation point.
Memory Pages/s
The rate at which pages are read from or written to disk, calculated as the sum of the memory pages input/sec and memory pages output/sec.
Processors:
Avg. CPU queue length
Gauges how busy the processor cores are by measuring the average processor queue length from the number of threads in the processor queue waiting to be executed. If the CPUs get very busy, then CPU requests have to be queued, waiting their turn to execute. The longer the queue, the more likely there is a CPU bottleneck.
Operating system properties
Sample output:
Edition: | Microsoft Windows Server 2016 Datacenter |
---|---|
Version: | 10.0.14393 |
Build Number: | 14393 |
VMWare metrics
VMWare Host properties
Physical Host: | network-qualified name |
---|---|
Host Processor Speed: | 3504 MHz |
CPU Limit: | Unlimited |
CPU Reservation: | 0 MHz |
CPU Shares: | 4000 |
VMWare Counters
VMWARE CPU
Processor time
Total processor utilization (across all CPUs and cores) on the physical host machine on which the VM was running.
Guest CPU ready time
The percentage of time that the virtual machine was waiting to be allocated a physical CPU core or logical processor.
Effective VM speed MHz
The approximate effective speed of the virtual machine's virtual CPU in MHz.
VMWARE MEMORY AND I/O
Guest Memory latency
Percentage of time that the virtual machine had to wait on memory due to it being swapped, compressed or ballooned.
Guest Memory ballooned
Amount of memory reclaimed from the virtual machine back to the host using ballooning technique.
Disk latency
Highest latency across all disks used by the physical host machine.
Disk usage
Per disk or mount point volume: space used, average disk read and write times, disk transfer rates. For example:
Disk | Space used (GB) | Avg. Read Time | Avg. Write Time | Transfers/sec |
---|---|---|---|---|
System (C:) | 54.32 GB free of 102.3 GB | 0.34 ms | 0.62 ms | 7.83 |
Backups (D:) | 43.45 GB free of 97.65 GB | 0.00 ms | 0.00 ms | 0.00 |
Data (E:) | 844.9 GB free of 976.6 GB | 0.30 ms | 0.06 ms | 1.47 |
Logs (F:) | 717.4 GB free of 792.9 GB | 0.00 ms | 0.05 ms | 6.02 |