Query Store buckets runtime statistics into fixed time intervals, which means a query that ran steadily all week doesn't produce one row of history — it produces one row per interval, split further by execution outcome. Averaging those rows unweighted is how a "most expensive queries" report quietly gets the ranking …

Buffer pool counters explain data cache pressure but go silent the moment memory trouble comes from somewhere else. sys.dm_os_memory_clerks is the DMV that covers everything the buffer pool doesn't — plan cache, In-Memory OLTP, Query Store, query memory grants, Extended Events sessions — by reporting pages_kb per …

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 …

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 …

A query that ran in milliseconds last month now scans a million rows and spills to tempdb. Nothing changed in the code — but the data grew, and the statistics the optimizer relies on went stale, so its row estimates drifted far from reality and it chose a bad plan. Refreshing statistics is the fix, and knowing how and …

Excessive virtual log files (VLFs) inside the transaction log slow down database recovery, backup, and restore operations — often by minutes on large logs — and the problem compounds silently every time autogrowth fires with a small increment. This script queries DBCC LOGINFO across every online database on the …

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 …

A heap table — a table with no clustered index — stores rows in no particular order, forces full table scans for most non-indexed queries, and accumulates forwarded records every time an UPDATE causes a row to outgrow its page. This script queries sys.tables, sys.indexes, sys.partitions, sys.allocation_units, and …

Foreign keys enforce referential integrity, but SQL Server does not automatically create indexes on the referencing (child) columns. An unindexed foreign key column forces a full table scan every time a join or a DELETE on the parent table triggers a referential check. This script identifies all foreign key columns in …

MAXDOP (max degree of parallelism) controls how many CPU threads SQL Server can use for a single parallel query. The default value of 0 allows SQL Server to use all available processors, which can cause large parallel queries to monopolise the server. This script reads the CPU topology from system DMVs and calculates …