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.
Course info
Rating
(218)
Level
Beginner
Updated
Jun 23, 2016
Duration
3h 21m
Table of contents
Getting Started
Analyzing SQL Statements for Performance
Building Effective Indexes
Finding Performance Bottlenecks in SQL Server
Capturing What Your Application is Doing Inside SQL Server
Applying Common Performance Practices
Description
Course info
Rating
(218)
Level
Beginner
Updated
Jun 23, 2016
Duration
3h 21m
Description

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.

About the author
About the author

David Berry is a software engineer with over 15 years of experience developing applications in languages such as Java and C#. Throughout his career, he has worked extensively with enterprise database systems including Oracle and SQL Server.

More from the author
More courses by David Berry
Section Introduction Transcripts
Section Introduction Transcripts

Building Effective Indexes
Hello. My name is David Berry. Welcome to this module on Building Effective Indexes. Probably the most important step you can take to make sure the SQL in your application performs well is to create effective indexes on the tables in your database, and that is exactly what we are going to cover in this module. We are going to start off with a refresher on index terminology just to make sure some definitions are fresh in your mind as we go through this module. Second, we'll discuss what columns in your database you want to index and why. Then, we're going to turn our attention to two characteristics you need to pay attention to in order to make sure that your indexes are effective, and those are the order of the columns in the index and the selectivity of your indexes. We'll move on and talk about covering indexes and include columns and when those are useful, and then we'll discuss the effects of using a function in your WHERE clause, and how this affects if SQL Server can use an index for your statement. After that, we'll turn our attention to over-indexing, which is when too many indexes are created on a table, which slows down DML statements like inserts and updates against the table. And then we'll wrap up with a discussion about the index recommendations provided to you by SQL Server, and some advice on how to interpret those. We have a lot of material to cover, so let's jump right in and review some index terminology.

Applying Common Performance Practices
Hello. My name is David Berry. Welcome to this module on Applying Common Performance Practices. In the other modules of this course, we have concentrated on what is happening inside of SQL Server. In this module, our focus is a little different, in that we are going to go through some common performance practices that you want to apply in your application code. First, we'll talk about why you want to make sure to use parameterized SQL when you write your data access code. Then we'll talk about if it really is faster to use stored procedures instead of including your SQL directly inside of your application. Next, we'll talk about commit frequency and how that can affect the performance of your data access layer. Then we'll talk a little bit about Object Relation Mappers, or ORMs. First we'll talk about how using an ORM can make what is happening inside of SQL Server a little less visible, and what you can do about it. And finally, we'll wrap up by talking about the N+1 selects problem, which is a commonly encountered performance problem when using ORMs, but fortunately is easy to resolve. So let's get started by talking about why it is important to parameterize the SQL in your application.