Inventory Open Cursors per Session with sys.dm_exec_cursors
Nov 3, 2026 / · 9 min read · sys.dm_exec_cursors sys.dm_exec_sessions sys.dm_exec_sql_text sys.dm_exec_requests sys.dm_exec_xml_handles sql server cursors dmv monitoring tsql tempdb ·If code declares a GLOBAL cursor and never deallocates it, the cursor stays allocated until the connection closes. While it stays open it can hold tempdb space, row locks or both. The collector query below snapshots every cursor on the instance with its properties, state, I/O and idle time, and a short follow-up query …
Read MoreTrack Azure SQL Database Load with sys.dm_db_resource_stats
When an Azure SQL database slows down, the first thing to find out is whether it has hit the limits of its service tier. sys.dm_db_resource_stats answers that with one row for every 15 seconds, even when the database is idle, and keeps about an hour of them. The three queries below return the raw rows, a DTU-style peak …
Read MoreRank Index Contention with sys.dm_db_index_operational_stats
Oct 22, 2026 / · 7 min read · sql server sys.dm_db_index_operational_stats sys.indexes sys.dm_os_wait_stats sys.dm_db_index_usage_stats lock contention row locks blocking indexes performance tuning dmv tsql ·Once sys.dm_os_wait_stats points to locking, the next question is which index the waits belong to. The script below reads sys.dm_db_index_operational_stats for the current database and ranks each user-table index by its number of row-lock waits. Next to each count it returns the share of row-lock requests that had to …
Read MoreMeasure Internal Latch Contention with sys.dm_os_latch_stats
Oct 21, 2026 / · 8 min read · sql server sys.dm_os_latch_stats sys.dm_os_wait_stats sys.dm_os_sys_info sys.dm_pdw_nodes_os_latch_stats latch contention latches wait statistics performance tuning dmv ·When LATCH_* waits show up near the top of sys.dm_os_wait_stats, you know threads are waiting on non-buffer latches. You don't know which engine structure they're waiting for. sys.dm_os_latch_stats splits those waits by latch class. The script below ranks the non-buffer classes that make up the first 95% of latch wait …
Read MoreAge Open Transactions with sys.dm_tran_active_transactions
Aug 2, 2026 / · 10 min read · sql server sys.dm_tran_active_transactions sys.dm_tran_session_transactions sys.dm_exec_sessions long running transactions transaction log blocking monitoring dmv scripts ·A transaction that never commits keeps every log record written after it pinned in place, no matter how much work piles up behind it. sys.dm_tran_active_transactions lists every transaction currently open on the instance, but on its own it only shows a transaction ID and a start time — pairing it with …
Read MoreRank Top Memory Consumers with sys.dm_os_memory_clerks
Jul 20, 2026 / · 8 min read · sql server sys.dm_os_memory_clerks memory clerks memory pressure plan cache xtp query store dmv performance monitoring scripts ·Buffer pool counters explain data cache pressure but go silent the moment memory trouble comes from somewhere else. sys.dm_os_memory_clerks lists every active memory clerk — buffer pool, plan cache, In-Memory OLTP, Query Store, query memory grants, Extended Events — and reports pages_kb per clerk. Grouping those rows …
Read MoreBreak Down Buffer Pool Memory by Database
Jul 12, 2026 / · 8 min read · sql server sys.dm_os_buffer_descriptors buffer pool data cache memory database memory dmv performance monitoring scripts ·sys.dm_os_buffer_descriptors is the DMV that maps SQL Server's data cache down to the individual page level — one row per 8 KB page currently held in the buffer pool, tagged with the database_id and file_id it belongs to. Rolling those rows up with a simple GROUP BY database_id turns millions of individual page entries …
Read MoreMeasure Disk I/O Performance per Database File
Jul 3, 2026 / · 8 min read · sql server sys.dm_io_virtual_file_stats sys.master_files disk io io stall disk latency performance database files dmv monitoring scripts ·I/O latency is where disk bottlenecks hide. SQL Server accumulates every millisecond of file-level read and write wait in sys.dm_io_virtual_file_stats — one row per database file, reset at each service restart. Joining that DMV to sys.master_files replaces raw file IDs with physical paths and file types, and dividing …
Read More