SQL Performance Monitor
Overview
Most Database Health Monitor reports show a snapshot or history. The SQL Performance Monitor (the window is titled Performance Viewer) is the other kind of tool: you point it at one SQL Server, leave it running while a problem happens or a load test runs, and read what it saw during that time. It answers what was blocking, running long, waiting or burning CPU while I was watching?
It is a separate program that Database Health Monitor starts for you, so it keeps running if you close or move around the main window. It monitors one instance at a time. It has:
- a CPU and RAM chart and a connections chart at the top, updated every few seconds,
- five tabs: Missing Indexes, Blocking Queries, Long Running Queries, Wait Stats and High CPU,
- a Reports menu that turns what was collected into an HTML report, and a Timed Full Report that creates one for you later.
Where to find it
| Route | How |
|---|---|
| Menu bar | Tools → SQL Performance Monitor (asks which server to connect to) |
| Server tree | Right-click the server → SQL Performance Monitor connect to server name |
From the server tree the server name is filled in for you. In both cases the connect dialog is shown first, with the last server and sign in type used remembered. Cancel at that point closes the window.
Start and stop
The monitoring starts as soon as you connect. The button under the charts reads Stop Monitoring while it is running and Start Monitoring when it is not.
- Stop Monitoring stops every background reader and clears the tabs and the report area.
- Start Monitoring begins again with an empty slate. Everything collected is a new session: the charts are cleared, the wait baseline is taken again and the missing index counters start from zero.
- Connect on the menu bar picks a different server.
Each tab reads on its own connection, so a slow query on one tab does not hold up the others. A reader whose connection fails keeps retrying with a growing delay rather than hammering the server.
The charts
| Chart | What it shows |
|---|---|
| CPU % and RAM MB | CPU % is the SQL Server share of CPU from the scheduler monitor ring buffer (100 minus system idle), on the left axis. RAM MB is the memory the SQL Server process is using (physical_memory_in_use_kb), on the right axis. The last 100 points are kept. |
| Connections | The number of user sessions (session ids above 50 in a user database context), smoothed over the last two readings. |
The labels beside the charts give the latest values: Average CPU over last 60 seconds, SQL Server Memory Usage and the connection count. Right after a restart the ring buffer can be empty; that reading is skipped rather than plotted as zero.
The tabs
Missing Indexes
The missing index DMVs only ever grow since the service started, which makes them hard to read during a test. This tab therefore takes a baseline at the first poll and shows what the workload asked for since monitoring started. Each suggestion is vetted before it is shown, with a verdict and a reason in the details pane.
| Column | Meaning |
|---|---|
| Verdict | Recommended, Consider, Duplicate or Insufficient evidence (see below). |
| Table | Database, schema and table. |
| Score | The DMV score: average cost times average impact times seeks and scans. |
| Session | How much that score grew since you started monitoring. |
| Seeks | User seeks that wanted the index. |
| Impact % | The average estimated improvement. |
| Table MB | Reserved size of the table. |
| Existing Indexes | Nonclustered indexes the table already has. |
| Writes Seen | Updates the table has taken. |
How the verdict is reached, in order:
- Duplicate: an existing index already leads with the same key columns and carries the includes.
- Insufficient evidence: table statistics could not be read, SQL Server has been up less than 24 hours, fewer than 25 seeks and scans wanted it, or the table has fewer than 1,000 rows.
- Consider: writes are at least 10 times the reads (the maintenance may outweigh the gain), the table already has 8 or more nonclustered indexes, or the score is under 5,000 or the impact under 40 percent.
- Recommended: score of at least 5,000 and impact of at least 40 percent, with none of the above.
Suggestions on the same table where one key list is a prefix of another are merged into the wider one. When a High CPU plan names the same missing index, the query that asked for it is shown.
Right-click a row for Copy Index Script, Export to File or Script all recommended (the Recommended rows only, as one script). Scripts are text for you to review; the monitor never runs them. Options has Set Missing Index Count (default 20, 1 to 500) and Show all missing indexes, not just this session, which lists everything the DMVs hold. The tempdb database is left out.
Blocking Queries
Every 10 seconds the monitor reads the sessions and builds the blocking chains, naming the lead blocker of each chain, the databases and logins involved, the query text and, when the server has it, the plan. A chain of one session is ignored. The tab counts how many polls each blocking query was seen in and lists the worst first (the top 30), so a query that blocked for ten minutes ranks above one that blocked once. The tab title shows the countdown to the next poll.
Deadlocks are listed on the same tab, above the blocking entries. The monitor reads the system_health Extended Events file for new xml_deadlock_report events, starting from the end of the file when you start, so only deadlocks raised while you watch are shown. Each shows the victim and the winners with login, database and query; select it to see the full detail.
Select an entry for the blocking tree and queries in the details pane. Right-click for Copy or View Execution Plan.
The blocking read needs SQL Server 2008 or later; an older server gets a one line message saying so.
Long Running Queries
Every 10 seconds the monitor lists requests that have been running for at least the threshold (30 seconds by default), longest first, up to 30 per read, skipping system sessions and itself. Each request is listed once, the first time it is seen over the threshold. The line shows the elapsed seconds, session, database and start time with the start of the query; the details pane has the login, host, status, wait type and the full text. Change the threshold in Options → Set Long-Running Query Threshold (1 to 3,600 seconds). Right-click for Copy or View Execution Plan.
Wait Stats
Every 30 seconds the monitor reads sys.dm_os_wait_stats and shows the change since monitoring started, not the total since the server started. Idle and housekeeping waits (sleep, broker, checkpoint, XE dispatcher and similar) are left out. The top 20 are listed by wait time.
| Column | Meaning |
|---|---|
| Wait Type | The wait. |
| Wait ms | Wait time during the session. |
| Signal ms | The part of it spent waiting for a CPU after the resource was free. |
| % Total | Share of all the wait time in the session. |
| Count | Waiting tasks during the session. |
The first read is only the baseline, so the list fills after the second read. If a counter goes backwards (the stats were cleared or the instance restarted) the baseline is taken again.
High CPU
This tab is different: it records as well as reads. Every 10 seconds the monitor calls the TrackActiveQueryCpuSnapshot procedure in the DBHealthHistory database, which saves active queries above the CPU or elapsed thresholds, then lists the top 50 queries recorded since you started. DBHealthHistory therefore has to exist on that instance (see Historic Overview). If it does not, the tab stays empty and the problem is written to the debug log.
| Column | Meaning |
|---|---|
| Database | The database the query ran in. |
| Samples | How many snapshots caught the query. |
| Max CPU ms / Avg CPU ms | Highest and average CPU time of the query when caught. |
| Total Logical Reads | Logical reads summed over the samples. |
| First Seen / Last Seen | When it was first and last caught, on the server’s clock. |
Select a row for the query text; right-click for View Execution Plan when a plan was captured. Options → CPU Tracking Settings sets the thresholds: Min CPU to record (ms) (default 50), Min elapsed to record (ms) (default 100) and Capture query plan above CPU (ms) (default 500). These thresholds apply to the current run of the window and are not saved.
The Reports menu
| Item | What it makes |
|---|---|
| Blocking Query Report | The blocking and deadlock entries as HTML. |
| Missing Indexes Report | The vetted suggestions as HTML. |
| Long Running Queries Report | The long running queries seen (with the threshold used). |
| Wait Stats Report | The wait table. |
| High CPU Report | The High CPU table. |
| Full Report | All five sections in one page. |
| Timed Full Report | The same full report, created later. |
The report shows in the Reports tab. Timed Full Report asks for Minutes (0 means right now) and whether to Email it, Save to File as HTML, or both. Emailing asks for the recipients, the sender, the SMTP server and port, an optional user name and password and whether to use SSL. Closing the window before the time is up cancels the report.
Permissions and versions
The monitor connects with the sign in you choose in the connect dialog (Windows or SQL authentication) and reads from master. Without VIEW SERVER STATE the DMV based tabs (sessions, waits, missing indexes, CPU) come back empty or show their connection problem in the debug log. Reading deadlocks needs permission on the system_health event file. The High CPU tab also needs access to DBHealthHistory and permission to run TrackActiveQueryCpuSnapshot there.
The monitor reads only and does not change anything on the server apart from the High CPU snapshot rows written into DBHealthHistory. Options → Debug Mode shows a debug text pane with what each reader is doing.
Related reports
| Report | Why you would go there |
|---|---|
| Blocking Tree | The same chains, from the main window’s real time view. |
| Missing Indexes | The full database level missing index report, with the Index Consolidation Plan. |
| Waits | Waits since the server started, and history when Historic Monitoring is on. |
| Active Queries | What is running right now. |
| Deadlock History | Deadlocks that were captured before you started watching. |