How to Improve SQL Server Performance

SQL server performance can be tricky. But there are a lot of things you can do to increase the speed of your queries.

We just published a course on the freeCodeCamp.org YouTube channel that will teach you how to improve performance on SQL Server.

Rajan Arora developed this course. Rajan is a very experienced developer and a great teacher.

In this course 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.

Below are the sections in this course.

Getting Started

  • 1.1 Course Introduction
  • 1.2 Why Developers should Understand SQL Performance
  • 1.3 Tools you Need
  • 1.4 Restore the Sample Database
  • 1.5 Table Concept
  • 1.6 Index Concept
  • 1.7 Summary

Analyzing SQL Statements for Performance

  • 2.1 Introduction
  • 2.2 Understanding How SQL Server Will Execute a SQL Statement
  • 2.3 Reading and Interpreting an Execution Plan for a SQL Statement
  • 2.4 Getting Execution Statistics for a SQL Statement
  • 2.5 Improving Statement Performance by Adding an Index
  • 2.6 Rewriting SQL Statements for Improved Performance
  • 2.7 Common Execution Plan Operations
  • 2.8 Summary

Building Indexes

  • 3.1 Introduction
  • 3.2 Index Terminology Refresher
  • 3.3 What Should I Index in My Database?
  • 3.4 Why Index Column Order Matters
  • 3.5 Index Selectivity Explained
  • 3.6 LIKE Clauses and Index Selectivity
  • 3.7 How Functions in the WHERE Clause Affect Indexes
  • 3.8 Include Columns and Covering Indexes
  • 3.9 Over-indexing
  • 3.10 Interpreting SQL Server Index Recommendations
  • 3.11 Summary

Finding Bottlenecks in SQL Server Performance

  • 4.1 Introduction
  • 4.2 Getting Information About SQL Server Sessions and Resource Usage
  • 4.3 Finding What SQL Statements are Currently Executing
  • 4.4 Finding the Slowest, Most Expensive SQL Statements
  • 4.5 Getting SQL Server’s Recommendations on Missing Indexes
  • 4.6 Finding Indexes That are Not Being Used
  • 4.7 Summary

Capturing Trace Logs of Application from SQL Server

  • 5.1 Introduction
  • 5.2 Setting up a SQL Profiler Trace
  • 5.3 Running a SQL Profiler Trace
  • 5.4 Running a Trace as a Server Side Trace
  • 5.5 Introduction to Using Extended Events for SQL Tracing
  • 5.6 Setting up an Extended Events Trace Session
  • 5.7 Running and Configuring the Display Settings for an Extended Events Trace
  • 5.8 Analyzing Extended Events Trace Data
  • 5.9 Using Extended Events in SQL Azure
  • 5.10 Summary

Apply Common Practices for Better Performance

  • 6.1 Introduction
  • 6.2 Use Parameterized SQL
  • 6.3 Are Stored Procedures Faster Than SQL in Application Code?
  • 6.4 Commit Behavior and Performance
  • 6.5 Object Relational Mappers Just Generate SQL
  • 6.6 Solving the N+1 Selects Problem
  • 6.7 Summary

Watch the full course below or on the freeCodeCamp.org YouTube channel.

[embedded content]

Posted by Contributor