> ## Documentation Index
> Fetch the complete documentation index at: https://docs.vortexiq.ai/llms.txt
> Use this file to discover all available pages before exploring further.

# Microsoft SQL Server audit profile, Vortex IQ

> What the Vortex IQ Microsoft SQL Server health audit checks: SQL Server: Query Performance, Concurrency, AG Health & Backup Recency

**[Nerve Centre KPIs](/nerve-centre/kpi-cards/mssql) · [Audit Profile](/nerve-centre/kpi-cards/mssql/audit) · [Sentiment Settings](/nerve-centre/kpi-cards/mssql/sentiment)**

The questions every SQL Server DBA asks at 9am on Monday: which wait types are climbing and which query plans regressed in Query Store, is anything blocked or deadlocking, are the Always On AG secondaries synchronised and failover-ready, is tempdb / the transaction log filling, and how stale is the last backup. Cross-references ecommerce platforms - a blocking chain or slow DMV query co-occurring with a checkout drop, or product-table row-count drift vs the ecom catalog, indicates a database bottleneck costing revenue.

## What this audit checks

### Authentication & access

* Monitoring login authenticates over TDS (TLS / Encrypt enforced) without error
* Login has VIEW SERVER STATE (on-prem / Managed Instance) or VIEW DATABASE STATE (Azure SQL Database) - DMVs return rows, not permission errors
* VIEW ANY DEFINITION granted so plan / index / missing-index metadata is readable
* Failed-login spike >100 in 24h (msg 18456 / Logins Failed/sec - brute force or stale app credentials)

### Query Performance & Plans

* Avg query duration >200ms sustained 15m (total\_elapsed\_time / execution\_count from sys.dm\_exec\_query\_stats)
* Slow-query rate >5% of executions over the 200ms threshold (query-stats deltas)
* High-impact missing-index recommendation present (sys.dm\_db\_missing\_index\_details with large avg\_user\_impact × seeks)
* Top wait type is a resource wait (PAGEIOLATCH\_\* / CXPACKET / RESOURCE\_SEMAPHORE) climbing >baseline in sys.dm\_os\_wait\_stats

### Concurrency & Contention

* Any session blocked >30s (sys.dm\_exec\_requests.blocking\_session\_id chain)
* Deadlocks >0 in last 5m (Number of Deadlocks/sec counter + system\_health XEvent ring buffer)
* Lock Waits/sec climbing sustained >baseline (contention building before deadlocks surface)

### Capacity & Memory

* Connection saturation >90% - active sessions / @@MAX\_CONNECTIONS (login timeouts imminent)
* TempDB space used >85% (sys.dm\_db\_file\_space\_usage - full tempdb stalls every sort / hash / version-store op)
* Buffer cache hit ratio `<95%` OR Page Life Expectancy dropping `<300s` (memory pressure flushing the buffer pool)
* Data / log file usage >90% of allocated (sys.dm\_db\_file\_space\_usage / sys.master\_files)

### Always On AG & Backup

* Any AG replica synchronization\_health != HEALTHY OR synchronization\_state != SYNCHRONIZED (sys.dm\_hadr\_database\_replica\_states)
* AG secondary lag >10s OR redo queue >100MB sustained (redo\_queue\_size / redo\_rate - secondary falling behind)
* Last successful full backup older than 72h (MAX(backup\_finish\_date) from msdb.dbo.backupset per database)
* Log backup gap >30m on FULL / BULK\_LOGGED recovery DBs (transaction log growing, RPO window shrinking)

### Cross-channel: database vs ecommerce reconciliation

* Blocking chain or slow query co-occurs with an ecom checkout drop in the same 5-min window (sibling = bigcommerce.checkout / shopify.checkout)
* Batch-requests spike with no matching ecom order spike (= bot / scraper / runaway job, sibling = bigcommerce.order / shopify.order)
* Connection saturation >90% during an ecom traffic burst (app gets login / connection timeouts)
* Product-table row count drifts vs ecom catalog product count (sync failure, sibling = bigcommerce.product / shopify.product)

## Data sources

* `GET sys.dm_os_sys_info` - Instance uptime, scheduler / CPU count, server start time
* `GET sys.dm_os_performance_counters` - Batch Requests/sec, Buffer cache hit ratio, Page Life Expectancy, Deadlocks/sec, Lock Waits/sec, Errors/sec, Logins Failed/sec
* `GET sys.dm_os_wait_stats` - Cumulative wait-type breakdown - signal vs resource waits
* `GET sys.dm_exec_query_stats` - Per-plan execution count, elapsed time, logical reads, worker time - slow-query surface
* `GET sys.dm_exec_requests` - Live requests, blocking\_session\_id chains, wait state
* `GET sys.dm_exec_sessions` - Session count, status, program\_name (app connection origin)
* `GET sys.dm_db_file_space_usage` - TempDB + user DB space usage
* `GET sys.dm_db_resource_stats` - Azure SQL Database - CPU / IO / memory % of service objective
* `GET sys.dm_db_missing_index_details` - Missing-index recommendations with impact estimates
* `GET sys.dm_hadr_database_replica_states` - Always On AG - sync state, redo\_queue\_size, last\_commit\_time per replica
* `GET msdb.dbo.backupset` - Backup recency - backup\_finish\_date per database (on-prem / Managed Instance)