I know that is a heavy way to start. But I needed to say it first, because I have been carrying that question for over a year, and I think some of you are carrying it too. You just have not said it out loud yet. Maybe to a spouse late at night. Maybe to yourself in the car after a long call. Maybe you have not even said it to yourself, because saying it makes it real.

So let me be the one who says it first. And let me say the part underneath it too, the part I almost did not write.

I was scared. Not a little scared. Not the fashionable kind of scared you admit on a panel. The real kind. The kind that wakes you up at 4 AM and makes you stare at the ceiling and wonder if the twenty years of intuition you built, the muscle memory of reading an execution plan at a glance, the quiet confidence of knowing which wait type meant trouble, was about to be approximated by a machine that learned it in twenty seconds.

That is the fear nobody in our field wants to name. Not “will my job change.” The sharper one. What if the thing I spent my life developing can be copied by something that never lost a weekend doing it?

I want to tell you what I did with that fear. Because I did not conquer it. I still feel it. But I stopped running from it, and something changed.

One Monday morning I will never forget

Let me tell you about a specific Monday, because I think the abstraction is a way we avoid the truth.

It was month end at a mid sized finance client. 9:07 AM. The blocking started the way it always did there, like weather you could set a watch by. A senior developer, I will call him D, was on the call. His query had been named, publicly, in the previous postmortem. You could hear it in his voice, that thin careful tone people use when they are being careful not to sound defensive.

The DBA, I will call her M, had not slept properly in four nights. Her daughter had a school event she had missed on Friday. She was trying, and she was out of patience, and both things were true at the same time.

The manager was on mute, which somehow made it worse. A muted manager is louder than a shouting one.

I watched D shrink in his little video square while M explained, for the third month in a row, that the plan was flipping because of parameter sniffing interacting with a statistics refresh that ran at 8:55. I watched M’s jaw tighten every time she had to say it again. I watched a good team quietly hating each other over a problem that was nobody’s fault.

That is the room I want you to picture. Not a server. A room. Three tired humans, one muted manager, and a query that had become a character in a story nobody wanted to be in anymore.

That was the morning I stopped believing the problem was technical.

Every production incident is a room before it is a ticket.

The questions clients used to ask

For years, my inbox looked the same, and I loved it.

Why is this query slow. Why did this job fail last night. Why are waits so high. Can you review these indexes. Why is there blocking at 9 AM. Can you tune this before go live.

Reading those emails now, I feel tenderness toward them. They were simple. They were honest. There was a quiet dignity in that work. Nobody wrote articles about it. Nobody put it on a keynote slide. But it kept businesses running. It kept paychecks landing. It kept families from being woken up at 3 AM by angry phone calls.

The questions clients ask now

Somewhere in the last couple of years, the questions changed shape.

Can we make our team smarter without adding more people. Can we stop repeating the same confusion every month. Can we avoid depending on one expert every single time something breaks. Our VP read something about AI powered databases, can you help us figure out what is real. Are we falling behind by not using AI?

That last one. That is the question they are embarrassed to ask. That is the question their VP asked them and they did not know how to answer. And when I heard it out loud for the first time, I realized something that made me sit down.

It was the same question I was asking myself.

They were not asking me for AI. They were asking me for permission to be confused without being humiliated. They were asking me to be the adult in the room while the world yelled buzzwords at them.

And I could do that. Because I was confused too. And I had stopped being ashamed of it.

The language I refuse to use

Let me be blunt, friend to friend. Most of the loud words mean nothing when a backup fails.

“Unlock productivity” does not explain a PAGEIOLATCH spike at month end. It definitely does not explain it to a CFO who just wants to know why payroll is late. “Leverage AI” does not calm a developer who feels blamed for a slow query that is actually caused by a bad statistics refresh.

Buzzwords survive conference rooms. They do not survive 9 AM on a Monday.

I am not against AI. I want that on the record. I am against pretending. I am against the loud, glossy, confident language that makes good people feel small for not understanding it. I have watched that language make excellent DBAs feel stupid. Excellent DBAs are not stupid. They are the quiet backbone of entire industries, and nobody gets to make them feel like the future left without them.

What I wanted was something smaller. Something quieter. Something more honest. I wanted AI to help my clients stop repeating the same confusion over and over. That was the whole mission. No slogans. Just that.

So I built six small, practical things with my clients

None of these are flashy. None will win a keynote. None will trend. But each one changed the temperature in the room when things went wrong, and that is the only measure I trust anymore.

1. A custom SQL Server error parser

You know the moment. A SQL Server error shows up. Somebody pastes it into a group chat. Somebody asks, “Didn’t we see this last year?” Somebody says, “Ask Raj, he will remember.” Twenty minutes pass before anyone has turned the raw error into meaning. Twenty minutes of quiet panic. Twenty minutes of a manager refreshing their inbox. Twenty minutes of a junior developer praying nobody asks them a question.

Panic is the real enemy, not the error. Panic is what happens when nobody in the room has a first sentence.

So we built an AI assisted SQL Server error parser. It takes the raw error and produces a calm first response. What the error likely means. The probable root cause. The SQL Server area involved. The severity. The next safe checks. The possible business impact. And a short note on what not to do in panic.

It does not replace the DBA. Please hear me on this. It does not replace anyone. It removes the blank page fear. The DBA still owns the call. The human still decides. But now they are not starting from zero while everyone watches. They are starting from a calm paragraph. And you would be amazed what a calm paragraph can do for a scared team.

First response time on common issues dropped from around twenty minutes to about three. But the real number I care about is this. Nobody had to page M on a Sunday for the rest of that quarter.

2. An AI assisted query tuning workflow

Query tuning becomes emotional faster than most outsiders realize.

I think again of that Monday morning. D, shrinking. M, tightening. A muted manager. The workflow we built takes the query text, the plan details, wait symptoms, index information, row estimate issues, memory grants, spills, Query Store history, and runtime metrics, and produces a set of tuning hypotheses with validation steps.

The validation part mattered to me more than the suggestion part. It forces the team to ask: did logical reads actually drop. Did CPU come down. Did duration improve. Did waits shift. Did the spill disappear. Did the plan hold, or did it flip back on the next parameter set.

The real change was not technical. It was human. The conversation moved from “who wrote this bad query” to “what does the evidence say.” Nobody was the villain anymore. The evidence was the villain, or the hero, depending on the day.

Blame is what a team reaches for when it does not have evidence. Evidence is what a team reaches for when it has finally grown up.

Two months after we rolled this out at that client, D and M were getting coffee before the stand up. I am not making that up. I watched it happen on a Tuesday and I had to look away for a second so I did not say something sentimental into a work call.

That is the work. That is really the work.

3. An index recommendation reviewer

SQL Server happily tells you about missing indexes. Teams happily create them. And over time, quietly, the database becomes a graveyard. Duplicates. Overlaps. Unused indexes. Wide indexes. Slower writes. Longer maintenance windows. Creeping storage costs nobody wants to explain on the next budget call.

The reviewer takes the missing index recommendation alongside the existing indexes on that table, looks at column overlap, key order, includes, usage since last reboot, and write overhead, and produces one of four verdicts: create as suggested, modify an existing index instead, create a narrower version, or do nothing and here is why. Each verdict comes with the script, the rollback, and a plain language explanation a developer can paste into a pull request review.

That fourth verdict is the one I am proudest of. Because it asks the question I wish every DBA kept on a sticky note above their monitor.

Is this index good for the whole system, or is it only good for this one query right now?

Sometimes the mature decision is to do nothing. And doing nothing, in this industry, takes more courage than people realize. It does not show up in a ticket. It does not earn applause. But it protects the system, and protecting the system is the whole job.

Restraint is the most underrated skill in our profession. Nobody gets promoted for the indexes they did not create. But they should.

4. A blocking and deadlock explanation generator

A deadlock graph is an ugly thing. Technical. XML. Hard to explain at 10 AM on a Monday when three people are waiting for certainty you do not yet have. I have stood in those rooms. I have felt that heat behind my ears. I have watched my own voice go a little thin as I tried to explain something complex to people who needed it simple yesterday.

The generator produces something human. Who held the lock. Who was waiting. Which session did what. Which object was involved. How long it lasted. Whether this is a one time event or a pattern. What is safe right now. What should be reviewed later, calmly, when the room is not on fire and nobody is guessing.

The impact is emotional more than technical. The DBA can communicate. The developer hears an explanation without feeling attacked. The manager hears a story instead of XML.

When a manager hears a story, they stop panicking. When they stop panicking, the whole team exhales. That exhale is worth more than most dashboards ever built.

5. A SQL Server health report summarizer

Most teams already have data. The problem is not data. The problem is noise. Health reports are long. They are emailed. They are ignored. They sit in inboxes quietly, like unread letters, and they are only rediscovered after something breaks, usually in the most embarrassing way possible, usually in a postmortem where someone asks, “Wait, did anyone check this report?” and the silence that follows is the real punishment.

The summarizer reads daily health data and produces a one page morning brief with four sections. What changed since yesterday. What needs attention today. What is only informational. What should not wait. Each item links to the underlying evidence, so nothing is hidden. Failed jobs, backup status, disk growth trajectories, Query Store regressions, long running queries, wait trend shifts, CPU and memory pressure, error log anomalies — all condensed into a page a human can actually read with their first cup of coffee.

A twenty page report is not insight. It is homework. And nobody in a busy team has time for homework.

The summarizer protects human attention, which is the scarcest, most overlooked resource in every team I have ever worked with.

6. An AI powered wait statistics interpreter

Wait statistics are one of the most honest things SQL Server gives us. They are also one of the most ignored. Teams say “SQL Server is slow.” But SQL Server is not slow. SQL Server is waiting for something. It is telling you, in its own quiet language, exactly what it is waiting for. The tragedy is that most people never learn to listen.

The interpreter reads the top waits over a chosen window, categorizes each one (CPU, storage, memory, locking, log, network, parallelism), explains what it likely means in the context of this specific workload, flags what is probably harmless, flags what needs attention, and ends with a short list of follow up queries to run to confirm or rule out each hypothesis. PAGEIOLATCH. CXPACKET. CXCONSUMER. ASYNC_NETWORK_IO. WRITELOG. RESOURCE_SEMAPHORE. LCK waits. SOS_SCHEDULER_YIELD. Each of these tells a story if you listen. Each of these is SQL Server whispering a truth that most teams are too busy to hear.

SQL Server is not slow. It is waiting. The question is whether we are mature enough to listen.

I want to sit with that line for a second, because I think it is the quietest thing I believe. Most of our database pain is not mystery. It is unread mail. SQL Server has been writing us letters for years. We just never opened them.

What the savings actually are

I could give you numbers. In one client, three repeating monthly incidents stopped repeating, which, by their own accounting, was worth forty thousand dollars a year in recovered engineering time. I will stand behind that number because I watched them calculate it.

But I no longer lead with money. I used to. I do not anymore. Because in that same client, M took her first real vacation in four years. Two weeks. She turned her laptop off. Her daughter was in every photo. Nothing exploded.

That is the number I care about. I do not know how to put it on an invoice. But anyone who has ever been the person the team cannot function without knows exactly what it is worth.

The fear, named once, properly

I told you at the start I was scared. Let me tell you what happened to the fear.

It did not go away. I want to be honest about that too. Some mornings I still read an announcement and my stomach drops for half a second before my brain catches up. I think that half second is permanent now. I have made peace with it.

But the fear got smaller because I found the part of the work that cannot be copied. And I want to give it to you, in case you need it.

AI can read a plan. It cannot read a room.

Sit with that. Because everything I am about to say lives underneath it.

AI can suggest an index. It cannot decide whether this team, this quarter, with this on call rotation, can absorb the write penalty. AI can explain a deadlock. It cannot tell when a developer is about to quit over how the deadlock is discussed. AI can generate a hypothesis. It cannot carry the scar of the last time that hypothesis was wrong in production at 2 AM with a CFO on the line.

AI does not have scars. You do.

That is not a weakness. That is the whole point. Knowing the answer is cheap. Knowing when the answer is confidently wrong is the job. And AI is very good at being confidently wrong. You cannot automate judgment. You can only earn it, slowly, one incident at a time, in rooms nobody wrote about.

What I am actually proud of

I am not proud because I used AI. That is a small thing. Anyone can use AI. Using AI is not an achievement. It is just pressing buttons.

I am proud because an error became a first response.

• A slow query became an evidence path.
• An index suggestion became a review.
• A deadlock became a readable story.
• A health report became a decision.
• Wait statistics became something the team could finally listen to.

D and M got coffee before the stand up. M went on vacation.

The output was never the point. The trust was.

That is the work. That is what it was always supposed to be. Quieter rooms. Calmer mornings. Teams that trust each other a little more at the end of the week than they did at the start.

So I will say it again. But I mean something different now.

Relevance is not keeping up with the noise. It is becoming the kind of professional a tired team can exhale around. The kind of person who makes rooms quieter, not louder. The kind of person who leaves teams trusting each other a little more than they did before.

Calm is not a personality trait. It is a practice.

That is what relevance actually is. Not the buzzwords. Not the tools. The calm you bring into the room with you.

So I will ask one more time, the way a friend would ask. Not as a challenge. Not as a threat. Just as one person reaching across the table to another, with both of our coffees cold by now.

I am staying relevant. Are you?

Reference: Pinal Dave (https://blog.sqlauthority.com/), X

First appeared on SQL and AI – I Am Staying Relevant. Are You?

Why Fragmented Environments Break Monitoring

Modern SQL Server estates rarely live in one place. A typical mid-size organization might run on-premises instances for core transactional workloads, Azure SQL Database for SaaS-connected applications, Azure SQL Managed Instance for lift-and-shift migrations that needed full CLR or cross-database queries, and Amazon RDS for SQL Server where a team made an early AWS commitment. Each platform exposes performance data differently.

The table below captures the key differences across DMV access, Extended Events, operating system visibility, and authentication. These gaps are not cosmetic. They determine what a monitoring tool can and cannot see, and whether the numbers it reports are conceptually comparable across environments.

Table 1: SQL Server monitoring surface area by deployment platform

A monitoring tool that ignores these differences will either surface empty panels or compare metrics that are not conceptually equivalent across tiers. Both outcomes lead to wrong conclusions during an incident.

What Unified Monitoring Actually Requires

A single dashboard sounds impressive during a product demo. In production, it solves very little on its own. What matters is whether the numbers actually mean the same thing everywhere. If wait stats on Azure behave differently from wait stats on-premises, or if replication lag on one platform is not comparable to its cloud equivalent, then you are not monitoring. You are just staring at graphs.

The real requirement is normalization. Wait statistics remain the most reliable cross-environment diagnostic signal. CXPACKET accumulation can indicate parallelism skew or an overly aggressive MAXDOP setting, though it should always be read alongside workload context rather than treated as an automatic misconfiguration flag. CXCONSUMER, separated from CXPACKET in SQL Server 2017 CU3, typically represents normal consumer-side parallel exchange waits and is not itself a tuning signal. PAGEIOLATCH_SH and PAGEIOLATCH_EX waits indicate buffer pool pressure or storage latency. LCK_M waits tell you that a session is blocked on a row, page, or object lock held by another transaction. These categories behave consistently whether you are looking at an on-premises instance or Azure SQL, though the collection mechanism differs.

Query Store data is the other universal anchor. Introduced in SQL Server 2016 and enabled by default in Azure SQL, Query Store persists execution statistics and plan history in the user database itself. A monitoring layer that exposes top queries by CPU, logical reads, or total elapsed time, and that surfaces plan regressions alongside forced plan status, gives DBAs something to act on rather than a CPU utilization graph that only tells them something is slow.

Deadlock Visibility Requires Platform-Aware Capture

Deadlock capture is one of the clearest examples of how platform differences break naive monitoring assumptions. The mechanism differs significantly depending on where SQL Server is running, which means a monitoring tool must handle multiple capture paths and normalize the output into a consistent view.

On on-premises instances and Azure SQL Managed Instance, the xml_deadlock_report event fired through Extended Events is the correct and recommended mechanism, typically captured by the system_health session into a ring buffer or file target. On Azure SQL Database, there is no instance-level Extended Events session. Deadlock information is accessible through Extended Events configured at the database scope, through sys.event_log, or through Intelligent Insights depending on the service tier. On Amazon RDS for SQL Server, deadlocks can be captured through custom Extended Events sessions or the RDS event log.

Tools like Idera SQL Diagnostic Manager that normalize deadlock graph presentation across these different capture paths, rather than requiring DBAs to manually decode raw XML from each source, meaningfully shorten diagnosis time by surfacing a consistent view regardless of where the deadlock originated.

The Hybrid-Specific Technical Problems

If you run entirely on-premises, you develop one set of operational instincts. If you run entirely in the cloud, you develop another. Hybrid environments force you to juggle both at the same time. That is where subtle problems appear, and they rarely show up during calm business hours.

Three issues tend to surface repeatedly in hybrid SQL estates.

Metric Normalization

I/O latency on-premises reflects physical or SAN-backed storage, measured directly via sys.dm_io_virtual_file_stats against local disks. The same DMV on Azure SQL reflects a distributed storage backend shared across tenants, with different latency characteristics and a different ceiling for what is considered normal. Treating these numbers as directly comparable produces wrong conclusions.

Authentication Boundary Management

On-premises instances use Kerberos or NTLM through Windows authentication, or SQL authentication. Azure SQL uses Microsoft Entra ID, formerly Azure AD, in addition to SQL authentication. A monitoring tool connecting across both must handle token-based authentication for cloud endpoints without storing credentials insecurely, which quickly becomes a credential management problem at scale.

Collection Architecture Under WAN Conditions

Monitoring agents positioned on-premises and polling cloud-hosted instances introduce round-trip latency into collection cycles. For hourly trend data, this is acceptable. For real-time blocking detection with sub-minute collection intervals, WAN latency can create collection gaps that make alert timing unreliable. Agentless architectures, the approach taken by Idera SQL Diagnostic Manager, reduce some of this friction by avoiding the need to install and maintain collection software on cloud-managed hosts where that option may not exist at all.

Where Idera SQL Diagnostic Manager Fits

Idera SQL Diagnostic Manager is built for teams that have moved beyond the single-platform comfort zone. It brings on-premises SQL Server, Azure SQL Database, Azure SQL Managed Instance, Amazon RDS for SQL Server, and SQL Server on GCP Compute Engine into one operational view, with normalized wait statistics, deadlock graphs, Query Store insights, blocking analysis, and index health.

Dynamic baselines matter here because fixed thresholds across mixed platforms create noise very quickly. When everything looks critical, nothing actually is.

For organizations where the 2 AM incident scenario is not hypothetical but routine, evaluating a unified monitoring approach is less about convenience and more about reducing operational friction.

Reference: Pinal Dave (https://blog.sqlauthority.com/), X

First appeared on SQL Server Monitoring Across Cloud, Hybrid, and On-Prem

The Gap Between Data and Diagnosis

SQL Server produces an enormous amount of real-time performance data. The dynamic management views alone, including sys.dm_exec_requests, sys.dm_os_wait_stats, sys.dm_exec_query_stats, sys.dm_exec_sessions, and sys.dm_db_index_operational_stats, contain everything a DBA needs to understand what is happening on an instance at any given moment. The problem is not availability of data. The problem is that raw DMV output is not a troubleshooting interface.

A query against sys.dm_exec_requests joined to sys.dm_exec_sql_text and sys.dm_exec_query_plan gives you the active request, the statement text, and the cached execution plan. That is genuinely useful. But writing that query under pressure, at 9 PM, while someone is waiting for an update, is a very different experience from having it presented in a live view that refreshes every few seconds and highlights what matters. Monitoring platforms such as Idera SQL Diagnostic Manager exist specifically to surface those DMV relationships in a live operational view rather than forcing DBAs to reconstruct them manually during an incident.

Real-time performance views bridge that gap. Done well, they take the same underlying data and present it in a way that compresses the time between opening a monitoring tool and knowing what to do next.

What Belongs in a Real-Time View

Not everything belongs in a live dashboard. Cluttering a real-time view with metrics that only make sense over hours or days, such as index fragmentation trends, storage growth, and backup history, turns a diagnostic tool into a reporting tool. They serve different purposes.

The metrics that genuinely belong in a real-time troubleshooting view are those where a change in the last 30 to 60 seconds is diagnostically meaningful. The table below maps each category to its source DMV, the appropriate collection interval, and what it actually tells you.

Table 1: Real-time metric categories, their DMV sources, and diagnostic value

How Collection Architecture Shapes What You See

The flow is straightforward. DMV sources feed a collection engine, and that collection engine drives the real-time views a DBA actually works with. The collection intervals are not arbitrary. They reflect a real tradeoff between diagnostic resolution and overhead on the monitored instance.

Higher collection frequency means more load on the monitored instance and more storage consumed by the monitoring infrastructure. Well-designed tools manage this by applying different intervals to different metric categories: live session data at 15-second intervals, wait stat deltas at 60 seconds, and index and storage metrics at longer intervals. The goal is to capture what needs capturing without adding meaningful overhead to an already pressured instance.

For blocking chain analysis, collection frequency matters more than most people realize. A blocking event that resolves in three minutes and causes a burst of application errors may never appear in a tool with five-minute refresh cycles. Sub-minute collection is what allows those transient events to be caught, preserved in history, and reviewed after they have already cleared.

The Execution Plan Problem

One of the more underused capabilities in real-time troubleshooting is cached execution plan access. sys.dm_exec_query_plan returns the cached plan associated with a running request, which is the plan SQL Server is actually using to execute the query and what a DBA needs during an incident. sys.dm_exec_text_query_plan returns it as XML, filterable by statement offset, which lets you isolate the relevant plan for a specific statement inside a multi-statement batch.

The important distinction is that this is the cached plan, not a live execution plan reflecting current runtime statistics. For in-flight execution statistics such as rows actually processed per operator and actual versus estimated row counts, sys.dm_exec_query_profiles provides operator-level data for active queries, though it requires the query to have been compiled with runtime statistics collection enabled. In most incidents, the cached plan from sys.dm_exec_query_plan is sufficient to identify the problem.

This matters because a query can perform well under one set of conditions and catastrophically under another, using the same cached plan. If a parameter-sniffed plan that was optimal for a small result set is now running against a large one, the plan itself will show you exactly where the cost is landing: on a nested loop join that is iterating millions of times, on a key lookup against a non-covering index, or on a hash match that has spilled to tempdb. You cannot reliably infer this from wait statistics alone.

A real-time view that surfaces the cached plan for any selected active request, rendered graphically rather than as raw XML, collapses what used to be a multi-step investigation into a single click. The DBA sees the request, sees the plan, and can make a decision about whether the fix is a hint, an index, or a statistics update, all without leaving the monitoring interface.

Why Refresh Rate Matters More Than People Think

A real-time view that refreshes every five minutes is not a real-time view. It is a slow reporting tool with a hopeful name.

For blocking chain analysis, a five-minute refresh means you may never see a blocking event that resolves in three minutes, which is exactly the kind of event that causes a burst of application errors and then disappears before anyone investigates. Sub-minute collection intervals, typically 15 to 30 seconds for live session data, are what allow a monitoring tool to catch transient blocking events and preserve them in historical data even after they have cleared. Idera SQL Diagnostic Manager collects live session and blocking data at these sub-minute intervals, which means a blocking chain that forms and clears within two minutes still appears in the historical record and can be reviewed after the fact.

There is a real tradeoff here. Higher collection frequency means more load on the monitored instance and more storage consumed by the monitoring infrastructure. Well-designed tools manage this by applying different collection intervals to different metric categories: live session data at 15-second intervals, wait stat deltas at 60 seconds, and index and storage metrics at longer intervals. The goal is to capture what needs capturing without adding meaningful overhead to an already pressured instance.

Wait Statistics: Deltas Over Totals

sys.dm_os_wait_stats is cumulative since the last service restart. A server that has been running for three weeks carries three weeks of accumulated wait history in every row. The cumulative total is useful for trend analysis over long periods. It is not useful for understanding what is happening right now.

The signal you want in a real-time context is the rate of change. How much have PAGEIOLATCH_EX waits increased in the last two minutes? Are SOS_SCHEDULER_YIELD waits climbing, indicating CPU saturation? Has ASYNC_NETWORK_IO spiked, which often points to a client that is not consuming result sets fast enough? A monitoring tool that samples wait stats at short intervals and presents the delta, rather than the running total, exposes pressure that cumulative views hide completely.

PAGEIOLATCH_SH / PAGEIOLATCH_EX

Waits on data pages being read from disk into the buffer pool. A rising delta points to disk I/O pressure or buffer pool churn from insufficient memory.

CXPACKET

Producer-side parallel exchange wait. A rising delta alongside high CPU can indicate plan skew, MAXDOP misconfiguration, or a workload that is being over-parallelized.

CXCONSUMER

Consumer-side parallel exchange wait, separated from CXPACKET in SQL Server 2017 CU3. Typically benign. A high CXCONSUMER delta without a corresponding CXPACKET rise is usually not a tuning signal.

SOS_SCHEDULER_YIELD

A thread voluntarily yielded the scheduler under heavy CPU load. A rising delta means the instance is CPU-bound.

ASYNC_NETWORK_IO

SQL Server is waiting for the client to consume data. Often a client-side bottleneck, but it still occupies sessions and can create apparent blocking.

LCK_M_X / LCK_M_S / LCK_M_U

Lock waits. Any meaningful delta here means blocking is active or was recently active. The session view will tell you who is involved.

Reading the View Under Pressure

The most important quality of a real-time performance view is that it should be readable by someone who is already stressed and moving fast. That sounds obvious. Most monitoring interfaces still miss it.

Effective real-time views lead with the answer. If there is blocking, the blocking view should be the first thing visible, not buried under a navigation menu. If a wait type has spiked in the last two minutes, it should stand out visually from the ones that have not. If a query is consuming 10 times its usual CPU, that deviation should be immediately obvious without requiring the DBA to remember what the usual number is.

This is the difference between a view that presents data and a view that supports a decision. The underlying SQL Server data is the same in both cases. The difference is in how it is structured, filtered, and surfaced.

Where Idera SQL Diagnostic Manager Fits

Idera SQL Diagnostic Manager approaches real-time monitoring with this kind of operational thinking built in. Its live session browser surfaces active requests, blocking chains, and wait types in a single view with sub-minute refresh, without requiring custom queries or multiple tool switches. The execution plan viewer pulls the cached plan for any selected session directly from sys.dm_exec_query_plan and renders it graphically, letting DBAs move from symptom to plan to decision in one workflow.

The wait statistics view presents deltas rather than cumulative totals, which is the correct framing for real-time troubleshooting. Tempdb and memory pressure indicators are surfaced alongside session data rather than in separate dashboards. And because SQL Diagnostic Manager maintains historical baselines, what appears in the real-time view can be immediately compared to what normal looks like for that specific instance, removing the guesswork that makes troubleshooting under pressure harder than it needs to be.

For teams whose troubleshooting workflow currently involves running DMV queries from memory while an incident is in progress, that is the gap worth closing.

Reference: Pinal Dave (https://blog.sqlauthority.com/), X

First appeared on Real-Time Performance Views That Make Troubleshooting Easier

The Problem With Alerting Without Context

An alert tells you something is wrong. What it almost never tells you is why. CPU is at 90 percent. That is the alert. But is it one rogue query, a sudden connection spike, a parallelism misconfiguration, a plan regression triggered by stale statistics, or a scheduled batch that runs every night and always looks exactly like this?

The alert does not know. And the tools a DBA typically reaches for to find out are different from the tool that fired the alert. That gap, between notification and diagnosis, is where incident resolution time disappears.

The answer is not more alerts or more dashboards. It is deeper context surfaced in the same interface, at the same moment the alert fires. The sessions running when the threshold was crossed, the wait statistics accumulating at that moment, the top resource-consuming queries, and how all of it compares to the historical baseline for that instance. Without that context, every alert is the beginning of an investigation that requires switching tools, writing queries from memory, and hoping the problem is still visible by the time you find it.

Dynamic Baselines: Why Fixed Thresholds Fail

A CPU threshold of 80 percent sounds like a reasonable starting point until you realize that a specific instance runs a nightly ETL that pushes CPU to 85 percent every night between 2 AM and 4 AM and has done so without incident for three years. An alert that fires every night at 2 AM teaches the on-call team to ignore it. When CPU hits 85 percent at 2 PM on a Tuesday because something is genuinely wrong, nobody responds in time.

The same problem occurs with I/O thresholds, memory thresholds, and wait time thresholds. Every instance has its own normal, and that normal shifts by time of day, day of week, and workload cycle. A fixed threshold applied uniformly across instances with different workload patterns is either too sensitive, producing noise, or too permissive, missing real problems.

Dynamic baselines solve this by building expected behavior from actual historical data on a per-instance, per-metric basis. The monitoring system compares current values against the historical distribution of values for that instance at that time of day on that day of the week. Deviations from the learned baseline fire alerts. Expected elevated activity does not. The table below shows the diagnostic categories where this depth of context matters most.

What a Deeper Diagnostic View Covers

The categories that matter most during a SQL Server incident, the data source behind each one, the appropriate observation window, and what each actually tells you are mapped below.

The Diagnostic Path From Alert to Root Cause

The flow is simple in theory. The alert is the starting point. What follows should be a steady narrowing from surface symptom to root cause, with each layer providing the context needed to move to the next step.

Blocking Chain Analysis: Finding the Head, Not Just the Tail

When an application reports timeouts or slow response, the instinct is to look at what the application is waiting for. That session is usually blocked. But the session it is waiting on may also be blocked. And the session that one is waiting on may be blocked by something else. Finding the real cause requires tracing the blocking_session_id chain through sys.dm_exec_requests until you reach a session that is not itself waiting on a lock held by another.

In a raw DMV workflow, that is a manual process written under pressure. A monitoring tool that renders blocking chains as a tree structure makes the head blocker and every dependent session visible immediately without writing a single query. More importantly, the tool should preserve this history because by the time the DBA opens the monitoring screen, the blocking chain may already have cleared.

A four-minute blocking event that caused a burst of application errors and then disappeared is still extremely useful diagnostically. It tells you which query held the lock, which sessions were waiting, how long each waited, and when the chain cleared. That is the information needed to stop the problem from coming back.

Execution Plan Access During an Active Incident

Wait statistics tell you what the server is waiting for. They do not tell you which query is responsible or why that query is behaving badly. For that, you need the execution plan.

sys.dm_exec_query_plan returns the cached execution plan for a query given its plan_handle. Used together with sys.dm_exec_requests, it allows the cached plan for a currently executing request to be retrieved. sys.dm_exec_text_query_plan returns the same data in XML form and can isolate the plan for a specific statement inside a larger batch. In most incidents, the cached plan is enough to explain what is going wrong.

The real value is not that the DMV exists. Any DBA can query it. The real value is having the plan rendered graphically for any selected active session without leaving the monitoring tool. A parameter-sniffed plan that was perfect for a small result set and is now running against a large one will quickly reveal where the pain is landing.

Index Health as a Proactive Discipline

Index health is one of those things that quietly waits in the corner until it turns into a production problem. Fragmentation grows gradually. Missing index recommendations sit in sys.dm_db_missing_index_details asking for attention like polite interns. Unused indexes keep consuming space and slowing writes without making much noise.

The data is available. sys.dm_db_index_usage_stats shows access patterns. sys.dm_db_index_physical_stats shows fragmentation levels. sys.dm_db_missing_index_details shows what the optimizer has been asking for. The problem is not availability. The problem is that very few DBAs are manually reviewing all of this across many servers and databases on a regular schedule.

A monitoring tool that collects this information continuously and presents it in a prioritized form changes the operating model from reactive to proactive. The backlog becomes visible before it becomes a midnight surprise.

Query Store as the Historical Performance Record

Query Store persists execution statistics and plan history inside the user database. For each query, it stores execution count, CPU, reads, duration, and plan history including forced plans. It acts like a flight recorder for query performance.

A monitoring layer that surfaces Query Store data gives DBAs fast access to plan regressions without manually inspecting Query Store views. Correlating current executions with historical behavior makes it clear whether a query running right now is performing within its normal range or has shifted away from that baseline.

A query that is slow and has always been slow needs a different response than one that was fast yesterday and is suddenly slow today. That distinction changes the entire troubleshooting path.

The Gap That Matters in Practice

Generic monitoring tools can tell you that something is wrong. The diagnostic depth described above, including blocking chain history, execution plan access, dynamic baselines, Query Store integration, and index health visibility, is what allows a DBA to determine why something is wrong and decide what to do next without jumping between five tools and ten tabs.

The difference between resolving an incident in 12 minutes and taking 90 is rarely knowledge. It is usually access to the right context, organized properly, at the right moment.

Where Idera SQL Diagnostic Manager Fits

Idera SQL Diagnostic Manager is built for the diagnostic depth this kind of environment requires. It surfaces blocking chain trees with sub-minute historical capture, graphical execution plans for active sessions, Query Store regression detection, wait statistics presented as deltas, tempdb and memory pressure indicators, and index health across monitored instances.

Dynamic baselines calibrated per instance mean alerts reflect genuine anomalies rather than expected workload patterns. Agentless architecture means it can work across on-premises SQL Server, Azure SQL Database, Azure SQL Managed Instance, Amazon RDS for SQL Server, and SQL Server on GCP Compute Engine without requiring software installation on managed hosts.

For teams where incident resolution still involves switching between multiple tools and writing diagnostic queries from memory, that is the gap SQL Diagnostic Manager is designed to close.

Reference: Pinal Dave (https://blog.sqlauthority.com/), X

First appeared on Deeper Diagnostics and Actionable Dashboards

Let Me Tell You What I Actually Needed

I wasn’t trying to do anything particularly impressive when I started this. I just needed to see what was happening inside a SQL Server, right now, without the usual drama of setting up a full monitoring stack.

I didn’t want Grafana or Datadog. I didn’t want agents to install, a new license to justify, or a whole weekend to sacrifice just to get some visibility. I just wanted a clean look at my wait stats in a browser. That was the whole requirement.

So I described it in plain English. Twelve lines of text. I gave those lines to Claude Code and this dashboard is what came back. It is dark-themed, auto-refreshing, and reads live from sys.dm_os_wait_stats and sys.dm_exec_requests. The best part is that it’s built entirely in HTML and JavaScript with no external tools or middleware sitting behind it.

What It Does

The dashboard connects directly to two DMVs. sys.dm_os_wait_stats gives the cumulative picture since the last server restart. sys.dm_exec_requests shows what is actively waiting right now. No middleware. No agent. Just those two views and some JavaScript doing the work.

Here is what it covers:

• Categorises every wait type into CPU, I/O, Memory, or Other
• Shows cumulative wait time since the last server restart
• Lists top wait types by total wait time
• Shows average wait time per task
• Shows active waits happening at this exact moment
• Charts for wait distribution across all categories
• Breaks down signal wait versus resource wait
• Recommendations panel based on what the server is actually doing
• Auto-refreshes every few seconds
• Highlights CPU pressure when signal wait crosses 25%

The signal wait versus resource wait split is honestly the most important thing here. Signal wait is how long a thread sat waiting just to get back on the CPU after it was already ready to run. That has nothing to do with your disk. Nothing to do with memory. That is purely CPU pressure. When that crosses 25% of total wait time, you have a specific problem, and this dashboard will tell you clearly.

The recommendations panel at the bottom is not generic. It reads what is actually happening and gives you something practical. The kind of thing a DBA would say after looking at the numbers, not after reading a checklist.

The Exact Prompt

I want to be honest about this. I did not write clever prompt engineering. I just wrote what I wanted.

Create a SQL Server wait statistics analysis dashboard.
Use sys.dm_os_wait_stats and sys.dm_exec_requests.

Requirements:
- Categorize waits into CPU, I/O, Memory and Other
- Show cumulative wait time since last restart
- Display top wait types
- Show average wait time per task
- Show active waits
- Charts for wait distribution
- Signal vs resource waits
- Recommendations based on patterns
- Modern dark themed dashboard
- Auto refresh every few seconds
- Highlight CPU pressure if signal wait exceeds 25%

That is it. Claude Code took that, figured out the right DMVs, categorised the wait types correctly, filtered out the benign ones, wrote the delta logic for the refresh, built the charts, wired the recommendation engine, and produced the full CSS. One pass. The screenshot above is what came out.

Why I Was Building This

This isn’t just a hobby project; I am actually building this as a Proof of Concept for an organization I am helping. I will deliver this first version, and then their internal team will take it over and adapt it for their own environment. My role eventually shifts into mentoring and guiding them as they build it out further.

Interestingly, this is the second time in the last month that a project has followed this exact pattern. An expert comes in, uses AI to compress weeks of work into a few days, delivers a working foundation, and then moves into a teaching role. It is starting to feel like a new standard for how technical consulting is going to work from now on.

Some honest reflections

I want to be honest about this part as well.

I have been doing this work for more than twenty years, so I know very well what it normally takes to build what you see in that screenshot. Just the query part alone is a heavy lift. You have to get the categorization right, decide which waits to ignore, and make sure the refresh is actually showing rates instead of just raw totals. That alone is a solid afternoon of work for someone who really knows these DMVs. This isn’t a task for a junior person; it is for someone who has lived inside this data for years.

Then you have to add the UI. You need a refresh loop that does not abuse the server, the threshold logic for alerting, and a recommendation engine that actually says something useful instead of something obvious. That is easily another half day of effort.

I have built different versions of this many times across many different projects. So when I watched something like this come out of just twelve lines of plain English, I did not feel proud. I did not feel excited in the usual way. I felt something I still have not fully named. It was something quiet and a little unsettling.

The bottleneck was never just the code. It was always knowing what to ask for. That gap has now become very small.

And here is what keeps coming back to me. I knew what to ask for because I have spent years doing this the hard way. I know what signal wait means because I have watched it climb during real incidents, at night, when the pressure was on. I knew what it meant before I even finished reading the number.

I know why 25% is the right threshold because I have seen both sides of it enough times that it stopped being a number and became a feeling.

The prompt was twelve lines. What was behind the prompt was twenty years.

The Thing Nobody Wants to Say Directly

There is a comfortable version of this story where AI just helps people build faster, barriers come down, and more people can create useful things. All of that is true. But sit with this for a moment.

The junior developer who spent three years building things slowly and getting them badly wrong was not wasting time. Every execution plan that left them confused, every dashboard they built that answered the wrong question, and every 4 AM incident where they guessed wrong three times before finally getting it right was depositing something into them. It is something very difficult to name but very easy to feel when you are in a room with someone who has it and someone who does not.

That slow, painful, and sometimes humiliating process is now being compressed.

The honest question, the one that nobody at a conference will ask out loud, is whether compressed experience produces the same thing as slow experience. We have to wonder if it produces something that looks exactly the same until the moment everything is on fire and it suddenly does not.

I am not saying one is better than the other, but they are certainly different. That difference will show up at the worst possible time: when the dashboard looks fine, the query looks correct, the wait stats look normal, and yet the system is still struggling. That moment is coming for everyone, and the person you want in the room at that moment is very specific.

What This Actually Means If You Are a SQL Server Professional

If you are a junior or mid-level data pro, the “grunt work” of the job is basically changing overnight. The hours spent writing standard monitoring queries, building routine dashboards, or setting up basic alerting are going to be compressed into minutes. That part is moving faster now, and there is no going back.

But there is a core part of this job that isn’t going anywhere: the intuition. AI can build the instrument, but you still need someone in the room who actually knows how to read the gauges. Knowing which DMV to point at for a specific symptom is one thing, but understanding that CXPACKET by itself tells you nothing without checking your MAXDOP settings and the actual query plans is another. It is that split-second judgment to look at an automated recommendation and know if it actually fits your workload or if it is just generic advice that might actually tank your server performance.

That kind of “knowing” does not come from generating tools quickly. It comes from years of watching those tools give you the wrong readings and learning exactly when to trust the dashboard and when to ignore it and dig deeper.

The dashboard came together in minutes. What it takes to understand what the dashboard is actually telling you, that still takes years.

The one thing that has not changed in all of this is that your server is waiting on something right this second. There is critical information sitting inside sys.dm_os_wait_stats right now that you probably have not looked at this week, or maybe even this month, mostly because setting up that kind of visibility always felt like a massive chore.

Well, that excuse is officially gone. The prompt is in the blog post. Go run it, get the dashboard up, and then actually take the time to sit with what the numbers are telling you. That is the one part of the job I cannot put into a prompt for you.

Reference: Pinal Dave (https://blog.sqlauthority.com/), X

First appeared on AI Built SQL Server Wait Statistics Dashboard in Minutes