Slow database creation in SQL Server comes down to its files: how big they are, and whether SQL Server must zero every byte. This post checks the three usual suspects, the size of the model database, instant file initialization and the log file, and times each one with a script. It also shows the wait type to look for while a large CREATE DATABASE runs.
Row and Page Compression: Estimating Savings Before You Compress
Estimate row and page compression savings per index and partition, then compare storage, CPU, reads, and maintenance before deployment.
Out-of-Row Storage for varchar(max): Keeping Data Pages Small
Turn on the out-of-row option and nothing happens until you rewrite the data. I measure pages and reads at each step so you can see the real trade.
FOR JSON Data Types: How bit, Dates and Money Come Out
Your API returns 1 and the client wanted true. Here is how FOR JSON picks a JSON type for bit, dates, decimal, money, binary and NULL, and how to fix it.
Autogrowth Settings: Why 1 MB and 10 Percent Still Hurt
Inspect autogrowth settings across database files, review retained growth events and VLFs, and choose tested fixed-size increments.
Deleting From a Heap: Why Empty Pages Stay Behind
The rows are gone but the table is the same size and scans are just as slow. Here is why, and two ways to fix it.
Append-Only Ledger Tables for Audit Logs That Cannot Be Edited
Can someone quietly edit your audit table? With an append-only ledger and a stored digest, the answer becomes no, and you can prove it.