Hear me out - Don't go offline because of your index deployment strategy
summary
A production table goes slow and CPU spikes after a deployment that drops an index then recreates it offline; the mistake is non-atomic drop/create and table locks. Fixes demonstrated: create the new index side-by-side or use DROP_EXISTING=ON to do a single safe swap, enable ONLINE=ON to avoid long table locks, and use RESUMABLE=ON plus MAX_DURATION and MAXDOP to pause/resume long builds and allow log truncation; compression reduced page count ~50% in the example and cut I/O.
AI-written summary · about
chapters (15)
- 0:00Index deployment risks
- 1:24Index deployment disaster case
- 2:51Original deployment script flaws
- 3:35Deployment options
- 5:01DROP EXISTING method
- 7:13Data compression benefits
- 9:23ONLINE deployment trade-offs
- 11:13MAXDOP setting
- 11:53RESUMABLE index creation
- 14:27MAXDURATION with resumable
- 15:09LOW_PRIORITY option
- 16:37Correcting deployment approach
- 17:41Recommended strategy
- 18:01Database config for indexes
- 19:05Recovery benefits
related talks
20mHear me out - Don't go offline because of your index deployment strategy
40mrails db:migrate:safely
53mIndexing Internals for Developers & DBAs
34mZero Downtime Database Migrations and Deployments
20mWhat Playing Minecraft Taught Me About DevOps- 53mIndexing Internals for Developers & DBAs
32mZero Downtime Deploys Made Easy
58mTroubleshooting CPU Performance Issue for SQL Developers