Review concepts and strategies to help you enhance your app's performance on Microsoft SQL Server. Discover best practices for building efficient, scalable database applications.
Overview
Syllabus
Introduction
- Welcome
- What you should know
- Execution plans introduction
- How to read an execution plan
- Execution plan XMLs
- Key lookup: When it's good and when it's bad
- Nested loop vs. hash join
- Query Store
- Stored procedures vs. dynamic SQL
- Missing index warnings
- Why cursors are bad
- Why while loops are cursors
- What to do instead of cursors
- Why scalar UDFs are expensive
- Views and vested views
- Why stored procedures are better than views for reports
- The importance of datatypes
- GUIDs vs. INT/BIGINT
- Unicode data
- The perils of storing files in your database
- What datatype conversions do to your query performance
- Temp tables
- Table variables
- In-memory temp tables
- Choosing a PK or clustered index key
- Why you want a clustered index
- Where you might not want a clustered index
- Overview of nonclustered indexes
- Included column explanation
- Filtered indexes
- What is a columnstore index?
- Clustered columnstore and DW
- Nonclustered columnstore: Why?
- Using filtered nonclustered columnstore for HTAP
- Transaction isolation
- RCSI: Why it's good
- Implicit transactions: Why they're terrible
- Next steps
Taught by
Joey D'Antoni
