Docs / Connections: SQL Server

Ask SQL Server

Attach a server and ask. Verbiqo reads the system views and msdb’s history, never your rows.

Attach

/mssql sales

Verbiqo shows the product, version and edition, and the availability groups the server is in.

What to ask

Every check and the words that bring it in: SQL Server checks.

Questions no check covers

Available from Verbiqo 0.1.26. This is in the next release; 0.1.25 does not have it yet.

Why it helps: Ask anything reasonable about the server in SQL Server’s terms, not only what Verbiqo’s checks cover, and get the answer read from the server rather than told how to read it yourself.

Ask List tables. What columns does dbo.orders have? Which tables are in the sales schema?

1. What exists

   Verbiqo first reads what is there: the schemas with how many tables each has, the tables and views with their sizes (every name up to about 200; above that, the counts and the largest), and anything the question names. Names and sizes only, never rows.

2. The model reads

   The model writes its reads with the real names, and Verbiqo checks and runs each one, as for every question. If the model explains how to do it instead (sp_help, for example: only SELECT runs), Verbiqo sends it back once to read.

3. Which one?

   When the answer depends on which one you mean, such as two tables called orders in different schemas, the model may ask you, offering only names that exist.

4. Long lists

   A read returns at most 500 rows, and the answer says when a list was cut. Say show the next 500 to continue: Verbiqo reads the next part itself.

Limits:

• One answer does not list thousands of names. A large list comes in parts: by schema, by a name pattern, or 500 at a time.
• Only what the monitoring account can see. SQL Server shows every table with VIEW ANY DEFINITION; another database is read with three-part names, such as crm.sys.tables. Where it cannot see something, the answer says so rather than treating it as absent.
• A limited number of reads per question. A broad question may be answered in part, and the answer says what was not read.
• Checks keep their questions. A question one of Verbiqo’s checks covers (settings, slow queries, replication, sizes, whether a change helped, a pasted error or statement) takes that check, as before. The wording decides: For example, a question about logins takes the security check.
• Organization limits. Where your organization limits how much Verbiqo reads per question, the list of what exists is not read first; the rest still applies.

Explain a slow statement

Why it helps: it explains why one statement is slow, or blocks others, from its estimated plan, without running it, so you can fix that statement.

Ask Why is this slow? and paste the statement after it, for example:

Why is this slow? DELETE FROM dbo.orders WHERE amount < 300

1. Plan

   Verbiqo takes the statement from your question and asks SQL Server for its estimated plan with SET SHOWPLAN_XML ON, under which SQL Server compiles a statement without running it. An UPDATE or DELETE is planned as the SELECT that finds the same rows; an INSERT … SELECT as its SELECT.

2. Read it

   The index the optimizer asks for (with the CREATE INDEX), tables read in full, rows looked up one at a time, implicit conversions (an N'…' value against a varchar column), functions around indexed columns (YEAR(created)) with the rewrite, joins with no condition.

3. For a write

   The rows it locks while it searches, lock escalation to the whole table past about 5,000 rows, foreign keys that cascade, the triggers that run for it, and the indexes it maintains.

Plans of application tables need SHOWPLAN and SELECT in their database (how, and only if your policy allows); without them Verbiqo says which grant. Nothing is run either way. A name through a linked server is not planned, and MERGE is not planned yet.

Check that a change helped

Why it helps: it tells you, from the server’s own counters, whether a setting you changed did what it was meant to, so you keep what works and undo what does not.

1. Review

   Ask What should I tune? Verbiqo saves a reading of the server on this computer: the settings, what it recommended, its counters, its waits and its busiest statements.

2. Apply

   Run the statements you choose (sp_configure with RECONFIGURE).

3. Wait

   Give it at least a day of normal work; a week if the workload has a weekly cycle.

4. Ask

   Ask Did the changes help?

Under the answer, Verbiqo shows which recommendations you applied, applied differently, did not apply, or configured without RECONFIGURE; when they took effect; a before-and-after table per batch; and a verdict per change: improved, measure improved, speed unchanged, no measurable change, worse, mixed or not enough data.

Good to know:

• When a change took effect. SQL Server keeps no time for a setting change that a monitoring login can read. Changing max server memory, MAXDOP or cost threshold with RECONFIGURE empties the plan cache, so the oldest plan compiled after the review dates it; a restart dates it too. When neither does, and no reading was taken in between, the verdict is not enough data rather than a guess.
• Several changes at once share the statements’ time, and the verdict says so. A mixed verdict for MAXDOP and cost threshold is common: parallel waits fall while some large queries run serially and take longer. Check those queries before keeping the change.
• Too little work, no verdict. A window with fewer than 1,000 batches (asked again soon after, or a quiet weekend) gives not enough data: a few batches can show any change.
• Cleared wait statistics (DBCC SQLPERF) and restarts are noticed: those figures are counted from the reset or left out, never misread.
• The readings stay on this computer. They are never sent anywhere; the last 20 per connection are kept.

Permission errors

Why it helps: it turns SQL Server’s error into the one statement that should fix it, for the right user, so you do not grant more than the application needs.

Paste the error as you got it, from any client, tool or log:

Our report fails with: The SELECT permission was denied on the object 'orders', database 'shop', schema 'dbo'.

1. Read the error

   What was refused and where, from SQL Server’s own message.

2. Ask if it is not clear

   A permission error never names the user. Verbiqo asks which user the failing connection uses, and lists the database’s users that lack it, and those that have it, so you can pick one. Name it up front to skip the question: … it connects as report.

3. Read the catalog

   That user’s grants at object, schema and database level, its roles, and any DENY (which beats every GRANT). For a login error: the login, the database and the user it maps to.

4. Answer

   The GRANT it lacks; or the DENY that blocks it, with the REVOKE where it is; or the user it needs in the database (CREATE USER), or the orphaned user to map after a restore (ALTER USER … WITH LOGIN).

For a failed login, paste the error log line too (Error: 18456, Severity: 14, State: 8): the state says why, and the client never gets it. Nothing is granted by Verbiqo.

How far to trust an answer

Why it helps: it tells you which parts of an answer Verbiqo read and checked, and which are the model’s own, so you know what to verify before you change anything.

• Figures come from what Verbiqo read. A number in the answer that no reading backs (a wait time, a counter, a size) goes back to the model to correct; if it still has nothing behind it, the answer says it could not be verified. A value the answer proposes for a setting (set max server memory to 12,288 MB) is a suggestion, not a reading, and is allowed.
• A change the model suggests that no finding calls for (KILL, DBCC FREEPROCCACHE, a shrink, sp_configure, an availability group failover, a log backup to NUL) is named under the answer as the model’s own suggestion. Check it before running it.
• A secondary replica answers for itself. Its query statistics, waits, blocking and plan cache cover only the reads sent to it, and Verbiqo says so under the answer, naming the primary. An answer may not call queries or locks fine for the whole availability group from there.

Ask How busy is this server, and what is page life expectancy?

Every figure in the answer should match a finding or a reading under it.

What happened earlier

/splunk prod
/mssql sales
/prom prod

Splunk adds the error log (Log findings); Prometheus adds the machine’s history and reboots.

From a terminal

In verbiqo chat, attach with /mssql as in the app. To run one report, or one statement of your own:

verbiqo mssql run sales blocking
verbiqo mssql query sales "SELECT TOP 10 wait_type, wait_time_ms FROM sys.dm_os_wait_stats ORDER BY wait_time_ms DESC"

Reports for run: sessions, blocking, waits, top-queries, databases, log-space, file-latency, memory, memory-clerks, availability-groups, backups, failed-jobs, missing-indexes, settings, tempdb, connections. query runs one SELECT the way a question does: planned first, system views only, rolled back (how each statement stays read-only); --json works on every verb.