Dealing with Very Large Tables in SQL Server
summary
Very large tables (10M–20B rows, multi‑TB) turn small mistakes into huge costs: an unindexed 20B‑row, 4.6TB table returns 3 rows with a 28‑minute, 72M logical‑read scan; functions, arithmetic on predicates, and implicit type conversions cause index scans; rebuilding indexes blindly and running default stats on huge tables wastes time. Use persist_sample_percent with a higher sample (≥1–5%, 5% for multi‑TB), mark stats NORECOMPUTE, enable AUTO_UPDATE_STATISTICS_ASYNC, precreate stats before rollout, and prefer filtered stats or manual updates instead of default sampling.
AI-written summary · about
chapters (13)
- 0:00Large tables in SQL Server
- 1:09Defining very large tables
- 3:57Challenges with large tables
- 7:28Missing indexes cost
- 9:57Query writing impact
- 14:10Cardinality estimation issues
- 24:33Fixing cardinality issues
- 28:44Auto update stats async
- 35:38Auto create stats risks
- 39:13Querying log tables
- 42:31Ascending key problem
- 46:18Identity column changes
- 50:33Q&A
related talks
51mDealing with Very Large Tables in SQL Server- 51mDealing with Very Large Tables in SQL Server
54mLessons learned from refactoring multi terabyte databases for the last 10 years
53mWhen to stop tuning a query- 53mWhen to stop tuning a query
50mKeeping historical data in tables forever – mission (im)possible!- 54mLessons learned from refactoring multi terabyte databases for the last 10 years
27mMaintaining Large Tables in PostgreSQL