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.
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.
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.