Mini DBA Blog | August 2026

SQL Server monitoring scripts are indispensable. A focused DMV query can answer a precise question quickly, is easy to review and can be adapted to an unusual environment. The limitation appears when a production problem happened before anyone knew which script to run, or when a team needs the same evidence collected consistently across many servers.

The useful comparison is not “scripts or monitoring software?” Most experienced DBAs use both. The decision is which work should remain an on-demand investigation and which evidence should be collected continuously before an incident occurs.

Where SQL Server monitoring scripts are strongest

A script is often the best tool when the question is already clear. It can inspect a specific DMV, validate a configuration value, test a hypothesis or gather evidence that is unique to an application. The DBA can read the statement, understand its permissions and decide exactly when it runs.

Scripts are particularly useful for:

• one-off validation after a deployment or configuration change;
• specialist checks that are not needed on every server;
• controlled data collection during a live incident;
• prototyping a check before it becomes part of an operational standard;
• confirming a monitoring result directly against SQL Server.

These strengths do not disappear when continuous monitoring is introduced. Scripts remain an important way to verify and deepen an investigation.

The collection problem starts before the diagnosis

Many SQL Server incidents are reported after the blocking chain, query spike, memory-grant queue or I/O burst has ended. An on-demand script can show the current state, but it cannot reconstruct evidence that was never retained.

A script-based monitoring system therefore needs more than diagnostic SQL. It also needs scheduling, storage, retention, schema upgrades, timestamps, server identity, error handling and a way to align results from different collectors. Without those pieces, the team has a folder of useful queries rather than an operational history.

Mini DBA SQL Server monitoring continuously collects related server, database, query, wait, session, file and alert evidence so an investigation can return to the affected time instead of starting from an empty current snapshot.

Alerting requires state, not just a threshold query

A query can determine whether a threshold is exceeded. Production alerting must also decide whether the condition lasted long enough to matter, whether an alert is already open, when another notification is justified and when the condition has recovered.

A maintainable alert workflow commonly needs:

• collection frequency and duration rules;
• severity and server-specific thresholds;
• suppression for maintenance or expected workload;
• open, repeated and recovered state;
• delivery retries and notification routing;
• the metric and workload context that explains why the alert fired.

That engineering can be built around scripts, but it is a monitoring product in its own right. Mini DBA licensed Engines provide the state and retained alert evidence, while the free Community Edition deliberately does not evaluate or retain alerts.

Context is what turns a symptom into a cause

A high CPU result is a starting point. The investigation still needs to know which queries ran, whether executions or duration changed, what SQL Server waited for, whether plans changed and which database or application produced the workload.

Separate scripts often return separate result sets with different sampling times. Continuous monitoring can align those signals around the same incident window. That makes it easier to distinguish a CPU-bound query from lock contention, memory pressure, storage latency or a maintenance task that happened at the same time.

The SQL Server Activity view, query history and wait statistics provide separate drilldowns while preserving the wider server context.

Consider the operational cost of a script estate

A script that works on one server is not automatically a monitoring standard. SQL Server versions, Azure SQL limitations, permissions, Always On topology, naming conventions and application databases can change the result or make a collection fail.

For every scheduled script, decide who owns:

• compatibility testing after database upgrades;
• least-privilege permissions and credential rotation;
• collector failures and gaps in history;
• storage growth, cleanup and backup;
• dashboard or report presentation;
• alert routing and escalation;
• documentation when the original author is unavailable.

This does not make scripts a poor choice. It makes their lifecycle cost part of the comparison. The Mini DBA comparison page separates the strengths of on-demand scripts from the operational work handled by continuous monitoring.

Shared browser access changes the workflow

Script output often remains in a query window, text file or DBA-specific repository. A browser Console gives DBAs, developers and operators a common view of the same retained evidence without distributing a script library and direct database credentials to every participant.

Access still needs governance. The Console should use HTTPS and appropriate authentication, the Engine should use dedicated monitoring identities, and optional AI, MCP and notification destinations should be reviewed before monitoring evidence leaves the environment. The Mini DBA security and data handling page describes those boundaries.

A practical hybrid approach

A sensible operating model uses continuous monitoring for repeatable collection and scripts for specialist validation:

1. Collect core server, database, query, wait, storage and alert evidence continuously.
2. Start the incident review from the retained timeline and identify the first signal that changed.
3. Use focused scripts to validate the leading hypothesis or collect application-specific detail.
4. Turn a repeated specialist check into a documented custom check or product requirement.
5. Compare the same signals after remediation to prove whether the change worked.

This preserves the transparency and flexibility of SQL while avoiding the expectation that an engineer must anticipate every incident and run the right script before the evidence disappears.

Choose based on the problem you need to solve

Use scripts when the question is narrow, the operator is present and the result does not need a durable operational workflow. Add continuous monitoring when incidents are intermittent, alert state matters, several people need access, or you need consistent evidence across an estate.

You can try Mini DBA in the populated online demo without installing anything, or download the 14-day trial to compare continuous monitoring with your existing SQL Server scripts.

An Azure SQL resource percentage tells you that a limit is being approached; it does not tell you why. High DTU, CPU, data I/O or log I/O can be caused by a single inefficient statement, a burst of concurrency, blocking, plan regression, maintenance work or a service tier that no longer fits the workload.

The goal of an effective investigation is to connect the Azure resource signal to the database activity that produced it. That gives you a choice between tuning the workload, changing its timing, correcting contention or scaling capacity—with evidence for the decision.

Understand what the service model is showing

In the DTU purchasing model, compute, data I/O and log I/O contribute to a bundled capacity measure. A high DTU percentage can therefore represent different underlying constraints at different times. In the vCore model, CPU and storage-related signals are more explicit, but a high percentage is still a symptom rather than a diagnosis.

Start with the exact database, time window and resource that reached pressure. Record whether the event was brief, sustained or repeated. Then compare it with the same database's normal workload rather than using an arbitrary threshold in isolation.

The Mini DBA Azure database monitoring page shows how Azure SQL resource metrics, waits, queries, alerts and history can be investigated in one browser workflow.

Separate CPU, data I/O and log I/O pressure

Each resource points to a different first set of questions:

• CPU pressure: Which queries consumed CPU? Did execution counts, parallelism or compilation rise? Did a plan change increase the work per execution?
• Data I/O pressure: Which statements performed large reads? Was a scan expected? Did cache churn, missing indexes or a reporting workload increase physical access?
• Log I/O pressure: Did transaction volume, batch size, index maintenance or a long transaction increase log generation? Were applications committing unusually frequently?

When several percentages rise together, do not assume several independent problems. One badly estimated query can consume CPU, read far more pages than expected and generate tempdb or log activity at the same time.

Find the queries active during the spike

Rank queries by the resource that was constrained, then inspect execution count and duration. A query that is moderately expensive but runs thousands of times can matter more than the single longest execution. Conversely, a reporting statement or maintenance operation may dominate a short incident even with a low execution count.

Compare SQL text and execution plans with an earlier healthy period. Look for changed join strategies, scans, spills, missing-index evidence, inaccurate estimates and parameter-sensitive behaviour. The Azure SQL queries view provides the workload detail needed to connect the platform metric to a statement.

Check waits and blocking before scaling

A database can feel slow without exhausting its purchased compute. Lock waits, blocked sessions and transaction contention can delay work while headline resource percentages remain moderate. Equally, blocking can create a queue that produces a resource surge when it clears.

Use Azure SQL wait analysis to identify whether the workload was waiting on CPU scheduling, storage, locks, log flushes or another resource. Review blocking chains and long-running transactions during the same interval. This prevents a scale-up from masking a concurrency problem that additional capacity will not solve.

Include elastic-pool context

For databases in an elastic pool, investigate both the database and the pool. One busy database can consume shared capacity and affect quieter neighbours. Check whether pressure aligns with activity elsewhere in the pool, whether workload schedules overlap and whether per-database limits are contributing.

A recurring pool-level incident may be resolved by workload scheduling, moving an outlier, changing pool capacity or tuning the database that drives the peaks. The right answer depends on the history, not just the current percentage.

Use history to distinguish a regression from growth

Compare the incident with the previous day and the same business period in earlier weeks. A sudden step change after a deployment suggests a plan or application regression. A gradual increase may reflect data growth, higher concurrency or a service tier approaching its intended limit. A predictable daily peak may be a reporting or maintenance schedule.

Useful comparisons include:

• resource percentage and absolute workload volume;
• top queries and their execution plans;
• wait distribution;
• blocking and transaction duration;
• database size, log generation and I/O;
• deployments, jobs and known business events.

Choose between tuning and scaling

Tune first when a small number of statements, a plan regression, avoidable scans, blocking or poor workload timing explains the incident. Scaling is reasonable when an efficient workload has genuinely outgrown the available capacity, when a temporary business event needs headroom or when tuning cannot meet the required response time within the operational window.

Scaling and tuning are not mutually exclusive. Temporary scaling can protect service while a durable query or application fix is prepared. Record the reason, expected outcome and review date so temporary capacity does not become an unexplained permanent cost.

Create alerts that lead to an investigation

Alert on sustained pressure and meaningful recurrence rather than every brief peak. Preserve enough query, wait and metric context to understand what was active when the threshold was crossed. The Azure SQL alert documentation covers the monitored alert workflow.

Mini DBA keeps Azure SQL history alongside query and wait evidence. Its AI Assistant can explain the selected metrics, alerts and execution-plan context and recommend practical diagnostic steps. Explore the online demo to see the investigation flow without installing software.

SQL Server memory pressure is easy to suspect and surprisingly easy to misdiagnose. A falling Page Life Expectancy value, a large SQL Server process or a slow query can all look like a memory problem, but each can have a different cause. The safest investigation connects operating-system capacity, SQL Server configuration, memory grants, cache behaviour, waits and the workload timeline before anyone changes a setting.

This guide presents a practical workflow for diagnosing memory pressure. It is designed for production incidents where the first question is not simply “is memory high?” but “which workload or constraint is creating pressure, and what evidence supports the fix?”

Start by separating memory use from memory pressure

SQL Server is expected to use memory. A database engine that has allocated most of its permitted memory is not automatically unhealthy; keeping useful data and plans in cache avoids physical I/O. Pressure exists when the operating system or SQL Server cannot satisfy useful allocations without harmful waiting, paging, eviction or repeated cache churn.

Begin by establishing the incident window and asking:

• Did the whole host slow down, or only one database or workload?
• Was operating-system available memory exhausted?
• Were queries waiting for execution memory grants?
• Did buffer-cache turnover or physical reads rise sharply?
• Did the change coincide with a deployment, maintenance task or reporting workload?

A monitoring history is valuable here because the current state may look normal by the time a DBA opens a diagnostic session. The Mini DBA SQL Server memory monitoring page explains how memory, waits, queries and historical context are kept together.

Check the operating system before changing SQL Server

Review available physical memory, paging activity and the memory used by other processes. If the host is under pressure, confirm whether SQL Server is the main consumer or whether backup software, antivirus, integration services, another SQL instance or an application process is competing for RAM.

Virtual machines add another layer. Verify the memory assigned to the VM, whether dynamic memory or ballooning is involved, and whether the host is under contention. A SQL Server configuration can look reasonable inside the guest while the underlying platform is reclaiming memory.

Validate max server memory in context

max server memory should leave enough capacity for Windows, SQL Server allocations outside the buffer pool, drivers, monitoring agents and every other service on the host. There is no single correct percentage for every server. The right value depends on total RAM, workload concurrency, enabled features and what else runs on the machine.

Do not lower the setting simply because the SQL Server process is large. First establish that the operating system lacks headroom or that another required consumer is being starved. Equally, do not increase it to solve a query memory-grant problem until you know whether a small number of plans are requesting excessive workspace memory.

Investigate query memory grants

Sorts, hashes, index operations and some parallel plans need workspace memory. When requests exceed the memory available for grants, runnable queries can wait even though the database engine already owns a large amount of RAM.

Look for:

• pending or queued memory grants;
• RESOURCE_SEMAPHORE waits;
• large requested grants compared with memory actually used;
• spills to tempdb in execution plans;
• cardinality errors that inflate row-count estimates;
• several concurrent copies of an otherwise acceptable reporting query.

The corrective action might be query or index tuning, updated statistics, reduced concurrency, a plan correction or more memory. The evidence should decide. Use the SQL Server query view and execution-plan analysis to move from a server-level symptom to the statements responsible.

Read cache indicators as a trend

Page Life Expectancy is most useful as a workload-specific trend, not a universal pass/fail number. A sharp drop combined with rising reads and slower queries can indicate cache churn. A low but stable value on a busy system may be less important than a sudden change from that server's normal range.

Review buffer cache hit ratio, page reads, lazy writes and database-level I/O alongside the workload. If one scan or maintenance operation displaced useful pages, the history should show when it began and which database was active. If cache pressure repeats every day, compare the same period across several days rather than treating each occurrence as an isolated incident.

Correlate waits instead of diagnosing from one counter

Memory pressure often appears through related waits and secondary symptoms. Grant pressure can produce RESOURCE_SEMAPHORE; cache churn can increase physical I/O and storage waits; excessive compilation can put pressure on the plan cache and CPU. The SQL Server waits view helps confirm what the workload was waiting for during the same time window.

A single counter can tell you where to look. A correlated timeline is what lets you explain the incident.

Common mistakes to avoid

• Treating high SQL Server memory use as proof of a leak.
• Using one Page Life Expectancy threshold for every server.
• Increasing memory before reviewing oversized query grants.
• Ignoring other instances and services on the host.
• Looking only at the current state after the expensive workload has finished.
• Changing several settings at once, making the result impossible to attribute.

Validate the fix against the original evidence

After making one justified change, compare the same workload window. Confirm that grant waits, paging, cache churn or query duration improved without creating a new bottleneck. Keep the original baseline so a temporary quiet period is not mistaken for a permanent fix.

Mini DBA retains metric, query and alert history and can use AI-assisted analysis to explain the evidence and suggest practical next checks. You can try the populated online demo or review the SQL Server memory documentation before monitoring your own SQL Server estate.