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 …

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 …

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 …

Knowing how much space each table and each index consumes is the foundation of capacity planning, archive policy, and index cleanup decisions on any SQL Server instance. This T-SQL script reads the partition-level storage statistics out of sys.dm_db_partition_stats and reports the size of every index on a user table — …

Running out of space inside a data or log file halts SQL Server transactions immediately — and discovering that a volume is full only after an outage is too late. This script queries sys.master_files and sys.dm_os_volume_stats across every online database on the instance to report each file's allocated size, space …

TempDB is a shared system database used by every session in SQL Server. When TempDB space grows or performance slows, it can be difficult to know which sessions are responsible. This script queries sys.dm_db_session_space_usage to show a clear breakdown of TempDB consumption per session, including the current SQL …

This script queries sys.dm_exec_requests and sys.dm_exec_sql_text to show all currently executing queries on the SQL Server instance, sorted by how long they have been running, along with CPU time, I/O activity, blocking status, and wait type information. Purpose and Overview A query that normally completes in seconds …

Which databases on this instance hold the most disk, and how large is each one in MB and GB? One query against sys.master_files and sys.databases answers that for every user database, sorted with the largest first. Purpose and Overview Capacity planning starts with a number per database: how much file space each one …

Blocking happens when one session holds a lock that another session needs, so the second session waits. The query below lists every request that is waiting on another session right now: who is blocked, who is blocking, the wait type and wait time, the database, and the SQL text on both sides. Purpose and Overview The …

Checking how much space every table in a database uses means running sp_spaceused once per table, and typing those calls by hand stops being practical after a dozen tables. This one-line query reads the object catalog and writes the sp_spaceused call for every user table, so the whole set can be pasted into a query …