Introduction to SQL Server Query Optimization Understanding Query Optimization and Built-in Optimization Tools

  • Edward Pollack

Your browser needs to be JavaScript capable to view this video

Try reloading this page, or reviewing your browser settings

You're watching a preview of subscription content. Log in to check access

Begin your journey into query optimization by learning the basics of how SQL Server processes a query from start to finish. Once this foundation is established, you will focus on execution plans: What are they? How do we read them? And what are their more common operations? A variety of statistics options will be introduced that provide insight into query duration and the load that a given query presents on memory and storage resources.

With a set of tools defined, you will tackle a variety of common optimization challenges. You’ll review some common problems in which performance is inadequate and then solutions to mitigate those problems and improve performance. These basic patterns will provide a format for viewing and analyzing queries with the goal of being able to identify the source of the problem when a query is running too slow.

What You’ll Learn

  • Discover how SQL Server optimizes a query and generates an execution plan

  • Enable, read, and decipher execution plans for query analysis

  • Identify a variety of common execution plan operators

  • Use STATISTICS IO to gauge query impact on memory and disk resources

  • Measure query duration using STATISTICS TIME

  • Analyze problem queries to find the source of performance problems

  • Identify common query mistakes and how to quickly resolve them

Who This Video Is For

Database administrators, developers, or anyone who writes TSQL queries on a regular basis. For all database professionals who need to write efficient code that executes quickly while consuming minimal resources.

About The Author

Edward Pollack

Edward Pollack is a data enthusiast and works currently as a senior database administrator at Datto with 20 years of experience in systems and data. He is the author of Dynamic SQL: Applications, Performance, and Security, and writer of many articles on a wide variety of database topics. Ed regularly speaks at SQL Saturday conferences, as well as at user groups, virtual groups, and other local technical events. He has spoken at PASS Summit and continues to find new ways to share his ideas and code with the community. Ed lives in Albany, NY with his wife Theresa and sons Nolan and Oliver. In his free time, he is often found playing video games, travelling, and enjoying absurdly spicy foods.

 

About this video

Author(s)
Edward Pollack
DOI
https://doi.org/10.1007/978-1-4842-5144-7
Online ISBN
978-1-4842-5144-7
Total duration
48 min
Publisher
Apress
Copyright information
© Ed Pollack 2019

Video Transcript

Welcome, and thank you for joining me for an introductory discussion of Query Optimization and SQL Server. This is a massive topic that can, and does, fill books with advice, techniques, and solutions. Today, we will be focusing on the basics of query tuning.

A bit about me. I’ve been interested in technology and computing for most of my life, and data has become my passion as an area where there is always more to learn and new challenges to tackle. I’ve spoken at many events over the years, including SQL Saturdays, PASS Summit, and 24 hours of PASS. In addition, I’ve published a book of Dynamic SQL that just entered its second edition this year. I’ve also written many articles, blog posts, and solutions to common SQL Server problems, and I’ve coordinated SQL Saturday Albany for the past six years.

When data isn’t challenging me, I can be found at home with my family in Albany, New York where I encounter far different challenges in parenting. A big shout out to Theresa, [? Nolan, ?] and Oliver for keeping me on my toes even when I probably should be asleep.

Our goal is to introduce the basic concepts of optimization. To do this, we will define optimization and latency and discuss how a query is processed. With those prerequisites out of the way, we’ll introduce execution plans and statistics IO as invaluable tools that can assist in measuring latency and progress as we tune queries. We’ll then talk about how data is stored in memory, within the buffer cache, for reuse in future queries.

Statistics time will be introduced as a more accurate way to gauge query runtime as we look to quantify progress as we optimize. For the remainder of this series, we will solve common problems such as how to manage unwanted table scans, eliminate implicit conversions, fix bad joins, and remove the latency caused by overly complicated filters.