Dealing with Very Large Tables in SQL Server

on YouTube ↗51m

" summary

Shows a real 20 billion–row / 4.6 TB table that takes 28 minutes and ~72 million logical reads to return 3 rows when missing an index, so adding indexes must be planned (Enterprise resumable index builds suggested) and rebuilding everything is dangerous. Default sampling causes huge cardinality errors (examples: 44x–130x overestimation); fix by using persist_sample_percent (aim ≥5%), update with NORECOMPUTE, enable AUTO_UPDATE_STATISTICS_ASYNC for large DBs, prefer filtered stats/columnstore tricks, and use ALTER TABLE ... SWITCH to remove identity without copying data when foreign keys allow.

AI-written summary · about

" chapters (13)

1. 0:00Large tables in SQL Server
2. 1:09Defining very large tables
3. 3:57Challenges with large tables
4. 7:28Missing indexes cost
5. 9:57Query writing impact
6. 14:10Cardinality estimation issues
7. 24:33Fixing cardinality issues
8. 28:44Auto update stats async
9. 35:38Auto create stats risks
10. 39:13Querying log tables
11. 42:31Ascending key problem
12. 46:18Identity column changes
13. 50:33Q&A

" related talks

• 51mDealing with Very Large Tables in SQL ServerMilos Radivojevic · SQLBits 2022
• 51mDealing with Very Large Tables in SQL ServerMilos Radivojevic · SQLBits 2019
• 50mKeeping historical data in tables forever – mission (im)possible!Margarita Naumova · SQLBits 2022
• 54mLessons learned from refactoring multi terabyte databases for the last 10 yearsSergey Olontsev · SQLBits 2022
• 53mIndexing Internals for Developers & DBAsBradley Ball · SQLBits 2022
• 53mWhen to stop tuning a queryMilos Radivojevic · SQLBits 2022
• 53mIndexing Internals for Developers & DBAsBradley Ball · SQLBits 2019
• 54mLessons learned from refactoring multi terabyte databases for the last 10 yearsSergey Olontsev · SQLBits 2019