Roadmap › 2. Database, ETL & BI › Step 29
SQLi
SQL Server Internals — How the Database Engine Really Works
The SQL Server architecture (protocol layer, relational engine, storage engine, SQLOS), the life of a query from TDS packet to result, pages, extents and allocation maps, heaps vs clustered tables and B+ tree index structure, row layout, statistics and cardinality estimation, the query optimizer and plan cache, parameter sniffing, execution plan operators and join algorithms, buffer pool and memory grants, the transaction log, write-ahead logging, checkpoints and recovery, locking, latches, isolation levels, row versioning and tempdb, deadlocks, waits and DMVs, columnstore and In-Memory OLTP.
Interview priority:★ Must know High priority — asked in almost every interview, learn firstMedium asked regularlyLow good to know
Module 1: Architecture and Storage
- 1.Engine components and SQLOS — schedulers, workers and waitsIntermediate ★ Must know
- 2.Pages, extents, files and allocation mapsIntermediate ★ Must know
- 3.Heaps, clustered and nonclustered indexes — B+ tree internalsIntermediate ★ Must know
Module 2: Query Processing
- 4.Statistics, cardinality estimation and the query optimizerAdvanced ★ Must know
- 5.Plan cache, parameter sniffing and Query StoreAdvanced ★ Must know
- 6.Reading execution plans — operators and join algorithmsAdvanced ★ Must know
- 7.Memory — buffer pool, plan cache and memory grantsIntermediate ★ Must know
Module 3: Transactions, Logging and Concurrency
- 8.The transaction log, write-ahead logging, checkpoint and recoveryAdvanced ★ Must know
- 9.Locking, latches, isolation levels and row versioningAdvanced ★ Must know
- 10.Deadlocks, tempdb and how they relate to app designIntermediate ★ Must know
- 11.Columnstore, In-Memory OLTP and other storage optionsAdvanced Medium
Next course in the roadmap: 30. ETL & Data Warehousing Concepts →