SQL Server checks

Every check Verbiqo can run on SQL Server 2016 to 2022 (supported versions), grouped by area. You ask in plain words; the model says which areas the question needs, and Verbiqo runs those checks first. A database that is not online, a full log or an unhealthy replica is reported on any question.

How to read this page. Each section is one area. Why it helps says what you get from it. Ask is a question to type that runs it. The table under it lists what Verbiqo checks there: you do not run those one by one. When a check finds a problem, it appears under the answer as a finding, with its evidence and a suggested fix.

How to ask for each area

Ask in your own words. The model reads the question and says which areas it needs; Verbiqo runs those checks before the model reads anything itself. The words below are what Verbiqo goes by when the model cannot say.

Area	Words Verbiqo goes by without the model	Ask, for example
Right now	slow, hanging, stuck, blocked, locks, waiting, busy, right now, currently	The sales app is hanging, what is going on?
Queries and indexes	slow, query, queries, index, CPU, plan, performance, tune	Which queries use the most CPU?
Plans and Query Store	the words for queries above	Did a query get slower?
Deadlocks	deadlock, 1205, victim, and any question about now or queries	Are we getting deadlocks?
Upkeep	maintenance, CHECKDB, DBCC, corruption, integrity, VLF, autogrowth, growth, shrink, statistics, page verify, compatibility, TRUSTWORTHY, and the words for settings	When did CHECKDB last run?
Availability and backups	backup, availability, Always On, failover, replica, job, agent, log full, log space, recovery, online, offline, suspect	Are the backups healthy?
Settings and storage	tune, tuning, optimise, recommendations, best practices, standards, settings, configuration, memory, parallelism, MAXDOP, waits, latency, disk, storage, tempdb	What should I tune?
Security	security, secure, login, sysadmin, password, encryption, audit, harden	Is this server secure?
Capacity	capacity, forecast, how long until, fill up, growth, sizing, headroom, planning	Capacity planning for this server
What happened	what happened, timeline, incident, outage, yesterday, at 3pm, when did, root cause	What happened last night?
What Verbiqo can see	what should I enable, readiness, permissions	What should I enable?

A broad question, such as health check, runs every area.

Right now: requests and blocking

Why it helps: When something hangs now, it shows the running requests, what they wait on and who blocks them, so you know what to clear.

Ask The sales app is hanging, what is going on?

Verbiqo samples requests, blocking and waits once a second for 10 seconds (see sampling).

Check	What it looks for
Blocking chain	The session at the head of a chain, how many wait behind it and for how long. When the head is idle with a transaction open, the `KILL` to end it, for you to review.
Long requests	Requests running more than a minute.
Dominant wait	The wait that took most of the time during the sample (locks, disk, log, parallelism, CPU, memory grants, the client, availability group commits).

A statement’s plan

Why it helps: Explains why one statement is slow, or blocks others, from its estimated plan, without running it.

Ask Why is this slow? UPDATE dbo.orders SET status = … WHERE …

Needs SHOWPLAN and SELECT in the statement’s database (optional). An UPDATE or DELETE is planned as the SELECT that finds its rows.

Check	What it looks for
Missing index	The index the optimizer estimates would serve the statement, with its cost cut and the `CREATE INDEX`.
Full scan	A table of 100,000 rows or more read in full.
Lookups	1,000 or more rows looked up one at a time for columns the index lacks.
Implicit conversion	A column converted in every row because the value is another type, so its index cannot be sought.
Function on an indexed column	A filter that wraps a column an index starts with (`YEAR(created)`, `ISNULL(col, …)`), with the rewrite.
No join condition, no statistics	Tables joined with nothing to join on; columns the optimizer has no statistics for.
What a write locks	A search that reads the table in full takes an update lock on each row as it reads it; past about 5,000 changed rows, the lock escalates to the whole table.
Cascades, triggers, indexes	Foreign keys that cascade the change, the triggers that fire for it, and the indexes (or the clustered key) it changes.

Queries and indexes

Why it helps: Shows the statements using the most CPU, the indexes the optimizer asked for, and the indexes that cost without serving, so you fix the few that matter.

Ask Which queries use the most CPU? Are there index problems?

Check	What it looks for
One statement dominates	A single statement taking a large share of the CPU used by cached plans.
Missing index	An index the optimizer asked for, with its columns and estimated benefit.
Covered indexes	An index that duplicates another, or whose keys another index starts with: the other serves every query it served, and each write maintains one more.
Unused indexes	Indexes maintained on every write and never read, on tables of 10,000 rows or more. Only after a week of uptime, since the usage counters start again at each restart.
Forwarded records	Heaps read through 10,000 or more forwarded records: rows that grew and moved, each read costing an extra page.

The index review reads the catalog and the usage counters SQL Server already keeps, in the five largest user databases. It never reads the indexes themselves (sys.dm_db_index_physical_stats), the expensive query on a large database, so fragmentation is not reported: on current storage it is rarely what makes a server slow.

Plans and Query Store

Why it helps: Finds queries whose plan changed for the worse, from Query Store, so you can pin the good plan back.

Ask Did a query get slower?

From the plan cache (no statement is run again) and Query Store over the last 7 days, in up to five databases.

Check	What it looks for
Implicit conversion	A column converted in every row, so its index cannot be used: usually nvarchar sent for a varchar column. Names the column and the rows read.
One plan, very different row counts	A cached plan returning from a few rows to many: parameter sniffing.
Key lookups	Thousands of rows fetched one at a time per run.
Spills and grants	Sorts or hashes spilling to tempdb; memory granted and not used.
Repeated scans	A whole large table read on every run of a frequent statement.
No join condition	Tables joined without one.
Plan regression	A query whose newest plan is at least twice as slow as an earlier one, with the plan to force.
Forced plan failing	A forced plan SQL Server can no longer use.
Query Store stopped	Query Store read-only because it is full.

The plan report

What the cached plans of the slowest statements say, one row per statement, values masked:

Column	Holds
`runs`	How many times the plan has run since it was cached.
`avg_ms`	Elapsed time per run, waiting included. Far above `cpu_ms` per run means the statement was waiting (blocking, I/O), not working.
`cpu_ms`	Total CPU time over all runs.
`rows`	The fewest and most rows one run returned (`12-48000` is one plan serving very different row counts).
`spills`	Sorts or hashes that spilled to tempdb, over all runs.
`access`	How the plan reaches each table: scan or seek, and on which index.
`notes`	conversion (an implicit conversion), lookup (key lookups), no join condition, optimizer timed out.
`starts`	The first words of the statement.

Units. SQL Server keeps dm_exec_query_stats, dm_exec_procedure_stats and Query Store times in microseconds, and dm_exec_requests and dm_exec_sessions in milliseconds. Any result that reads those columns ends with a Units: line naming each timed column’s unit, from what the column is made of rather than what it is called, then the first rows converted to seconds. A column named _ms that holds microseconds is called out, so the model cannot read a 1,000-fold error.

Deadlocks

Why it helps: Shows recent deadlocks, the statements in each and why, so you can change the order or the indexes that cause them.

Ask Are we getting deadlocks?

From the system_health session every instance runs: its in-memory ring buffer, which keeps the most recent few hours to days.

Check	What it looks for
Deadlocks	Deadlocks in the last week grouped by the objects and indexes fought over, with the victim, the other session, their logins, hosts and statements (values masked), and the fix that usually fits: the same lock order, or a covering index when the two sessions used different indexes of one table.

Upkeep

Why it helps: Finds upkeep that has slipped (integrity checks overdue, a log split into too many pieces, files growing in bad steps), so corruption and slow recovery are caught early.

Ask When did CHECKDB last run?

From metadata only. Index fragmentation is left out on purpose: measuring it reads index pages from disk.

Check	What it looks for
CHECKDB overdue	Databases without a clean CHECKDB in 7 days, or never.
Too many VLFs	A log split into more than 1,000 virtual log files.
File growth	Large files growing by percent, or in steps of 64 MB or less.
Auto-shrink, auto-close	Either on.
Page verify	Not CHECKSUM.
Automatic statistics	Creation or updates off.
Stale statistics	Statistics on tables of 10,000 rows or more with a fifth or more of the rows changed since the last update.
TRUSTWORTHY	On for a database other than msdb.
Old compatibility level	Three or more versions behind the server.
tempdb files	Too few data files for the CPUs, or files of different sizes.

Availability and backups

Why it helps: Shows backups, availability groups and what holds each log, so you know you can restore and fail over.

Ask Are the backups healthy?

Check	What it looks for
Database not online	A database restoring, recovering, suspect, in emergency or offline.
Log full	A transaction log nearly full, and what holds it.
Replica unhealthy	An availability group replica disconnected or not healthy.
Replica behind	A database with log waiting to be sent or redone on a secondary.
No recent full backup	Databases without a full backup in the last 7 days.
No log backup	Databases in FULL recovery without a log backup in the last day.
Failed Agent jobs	SQL Server Agent job steps that failed or were cancelled in the last day.

Availability groups, clusters and log shipping

Ask Is the availability group healthy, and can it fail over?

Check	What it looks for
Data at risk	What a failover now would lose: how far each secondary’s last commit trails the primary’s, and the log not yet sent (Microsoft’s estimate of data loss). Any secondary not both synchronous and synchronized.
Time to recover	The redo a secondary still has to apply, at the rate it redoes (Microsoft’s estimate of failover time).
Automatic failover	Whether a replica could take over on its own right now; when it could not, which database is not synchronized or which replica is disconnected. No replica set for it at all.
Suspended data movement	A database whose data movement is suspended, with why and the `SET HADR RESUME`.
Seeding, listener, kind of group	Automatic seeding that failed in the last week; a clustered group with no listener; a read-scale group (no cluster), which never fails over on its own.
Failover cluster instance	Each node’s state and which runs the instance; a node down, critical when none is left to fail over to.
Log shipping	Backups and restores later than their own thresholds (needs `SELECT` on msdb’s log shipping monitor tables: optional).
Replication agents	A distribution or log reader agent failing, including one whose job keeps restarting it (counted over the last hour, as its latest state alone can read in progress); one that failed and recovered; one silent for an hour or more.
Replication latency	An agent a minute or more behind, from the change at the publisher to its delivery; a warning past ten minutes.
Replication errors and subscriptions	The last day’s errors (key values masked), and subscriptions inactive until reinitialised. Needs the optional column grants on the distributor; a publisher whose distributor is another server says which one to add.

On a secondary replica, Verbiqo sees that replica only: it names the primary, and says its query statistics and waits cover only the reads sent to it.

Settings and storage

Why it helps: Sizes the settings that most often matter for this machine, to the usual standards, with the statement for each, and says what the waiting since the start points at.

Ask What should I tune? or Is this server configured well?

The machine is what SQL Server reads itself: its memory, and its processors per NUMA node. A size you state (16 GB, 4 cores) is used only where SQL Server cannot say, and Verbiqo tells you when SQL Server sees something different (a container or memory.memorylimitmb on Linux, a VM’s size). Nothing is recommended for a value already in use.

Check	What it looks for
max server memory	Unlimited, or above the machine’s memory. The value leaves the operating system 1 GB, plus 1 GB for every 4 GB from 4 to 16 GB, plus 1 GB for every 8 GB above (10 GB of 64 GB), never under half the memory.
Max degree of parallelism	Above Microsoft’s guidance for the logical processors per NUMA node (soft-NUMA nodes on 2016 and later): one node, up to 8 processors: their number, more: 8; several nodes, up to 16 per node: that number, more: half, at most 16.
Cost threshold for parallelism	Left at 5, the default; 50 is the usual start. The share of parallel waits is given beside it.
Waits since the start	A kind of wait taking a quarter or more of the waiting (disk reads, the log, parallelism, CPU, memory grants, worker threads, locks, tempdb contention, a slow client, a synchronous secondary), with what to look at first. Not judged on a server up less than an hour.
Plans used once	A quarter or more of the plan cache (and 50 MB or more) in ad hoc plans run once, with `optimize for ad hoc workloads` off.
Configured but not in use	A setting changed with `sp_configure` without `RECONFIGURE`, or one waiting for a restart.
Instant file initialization	Off on Windows: file growth and restores fill new space with zeros.
Lock pages in memory	Not granted on a Windows server with 32 GB or more.
Memory low	The operating system reporting physical memory low.
Page life expectancy	Below 300 seconds for every 4 GB SQL Server holds. Not judged on a server up less than an hour: it cannot be higher than the time since the start.
File latency	Data files averaging 50 ms or more per read, log files 20 ms or more per write.

Security

Why it helps: Shows logins with too much access, weak settings and unencrypted connections, so you close the gaps an audit would find.

Ask Is this server secure?

Check	What it looks for
sa enabled	The built-in `sa` login enabled, even when renamed.
Password policy	SQL logins that bypass the password policy.
Many sysadmins	Many enabled logins in `sysadmin`.
xp_cmdshell	`xp_cmdshell` enabled.
Unencrypted connections	Connections arriving without encryption.
Orphaned users	Users in the connection’s database whose login is gone, usually after a restore from another server, with the `ALTER USER … WITH LOGIN` for each.

The security review runs these and more (Ad Hoc Distributed Queries, CLR, cross-database ownership chaining, TRUSTWORTHY databases, an Audit running, recent backups, a supported version) as controls across every saved connection, mapped to CIS Controls, ISO 27001 and SOC 2.

SSIS and Reporting Services

Why it helps: Finds the packages that failed or ran long, and the reports and subscriptions that are slow or failing, from their own history, with where the time goes.

Ask Are any SSIS packages failing? Are the SSRS reports or subscriptions having problems?

Needs the optional grants. Words that bring it in: SSIS, SSISDB, SSRS, Integration Services, Reporting Services, report server, packages, subscriptions, ETL.

Check	What it looks for
Failed packages	SSIS runs that failed, were canceled or ended unexpectedly in the last week, with the catalog’s first error message for each.
Slow packages	A run that took, or has been running, twice the package’s usual time or more (the median of its successful runs in 30 days).
SSISDB growth	Catalog cleanup off, or SSISDB of 20 GB or more, with the retention to set.
Slow reports	Reports taking 10 seconds or more a run, split into data retrieval (the query), processing (the report) and rendering (the format and size), with what to look at.
Failing reports and subscriptions	Reports with failed runs in the last week, and subscriptions whose last delivery failed (e-mail addresses masked).

Permission errors

Why it helps: Turns a pasted SQL Server permission or login error into the statement that should fix it, for the right user, and no wider.

Ask Our report fails with: The SELECT permission was denied on the object …

Error	What Verbiqo gives
229, 230, 262: a permission denied on an object, column or database	The error names no user, so Verbiqo asks which one (with the database’s users to choose from), then reads its grants and roles: the `GRANT` it lacks, the role it already has it through, or the `DENY` that blocks it, with the `REVOKE` at the DENY’s own level.
916, 4060, 4064: the login cannot open the database	The database offline or single-user, no user for the login there (`CREATE USER`), an orphaned user from a restore (`ALTER USER … WITH LOGIN`), or a user without `CONNECT`.
18456: login failed	The login missing, disabled, locked out or expired, or the server allowing Windows authentication only; with the state from the error log (Error: 18456, Severity: 14, State: 8), what that state means.
297, 15247, 15151, 1088	What the error can and cannot tell, and what to look at.

More: Permission errors.

Ready-made reports

A report is one fixed reading of one area: no question, no model, the same output every time. Use one to look at an area yourself, or to share what it shows.

From a terminal, it prints the report:

verbiqo mssql run erp blocking

In verbiqo chat, it loads the report into the conversation so you can ask about it. help lists the reports:

/mssql erp blocking
/mssql erp help

In the desktop apps, there is no report command: ask in your own words, and Verbiqo reads what the question needs.

Report	Shows
`sessions`	Requests running now, longest first, with what each waits on.
`blocking`	Who waits, on whom, for how long, and what each was running.
`waits`	What the server has waited on most since it started, idle waits left out.
`top-queries`	Statements by total CPU since their plans were cached.
`databases`	Every database: state, recovery model, what holds its log, and size.
`log-space`	Transaction log size and how full each is.
`file-latency`	Read and write latency per database file since the server started.
`memory`	What SQL Server uses and targets, the machine’s memory, and page life expectancy.
`memory-clerks`	The largest memory clerks.
`availability-groups`	Replicas, roles, health and each database’s queues.
`backups`	The last full, differential and log backup of every database.
`failed-jobs`	SQL Server Agent job steps that failed or were cancelled, latest first.
`missing-indexes`	Indexes the optimizer asked for, by estimated benefit.
`settings`	The settings that most often matter, as configured and in use.
`tempdb`	Sessions using the most tempdb space.
`connections`	Sessions by login, host and program.

The login and what it may hold are in Set up SQL Server.