SQL Server Performance Monitoring: From Fragmented Alerts to Root Cause in One View

A SQL Server slowdown rarely starts where it shows up. The engine reports high waits, a stalled query, or a cache that won't hold its working set, but the trigger is frequently a layer away: storage saturated by a backup, memory clawed back by the hypervisor, or a lock left open by code that shipped that morning. A monitor that watches only the database records the symptom accurately and says nothing about the cause. The counter goes red, the alert fires, and the reflex is to tune whatever the dashboard is pointing at, which is how an incident loses an hour in the wrong place before anyone checks the layer that actually broke.

Breaking that reflex is what this guide is for. It works through the counters and wait types that earn a place in an alert, how to judge them against an instance's own baseline instead of a hard-coded number, and how to line a database symptom up against the storage, host, and application events around it. Done well, that turns "the database is slow" into a specific, correctly routed root cause on the first attempt. The starting point is understanding why a database-only view misleads so reliably.

The Before State: Why Database-Only Monitoring Fails You

An alert that trips on a threshold answers one narrow question: did this number cross a line? It says nothing about what else shifted at the same moment, or whether the number that moved is the problem itself or just the visible edge of something two layers down.

This is where monitoring and observability diverge. Monitoring watches predefined counters and reacts when one trips. Observability lets you interrogate the system after the fact, joining metrics, logs, and traces from every layer that could be involved, which is why a SQL Server instance can read green across the board while users wait. The fault lives in how the signals relate to one another, not in any single one of them.

Wait statistics make the hazard concrete. The same wait type can mean trouble inside the engine or trouble in the storage or operating system beneath it, and the number alone won't say which. Two identical readings can call for opposite responses.

Three incidents that all look like a database problem

These three scenarios run as the connective tissue through the rest of this guide. Each one presents as a database symptom. None of them has a database root cause.

The hypervisor reclaim. On a virtualized host, the balloon driver hands guest memory back to the hypervisor, and the SQL Server VM is the one that gives up pages. Page Life Expectancy falls off a cliff within minutes instead of drifting down over hours. Read in isolation, the graphs scream memory misconfiguration. The catch is that the instance never chose to release the memory; that decision happened a layer below it, and the only real fix is a conversation with whoever runs the virtualization platform.

The storage snapshot. A snapshot job or host-level backup floods shared storage with I/O. PAGEIOLATCH_SH waits climb and every read-heavy query drags. On the database side it looks like classic index pain on a hot table, so that is where the tuning goes, and it changes nothing, because the bottleneck is off-box. The moment the snapshot finishes, the symptom evaporates.

The deployment lock. A new release wraps a transaction around an operation that runs longer than the previous version did, leaving a lock on a busy table held far past its expected lifetime. Blocked sessions accumulate. The database view shows lock contention, and the instinctive response is to investigate locking behavior in the database. The actual cause is application code that moved a network or external-service call inside a transaction boundary, and the fix belongs in the next deployment, not in a database setting.

The common thread is that the database is the messenger, not the culprit. In each case the symptom is loud and local while the cause sits a layer away, and acting on the symptom instead of the cause is what makes these incidents expensive.

The Cost of Debugging the Wrong Layer

Understanding why database-only monitoring fails is one thing; the operational cost of that failure is another. Wrong-layer diagnosis is a repeatable failure mode, and its cost structure compounds. When a DBA spends the first hour of an incident on index tuning before escalating to storage, every minute of that hour is incident time that didn't advance resolution. Industry research on incident response consistently identifies cross-team escalation latency, not the diagnostic work itself, as a leading driver of mean time to resolution in environments where each team monitors only their own layer.

A wrong-layer first hypothesis means:

• Additional teams drawn into the incident (storage, app, infra) after the initial wrong direction.
• Context-switch overhead as each team re-explains the symptoms from its own tool's perspective.
• A correction loop: investigation pauses, the new hypothesis must be validated, and context is rebuilt before progress resumes.

The signal literacy in the steps below doesn't eliminate incidents. It reduces the time spent investigating the wrong layer. The next four steps build that literacy, starting with deciding which signals to trust.

Bridge Step 1: Trustworthy Signals and Baselines

The first bridge step is deciding which signals to trust. Not all counters are equally useful in an incident. A focused set of high-signal counters, read against a proper baseline rather than a fixed threshold, tends to be more actionable than a wide dashboard of uncalibrated metrics.

Pull the core counters straight from sys.dm_os_performance_counters:

-- Key SQL Server performance counters via sys.dm_os_performance_counters
SELECT
    object_name,
    counter_name,
    instance_name,
    cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name IN (
    'Buffer cache hit ratio',
    'Page life expectancy',
    'Batch Requests/sec',
    'SQL Compilations/sec',
    'SQL Re-Compilations/sec',
    'Lock Waits/sec',
    'Number of Deadlocks/sec',
    'Active Transactions'
);

The same values are exposed as PerfMon objects (SQLServer:Buffer Manager\*, SQLServer:SQL Statistics\*, and SQLServer:Locks(_Total)\*), which helps when you want them on one chart next to host metrics. For a named instance, the object prefix becomes MSSQL$<InstanceName> in place of SQLServer.

The three signal groups that announce incidents

Memory pressure. Buffer Cache Hit Ratio and Page Life Expectancy move first when the buffer pool can't hold the working set, but only the trend matters, not the absolute number. A large buffer pool sits above 99% BCHR with PLE in the thousands or tens of thousands of seconds, so the old 90% and 300-second floors are noise. Read movement against the instance's own normal: a slow PLE slide means the working set is outgrowing memory, while a near-vertical drop over a few minutes is the signature of external eviction, the hypervisor-reclaim case.

Locking and long-running transactions. Lock Waits/sec, Deadlocks/sec, and Active Transactions only mean something read against application activity. Deadlocks spiking right after a release point to a lock-ordering change in the new code; lock waits climbing in a batch window point to OLTP and reporting colliding on one instance; Active Transactions rising while Batch Requests stay flat point to a session holding an uncommitted transaction, usually behind a slow external call. Log space belongs here as a hard ceiling: near capacity, you are one transaction from a full transaction log that stops writes cold.

Throughput and plan health. Batch Requests/sec against SQL Recompilations/sec exposes plan churn: if throughput sags while CPU stays pinned, either recompiles are eating the gains or the cache is serving a sniffed plan that fits one parameter set and punishes the rest. Read together, they separate "busy" from "busy doing useless work."

Other counters add detail (latch wait times, pending memory grants, plan cache hit ratio), but those three groups are the ones that announce an incident.

Why static thresholds lie

A PLE floor in the low thousands is meaningless on a box with hundreds of gigabytes of buffer pool. One Batch Requests/sec line can't serve both a Tuesday-afternoon peak and a dead-quiet Sunday: pin it to the peak and quiet-hour anomalies slip by; pin it to the lull and every ordinary afternoon sets off pages. Baselining is the way out.

Sample the core counters over a two-to-four-week window that spans a month-end run, business-hours peaks, and maintenance windows, then alert on departure from that envelope (roughly mean plus or minus 2 standard deviations, split by peak and off-peak) rather than on a flat number. By hand, that means PerfMon Data Collector Sets or a scheduled job dumping sys.dm_os_performance_counters into a staging table on a timer.

ManageEngine OpManager Nexus builds that per-instance baseline automatically and alerts on deviation with dynamic adaptive thresholds, so a PLE drop fires against the instance's own history instead of an absolute set at install time. The baseline re-adjusts as the workload profile shifts, with no manual rebuild.

A baseline tells you when a number has gone abnormal. What it can't tell you is what the engine is stuck behind, which is the next step.

Bridge Step 2: Reading What the Engine Is Waiting On

Wait statistics are the signal that routes an incident. Each worker thread logs the resource it waited on and how long it lost on its way through an operation. sys.dm_os_wait_stats rolls those totals up per instance, counting from the last service restart or the last explicit clear with DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR). Read correctly, they point you at the right layer before you change a single index or setting.

Filtering noise and computing deltas

The DMV exposes hundreds of wait types, and the bulk of them are idle background chatter. Strip the benign ones out before computing any percentages:

-- Actionable wait categories by relative share
-- Run against cumulative totals for trend reading, or use the delta pattern below
WITH ActionableWaits AS (
    SELECT
        wait_type,
        wait_time_ms,
        waiting_tasks_count,
        CASE
            WHEN wait_type LIKE 'LCK%'           THEN 'Lock'
            WHEN wait_type LIKE 'PAGEIOLATCH%'   THEN 'I/O'
            WHEN wait_type IN ('ASYNC_IO_COMPLETION',
                               'IO_COMPLETION')  THEN 'Disk I/O'
            WHEN wait_type IN ('WRITELOG', 'LOGBUFFER')
                                                 THEN 'Log Write'
            WHEN wait_type LIKE 'LATCH_%'        THEN 'Latch'
            WHEN wait_type = 'CXPACKET'          THEN 'Parallelism'
            WHEN wait_type IN ('SOS_SCHEDULER_YIELD', 'THREADPOOL')
                                                 THEN 'CPU'
            WHEN wait_type IN ('ASYNC_NETWORK_IO', 'NET_WAITFOR_PACKET')
                                                 THEN 'Network'
            ELSE 'Other'
        END AS wait_category
    FROM sys.dm_os_wait_stats
    WHERE wait_type NOT IN (
        -- Benign background and idle waits
        'SLEEP_TASK',                   'BROKER_TO_FLUSH',
        'BROKER_EVENTHANDLER',          'CHECKPOINT_QUEUE',
        'DBMIRROR_EVENTS_QUEUE',        'DISPATCHER_QUEUE_SEMAPHORE',
        'FT_IFTS_SCHEDULER_IDLE_WAIT',  'HADR_WORK_QUEUE',
        'LAZYWRITER_SLEEP',             'LOGMGR_QUEUE',
        'REQUEST_FOR_DEADLOCK_SEARCH',  'RESOURCE_QUEUE',
        'SERVER_IDLE_CHECK',            'SLEEP_DBSTARTUP',
        'SLEEP_DCOMSTARTUP',            'SLEEP_MASTERDBREADY',
        'SLEEP_MASTERMDREADY',          'SLEEP_MASTERUPGRADED',
        'SLEEP_MSDBSTARTUP',            'SLEEP_SYSTEMTASK',
        'SLEEP_TEMPDBSTARTUP',          'SNI_HTTP_ACCEPT',
        'SP_SERVER_DIAGNOSTICS_SLEEP',  'SQLTRACE_BUFFER_FLUSH',
        'WAITFOR',                      'XE_DISPATCHER_WAIT',
        'XE_TIMER_EVENT',               'BROKER_TRANSMITTER',
        'DBMIRROR_SEND',
        -- CXCONSUMER is benign (consumer side of parallel exchange)
        -- Introduced in SQL Server 2017 CU3, backported to 2016 SP2
        -- Do NOT treat this as a parallelism problem
        'CXCONSUMER'
    )
)
SELECT
    wait_category,
    SUM(wait_time_ms)                                                   AS total_wait_ms,
    SUM(waiting_tasks_count)                                            AS total_waits,
    CAST(
        100.0 * SUM(wait_time_ms)
            / NULLIF(SUM(SUM(wait_time_ms)) OVER (), 0)
        AS DECIMAL(5,2)
    )                                                                   AS wait_pct
FROM ActionableWaits
GROUP BY wait_category
ORDER BY total_wait_ms DESC;

Keeping Other in the output is deliberate. A previous version of this query dropped that bucket and quietly lost high-impact waits such as WRITELOG and IO_COMPLETION that simply hadn't been categorized yet. Anything significant on your particular instance should surface rather than disappear into an unmapped category.

Cumulative numbers are fine for spotting a trend, but live diagnosis wants the difference between two snapshots taken across the window you care about, and that arithmetic has to tolerate a counter reset landing between the two reads:

-- Reset-safe delta: two snapshots across a collection interval
-- Handles both service restarts and manual DBCC SQLPERF resets
SELECT wait_type, wait_time_ms, waiting_tasks_count
INTO #WaitSnapshot1
FROM sys.dm_os_wait_stats;

WAITFOR DELAY '00:01:00';  -- adjust interval as needed

SELECT
    s2.wait_type,
    CASE WHEN s2.wait_time_ms >= s1.wait_time_ms
         THEN s2.wait_time_ms - s1.wait_time_ms
         ELSE s2.wait_time_ms  -- counter was reset between snapshots
    END AS delta_wait_ms
FROM sys.dm_os_wait_stats s2
LEFT JOIN #WaitSnapshot1 s1 ON s2.wait_type = s1.wait_type
ORDER BY delta_wait_ms DESC;

The CASE arm reads any drop in the running total as a reset and falls back to the post-reset value, so neither a restart nor a manual DBCC SQLPERF clear poisons the math. Carry the same benign-wait filter from the cumulative query into this delta SELECT, or the interval view fills right back up with idle noise.

The three categories that route you to the right layer

PAGEIOLATCH (I/O waits). PAGEIOLATCH_SH or PAGEIOLATCH_EX at the top of the list means the engine is parked waiting on storage. The reflex is to blame a missing or fragmented index, but saturation originating outside SQL Server is at least as likely, so look at read latency, queue depth, and any backup or snapshot overlapping the spike before you touch an index. Fragmentation earns suspicion only once storage comes back clean.

LCK_M (lock waits). These mean sessions are stacking up behind locks someone else holds. The wait type marks where the visible trail ends; picking the thread back up means tracing the blocking chain (Step 3). The cause almost always traces to how the application brackets its transactions, not to a server setting.

CXPACKET (parallelism). This is the parallelism wait that rewards attention, and the standard knee-jerk of cutting instance-wide MAXDOP is the wrong move. Begin in the plan. Lopsided CXPACKET usually comes from rows distributed unevenly across parallel threads; confirm it by opening the actual plan and checking the repartition-streams operators for large per-thread row skew or wide estimate-versus-actual gaps at the exchange. A statistics refresh or a query-scoped hint then fixes the offending query without taxing the rest of the workload. Its benign twin CXCONSUMER (shipped in SQL Server 2017 CU3 and backported to 2016 SP2) is the consumer side of the exchange; a list dominated by it is noise to drop, not a problem to chase.

Knowing the category narrows the layer. The next step pins down the exact statement.

Bridge Step 3: From Category to Culprit Query

A wait category tells you the class of problem. To name the statement behind it, join sys.dm_exec_query_stats to sys.dm_exec_sql_text, optionally pulling in sys.dm_exec_query_plan.

Two lists, two different problems

The usual misstep in building a "top queries" report is ranking by total elapsed time alone. That metric flatters anything that runs constantly: a 3ms statement fired 400,000 times a day racks up 1,200 seconds, yet it is almost never what took the system down. Rank by average elapsed time and the genuinely slow executions rise to the top; rank by total logical reads and the I/O gluttons appear regardless of how fast each call returns. Produce both lists, since they routinely finger different queries:

-- Top 5 queries by total elapsed time (high-count and long-running absolutes)
SELECT TOP 5
    qs.total_elapsed_time / qs.execution_count  AS avg_elapsed_us,
    qs.total_elapsed_time                        AS total_elapsed_us,
    qs.total_logical_reads,
    qs.execution_count,
    SUBSTRING(
        st.text,
        (qs.statement_start_offset / 2) + 1,
        ((CASE qs.statement_end_offset
              WHEN -1 THEN DATALENGTH(st.text)
              ELSE qs.statement_end_offset
          END - qs.statement_start_offset) / 2) + 1
    ) AS query_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY qs.total_elapsed_time DESC;

-- Top 5 queries by total logical reads (I/O pressure drivers)
SELECT TOP 5
    qs.total_logical_reads / qs.execution_count  AS avg_logical_reads,
    qs.total_logical_reads,
    qs.total_elapsed_time,
    qs.execution_count,
    SUBSTRING(
        st.text,
        (qs.statement_start_offset / 2) + 1,
        ((CASE qs.statement_end_offset
              WHEN -1 THEN DATALENGTH(st.text)
              ELSE qs.statement_end_offset
          END - qs.statement_start_offset) / 2) + 1
    ) AS query_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY qs.total_logical_reads DESC;

Capturing slow statements in production

In production, Extended Events is the replacement for the retired SQL Server Profiler. A session that captures sql_statement_completed above a duration cutoff (the filter takes microseconds, so 3000000 is the 3,000ms mark) costs far less than an old-style synchronous trace while still handing you the statement text, plan handle, and resource figures. Because XE buffers asynchronously, it stays off the foreground worker threads even under load:

-- XE session: capture slow statements (>3 seconds) in production
CREATE EVENT SESSION [SlowStatementCapture] ON SERVER
ADD EVENT sqlserver.sql_statement_completed (
    ACTION (
        sqlserver.sql_text,
        sqlserver.query_hash,
        sqlserver.plan_handle,
        sqlserver.database_name,
        sqlserver.username
    )
    WHERE duration > 3000000  -- microseconds; 3,000 ms
)
ADD TARGET package0.ring_buffer (
    SET max_memory = 65536  -- 64 MB ring buffer
)
WITH (
    MAX_DISPATCH_LATENCY = 5 SECONDS,
    TRACK_CAUSALITY = OFF
);

ALTER EVENT SESSION [SlowStatementCapture] ON SERVER STATE = START;

An in-memory ring_buffer target is non-persistent, which is what you want for live work. When the capture needs to outlast a restart, point it at an event_file target and read it back with sys.fn_xe_file_target_read_file(), shredding the event XML it returns. Note that sys.dm_xe_session_targets carries only target metadata and ring-buffer contents; it will not return the rows written to a file target.

Finding the head blocker

Blocking chains invite the wrong read. With fifteen sessions stuck, the temptation is to kill whichever one has waited longest, and that session is almost always a victim. The one you want carries blocking_session_id = 0 while at least one other session waits behind it:

-- Active blocking chain: list blocked sessions and the session blocking each
SELECT
    r.session_id,
    r.blocking_session_id,
    r.wait_type,
    r.wait_time / 1000.0          AS wait_seconds,
    r.status,
    SUBSTRING(
        st.text,
        (r.statement_start_offset / 2) + 1,
        ((CASE r.statement_end_offset
              WHEN -1 THEN DATALENGTH(st.text)
              ELSE r.statement_end_offset
          END - r.statement_start_offset) / 2) + 1
    ) AS current_statement
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
WHERE r.blocking_session_id > 0
ORDER BY r.wait_time DESC;

This query returns the victims, the sessions with a non-zero blocking_session_id. To inspect the head blocker itself, take any blocking_session_id value the query returns and look it up directly. If the head blocker has an active request, sys.dm_exec_requests joined to sys.dm_exec_sql_text returns its current statement. If the head blocker is an idle session (status sleeping with an open transaction and no active request row), join sys.dm_exec_sessions to sys.dm_exec_connections and read most_recent_sql_handle to recover the last statement it ran.

Killing a victim frees only its own locks; the head blocker keeps hold of its locks, and the chain reassembles as the next sessions reach for the same rows. When the head blocker is idle, sleeping on an open transaction, the real question is why the application never committed, which points straight at application code and hands off to the application-layer correlation in the next step.

Bridge Step 4: Correlating Across Layers

The three preceding steps produce a diagnosis within the database boundary. This is where that diagnosis connects to a root cause. The three scenarios from the Before State each require correlating a database signal against an event in a different layer. None of them resolves inside SQL Server alone.

Resolving the three incidents

PAGEIOLATCH_SH and storage I/O. The diagnosis is the timestamp. Set the PAGEIOLATCH_SH spike beside the host's storage activity, and a snapshot or backup running in the same minute is the answer, no query plan required. Without that storage signal in the same view, the spike reads as a database fault and the incident drains into index tuning that was never going to help.

PLE drop and OS memory pressure. The same move, one layer down. Line the Page Life Expectancy cliff up against a host-level memory-reclaim event at the same instant and the cause is plainly outside the engine, not in its memory configuration. With only the guest's PLE graph in view, the obvious response of retuning max server memory is the wrong one.

Blocking chain and application deployments. Here the deployment timeline is the tell. When the chain's start time matches a release that just shipped, the new build is the first suspect, not the database. The catch is having the deployment marker in the same view as the chain; without it, the link surfaces only through a slow back-and-forth between the DBA and whoever owns the release.

The same correlation discipline extends to distributed SQL Server environments and to the application layer above the database, each adding a layer to the root-cause picture.

Replica lag as a correlation layer

When the topology includes Always On Availability Groups, correlation has to reach past a single instance. A replica's health is its own signal, and it belongs next to the instance metrics rather than parked in a separate tool.

The data-loss exposure depends on the replica's availability mode. An asynchronous replica falling behind while primary write throughput is high means a failover would cause data loss, not just downtime, because the replica has not yet applied all committed log records. A synchronous replica must acknowledge the log write before the primary commits, so a healthy synchronous replica cannot fall behind; growing lag on a replica configured as synchronous points to a deeper problem, such as a network partition or replica storage saturation, that is also stalling primary commits. Whatever the mode, a PAGEIOLATCH_SH spike on the primary paired with rising lag on the secondary is its own diagnosis: the primary may be writing log faster than the secondary's storage can replay it, which reorders the remediation priority.

OpManager Nexus tracks Availability Groups, replicas, and availability databases in a dedicated Always On view, reporting each replica's sync mode (synchronous or asynchronous), send rate, lag, and failover-readiness state next to the same instance's CPU, memory, and wait data. The permissions the monitoring account needs for this sit in the setup section below.

Following a trace into the query

When the OpManager Nexus APM layer catches a slow request, you can follow that trace down to the exact query under it. A six-second checkout call that is slow because its query is slow because a blocking chain owns the table shows up as one connected path, instead of three tools stitched together after the fact.

Getting there means running the OpManager Nexus APM agent on the application hosts that talk to the database. The agent hooks into the runtime (Java, .NET, Node.js, and others) and carries trace context down into the database call. Both monitors, the SQL Server one and the APM one, have to live in the same OpManager Nexus instance for the joined view to render. The APM agent documentation lists the supported runtimes and the per-language install steps.

With the full correlation stack in place for self-hosted instances, the next section addresses what changes when SQL Server is cloud-managed.

Managed Instances: Cloud SQL Server and DMV Access

One caveat before moving from diagnosis to setup: SQL Server on Amazon RDS, Azure SQL Managed Instance, and Google Cloud SQL restricts or removes direct access to certain system views and server-level DMVs, because the platform, not you, owns the OS and storage beneath the engine. A self-hosted instance gives you the full DMV surface the steps above rely on; a managed one narrows it.

How much it narrows depends on the service. Azure SQL Managed Instance exposes more than Azure SQL Database. RDS surfaces wait-type data through Performance Insights, and sys.dm_os_wait_stats is generally queryable, though higher-privilege operations and host shell access are not. Cloud SQL applies its own access model. The practical implications:

• sys.dm_os_performance_counters may need elevated permissions some tiers don't grant, and some counters don't apply in a managed environment.
• VIEW SERVER STATE may have reduced effect, so wait-stat queries return partial data or need a platform-specific path.
• OS- and hypervisor-layer correlation (the hypervisor-reclaim and storage-snapshot signals) becomes the platform's concern, reached through its native monitoring rather than host metrics you control.

OpManager Nexus monitors RDS, Azure SQL, and Cloud SQL through the interfaces managed instances do expose (JDBC connections and vendor APIs), with no host agent on the database server. Confirm any specific capability against current product documentation, since managed-platform support shifts as providers revise their APIs. The setup below targets self-hosted instances; managed environments follow the same permission model wherever the platform allows.

Crossing Over: Standing Up Continuous Correlation

The manual queries in the preceding steps give you diagnostic capability when you're already in an incident. The bridge is complete when those same signals are continuously available, pre-populated, and correlated before a page fires. This section turns the steps into an always-on correlation layer.

Grant least-privilege permissions first

OpManager Nexus runs agentless here: nothing is installed on the database host. What it needs is a network path from the OpManager Nexus server to the instance on port 1433 (or the named-instance port), working credentials, and a monitoring login carrying the right server-level rights.

Set those rights up before you add the monitor. A login without VIEW SERVER STATE collects almost no DMV data, and it fails without complaint:

-- Minimum server-level permissions for the OpManager Nexus monitoring account
-- Run on each SQL Server instance to be monitored, from the master database
-- (VIEW SERVER STATE and VIEW ANY DEFINITION are server-scoped grants)

-- Required for DMV access (sys.dm_os_wait_stats, sys.dm_exec_requests, etc.)
GRANT VIEW SERVER STATE TO [sql_monitor_acct];

-- Required for Always On Availability Groups catalog views
GRANT VIEW ANY DEFINITION TO [sql_monitor_acct];

Under SQL Authentication, create the login first, then grant:

CREATE LOGIN [sql_monitor_acct] WITH PASSWORD = '<strong_password>';
GRANT VIEW SERVER STATE TO [sql_monitor_acct];
GRANT VIEW ANY DEFINITION TO [sql_monitor_acct];

For Windows Authentication (NTLM or Kerberos), the login already exists as a mapping from the AD account, so skip CREATE LOGIN and grant directly to the Windows account or AD group the OpManager Nexus service uses:

GRANT VIEW SERVER STATE TO [DOMAIN\sql_monitor_svc];
GRANT VIEW ANY DEFINITION TO [DOMAIN\sql_monitor_svc];

Add the monitor and verify

With the account in place:

1. In OpManager Nexus, open New Monitor > Add New Monitor.
2. Pick Microsoft SQL Server from the Database Servers group.
3. Give it a display name, then supply the host (name or IP) and port, 1433 by default; for a named instance, add the instance name in its field.
4. Select the authentication mode: SQL Authentication, Windows Authentication (NTLM), Kerberos, or Native (the jTDS driver). The Microsoft JDBC driver is the right pick for SQL Server 2012 and up; reach for jTDS (shown as Native) only on 2008 or 2008 R2.
5. Turn on Force Encryption if the instance requires encrypted connections.
6. Choose a polling interval. Five minutes fits most production work; drop to one minute on critical instances while you're actively working an incident.
7. Hit Test to check the connection, then Add Monitor(s) to save.

Once it's saved, the instance's performance views give you CPU, memory, disk I/O, and buffer-cache figures at a glance. The database-level views cover file usage and growth. If the instance is in an Availability Group, the Always On view confirms replica sync state and failover readiness.

From there, open the Performance Tab and scan for any session whose blocking_session_id isn't zero. One showing up means a blocking chain is live this second. The tab lays out the blocked session, the blocker's ID, the wait type, and how long it has been stuck, the same data the Step 3 sys.dm_exec_requests query returns, minus the query. That standing blocking view is the "continuous versus manual" difference in miniature: the DMV data you used to go fetch mid-incident is already on screen when you arrive. The closing section steps back to what shifts once these signals are correlated ahead of an incident instead of during one.

The After State: Root Cause in One Place

A wrong-layer incident is usually a setup problem, not a knowledge problem. The DBA chasing a PAGEIOLATCH_SH spike already knows what the wait type means. What they lack is the storage-layer event sitting in the same view, at the same timestamp, so making the connection takes a second escalation instead of a glance.

The steps in this guide don't add new knowledge to an experienced DBA's toolkit. What they add is the correlation discipline that collapses a multi-hour, multi-team investigation into a single read, from wait category straight to root cause without the wrong-layer detour.

When the next page fires, the question isn't whether you know which DMVs to query. The question is whether those signals are already correlated when you open the console. Build the monitoring posture that answers yes to that question before the incident happens, not after.