Earlier I wrote a blog post about how Data Compression for Performance can help improve the efficiency of SQL Server.
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.
ZIP Codes Stored as int: Lost Zeros and How to Repair Them
An int column turns 01234 into 1234 and never gives the zero back. Here is how to fix the column, and how to repair old rows without guessing.
tempdb-Heavy Queries: Finding Them in Query Store
The tempdb spike is over before you log in. Query Store remembers who caused it, and here is how to ask.
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.