Benchdog SQL Monitor

In VS Code, click Check Now on a server or Check All Now on a project in the Estate view. In the desktop app, F5 checks every server and Ctrl+R checks the picked one. Each area shows as a card with its state and a summary in plain words, the ones that need a look first.

From a terminal, run benchdog monitor check --server sqlhost --sign-in windows for a server on its own, or benchdog monitor check --project production.monitor.json --name Shop for one in a project. Add --fail-on critical or --fail-on warning to exit with 3 at that state or worse, for a script or a pipeline.

Run benchdog monitor service install --project production.monitor.json from a terminal opened as administrator on Windows, or with sudo on Linux and a Mac. It installs the collector as a Windows service, a systemd service or a launchd daemon that starts with the system, and starts it now. The program, the project file and your licences are copied in, so run the command again after you change the project or get a new licence. Installing needs a licence or a trial for SQL Monitor; under sudo it reads the licences of the person who ran sudo. The scripts in scripts/monitor do the same for people who would rather run a script.

benchdog monitor service status says whether the service is installed and running, and Collector Service Status in VS Code and the desktop app says the same and copies the install command. To run the collector in a terminal instead, run benchdog monitor collect --project production.monitor.json, or add --once to check every server once and stop.

For a container, pull ghcr.io/simonmurrell1979/benchdog-monitor, as the release number or latest, and mount the project at /config/project.monitor.json and your licence files in /config/licences. To build it yourself instead, run docker build -f docker/monitor/Dockerfile -t benchdog-monitor . from the engine folder. A container has no one signed in and no secure store, so its servers sign in with managedIdentity, as on Azure Container Apps or Azure Container Instances.

A service has no one signed in, so a server that signs in with Entra is refused when you install it. Its servers use windows, managedIdentity or sql instead. On Windows the service runs as its own virtual account, NT SERVICE\benchdog-monitor, or with --account a group managed service account such as CONTOSO\sqlmonitor$ or NT AUTHORITY\NetworkService, never an account with a password; give that account VIEW SERVER STATE on each server. On Linux it runs as a user of its own that systemd makes, with the rest of the system read-only to it.

For a SQL login, save its password for the service with benchdog monitor password --service benchdog-monitor --server legacy01 --user monitor, as administrator or with sudo. The install command lists each login whose password is not saved yet, with the command that saves it. On Azure, a VM, a container or an Arc machine signs in with its managed identity, system-assigned or user-assigned by clientId.

Add an alerts section to the project file with its channels, each with a name, a type of email, teams or slack, and a minimumState of warning or critical. Email gives smtpServer, port, security, from, to and user. VS Code gives IntelliSense for every setting.

Each channel's secret, the SMTP password or the Teams or Slack webhook address, is kept only in the system's secure store. Save it with benchdog monitor secret --project production.monitor.json --channel "DBA Teams", with Save Alert Channel Secret in VS Code, or by right-clicking the channel in the desktop app. For an installed service add --service benchdog-monitor, as administrator or with sudo. Then run benchdog monitor alert test --project production.monitor.json to show each channel works.

Sixteen built-in rules decide what fires. An alert is said when it fires, again every repeatMinutes while it stays, 60 by default, at once when it changes between warning and critical, and once more when it is fine again. Set a rule's least state under rules, such as "cpu": "critical" or "waits": "warning".

Open the server's Top queries tab and order them by CPU, time, reads, writes or runs, as totals or per run. Click a query for all of its text, then Show Plan to see each operator as a tree with its share of the cost, every warning in plain words, and each missing index with its CREATE INDEX statement to copy. Save Plan writes a .sqlplan for SQL Server Management Studio.

From a terminal, run benchdog monitor top --project production.monitor.json --name Shop --by reads --per-run for the plan cache, add --query-store Sales to read that database's Query Store, or --history --hours 24 for what the collector kept. Then run benchdog monitor plan --project production.monitor.json --name Shop --query-hash 0x1A2B3C4D5E6F7A8B --save query.sqlplan with a hash from that list.

With a history store, the server's History tab charts SQL Server's CPU, the memory in use and batch requests a second on one chart, with the same time a week ago dashed beneath while Compare with last week is ticked, then free disk and blocked sessions, and lists each check to open in full. The overview shows the latest alerts beside the server cards, and its Versions tab shows which servers are behind the latest update for their release.

From a terminal, run benchdog monitor history --project production.monitor.json --name Shop --hours 48 for one server's checks, benchdog monitor alerts --project production.monitor.json for every alert still open and those that fired in the last day, and benchdog monitor versions --project production.monitor.json for each server against the latest update.

Add a limits section to the project file. Each limit has a name such as cpuWarningPercent, cpuCriticalPercent, diskWarningFreePercent, diskCriticalFreePercent, longQueryCriticalSeconds or fullBackupHours, and a warning limit must come before its critical one. CPU is 80% a warning and 95% critical by default, disk 15% and 5% free, and a query running for half an hour is critical.

The same file sets how the collector runs: intervalSeconds between rounds, 300 by default and from 30 to a day, retentionDays for how long checks are kept, 30 by default, and topQueries for how many of each server's top queries are kept at each check.

Run benchdog monitor project new --project production --server sqlhost --sign-in windows --store-server sqlmon --store-database BenchdogMonitor --store-sign-in windows to make production.monitor.json watching one server with its history on sqlmon. Then benchdog monitor project add --project production.monitor.json --server legacy01 --sign-in sql --user monitor --name Legacy --group Finance adds a server, and benchdog monitor project remove --project production.monitor.json --name Legacy takes one out, leaving its history in the store.

At a terminal, anything a command needs and was not given is asked for, with numbered choices where there are some: the project files in the folder, a project's servers, how to sign in. Add --no-prompt in a script to fail instead of asking.

Checks read only the server's own views, never a row of table data, and change nothing. A check waits no longer than 5 seconds for a lock and gives way in a deadlock, so it never stops the server's own work. Nothing is installed on the servers it watches: the collector runs on one machine of yours and reaches each server over an encrypted SQL connection.

The sign-in needs VIEW SERVER STATE on SQL Server and Managed Instance, or VIEW DATABASE STATE on Azure SQL Database. A sign-in without the permission an area needs gets that area as not available, saying what it needs, and the other areas are still checked.

SQL Server, Azure SQL Database, Azure SQL Managed Instance and Amazon RDS for SQL Server. Azure SQL reads what it has, such as CPU and memory against its limits, and leaves backups and SQL Server Agent to Azure. Amazon RDS is known by itself: backups and the Multi-AZ standby are left to AWS, setting findings say to change them in the DB parameter group, and you sign in with a SQL login or with Windows through AWS Managed Microsoft AD.

In a SQL Server or Azure SQL database you choose, in a benchdog schema whose tables the collector makes the first time. The first time, the sign-in needs to be able to create a schema and tables there, as db_owner can; after that it only reads and writes them. On Azure SQL make the database first.

A round every 300 seconds keeps about 1.5 MB of history per server a day. Checks are kept for retentionDays, 30 by default, then removed a few at a time, never holding long locks. While the store cannot be reached, checks wait in memory and are kept once it can be.

Never in the project file, a log or an error. A SQL login's password, an SMTP password and a Teams or Slack webhook address are kept only in the system's secure store, and for an installed service in the system's store for services: DPAPI for the machine in a folder only SYSTEM, Administrators and the service can read, the System keychain on a Mac, or systemd's encrypted credentials on Linux. A webhook address must be https, and no error ever shows it.

One machine on your network that can reach the servers and the history store: Windows for a Windows service, Linux with systemd for a systemd service, a Mac for a launchd daemon, or Docker. It is started again after 30 seconds when it fails, checks a few servers at a time, and runs the same engine the VS Code extension, the desktop app and the benchdog command use.

Yes. Every tool has a thirty day trial with every feature, and no card. Ask for one at benchdog.dev/trial with your name and email, and the trial licence arrives by email, as a line to paste and a benchdog.licence file. One trial per person covers every tool at once.

One licence is for one person and covers the VS Code extension, the desktop app and the benchdog command, on every machine that person works from. There is no machine limit and no server count. Buying another tool reissues your licence with that tool added.

The licence shows whose it is: your name and email appear in each tool's About and account menu.

The last version released while you were paid keeps working. Only updates stop. A licence is checked offline and never expires a version it covers, so nothing stops working the day your year ends.

The VS Code extensions run in VS Code 1.95 or later on Windows, Linux and Mac. The desktop apps and the benchdog command are single programs for Windows (x64), Mac (Apple silicon) and Linux (x64), with no .NET install needed. The SQL tools work with SQL Server 2017 or later, Azure SQL Database and Azure SQL Managed Instance.

No. The engine runs on your machine, reads your vaults and databases as you, and keeps the results with you. A tool contacts benchdog.dev only when you click something that needs it, such as asking for your licence, and never in the background. Licences are checked offline.

Every change a tool makes runs in one transaction with XACT_ABORT on, so it all happens or none of it does. A SQL Schema Compare deploy, a SQL Data Comparer sync and a SQL Data Filler fill are each one transaction, and SQL Data Scrambler scrambles each table in one. The scripts the tools write for you to run in SSMS or sqlcmd are wrapped the same way and roll back if any batch fails. SQL Multi Runner runs your own script as it is, and wraps each database in a transaction when you tick that. Before any change, a tool says what it will do and asks first: SQL Schema Compare names the tables and columns whose rows go, SQL Data Comparer says how many rows change, and SQL Data Scrambler asks you to type the database's name. SQL Schema Compare also stops if either database changed after you read the plan. Even so, nothing replaces a backup, and the SQL Server tools can take one first.

Azure calls run as you, through a browser sign-in once or through your existing az login, in any tenant you belong to. The SQL tools use your Microsoft account, your Windows sign-in or a SQL login, and every connection is encrypted. A SQL login's password is kept until the app closes, or in your system's secure store (DPAPI on Windows, Keychain on Mac, libsecret on Linux) if you tick Remember. It is never written to a settings file, a project file or a log.

The sign-in lacks the permission that area reads with. Grant it with GRANT VIEW SERVER STATE TO [login]; on SQL Server or Managed Instance. On Azure SQL Database the area says Needs the VIEW DATABASE STATE permission. instead, and that is the permission to grant in the database.

Until then the area is shown as not available, the other areas are still checked, and an alert rule for that area neither fires nor ends.

The collector never asks for a password, so a SQL login's password has to be saved first. The message gives the command: benchdog monitor password --server legacy01 --user monitor, or with --service benchdog-monitor for an installed service, as administrator or with sudo. On Linux, restart the service afterwards with sudo systemctl restart benchdog-monitor, as systemd gives it its passwords when it starts.

SQL Server refused the saved password for monitor. means the saved password is wrong or has changed; save it again the same way.

Only an administrator can install a service. Run this from a command prompt opened as administrator. or Only root can install a service. Run this with sudo. says the terminal was not opened with the rights a service needs.

A service has no one signed in, so it can not sign in with Entra lists the servers in the project that sign in with Entra. Give each "signIn": "managedIdentity", "windows" or "sql" in the project file and install again.

SQL Monitor needs a licence to run as a service. means no licence or trial of yours covers SQL Monitor. Start a free trial with benchdog licence trial, or add the licence you bought with benchdog licence add <file>, then install again. A service already running never stops on a licence: while none covers it, it keeps checking and sends one note a week through its alert channels.

The Teams webhook address for DBA Teams is not saved. gives the command that saves it: benchdog monitor secret --project production.monitor.json --channel "DBA Teams", with --service benchdog-monitor for the installed service. A Slack webhook address starts with https://. means the address pasted was not https; copy it again from the Slack app's Incoming Webhooks page, or for Teams from the workflow or connector.

For email, smtp.office365.com did not take the email is followed by what the server said, with any secret taken out. A channel with a user needs security set to startTls, so the password is never sent in the clear. Run benchdog monitor alert test --project production.monitor.json after each change; it exits with 1 when any channel failed.

The history store in BenchdogMonitor was made by a newer Benchdog (version 3). Update Benchdog to use it. means another machine runs a newer release against the same store; update this one. Could not make the history store in BenchdogMonitor means the sign-in could not create the benchdog schema and its tables the first time; it needs to be able to, as db_owner can. On Azure SQL, The history store, BenchdogMonitor on sqlmon.database.windows.net, is not there. Make the database in the Azure portal first, then run again.

While the store cannot be reached the collector keeps checking, holds the checks in memory and keeps them once it can, and the historyStore rule raises a critical alert.

stopped on an error, and starts again shortly means the service failed and the system will start it again. Read its log, which the status command names: C:\ProgramData\Benchdog\benchdog-monitor\logs\benchdog-monitor.log on Windows, journalctl -u benchdog-monitor on Linux, or /Library/Logs/Benchdog/benchdog-monitor.log on a Mac. The log never holds a password or a connection string.

The usual causes are a server or store that the service's account cannot sign in to, or a project changed after it was installed. Run benchdog monitor project check --project production.monitor.json --service benchdog-monitor to sign in to everything with the service's own passwords and secrets, then install again to copy in the current project.

Turn on the diagnostics log: in a desktop app, Settings, then Write a diagnostics log; in VS Code, the extension's diagnostics setting; for the benchdog command, set the environment variable BENCHDOG_DIAGNOSTICS=1. Each run writes one file to the logs folder in the Benchdog folder (%LOCALAPPDATA%\Benchdog\logs on Windows, ~/Library/Application Support/Benchdog/logs on Mac, ~/.local/share/Benchdog/logs on Linux), and the last ten are kept. The desktop apps have Open logs folder, and VS Code has the Open Diagnostics Logs Folder command.

The log records sign-in requests, Azure calls, engine requests, SQL connections, times and errors. Passwords, connection strings, tokens, secret values and your data are never written, so it is safe to send to support.

That is the crash window, for an error the app did not expect. Copy details puts the error and its details on the clipboard, Email support opens a message to support with them, and Open logs folder shows the crash report, which is written even with the diagnostics log off. If the error happened on a background thread the app has to close, and the window shows when it next starts.

Browser sign-in keeps its tokens in the desktop keyring, and that machine has none unlocked, which is common in WSL. Install and unlock a keyring such as GNOME Keyring or KWallet, or sign in with az login and turn on Use az login in Settings or pass --cli to the benchdog command. Benchdog never falls back to a plain file for tokens.

The SQL Server you connected to uses a certificate your machine does not trust, which is usual for a server on your own network with a self-signed certificate. Pick Trust the server's certificate in the picker, or add --trust-server-certificate on the command line, for servers you know. For a server you do not know, install its certificate chain instead.

In a desktop app, choose Forget saved password from the menu. On the command line, run benchdog sql forget-password --server <server> --sql-login <login>. Either removes it from the system's secure store, which is the only place it was kept.

Write to support@benchdog.dev or use the support form on this site, and a person replies. Say which tool and version you were using (Help, About in a desktop app shows both), what you did and what you saw, and attach the diagnostics log if you have one.