Azure SQL operational statistics for PostgreSQL developers
This installment of Microsoft's PostgreSQL-to-SQL field notes compares how each engine helps you diagnose slow queries. It shows which Azure SQL dynamic management views and functions expose in-flight plans, last actual plans, wait statistics and memory grants without preinstalled extensions. Developers moving data access code or troubleshooting Azure SQL performance will find the setup details useful.
Key points
- Inspect a running query with sys.dm_exec_requests and sys.dm_exec_query_statistics_xml without cancelling it.
- Enable LAST_QUERY_PLAN_STATS with ALTER DATABASE SCOPED CONFIGURATION to keep last actual plans available.
- sys.dm_exec_query_plan_stats only returns plans that are still cached; enabling the feature does not recover past executions.
- sys.dm_db_wait_stats reports cumulative wait counts and durations by type, so compare snapshots over time.
- sys.dm_exec_query_memory_grants shows requested, granted and used memory for each query waiting or running.
- Some Azure SQL Database tiers may require elevated permissions to read in-flight query statistics.
This installment of Microsoft's PostgreSQL-to-SQL field notes compares how each engine helps you diagnose slow queries. PostgreSQL has mature cumulative statistics and EXPLAIN ANALYZE, but inspecting another session's running query or capturing completed plans typically needs extra configuration, extensions or external monitoring. Azure SQL exposes these capabilities natively through dynamic management views and functions.
Four areas stand out. sys.dm_exec_requests combined with sys.dm_exec_query_statistics_xml returns an in-flight plan with operator-level row counts. After enabling LAST_QUERY_PLAN_STATS, sys.dm_exec_query_plan_stats returns the last actual plan while it stays cached. sys.dm_db_wait_stats gives cumulative waits by type, and sys.dm_exec_query_memory_grants shows requested versus granted memory. Profiling is on by default, but some tiers need elevated permissions, and cached plans can disappear.
Real-world use
- Finding the bottleneck in a slow API query by inspecting its plan while it runs, without rerunning the workload.
- Checking the last actual plan of a nightly report to see where estimated and actual row counts diverge.
- Tracking wait types during peak hours to spot recurring locking or memory pressure.
- Validating query performance on Azure SQL for an Entity Framework Core app migrated from PostgreSQL.
Why it matters
Where PostgreSQL often needs extensions set up beforehand, Azure SQL exposes much of this data natively, so migrating teams should still plan which settings to enable before an incident.