The background job queue holds work that SQL Server runs outside your queries, such as asynchronous statistics updates. This post lists the active jobs, reads the queue counters, catches a real statistics job in a demo and kills it with KILL STATS JOB. It also corrects a common mix-up: the command takes the job ID, not the session ID.
Query Percentage Complete: Watch a Long Query Finish
Query percentage complete is not a column in any view, but sys.dm_exec_query_profiles reports how many rows each operator has produced against the plan’s estimate. This post builds a monitor query for a running statement, explains why a percentage can pass 100 or even run into the thousands, and shows which operators to trust.
Logging Free Space per Volume Into a Table Each Day
Log free space per volume from SQL Server volume metadata, keep one daily sample per mount point, and compare change with LAG.
Connections by Protocol: Shared Memory, TCP and Named Pipes
Connections by protocol tell you whether a client reaches SQL Server through shared memory, TCP or a named pipe, and from which address. This post reads the connection view for your own session, connects three ways to see each protocol, lists every connection with its host and program, and counts them by protocol.
Give Memory Back to the OS? Why SQL Server Doesn’t
This is a guest post by Brian Gale. It explains why SQL Server doesn’t give memory back to the OS, what the buffer pool keeps in that memory, and why max server memory needs a realistic value. Two queries show what is held right now.
How to Find IP Address of All SQL Server Connection? – Interview Question of the Week #280
Question: How to Find the IP Address of All SQL Server Connection?
I had recently received this question during a recent consultation.
Partition Function Fanout: Count Partitions in SQL Server
Count partitions in SQL Server by reading the fanout of a partition function. This post reads fanout and modify_date from sys.partition_functions, watches SPLIT RANGE and MERGE RANGE change them, and finds functions that no table uses.