Alert on long-running queries with a five-minute SQL Agent job, a deduplicated log, and one Database Mail notice per new request.
Oldest Statistics in SQL Server: Find the Stale Ones First
To find the oldest statistics in SQL Server, read the last update time and the number of changes since. This post builds a small demo, lists every statistic with its age and percent changed, and shows why a high change count on its own is not a reason to update by hand.
Spinlock in SQL Server: What It Is and How to Read One
A spinlock in SQL Server is a very short lock on an internal structure, and a thread that waits for it keeps spinning on the CPU instead of sleeping. This post explains how a spinlock differs from a latch and a lock. It reads sys.dm_os_spinlock_stats, measures a ten second window and lists the Extended Events that report backoffs.
Sleeping vs Suspended Process – SQL in Sixty Seconds #122
There are so many different statuses of the processes in SQL Server. Let us learn today the difference between Sleeping vs Suspended Process.
Recent Execution of Stored Procedure – SQL in Sixty Seconds #118
My clients were not sure which SPs are the recent SP and which SP is no longer used. Today we will learn about the recent execution of the stored procedure.
Watch a Running Query: Live Plan From Activity Monitor
To watch a running query, open Activity Monitor in SSMS 22, find the query in Active Expensive Queries, and open its live execution plan. It also shows the settings that older versions need and a T-SQL query that reads the same facts.
Statistics That Never Auto Update: Find the NORECOMPUTE Flag
Statistics that never auto update usually carry a hidden NORECOMPUTE flag, set when an index was created. This post builds a demo table, shows the flagged statistic go stale while its neighbor updates itself, and lists every flagged statistic in a database. It also shows which maintenance commands clear the flag and which keep it.