Recompress Heaps
Heaps, or tables without a clustered index, suffer from a little-known problem when configured with page compression. Page compression only takes place once a page is 100% full, i.e. when no more rows can be…
Compress Big Tables
Storing data in SQL Server can be expensive. Not necessarily because disk is expensive; it isn’t and is generally getting cheaper all the time. Data costs money because it consumes RAM, requires CPU resources to…
How to Corrupt a Database
Microsoft goes to great lengths to ensure data integrity remains its paramount concern with SQL Server data. After all, ACID compliance doesn’t mean much if the underlying data is corrupt. Having said that, there are…
Drives, with Capacity and Free Space
A typical part of the day for many DBAs includes managing available database capacity. Widely accepted best practices include setting a reasonable limit on maximum file sizes, for both data and log files. When a…
What SQL Text is that Session Running?
Use the code below if you have a long, complicated, stored procedure or piece of dynamic SQL running on a server, and you’d like to see exactly which piece of SQL Text, or code, is…
Blocked Process Detection and Analysis
Blocked process detection provides the DBA an excellent tool for improving the apparent responsiveness of SQL Server. This post provides example code for creating a blocked process Extended Events capture session. Also included is a…