SQL Server Performance for Developers

Course details

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.

Learning objectives
Query execution
How to read an execution plan
What not to do with SQL Server
Why cursors are bad
Why scalar UDFs are expensive
Datatypes and design
Indexing
What is a columnstore index?
Transaction isolation

Concepts

0. Introduction

01 - Welcome
02 - What you should know

1. Query Execution

03 - Execution plans introduction
04 - How to read an execution plan
05 - Execution plan XMLs
06 - Key lookup - When it's good and when it's bad
07 - Nested loop vs. hash join
08 - Query Store
09 - Stored procedures vs. dynamic SQL
10 - Missing index warnings

2. What Not to Do with SQL Server

11 - Why cursors are bad
12 - Why while loops are cursors
13 - What to do instead of cursors
14 - Why scalar UDFs are expensive
15 - Views and vested views
16 - Why stored procedures are better than views for reports

3. Datatypes and Design

17 - The importance of datatypes
18 - GUIDs vs. INT BIGINT
19 - Unicode data
20 - The perils of storing files in your database
21 - What datatype conversions do to your query performance

4. Temporary Objects

22 - Temp tables
23 - Table variables
24 - In-memory temp tables

5. Indexing

25 - Choosing a PK or clustered index key
26 - Why you want a clustered index
27 - Where you might not want a clustered index
28 - Overview of nonclustered indexes
29 - Included column explanation
30 - Filtered indexes

6. Columnstore

31 - What is a columnstore index
32 - Clustered columnstore and DW
33 - Nonclustered columnstore - Why
34 - Using filtered nonclustered columnstore for HTAP

7. Isolation Levels

35 - Transaction isolation
36 - RCSI - Why it's good
37 - Implicit transactions - Why they're terrible

Conclusion

38 - Next steps