Gaps in an identity column come from deletes, rollbacks, failed inserts and the identity cache. This post finds them as ranges with LEAD, catches the tail with IDENT_CURRENT and lists single values with GENERATE_SERIES.
Estimate Compression Savings for Every Table in a Database
To estimate compression savings for every table, loop over the database and call sp_estimate_data_compression_savings with ROW and PAGE. This post ranks tables by space saved and checks one estimate against a real rebuild.
Scalar Subqueries: An Empty Input Produces NULL
Scalar subqueries contribute one value to an outer row. Keep row absence separate from a present NULL and enforce the at-most-one-row contract.
Cached Plan Reuse: How to Tell if a Query Used a Cached Plan
Cached plan reuse is invisible in a result set. This post shows three checks on SQL Server: the compiled and runtime parameter values in the plan, the usecounts column of the plan cache, and the plan creation time.
Login Database Access: List the Databases a Login Can Open
Check login database access by matching the login SID against the users of every online database. One query shows the user, the CONNECT state and the guest back door, and it needs no impersonation.
Get Current Time Zone – SQL in Sixty Seconds #187
Lots of people often ask me how to get the current time zone in SQL Server when I am working on Comprehensive Database Performance Health Check.
Detecting Memory Pressure – SQL in Sixty Seconds #186
After my video on CPU running 100%, many of you asked for one on memory. Here is my SQL in Sixty Seconds video on detecting memory pressure.