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.

Developers face an array of challenges when trying to develop performant applications for Microsoft SQL Server. In this course, instructor Joey D'Antoni shares concepts and strategies that can help you enhance your app's performance on SQL Server. Throughout this course, Joey walks through best practices for building efficient, scalable database applications. He covers an array of topics, including execution plans, columnstore indexes, datatypes, temporary objects, and what not to do with SQL Server. He also goes over indexing, and shares how best to create temporary objects.

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

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