SQL Server Indexing, Statistics, and Parameter Sniffing Solving Performance Challenges by Designing Better Data Structures

  • 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

Dive into performance tuning by learning how data is structured and accessed in SQL Server. Learn from this video how to use execution plans, IO metrics, and query timing to identify problematic queries and trace latency directly to missing or incorrect indexes. Also learn about cardinality and how it affects execution plans and overall query performance. Understanding how indexes and statistics work provides a solid foundation for writing better queries and architecting more effective database objects.

The knowledge from this video helps you to dive further into procedural TSQL and identify why a stored procedure or ad-hoc query can perform unexpectedly badly. This video examines such cases through a discussion of the causes of poor performance in TSQL procedures along with solutions such as parameter sniffing. In addition, the video demonstrates how local variables perform differently from parameters and how plan reuse can benefit performance. When plan reuse harms performance, the correct solutions will be presented, allowing you to permanently solve a performance challenge without the use of hacks or temporary fixes.

What You Will Learn

  • Create effective table indexes with confidence

  • Identify queries where poor indexing is the cause of latency

  • Display and use statistical metrics to troubleshoot performance challenges

  • Optimize and speed up queries that make poor use of indexes

  • Find and resolve parameter sniffing problems in procedural TSQL

  • Identify when ad-hoc TSQL or local variables can negatively affect performance

  • Understand when plan reuse can inadvertently harm performance

Who This Video Is For

Database administrators, developers, and architects who need to write fast and efficient database queries. For database administrators who are asked to troubleshoot slow queries to make them faster. For anyone working against SQL Server who relies upon highly performant queries to perform important tasks.

Get ready to dive into performance tuning and a hands-on walkthrough of how indexing and statistics can be used to speed up slow queries, as well as learn about parameter sniffing and how to prevent it from becoming a problem.

About The Author

Edward Pollack

Edward Pollack is a data enthusiast and currently 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. He 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-5727-2
Online ISBN
978-1-4842-5727-2
Total duration
1 hr 12 min
Publisher
Apress
Copyright information
© Ed Pollack 2020

Related content

Video Transcript

Welcome and thank you for joining me for a discussion of some of the more intricate topics involved in performance tuning. Query optimization is a massive topic. But our focus today will be a set of critical areas that form the foundation for understanding why a query can perform poorly and how to resolve those problems.

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, including SQL Saturdays, PASS Summit, and 24 Hours of PASS. In addition, I’ve published a book on dynamic SQL that entered its second edition this year. I’m also a co-author of Expert T SQL window functions and SQL Server 2019. I’ve 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 at all times.

Our goal is to discuss three core concepts in query optimization: indexing, statistics, and parameterization. To start, we will discuss indexes, both their physical structure and how they’re used to speed up query execution. With an understanding of how indexes work, we will focus on solving common performance challenges using indexes.

Equally important will be the follow up discussion of caution when creating and using indexes as they require valuable resources to create and maintain.

To fully dive into how the query optimizer works, we will introduce cardinality and how it impacts execution plans and performance. A dive into statistics will follow, allowing us to correlate cardinality with the structure used by SQL Server to keep track of approximate RHO counts. Maintenance will also be discussed as an important follow up step to ensure the statistics remain accurate and useful over time.

With an understanding of cardinality, we will wrap up the series with parameterization. Understanding execution plan reuse allows us to identify when parameters sniffing is a problem, and when T SQL may need to be further optimized in order to address related performance problems.

This will provide the basis for understanding the performance of stored procedures and parameterized queries, as well as how to ensure that we do not resort to hacks or shortcuts in an effort to make our code go faster.