January 2020
Advanced
1h 12m
English
on-demand course
SQL Server Indexing, Statistics, and Parameter Sniffing: Solving Performance Challenges by Designing Better Data Structures
with Ed Pollack
Closed Captioning available in English
Overview
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.
Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Watch now
Unlock full access
More than 5,000 organizations count on O’Reilly
O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.Julian F.
I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.Addison B.
I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.Amir M.
I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.