What Every Developer Should Know About SQL Server Performance
This course is designed to teach developers the fundamentals of SQL Server performance, showing you the tools you will need to troubleshoot and resolve SQL Server performance problems.
What you'll learn
As a developer, SQL Server performance can be tricky. In this course, What Every Developer Should Know About SQL Server Performance, you will see how to diagnose what is happening with a slow running SQL statement and what strategies are available to make these statements run faster. First, you will be shown how developers should use database indexes throughout their database, including what columns should be indexed and how to make sure a SQL statement will use an index. Next, you'll go over how some built in diagnostic tools in SQL Server can help you find performance issues in your application, including how to identify the slowest running SQL in your application. Then, you will see how to trace all of the SQL that your application is generating inside of SQL Server and how to understand this data. Finally, you'll take a look at practices you can implement inside of your application to insure the best performance possible. By the end of this course, you as an application developer will have the tools you need to troubleshoot performance problems you may encounter when using SQL Server.
Table of contents
- Version Check 0m
- Introduction 2m
- Understanding How SQL Server Will Execute a SQL Statement 3m
- Reading and Interpreting an Execution Plan for a SQL Statement 9m
- Getting Execution Statistics for a SQL Statement 4m
- Improving Statement Performance by Adding an Index 4m
- Before and After Performance Comparison 1m
- Rewriting SQL Statements for Improved Performance 5m
- Common Execution Plan Operations 5m
- Summary 3m
- Introduction 1m
- Index Terminology Refresher 2m
- What Should I Index in My Database? 4m
- Why Index Column Order Matters 5m
- Applying Index Column Order Rules 2m
- Index Selectivity Explained 2m
- Index Selectivity Examples 9m
- LIKE Clauses and Index Selectivity 3m
- How Functions in the WHERE Clause Affect Indexes 4m
- Include Columns and Covering Indexes 4m
- Over-indexing 3m
- Interpreting SQL Server Index Recommendations 2m
- Module Review 5m
- Introduction 3m
- Required Permissions for Querying Dynamic Management Views 2m
- Getting Information About SQL Server Sessions and Resource Usage 3m
- Finding What SQL Statements are Currently Executing 5m
- Finding the Slowest, Most Expensive SQL Statements 4m
- Getting SQL Server's Recommendations on Missing Indexes 5m
- Finding Indexes That are Not Being Used 4m
- Summary 1m
- What is SQL Tracing and Why Developers Should Know About it 5m
- Setting up a SQL Profiler Trace 7m
- Running a SQL Profiler Trace 4m
- Running a Trace as a Server Side Trace 6m
- Introduction to Using Extended Events for SQL Tracing 3m
- Setting up an Extended Events Trace Session 8m
- Running an Extended Events Trace Session 1m
- Configuring the Display Settings for Extended Events Data 2m
- Analyzing Extended Events Trace Data 6m
- Using Extended Events in SQL Azure 4m
- Summary 3m
About the author
David Berry is a software engineer with over 15 years of application development experience. He started developing software in Java 1.0 using an Oracle 7 backend. Making the switch to Microsoft .NET when it was released, he has worked with every version of .NET since. He has also worked with every version of Oracle since Oracle 7 and ever version of SQL Server since SQL Server 7. His experience spans a broad range of industries including semiconductors, financial services, insurance an gove... more