Log Forced Plans That Automatic Tuning Reverts with XEvents
When a query regresses, automatic plan correction forces the last known good plan and then keeps checking it. The script below creates an Extended Events session that writes each check ending in an unforced plan to an XEL file on disk, one event per reverted correction. Purpose and Overview A plan that automatic plan …
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 MoreCatch Memory Grant Waits via sys.dm_exec_query_memory_grants
Oct 17, 2026 / · 10 min read · sql server sys.dm_exec_query_memory_grants sys.dm_exec_query_resource_semaphores sys.dm_exec_requests sys.dm_exec_sql_text sys.dm_os_memory_clerks sys.dm_exec_query_plan sys.dm_os_wait_stats sys.messages sys.dm_os_sys_info memory grants resource semaphore performance tuning scripts ·Finding the one query stuck behind a RESOURCE_SEMAPHORE wait starts with a plain fact most dashboards gloss over: a query waiting on a memory grant isn't failing yet — it's queued, holding a worker thread while SQL Server decides who gets the next slice of execution memory. If that wait outlasts the query's own timeout …
Read MoreSpot Low-Memory Conditions with sys.dm_os_process_memory
sys.dm_os_process_memory is SQL Server's process-level counterpart to the operating system's own memory counters — a single row reporting exactly how much of the machine's memory space the sqlservr.exe process is holding, down to large-page allocations, committed virtual address space, and two flags that fire the …
Read MoreDecode PLE and Buffer Cache Hit Ratio via cntr_type Codes
Sep 6, 2026 / · 10 min read · sql server sys.dm_os_performance_counters page life expectancy buffer cache hit ratio cntr_type buffer manager sys.dm_os_sys_info sys.dm_exec_requests numa monitoring scripts ·How many seconds should a data page sit in the buffer pool before SQL Server decides it isn't needed anymore, and where does that number actually come from? Page life expectancy is one row among hundreds returned by sys.dm_os_performance_counters, and reading it correctly — along with its neighbor, buffer cache hit …
Read MoreSQL Server Max Server Memory Calculator Script
Every SQL Server installation ships with max server memory set to its default value of 2,147,483,647 MB — effectively unlimited — which allows the Buffer Pool to consume all available RAM and starve the operating system of the memory it needs for network stack, disk I/O, and other critical processes. This script reads …
Read MoreSQL Server CPU Utilization History Report
When a CPU alarm fires on a SQL Server host, the first question is whether the database engine caused it or something else on the machine did. The scheduler monitor ring buffer keeps a short history of exactly that split, and a single query turns it into one row per sample: SQL Server CPU, everything-else CPU and total …
Read MoreSQL Server MAXDOP Recommendation Script
Apr 17, 2026 / · 8 min read · sql server maxdop performance sp_configure numa sql scripts database administration cpu configuration sys.configurations sys.dm_os_schedulers sys.dm_os_sys_info ·MAXDOP (max degree of parallelism) caps how many processors SQL Server uses for a single parallel query plan. The default value of 0 lets SQL Server use all available processors, up to 64, which Microsoft says is not the recommended value for most cases. This script reads the CPU and NUMA layout from system DMVs, …
Read MoreSQL Server Wait Statistics Report: dm_os_wait_stats
This T-SQL script reads sys.dm_os_wait_stats, removes the wait types that only record idle background threads, and ranks what is left by its share of the remaining wait time. The top rows show which resource the instance has spent the most time waiting for since the engine started or the counters were last reset. …
Read More