Server Overview
Overview
The Server Overview is what a SQL Server instance opens on. It is a stack of live panels, each answering one question, each refreshing on its own, and each a door into the full report that goes deeper.
Reading down the page, it answers four things in order:
- What is this instance? Version, edition, cores, memory, uptime, how much data it holds, and how many people are on it.
- Where do I go next? The report navigator, second from the top.
- What has it been waiting on? Waits by day, from historic monitoring.
- Are its CPUs keeping up? One tile per scheduler, and how long anything has been queued behind them.
The navigator sits directly under the instance band because it is how you leave this page, and a directory nobody scrolls to is a directory nobody uses. The two live readings follow it, longest view first: weeks of waits, then the last ten minutes of CPU.
Below them sit the readings you go looking for rather than scan: memory and cache, what is blocked, what is running, how the storage is answering, CPU by database, backup exposure, email alerts, availability groups, and the long tail of I/O, connections, plan cache, logins and plan reuse.
Every one of those positions is a default. Right-click any panel to move or hide it, and the arrangement is yours from then on – see Arranging the page.
The page always opens at the top, whatever the panels were doing while they were being built.
Where to find it
| Route | How |
|---|---|
| Server tree | Click the instance name |
| Back button | From any instance level report |
The page title reads Server Overview: <server name>.
Reading the page
The instance band
The band across the top is what this server is rather than what it is doing. Name, release, edition, build, operating system, whether it is virtual, and when it was installed.
Beside it are five numbers, and every one of them is a link:
| Number | What it says | Opens |
|---|---|---|
| Cores | Logical CPUs, and whether they are hyperthreaded | SQL CPU Schedulers |
| Memory | Memory on the machine, and how much SQL Server holds | Memory |
| Uptime | How long since the instance started | Error Log |
| Databases | How many, and the total size of every file | Databases by Size |
| Sessions | User sessions now, and how many are blocked | Sessions |
A restart inside the last hour turns the uptime figure amber: every cache is cold and every plan is new, which explains a great deal of odd behaviour on its own.
An out of support release raises a chip and a stripe. The dates are Microsoft’s extended support dates, so the warning appears a year before the release stops getting security updates rather than after.
The report navigator
Every instance level report this instance can actually open, grouped by the question it answers. The panel is titled Instance Reports and its subtitle counts them:
54 reports for this instance · type to search
Three ways in, because people arrive knowing different things.
Most used, along the top. Chips for the reports you actually open, most first, up to eight of them. It comes from your own history and reorders itself as you work. A fresh installation has no row here at all rather than a row of guesses.
The groups, which are the cards:
| Group | What is in it |
|---|---|
| Performance | SQL CPU Schedulers, CPU by Database, Waits, Memory, Sessions, Connections, What is Active |
| Blocking | Blocking Queries, Blocking Tree, Open Transactions, Deadlock History, Deadlock Objects, Deadlocks by Database, Optimized Locking |
| Storage | I/O by Drive, I/O by Database, Disk Space, Disk Space Forecast, File Utilization, Databases by Size, TempDB Metadata Contention |
| Recovery | Backup Status, DBCC CheckDB, Suspect Pages, Log Shipping, Availability Groups, Replication |
| Security | Security Posture, Orphan Users, TDE Status, Linked Servers, Quick Scan Report |
| Indexing | Missing Indexes, Problem Indexes, Index LOB Columns, Large Tables |
| Jobs and errors | Failed Jobs, Job History, Job Schedules, Error Log, Email Alert Log |
| Over time | CPU Load by Hour, Blocking by Hour, Disk Latency by Hour, Deadlocks By Hour, Long Running by Hour, Long Running History, Performance History |
| Configuration | Configuration Values, Trace Flags, Deprecated Features, Inventory, Schema Search, Structure Change Log, Migration Planner |
| More | Anything the product offers that has not been put in a group above |
Nothing is ever offered that cannot be opened. Only reports this instance actually built a menu entry for are drawn, so a report gated by edition or version cannot appear here as a dead link. The reverse holds too: a report the product offers but this page has not been told about lands in More rather than disappearing.
Search, for the person who already knows the name. Typing filters the groups live.
| Key | What it does |
|---|---|
| Enter | Opens the first match, preferring a report whose name starts with what you typed, so typing log offers Log Shipping before Error Log |
| Escape | Clears the box |
When nothing matches, the panel says so rather than going blank.
Why a card says “+3”
The navigator has no scrollbar of its own. The overview is one tall scrolling page, and a second scrollbar inside a panel on it is a trap: the wheel does something different depending on which few hundred pixels the pointer is over, and the panel swallows the scroll you meant for the page.
So the grid fits itself to the height it has been given. A card lists as many reports as its share of that height allows, up to seven, and puts +3 on its title line for however many it had no room for. Nothing is lost: the search box finds any report by name whether its card had room for it or not.
Two consequences worth knowing:
- Give the panel more height and the plus signs go away. The shipped height fits every card’s full list at a normal window width.
- On a very short panel whole groups are left out rather than every card shrinking to its title. The groups are ordered roughly the way somebody works through an instance, so what goes is the reference material at the end. Search still reaches all of it.
Right-click the navigator and choose Copy Chart to put the whole catalogue on the clipboard. The copy is not limited by the window it was taken from, so it lists every report in every group whatever the panel had room for on screen.
Historic waits
Waits by day for this instance, from the historic monitoring database rather than from the live DMVs. It is the one panel here that describes weeks rather than seconds, which is what puts it above the live CPU strip: the shape of the last month is the context for the shape of the last ten minutes.
Open Historic Waits in the panel header opens the full report. The link only appears once there is a chart to open: on an instance with no historic monitoring the panel carries a notice instead, and those are listed under Messages you may see.
The CPU schedulers strip
One tile per scheduler, grouped by NUMA node, with the instance’s queue history beside it. This is the same picture the SQL CPU Schedulers report draws, compressed into a strip, and clicking it opens that report.
| Fill | Meaning |
|---|---|
| Slate | Nothing waiting for the CPU |
| Amber | One task waiting |
| Deep amber | Two waiting |
| Red | Three or more waiting |
| Dashed outline | Offline – this instance is not using that CPU |
Colour is the queue and only the queue, so a calm wall means a calm server. How hard a scheduler is working is the bar across the bottom of its tile, scaled against the busiest scheduler on the instance. The line inside the tile is that scheduler’s own recent queue history.
The rail on the right carries the two numbers no single tile can:
- Worker threads used against
max_workers_count. This is the early warning forTHREADPOOLstarvation, which is the failure that takes an instance down and locks you out of it while it does. Past three quarters it turns red. - Signal wait ratio, the textbook CPU pressure reading. At or above 20% it turns amber.
Under them, ten minutes of tasks waiting for CPU across the whole instance. A plateau is the finding; a single spike almost never is.
Double-click any tile to open SQL CPU Schedulers with that scheduler already selected.
Memory and cache
| Number | What it says |
|---|---|
| Page life expectancy | How long a page survives in the buffer pool |
| Buffer pool | What SQL Server is holding, and whether it is still growing |
| Grants pending | Queries that have asked for memory and not been given it |
| Memory target | How much it would like to hold |
The page life expectancy threshold is scaled to the buffer pool actually in use, not the 300 seconds everybody remembers. That number came from a machine with 4 GB of buffer pool; on a server holding 200 GB, five minutes of page life means the entire buffer pool is being replaced every five minutes. The panel says what it expects for this instance underneath the figure.
Grants pending above zero is the clearest single sign there is that an instance is short of memory, and it turns the panel red on its own.
Storage latency by drive
Average stall per read and per write, one cell per drive, taken from sys.dm_io_virtual_file_stats and grouped by where the files actually live.
| Latency | Reading |
|---|---|
| Under 10 ms | Quiet |
| 10 to 20 ms | Amber – worth a look |
| Over 20 ms | Red |
The page already shows I/O volume by database elsewhere, which says who is doing the reading. This says whether the storage is keeping up, and the two are different questions: the database doing the most I/O is very often the one on the fastest disk.
These figures are cumulative since the instance last started. A bad hour last week is still in them, and a drive that went bad ten minutes ago is barely in them yet. For the recent picture, open I/O by Drive or Disk Latency by Hour by Day.
Backup exposure
Everything else on this page describes the last few seconds. This describes the last few hours and days.
| Number | What it says |
|---|---|
| Worst log gap | The longest any database has gone without a log backup |
| No recent full | Databases with no full backup in the last seven days |
| Oldest DBCC CheckDB | The longest any database has gone unverified |
Databases in SIMPLE recovery have no log chain and are not counted in the log gap. System databases are left out of it too – model ships in FULL recovery and nothing ever backs its log up, so counting it would put the same finding at the top of this panel on every SQL Server ever installed.
A user database in a logged recovery model that has never had a log backup reads as never rather than as a number, and takes the cell whatever else is on the instance: its log will grow until the disk fills.
The DBCC age is read from DATABASEPROPERTYEX, which needs SQL Server 2016 SP2 or newer. On older builds the cell reads unknown and the Last DBCC CheckDB Known Good report does the more expensive version.
Arranging the page
Every panel on the page is in one list, and the list is yours.
Right-click any panel for:
| Item | What it does |
|---|---|
| Move Panel Up / Move Panel Down | Reorders the page |
| Hide Panel | Leaves a single line placeholder in its place |
| Show Panel | Brings a hidden panel back |
| Reset Panel Positions | Returns to the shipped order |
A hidden panel keeps a one line row rather than vanishing, so it can be brought back from the same menu without resetting the whole arrangement.
The arrangement is stored per installation, not per instance, and is kept across upgrades. The one exception is a release that changes the page enough that an old arrangement is no longer an arrangement of this page: the order is then reset once, and everything after that is kept.
That has happened three times.
| Version | What changed |
|---|---|
| 2 | The rebuild. The masthead, the CPU strip, memory, storage latency, backup exposure and the navigator arrived; the server configuration text and the report button grid went. Fifteen panels became twenty. |
| 3 | The report navigator moved up four places, above memory and cache. |
| 4 | The navigator and the CPU schedulers strip traded places, putting the navigator directly under the instance band. |
Versions 3 and 4 are smaller than the rebuild and do not meet that bar on their own: no panel was added or removed, so a saved arrangement is still an arrangement of this page. They reset anyway, because the move is the change, and a default order only fresh installations ever saw would leave every existing reader with the launcher where it used to be. The cost each time is one reset of any arrangement made since the previous version.
Refresh
The live panels share one background reading, on the interval set by ServerRefreshInterval in settings, which defaults to five seconds.
Three things deliberately do not run on that interval:
- Backup exposure is read once when the page opens. It is the only reading here that touches a real table rather than a memory structure, and it answers the slowest moving question on the page.
- The instance band is read once. Cores, memory and install date are configuration.
- The report navigator is built once. It is a directory rather than a reading; the most used row reorders itself the next time the page is opened.
Messages you may see
Permission missing, when the login cannot read the instance:
VIEW SERVER STATE permission is missing from the configured user
Every panel on this page reads a dynamic management view, so the page says this once and stops rather than showing twenty panels failing one at a time.
Historic reporting not configured, on the historic waits panel:
Historic reporting is not configured for this installation. Click here to configure it, or for more detail.
Not monitored, when historic reporting is configured but this instance is not in it:
This instance is not being monitored. Click here to configure monitoring.
Collecting, when monitoring has started but has not gathered enough yet:
Not enough wait data has been collected yet. Try again later.
A Test Connection button appears with that last one, and the panel names the monitoring database it is reading from.
Related reports
| Report | Why you would go there |
|---|---|
| SQL CPU Schedulers | The core wall at full size, with the per scheduler grid |
| Memory | Where the memory has gone, database by database |
| Backup Status | Every database’s backup history rather than the worst case |
| I/O by Drive | Latency and throughput per volume over time |
| Quick Scan Report | A scored health check of the whole instance |
| Waits | What the instance spends its time waiting on, cumulatively |
Frequently asked questions
Why is a panel missing? It is hidden, or the instance has nothing to put in it. A hidden panel leaves a single line row saying so – right-click it and choose Show Panel. The availability groups panel is not built at all on an instance that is not using them.
Why did my panel order change after upgrading? Only when the page itself changed enough that the old arrangement no longer described it. The most recent one moved the report navigator above memory and cache. That reset happens once per such release; any arrangement you make afterwards is kept.
A group card says “+2”. Where are those reports? The card ran out of height before it ran out of reports. Type the name into the search box and it will be found, or give the panel more height and the card will list them. See Why a card says “+3”.
Why will the report navigator not scroll? Because the page scrolls instead. A scrollbar inside a panel on a scrolling page makes the wheel do different things a few hundred pixels apart, and swallows scrolls meant for the page. The navigator fits its grid to the height it has and tells you what did not fit.
A report I know exists is not in any group. Then it is under More, which collects anything the product offers that this page has not been told about. It opens the same way as everything else.
The CPU schedulers strip has a lot of empty space. The strip is sized for a sixteen core box across two NUMA nodes. A smaller instance centres its tiles rather than filling the space with larger ones, because a tile the size of a postcard would not mean anything more.
Why does page life expectancy say to expect more than 300? Because the threshold is scaled to how much buffer pool this instance is holding. See Memory and cache above.
Why is the drive latency so high when the server feels fine? It is an average since startup. A restore, a long index rebuild or a bad afternoon weeks ago is still in it. Open I/O by Drive for the recent picture.
Does this page put load on the server? The live panels read memory resident DMVs with no locks and no I/O. The one query against a real table, the backup history, runs once when the page opens. The refresh interval is configurable, and any panel can be switched off.