PostgreSQL to SQL Field Notes: Operational Statistics

Welcome to SQL

First, welcome to SQL! You’re going to like this.

I’ve said this before, but it’s still true: moving from PostgreSQL is real work, and depending on your old database, this process will vary widely in complexity. I am confident that you’re going to love the mindful and modern features of SQL. Take your time, make the most of the resources built to help you in Microsoft Learn, and know there is a huge community of SQL developers ready to help along the way.

This blog is for the PG developer

Good news! The SQL standard is shared across both engines; that should help you get up to speed quickly. However, there are differences, and there aren’t always equivalents. To help fast-track your learning, this series will highlight some differences worth knowing. Although some may surprise you, I believe most will be a delight.

PostgreSQL operational statistics

Let’s talk about something every developer eventually needs: understanding what the database is doing. Your application is slow. A query that normally takes milliseconds suddenly takes seconds. CPU usage is climbing and users are complaining. Is the problem a lock, memory pressure, or disk I/O? A bad execution plan? You need answers, and you need them quickly.

PostgreSQL provides a useful collection of system views for investigating these problems. You’ve probably used some of them already.

SELECT *
FROM pg_stat_activity;

This shows active connections, session states, current queries, and wait events. It’s often the first place you look when investigating a problem. It’s not the only view you can use either.

PostgreSQL’s cumulative statistics system is mature and extensive, collecting database, table, index, and I/O statistics natively. It also provides excellent execution-plan instrumentation through EXPLAIN.

EXPLAIN (ANALYZE, BUFFERS, WAL)
SELECT *
FROM orders
WHERE customer_id = 42;

This executes the query and reports actual execution statistics, including operator timing, row counts, buffer activity, and WAL information. So far, so good.

Unfortunately, you already know the caveat

PostgreSQL provides good operational statistics, but deeper production diagnostics can require additional configuration, extensions, or external monitoring, depending on the diagnostic.

1) Running queries are difficult to inspect

Imagine a query has been running for five minutes. You can see it in pg_stat_activity, but which execution-plan operator is consuming the time? Is a join processing millions of unexpected rows? Is a sort spilling to disk? PostgreSQL’s EXPLAIN ANALYZE can answer these questions, but it ordinarily requires executing the query under instrumentation rather than inspecting another session’s running execution.

Honestly, and I mean this, we could have stopped with this one caveat. It fundamentally changes how you approach diagnosing performance problems, both as an administrator and as a developer. But there’s more, and the goal of this article is to give you an overview of the differences. So, let’s keep going.

2) Completed query plans require preparation

What if the query has already finished? It happens! PostgreSQL’s pg_stat_statements extension provides aggregate execution counts, timing, and buffer activity, but it doesn’t retain actual execution plans for completed queries.

Azure SQL’s last actual plan feature also requires advance configuration. The distinction isn’t just freedom from preparation; it’s the ability to retrieve the last actual execution plan through a native function while the relevant plan remains cached.

3) Resource diagnostics are spread across components

PostgreSQL exposes current waits, memory contexts, temporary file activity, and extensive I/O statistics. For historical wait sampling, you might add pg_wait_sampling; for additional operating-system resource statistics, pg_stat_kcache; and for automatic plan capture, auto_explain.

PostgreSQL also delegates CPU scheduling to the operating system. On a self-managed server, that’s an advantage because administrators can use OS profilers and tracing tools unavailable in Azure SQL Database. Managed PostgreSQL services, however, restrict some host access and extensions. PostgreSQL’s modularity is intentional and useful, but comprehensive diagnostics will involve several independently configured components.

The operational cost

When an incident occurs, PostgreSQL administrators will correlate statistics views, extension data, server logs, and external monitoring. However, and this is the big deal, if all the appropriate instrumentation wasn’t carefully enabled and configured beforehand, evidence can be unavailable. That limitation isn’t exclusive to PostgreSQL. Azure SQL’s cached plans and transient statistics can disappear after eviction, cache resets, or service operations. Neither engine (nor any other database in the known universe) can reconstruct information it never collected. The meaningful distinction is which capabilities are built into the engine.

Great news! You’re using SQL now.

Microsoft SQL exposes extensive internal diagnostics through Dynamic Management Views and Functions, commonly called DMVs and DMFs. They provide visibility into execution, memory management, waits, locking, I/O, and other engine resources. Now, PostgreSQL’s statistics views are also relational and queryable. That’s not the differentiator. The advantage is the breadth of execution and resource-management information SQL exposes natively, including memory grants, accumulated waits, in-flight execution plans, and structured engine events. It’s amazing!

1) See what a running query is doing

Remember that query that’s been running for five minutes? You know your options in PostgreSQL. In-flight analysis isn’t really on the menu. In Azure SQL, however, you can inspect its execution plan while it’s still running. SQL’s sys.dm_exec_requests is amazing because you can see exactly what queries are doing right now, how long they’ve been running, what resources they’re waiting for, and whether another session is blocking them, all through a simple SQL query.

SELECT
    r.session_id,
    r.status,
    r.wait_type,
    r.cpu_time,
    r.total_elapsed_time,
    q.query_plan
FROM sys.dm_exec_requests AS r
CROSS APPLY
    sys.dm_exec_query_statistics_xml(r.session_id) AS q
WHERE r.session_id <> @@SPID;

sys.dm_exec_query_statistics_xml returns an in-flight plan containing operator-level runtime statistics, allowing you to investigate how many rows operators have processed and where execution time is accumulating. You don’t have to cancel the query or reproduce the workload; you can inspect the execution already happening.

2) See how a query actually executed

What if the query already finished? Azure SQL supports sys.dm_exec_query_plan_stats, which retrieves the last known actual execution plan for a cached query when the feature is enabled. SQL’s last known actual execution plan is amazing because you can see how a query actually executed, not just how the optimizer expected it to execute, even after the query has finished, without having to run it again.

ALTER DATABASE SCOPED CONFIGURATION
SET LAST_QUERY_PLAN_STATS = ON;

Then retrieve available last actual plans from the plan cache.

SELECT TOP (10)
    qs.execution_count,
    qs.total_worker_time,
    qs.total_elapsed_time,
    p.query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY
    sys.dm_exec_query_plan_stats(qs.plan_handle) AS p
ORDER BY qs.total_elapsed_time DESC;

Estimated plans tell you what the optimizer expects; actual plans can show what really happened. As any DBA knows, the difference matters, especially when estimates and actual row counts diverge. These SQL plans can contain actual operator row counts, execution timing, memory-grant information, and spill warnings. Provided the feature is enabled and the plan is still cached, you can investigate how a completed query executed without running it again.

3) Analyze accumulated wait statistics

Sometimes a query isn’t slow because it’s doing too much work. It’s slow because it’s waiting for locks, I/O, memory, or another resource. When investigating a slow query, I like to start with what SQL is waiting for. Is it blocked by another session? Waiting on I/O? Starved for memory? sys.dm_exec_requests shows the current wait for an active request, while sys.dm_os_waiting_tasks can expose the waiting tasks. Azure SQL also accumulates wait counts and durations natively, allowing you to investigate recurring contention.

SELECT
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    signal_wait_time_ms
FROM sys.dm_db_wait_stats
ORDER BY wait_time_ms DESC;

sys.dm_db_wait_stats is available in both Azure SQL Database and Azure SQL Managed Instance, with Managed Instance also exposing sys.dm_os_wait_stats for broader instance-level investigation.

Reading wait statistics

Sampling is a legitimate way to investigate contention, sure, and PostgreSQL extensions such as pg_wait_sampling can provide wait histories and aggregated samples. The distinction is native availability, not that sampling is inferior. Azure SQL’s counters are cumulative, so meaningful interpretation typically requires comparing measurements over time. These totals help identify broader patterns; they do not tell you what a particular query is waiting for right now. For that, start with the active request and its waiting tasks.

4) See where query memory goes

Once waits point you toward memory pressure, this is where things get interesting. SQL’s sys.dm_exec_query_memory_grants is amazing because you can see exactly how much memory a query requested, how much it received, how much it actually used, and whether it’s waiting for more. SQL’s engine uses memory grants for operations like sorting and hashing, and this DMV lets you inspect those decisions directly instead of guessing where your memory is going.

SELECT
    session_id,
    requested_memory_kb,
    granted_memory_kb,
    required_memory_kb,
    used_memory_kb,
    max_used_memory_kb,
    wait_time_ms
FROM sys.dm_exec_query_memory_grants;

How much memory did a query request? How much did it receive? How much did it use? Is it waiting for a grant? The DMV exposes these values and do not require you to infer them from symptoms.

It’s an architectural difference

Memory grants are an architectural choice, not a feature PostgreSQL forgot about. Azure SQL queries can queue for grants or receive excessive allocations, creating challenges PostgreSQL handles very differently. The operational advantage is visibility into engine decisions through SQL’s built-in DMV. This view doesn’t diagnose every spill or every kind of memory consumption, and Azure SQL Database restricts some information for tenant isolation.

5) Extended Events capture deadlocks, et al

Sometimes you need more than a snapshot of what’s happening right now. You need to capture what’s happening over time. Azure SQL’s Extended Events are amazing because you can capture exactly what’s happening inside the engine, including query execution, waits, contention, and deadlocks, with detailed diagnostics you can analyze later. It’s a native, lightweight framework that lets you collect the evidence you need without constantly monitoring the database. Azure SQL Database supports database-scoped sessions right out of the box.

Collecting deadlock reports

For example, Azure SQL Database supports the sqlserver.database_xml_deadlock_report event. Its Deadlocks diagnostic log category can also include deadlock_xml_s, containing the XML deadlock graph. Administrators configure diagnostic settings to send this to Log Analytics, Azure Storage, or Event Hubs, or create a targeted Extended Events session. Pretty cool!

6) Missing-index recommendations

Honestly, the stuff we’ve covered is amazing, sure, but sys.dm_db_missing_index_details is out of this world! Azure SQL’s execution plans warn you about implicit conversions, inefficient memory grants, and spills. But SQL’s optimizer actually recommends indexes to improve your queries, including which columns to index and estimates of their potential benefit. This DBA assistance is based on actual query activity, not just your static schema information.

SELECT TOP (10)
    d.statement AS TableName,
    d.equality_columns,
    d.inequality_columns,
    d.included_columns,
    s.user_seeks,
    s.user_scans,
    s.avg_user_impact,
    s.avg_total_user_cost
FROM sys.dm_db_missing_index_details AS d
INNER JOIN sys.dm_db_missing_index_groups AS g
    ON d.index_handle = g.index_handle
INNER JOIN sys.dm_db_missing_index_group_stats AS s
    ON g.index_group_handle = s.group_handle
WHERE d.database_id = DB_ID()
ORDER BY
    s.avg_total_user_cost
    * s.avg_user_impact
    * (s.user_seeks + s.user_scans) DESC;

The result of that query identifies proposed index columns, estimated query-cost improvements, and how frequently the optimizer identified opportunities to use those indexes. You’re smart. You’re already thinking about how you might automate some of this magic, huh? That’s what everybody eventually figures out!

Evaluate index recommendations

These are optimizer estimates, not measured performance improvements. The user_seeks and user_scans values count potential usage opportunities, not actual accesses to an index that doesn’t exist. Recommendations still require your judgment! Make a plan to evaluate overlapping indexes, write overhead, storage costs, and workload-wide impact. But notice how far down the road you already are already, thanks to SQL’s recommendations.

Here’s how it works

Obviously, the real benefit isn’t any single DMV. It’s how these capabilities work together. Imagine a production query taking much longer than expected. Start with sys.dm_exec_requests to identify the request and its current wait. Use sys.dm_os_waiting_tasks to investigate waiting tasks and blocking, and retrieve the SQL text to see exactly what’s running. Then inspect the live execution plan, examine memory grants when the waits point to memory pressure, review execution warnings, and evaluate optimizer recommendations. You can ask remarkably specific diagnostic questions using ordinary T-SQL. Pretty neat stuff!

Of course, these aren’t universal victories.

The truth is PostgreSQL has excellent native I/O, locking, vacuum, cumulative activity, spill, and execution-plan diagnostics. Self-managed PostgreSQL also offers operating-system visibility unavailable to Azure SQL Database users, while managed PostgreSQL services add their own monitoring capabilities. For investigating execution behavior, memory allocation, wait statistics, and internal resource contention, however, Azure SQL provides an unusually broad set of diagnostics natively, reducing the need to assemble independently configured components.

You are not alone

There are so many Microsoft MVPs and SQL experts out there who have written and collected incredibly useful scripts to help you along the way. It’s worth tapping into the vast SQL ecosystem of good people. I think you’ll discover a treasure trove of resources and a community of helpful professionals happy to point you in the right direction. Of course, you have Microsoft Learn and our full breadth of documentation, but sometimes a person or an agent can understand your unique circumstances faster and help you find exactly what you need.

Congratulations

Let me welcome you again to SQL! I hope it’s clear that you’ve joined a rich and thoughtful ecosystem.

PostgreSQL provides excellent operational statistics and a mature extension ecosystem. Azure SQL goes further in several important areas, exposing execution plans, memory grants, waits, and structured diagnostic events directly through an integrated T-SQL interface. And don’t forget all the tooling! The next time your application slows down, you have more than a collection of counters. You have a window into what the database engine is actually doing, and that visibility can make production problems considerably easier to investigate.

Happy querying!

Microsoft Learn references

• Dynamic Management Views and Functions, Live Query Statistics, and Last Actual Execution Plans.
• Query Memory Grants and Wait Statistics.
• Deadlock Diagnostics and Diagnostic Telemetry.
• Missing-Index Group Statistics.