Microsoft SQL

Microsoft SQL is the monitoring module for Microsoft SQL Server. You can monitor performance counters and get inventory reports for your SQL Servers and databases.

You can reach Microsoft SQL module monitoring and reports via left menu.

Module Reports

VirtualMetric collects following inventory reports for Microsoft SQL module.

Bare Metal Reports

Change Tracking
Event Log
Security Log
Firewall Log
Best Practices
Pending Updates
TCP Connections         
Security Threats         
File Change Tracking
User Sessions
Processes
Services
Hotfix
Programs
Windows Features         
Windows Defender
Firewall Rules
Network Teaming
Startup Commands
Local DNS Records
Administrators

Server Reports

Config
Properties
Endpoints
Logins
Roles
Resource Pools
Availability Groups
Availability Group Listeners
Availability Group Replicas

Database Reports

Config
Properties
Mirroring
Recovery
Files
Tables
Stored Procedures
Users
Replicas

Module Counters

VirtualMetric collects following performance counters for Microsoft SQL module.

Processor

% C1 Time
% C2 Time
% C3 Time
% DPC Time
% Idle Time
% Interrupt Time
% Privileged Time
% Processor Time
% User Time
C1 Transitions/sec
C2 Transitions/sec
C3 Transitions/sec
DPC Rate
DPCs Queued/sec
Interrupts/sec

Memory

Available MBytes
Pages/Sec
Page Faults/Sec
% Committed Bytes In Use
Free System Page Table Entries
Pool Nonpaged Bytes
Pool Paged Bytes

Paging File

% Usage

Network Interface

Bytes Received/Sec
Bytes Sent/Sec
Packets Received/Sec
Packets Sent/Sec
Output Queue Length

LogicalDisk

Disk Reads/Sec
Disk Writes/Sec
Disk Read Bytes/Sec
Disk Write Bytes/Sec
% Idle Time
Split IO/Sec
Current Disk Queue Length
Avg. Disk Read Queue Length
Avg. Disk Write Queue Length
Avg. Disk Sec/Read
Avg. Disk Sec/Write

PhysicalDisk

Disk Reads/Sec
Disk Writes/Sec
Disk Read Bytes/Sec
Disk Write Bytes/Sec
% Idle Time
Split IO/Sec
Current Disk Queue Length
Avg. Disk Read Queue Length
Avg. Disk Write Queue Length
Avg. Disk Sec/Read
Avg. Disk Sec/Write

Buffer Manager

Buffer cache hit ratio
Database pages
Free list stalls/sec
Lazy writes/sec
Page life expectancy
Page lookups/sec
Page reads/sec
Page writes/sec
Readahead pages/sec

Memory Manager

Connection Memory (KB)
Database Cache Memory (KB)
Free Memory (KB)
Granted Workspace Memory (KB)
Lock Memory (KB)
Maximum Workspace Memory (KB)
Memory Grants Outstanding
Memory Grants Pending
Optimizer Memory (KB)
Reserved Server Memory (KB)
SQL Cache Memory (KB)
Stolen Server Memory (KB)
Target Server Memory (KB)
Total Server Memory (KB)

SQL Statistics

Batch Requests/sec
SQL Compilations/sec
SQL Re-Compilations/sec

General Statistics

Connection Reset/sec
HTTP Authenticated Requests
Logins/sec
Transactions
User Connections

Access Methods

Full Scans/sec
Index Searches/sec
Page Splits/sec
Table Lock Escalations/sec

Locks

Average Wait Time (ms)
Lock Requests/sec
Lock Timeouts/sec
Lock Waits/sec
Number of Deadlocks/sec

Plan Cache

Cache Hit Ratio
Cache Object Counts
Cache Pages
Cache Objects in use

Databases

Active Transactions
Bulk Copy Rows/sec
Bulk Copy Throughput/sec
Data File(s) Size (KB)
Log Cache Hit Ratio
Log Cache Reads/sec
Log File(s) Size (KB)
Log File(s) Used Size (KB)
Percent Log Used
Transactions/sec
Write Transactions/sec

Database Files

Read Bytes/Sec
Write Bytes/Sec
IO Reads/Sec
IO Writes/Sec
Total Space
Used Space
IO Reads
IO Writes
Read Bytes
Write Bytes

Stored Procedures

Elapsed Time
Worker Time
Physical Reads
Logical Reads
Logical Writes
Execution Status

Index Stats

Allocated Space
Used Space
Free Space
Row Count
User Seeks
User Scans
User Lookups
Fragmentation Count
Page Count
% Fragmentation
% Space Usage

Table Stats

Allocated Space
Used Space
Free Space
Row Count
% Space Usage

Database Replica

Recovery Queue
Redone Bytes/sec
Log Send Queue
Log Bytes Received/sec
File Bytes Received/sec
Mirrored Write Transactions/sec
Transaction Delay
Total Log requiring undo
Log remaining for undo
Redo Bytes Remaining
Redo blocked/sec
Synchronization State
Commit LSN
End Of Log LSN
Hardened LSN
Received LSN
Redone LSN
Sent LSN
Recovery LSN
Commit State
Hardened State
Received State
Redone State
Truncation LSN

Availability Replica

Bytes Sent to Replica/sec
Sends to Replica/sec
Bytes Sent to Transport/sec
Sends to Transport/sec
Bytes Received from Replica/sec
Receives from Replica/sec
Flow Control Time (ms/sec)
Flow Control/sec
Resent Messages/sec
Member State
Connection State
Synchronization State
Connection Error

User Permissions

If you want to use non-administrator user for VirtualMetric, you need to add the following changes for the user account in addition to Bare Metal permissions.

MSSQL Service Configuration

You need to open CMD window to configure Security Descriptions for MSSQLSERVER Service. Type following command on CMD:

sc sdshow mssqlserver

You will see default security descriptor of the MSSQLSERVER Service.

Add the following security descriptor to MSSQLSERVER security descriptions:

(A;;CCLCRPRCLO;;;AU)

Update the Security Description via following command:

sc sdset MSSQLSERVER D:(A;;CCLCRPRCLO;;;AU)(A;;CCLCRPRC;;;IU)(A;;CCLCRPRC;;;SU)(A;;CCLCRPWPRC;;;SY)(A;;KA;;;BA)(A;;CC;;;AC)S:(AU;FA;KA;;;WD)(AU;OIIOFA;GA;;;WD)

SSMS User Configurations

Using SSMS add user to Server Logins:

Choose user and click OK button:

From Securables tab select Search and choose The Server selection.

And enable the following permissions:

Login Securables Options

Connect Any Database
View any database
View any definition
View server state

Requirements

Following SQL components are required to monitor Microsoft SQL:

SQL Server Shared Management Objects (SMO)

You can install required components via following MSI package:

ENU\x86\SharedManagementObjects.msi

Following Windows features are required to monitor Microsoft SQL Clusters:

Failover Cluster Module for Windows PowerShell

You can install required features with following PowerShell command:

Get-WindowsFeature RSAT-Clustering-PowerShell | Add-WindowsFeature