Lessons learned from refactoring multi terabyte databases for the last 10 years
summary
Ten concrete refactoring tactics for multi‑TB databases: use SQL Server in Docker for repeatable dev/test, keep schema changes tiny and fast, put external interfaces under tests, and deploy with ordered versioned scripts. Real numbers: a 3 TB system regained terabytes of free space and cut memory pressure by moving hot columns (10% columns → ~11% more cache, 35% → ~50% cache). Practical moves include partitioned tables and partition views, parallel runs (dual writes, replay events), page‑log deletes, and removing unused indexes/tables — these approaches worked in production.
AI-written summary · about
chapters (19)
- 0:00Intro
- 2:26Development Tools
- 3:39SQL Server Features Which Helps Refactoring
- 6:07User Access Documentation
- 6:57Document Interfaces Through Tests
- 8:51Step-By-Step Examples
- 11:37Deploy Scripts Versioning
- 12:41Benefits of Deploy Scripts
- 14:08Sandboxes
- 17:09An Event Log Before Database
- 23:23Internal Fragmentation
- 27:44Data Migration/Synchronization Techniques
- 33:21Parallel Run
- 36:13External API Pros
- 39:30Constraints
- 40:32Split Database First
- 40:46Split Code First
- 47:04Replace One-To-Many With Associative Table
- 48:59Data Exchange
related talks
54mLessons learned from refactoring multi terabyte databases for the last 10 years- 54mLessons learned from refactoring multi terabyte databases for the last 10 years
51mDealing with Very Large Tables in SQL Server- 51mDealing with Very Large Tables in SQL Server
24mDatabases beyond the tutorials
49mAssessing and Optimize Enterprise SQL Server Environments
37mTransforming Test Data Delivery for Shift Left Automation (Closing Keynote)
49mHidden Gems in SQL Server 2022 Database Engine