The First Hour on a SQL Server You Have Never Seen

Inheriting an instance is a normal part of the job. Somebody left, a company was acquired, a contract started, or a server nobody has looked at since it was built has suddenly become your problem. The temptation is to open the thing that was reported and start fixing. The better move is to find out what you are holding first, because almost every recommendation you might make depends on facts you do not have yet.

1. What Is It

Version, edition, patch level and uptime, in that order. Edition first because it decides what is available: several features people assume are present are Enterprise only, and several that used to be moved to Standard in 2016 SP1, so guessing from memory is unreliable in both directions.

SELECT SERVERPROPERTY('ProductVersion')   AS version,
       SERVERPROPERTY('ProductLevel')     AS level,
       SERVERPROPERTY('Edition')          AS edition,
       SERVERPROPERTY('IsClustered')      AS is_clustered,
       SERVERPROPERTY('IsHadrEnabled')    AS is_hadr;

Uptime belongs in this step rather than later, because it decides whether the performance counters you are about to read mean anything. Counters accumulate from startup, so an instance up for months is showing you history rather than today.

2. What Is It Supposed To Do

Databases, sizes, recovery models and owners. The recovery model is the one that catches people out: a database in FULL recovery with no log backup job will grow its log forever, and that combination is common on servers nobody owns. A database in SIMPLE that somebody assumes is point-in-time recoverable is the same problem wearing the opposite hat.

SELECT name,
       state_desc,
       recovery_model_desc,
       SUSER_SNAME(owner_sid) AS owner
FROM   sys.databases
ORDER  BY name;

3. What Is It Short Of

Now the resource question, and only now, because you know how long the counters have been running. Memory configuration, MAXDOP, and whatever the server spends its time waiting on. Configuration that differs from the default is the interesting part: somebody changed it deliberately, usually for a reason nobody wrote down.

SELECT name, value_in_use, is_dynamic, is_advanced
FROM   sys.configurations
WHERE  value_in_use <> value
   OR  name IN ('max server memory (MB)', 'max degree of parallelism',
                'cost threshold for parallelism')
ORDER  BY name;

4. What Is Unprotected

The question that matters most and gets asked last, because it is the one where a wrong answer is unrecoverable rather than merely slow. Three things: when each database was last backed up, when it was last checked for corruption, and who has sysadmin.

A server can look healthy on every performance metric and still be one disk failure away from data loss. Backup age and CHECKDB age are the two numbers that tell you whether the last month of work exists anywhere else.

What Not To Do In The First Hour

• Do not change anything. You do not yet know why it is set that way, and an unfamiliar server is the worst place to discover that a setting was load-bearing.
• Do not run a repair. If you find corruption, the first move is to protect the backups you already have, not to reach for REPAIR_ALLOW_DATA_LOSS.
• Do not restart it to “clear things down”. You lose every counter that would have told you what was happening, and you will need those.
• Do not kill sessions because a list looks long. Long-running is not the same as stuck, and the head of a blocking chain is not always the session doing the most work.

The One-Command Version

Everything above is a list of questions rather than a list of scripts, on purpose: the order is the useful part and it is worth knowing whether or not you use any particular tool. If you want the same ground covered in one pass, Run a Full SQL Server Health Check collects it and hands back a findings list rather than raw output, which is a shorter route to the same four answers.

Common Questions