SQL Server Performance for Developers

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.

Introduction

• Welcome
• What you should know

1. Query Execution

• 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

2. What Not to Do with SQL Server

• 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

3. Datatypes and Design

• 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

4. Temporary Objects

• Temp tables
• Table variables
• In-memory temp tables

5. Indexing

• 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

6. Columnstore

• What is a columnstore index?
• Clustered columnstore and DW
• Nonclustered columnstore: Why?
• Using filtered nonclustered columnstore for HTAP

7. Isolation Levels

• Transaction isolation
• RCSI: Why it's good
• Implicit transactions: Why they're terrible

Conclusion

• Next steps