SQL Server and host machine metrics

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:

SQL Server 2017 RTM CU20 (14.0.3294.2) 

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:

DiskSpace used (GB)Avg. Read TimeAvg. Write TimeTransfers/sec
System (C:)

54.32 GB free of 102.3 GB

0.34 ms0.62 ms7.83 
Backups (D:)

43.45 GB free of 97.65 GB

0.00 ms0.00 ms0.00 
Data (E:)

844.9 GB free of 976.6 GB

0.30 ms0.06 ms1.47 
Logs (F:)

717.4 GB free of 792.9 GB

0.00 ms0.05 ms6.02 

Next step

Processes and databases


Didn't find what you were looking for?