First Queries to Run on an Unfamiliar SQL Server

“Nobody knows what runs on this server” is a poor starting point for a change. An unfamiliar SQL Server needs a short, read-only inventory first. These queries establish what you inherited before you touch its settings.

Confirm Which Unfamiliar SQL Server You Reached

I start by checking the connection, even when the server name looks familiar. An alias, a listener, or a saved SSMS connection can take you somewhere different from your expectation. Record the instance name, version, edition, and update level together. A version number without an edition leaves important limits unexplained.

Run these examples on SQL Server for Windows. Several later queries need server-level visibility and access to msdb. On SQL Server 2022 and later, relevant performance DMVs require VIEW SERVER PERFORMANCE STATE. Older releases use VIEW SERVER STATE. Catalog visibility and backup-history access have their own permissions. Ask for approved read access rather than changing security to make a report work.

What database and instance does your query window show? Check that before saving any result. These are inspection queries. They do not restart the engine, change configuration, or reset counters. Day one already contains enough surprises without contributing a new one.

SELECT SERVERPROPERTY('ServerName') AS ServerName,
    SERVERPROPERTY('InstanceName') AS InstanceName,
    SERVERPROPERTY('ProductVersion') AS ProductVersion,
    SERVERPROPERTY('ProductLevel') AS ProductLevel,
    SERVERPROPERTY('ProductUpdateLevel') AS ProductUpdateLevel,
    SERVERPROPERTY('Edition') AS Edition,
    SERVERPROPERTY('EngineEdition') AS EngineEdition;

Put a Restart Time Beside the Evidence

Uptime gives the rest of your inspection a time frame. Wait totals collected since yesterday cannot be compared directly with totals collected over a long operating period. The start time also tells you whether a recent restart erased useful transient evidence.

The next query reports the engine start time and elapsed minutes. It does not report Windows uptime. Use the difference as context, not as proof that the instance was healthy throughout that period. A server can stay running while applications struggle.

I keep restart time beside the wait output whenever I inherit an undocumented instance. Otherwise, somebody eventually compares unrelated totals and calls the larger number a regression. Save the collection time as well. A read-only baseline is useful because another DBA can understand when it was captured. Do not reset wait statistics to make the first report easier to read. That removes evidence somebody else still needs.

SELECT GETDATE() AS CollectedAt,
    sqlserver_start_time AS EngineStartedAt,
    DATEDIFF_BIG(minute, sqlserver_start_time, GETDATE()) AS UptimeMinutes
FROM sys.dm_os_sys_info;

List Databases on an Unfamiliar SQL Server

Next, establish which databases exist, which are online, and which recovery models they use. The query separates allocated data space from allocated log space. It converts the file page counts into megabytes. These are allocated sizes, not used space and not free space on a Windows volume.

A large log file is a reason to investigate its workload and backup process. It is not a request to shrink it. FULL recovery also does not prove that log backups exist. We check that separately.

Keep offline databases in the inventory. Filtering them away hides an important operational condition. On an unfamiliar SQL Server, a database name gives you a starting clue, not an ownership record. Find the responsible application owner before renaming, moving, or removing anything. Check compatibility levels too, but leave them unchanged until application behavior and testing requirements are understood.

SELECT d.name, d.state_desc, d.recovery_model_desc,
    d.compatibility_level,
    SUM(CASE WHEN f.type = 0 THEN CONVERT(bigint, f.size)
             ELSE CONVERT(bigint, 0) END) / 128.0 AS DataAllocatedMB,
    SUM(CASE WHEN f.type = 1 THEN CONVERT(bigint, f.size)
             ELSE CONVERT(bigint, 0) END) / 128.0 AS LogAllocatedMB
FROM sys.databases AS d
LEFT JOIN sys.master_files AS f ON f.database_id = d.database_id
GROUP BY d.name, d.state_desc, d.recovery_model_desc,
    d.compatibility_level
ORDER BY d.name;

Read Waits as Clues With a Time Frame

Waits describe where worker tasks spent time waiting. The following list removes a small set of routine idle waits. It is a starting filter, not a complete definition of harmless activity. On a quiet server, the top rows can still be background waits that this short list does not remove. Review the wait names rather than treating the first row as a diagnosis.

The totals accumulate since the last engine restart or an explicit reset. They are summed across workers, so they can exceed elapsed wall time. Signal wait time is the portion spent waiting to run after the resource wait ends. That distinction helps separate resource delays from scheduling pressure.

Historical totals also include work that has finished. A high storage-related wait does not prove storage is slow right now. Compare another snapshot during the reported problem. Leave the counters intact and calculate differences between captures. For blocking complaints, inspect current requests and waiting tasks next. A lifetime ranking cannot identify the current blocking session by itself.

SELECT TOP (10) wait_type, waiting_tasks_count,
    wait_time_ms, signal_wait_time_ms,
    wait_time_ms - signal_wait_time_ms AS ResourceWaitMs
FROM sys.dm_os_wait_stats
WHERE wait_time_ms > 0
  AND wait_type NOT IN
    ('SLEEP_TASK', 'SLEEP_SYSTEMTASK', 'LAZYWRITER_SLEEP',
     'SQLTRACE_BUFFER_FLUSH', 'WAITFOR', 'BROKER_RECEIVE_WAITFOR',
     'XE_TIMER_EVENT', 'XE_DISPATCHER_WAIT', 'REQUEST_FOR_DEADLOCK_SEARCH')
ORDER BY wait_time_ms DESC, wait_type;

Find Recorded Backups Without Promising Recovery

Backup history deserves attention before performance tuning. The next query finds the latest recorded completed full, differential, and log backup for each database except tempdb. A missing value is a prompt to investigate. History cleanup, backups on another replica, or a different backup process also affect what msdb contains.

The full-backup column includes copy-only backups. Check their role before choosing a restore sequence, because a copy-only full does not become a differential base. SIMPLE recovery databases do not need log backups. FULL recovery databases require a working log-backup process for the intended recovery plan.

A backup_finish_date is evidence of a recorded backup completion. It does not prove that its file exists, that its encryption certificate is available, or that you can restore the chain. The last good backup means one you can actually recover from. Follow this inventory with file access checks and a restore test on a separate approved destination.

SELECT d.name, d.recovery_model_desc,
    MAX(CASE WHEN b.type = 'D' THEN b.backup_finish_date END)
        AS LastRecordedFullBackup,
    MAX(CASE WHEN b.type = 'I' THEN b.backup_finish_date END)
        AS LastRecordedDifferentialBackup,
    MAX(CASE WHEN b.type = 'L' THEN b.backup_finish_date END)
        AS LastRecordedLogBackup
FROM sys.databases AS d
LEFT JOIN msdb.dbo.backupset AS b
    ON b.database_name = d.name
    AND b.backup_finish_date IS NOT NULL
    AND b.is_damaged = 0
    AND b.backup_finish_date >= d.create_date
WHERE d.name <> N'tempdb'
GROUP BY d.name, d.recovery_model_desc
ORDER BY d.name;

Separate Configured Values From Active Values

Read memory and CPU-related settings after establishing the workload and protection picture. value is the configured value. value_in_use is what the engine currently uses. A difference needs explanation, especially for settings that require a restart.

This query reads selected rows directly from sys.configurations. You do not need to enable advanced options just to inspect them. Record maximum server memory, minimum server memory, MAXDOP, and the parallelism cost threshold together. Their meaning depends on host resources and concurrent work.

Do not copy values from another machine. Maximum server memory is not a cap on every byte the process allocates. MAXDOP also is not a promise about the total workers used by an entire request. Learn the intended configuration before changing either setting. A value that looks unusual can be deliberate, and an apparently familiar default can still be wrong for this host.

SELECT name, value AS ConfiguredValue,
    value_in_use AS ActiveValue, is_dynamic, is_advanced
FROM sys.configurations
WHERE name IN
    ('min server memory (MB)', 'max server memory (MB)',
     'max degree of parallelism', 'cost threshold for parallelism',
     'affinity mask', 'affinity64 mask')
ORDER BY name;

Plan the Next Step on an Unfamiliar SQL Server

Save the outputs with the collection time and connection identity. Then ask who owns the databases, what downtime is acceptable, and what recovery targets matter. Those answers turn an inventory into a useful plan. They also explain which apparent problems deserve attention first.

Leave service accounts, recovery models, file sizes, compatibility levels, and instance settings alone during this first pass. Do not rebuild every index or clear the plan cache because the server feels unfamiliar. Neither action creates documentation, and both change the evidence you just collected.

On an unfamiliar SQL Server, the first useful outcome is a list of known facts and specific unanswered questions. Pick the next read-only check from that list. If backup protection is unclear, resolve that before spending time on a parallelism preference. If an application is failing now, preserve the baseline and investigate its current request path. Curiosity works better than a bag of default changes.

Related reading on this blog: Stored Procedure are Compiled on First Run: SP taking Longer to Run First Time and Using PowerShell and Native Client to run queries in SQL Server.

A first inspection is not a tuning spree, it is the evidence that makes the next decision safe.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.