SQL Server Performance: Introduction to Query Tuning

SQL Server performance tuning is an art to master - for developers and DBAs alike. This course takes a systematic approach to planning, analyzing, debugging and troubleshooting common query-related performance problems. This includes an introduction to understanding execution plans inside SQL Server.
Course info
Rating
(508)
Level
Intermediate
Updated
Jun 1, 2012
Duration
3h 55m
Table of contents
Introduction
Execution Plan Basics
Essential Indexing Techniques
Query Design for Performance
Performance Tuning Tools
Tips and Tricks
Checklist: Performance Tuning
Description
Course info
Rating
(508)
Level
Intermediate
Updated
Jun 1, 2012
Duration
3h 55m
Description

SQL Server performance tuning is an in-depth topic, and an art to master. A key component of overall application performance tuning is query tuning. Writing queries in an efficient manner, and making sure they execute in the most optimal way possible, is always a challenge. The basics revolve around the details of how SQL Server carries out query execution, so the optimizations explored in this course follow along the same lines.

About the author
About the author

Vinod Kumar has worked with SQL Server extensively since joining the industry over a decade ago. Before joining Microsoft, he was a Microsoft MVP for SQL Server for more than 3 years. He now works with MTC as a Technology Architect. He is a well-known speaker at all major Microsoft and third party technical conferences.

More from the author
SQL Server Performance: Indexing Basics
Intermediate
1h 58m
Mar 5, 2012
SQL Server Questions and Answers
Intermediate
2h 13m
Jan 24, 2012
About the author

Pinal Dave is a Pluralsight Developer Evangelist.

More from the author
Getting Started with MariaDB
Beginner
2h 23m
Nov 2, 2018
Monitoring MySQL with Performance Schema
Intermediate
1h 59m
Apr 28, 2017
More courses by Pinal Dave
Section Introduction Transcripts
Section Introduction Transcripts

Performance Tuning Tools
In this module, we will discuss about the various tools which can help us to identify our performance problem. It is the developer's responsibility to write optimized queries. However, the world is not perfect. There are always instances when server is running fine, and suddenly it will go slow and everything will start going down. When disaster happens, a DBA has a most important task to get the server back online as fast as we can. Identifying an offending query and bottleneck is an extremely important task for any DBA and developer. Once a bottleneck is identified, the next step is to resolve it by tuning the query associated with it. Tools are very important as they help during identifying the query and resource bottleneck very quickly. In this module, we will see how we can use various tools to resolve various issues based on tools available with SQL Server and tools available on the internet. You will see how we can use native tools along with some of the tools created by the community, we can quickly identify various performance issues with SQL Server. Additionally, we will dedicate specific time to the SQL Server wait stats method. This is a great primer to start on SQL wait stats if you have not practiced it yet. We will create a real-world scenario using scripts, and we'll attempt to solve it using multiple optimization techniques. We have taken the best examples from SQL Server Wait Stats book, which I have authored. Vinod, would you like to take over and explain some of the tools which a developer and a DBA should keep handy in our toolbox?

Tips and Tricks
In this module, we will cover SQL Server Performance Tips and Tricks, which we have not covered in any other modules, but often see them prevailing in industry. When server gets slow or when any query is facing performance problem there are always multiple options. The biggest challenge is to select the right option in the critical mission-sensitive environment. There are instances when performance of certain area of the application goes down and complete business is at risk. Customers and clients deserve immediate response, and the best solution. However, there is no right or wrong answer. In this module we will cover some of the tips and tricks based on issues, which we have seen while performance tuning applications. We will see how we can replace the usage of cursor using set-based T-SQL. We will explore SQL Server 2012 column stored index and its advantages. Dynamic SQL is also a challenge, and we have seen quite a lot of people confusing around it. Vinod, would you explain some of the commonly occurring performance problems, which we have not covered so far?