SQL Server: Common Performance Issue Patterns

Learn how to recognize and diagnose more than 35 types of performance problems that can affect SQL Server; applicable to developers, DBAs, and anyone responsible for SQL Server, from SQL Server 2005 onwards
Course info
Rating
(266)
Level
Intermediate
Updated
Nov 21, 2012
Duration
2h 37m
Table of contents
Course Introduction
High-Level Issue Patterns
I/O Patterns
Concurrency Patterns
Memory Patterns
Tempdb Patterns
CPU Patterns
Application Design Patterns
Plan Quality Patterns
Description
Course info
Rating
(266)
Level
Intermediate
Updated
Nov 21, 2012
Duration
2h 37m
Description

There are a wealth of problems that can affect the performance of SQL Server workloads, and this course shows you more than 35 common performance issue patterns. With 17 detailed demos, you're shown how to recognize each pattern along with practical troubleshooting guidance that you can use. The course starts with high-level issues around people and practices, before moving to technical areas like I/O, concurrency, memory, and CPU. The course also has modules on specific areas like tempdb, application design, and factors that can affect query plan quality. This course is perfect for developers, DBAs, and anyone responsible for SQL Server, from complete beginners through to those with more experience who want a fresh way to troubleshoot SQL Server. The information in the course applies to all versions from SQL Server 2005 onwards.

About the author
About the author

Joe Sack is a Principal Program Manager in the SQL Server and Azure SQL Database product team at Microsoft, with a focus on query processing. With over 19 years of experience in the industry, Joe is an author and speaker, specializing in performance tuning and optimization.

More from the author
More courses by Joe Sack
Section Introduction Transcripts
Section Introduction Transcripts

Course Introduction
Joe Sack: Hi, this is Joe Sack from SQLskills. com, and I'm recording this course for Pluralsight. This is the SQL Server: Common Performance Issue Patterns Course, and you're watching the introduction where I'll briefly describe what to expect over the modules that make up this course. When you're working with SQL server, the performance issues that you encounter will be very much dependent on the environment infrastructure, application code, data distribution, and many other factors. In spite of the diversity of configurations, there are specific patterns that can emerge which can be reliably recognized. I've been working with SQL server since 1997, and this course will detail out some of the more common patterns I've seen over the years. I've broken out these patterns into the following categories: high-level issues -- this includes people- and process-related issues and not just technology-related issues, IO issues, concurrency, memory, 10 TB, CPU, application design, and plan quality. And in this course, I'll talk about the performance issue patterns, talk about how you can recognize them, and then also cover recommended areas to investigate and address.

High-Level Issue Patterns
Hi, this is Joe Sack from SQL Skills dot com, and I'm recording this course for Pluralsight. This is the SQL Server Common Performance Issue Patterns Course. And you're watching Module 2 where we'll talk about high level performance issue patterns. Performance issue root causes often aren't technology centric; rather the people and the processes behind the technology can play a significant role when it comes to systemic performance issues. In this module, I'll cover various high-level issues I've seen over the last 15 years including: Organizational vendor bias; issues around relying too heavily on accumulated expertise; making decisions based on missing or incomplete data; not taking a formalized troubleshooting methodology approach; or troubleshooting side effects instead of the root cause; not being mindful of capacity considerations; not enforcing standards; and not using a formalized change control in production environments.

I/O Patterns
Hi. This is Joe Sack from SQLskills. com and I'm recording this course for Pluralsight. This is the SQL Server Common Performance Issue Patterns course and you're watching module 3 where we'll focus on I/O related patterns. It's very common to have SQL Server I/O related performance issues or constraints so in this module we'll cover some of the more common patterns including the discussion of inadequate I/O paths, query execution plans which lead to high I/O activity, checkpoint I/O overhead and then also I/O contention related to collocation of you data basis with other high I/O neighbors.

Concurrency Patterns
Hi, this is Joe Sack from SQLskills. com and I'm recording this course for Pluralsight. This is the SQL Server: Common Performance Issue Patterns Course and you're watching Module 4 where we'll focus on concurrency-related performance issue patterns. In this module, we'll cover patterns related to concurrency and throughput. And the patterns I'll describe will mostly be applicable to OLTP-centric related data tiers. And by OLTP, we're taking about workloads with high volumes of small transactions. We'll cover patterns around long-running transactions, lock escalation, inconsistent object access, issues around object side-effects and concurrency issues related to hint or isolation level misuse or misconfiguration.

Memory Patterns
Hi, this is Joe Sack from SQLSkills. com, and I'm recording this course for Pluralsight. This is the SQL Server: Common Performances Issue Patterns course and you're watching Module 5 where we'll focus on memory-related performance issue patterns. In this module, we'll cover patterns related to memory and SQL Server. This includes a coverage of memory pressure which can manifest as external, internal or virtual pressure. And we will also cover query execution memory contention patterns.

Tempdb Patterns
Joe Sack: Hi. This is Joe Sack from SQLskills. com and I'm recording this course for Pluralsight. This is the SQL server common performance issue pattern scores and you're watching Module Six, where we'll go over tempdb related performance issue patterns. In this module, we'll cover tempdb system database performance issue patterns. This includes the coverage of allocation page contention issues, issues related to row versioning overhead, contention due to collocation with high I/O databases, and query workspace overhead.

CPU Patterns
Joe Sack: Hi, this is Joe Sack from SQLSkills. com, and I'm recording this course for Pluralsight. This is the SQL Server Common Performance Issue Patterns Course, and you're watching module seven where we'll go over CPU-related performance issue patterns. In this module we'll cover CPU performance issue patterns, and this includes, coverage of external CPU pressure patterns, high privilege time, CPU driven by IO, high compilation and recompilation overhead, observer overhead, and parallelism-related CPU bottlenecks.

Application Design Patterns
( Silence ) Joe Sack: Hi, this is Joe Sack from SQLSkills. com, and I'm recording this course for Pluralsight. This is the SQL Server Common Performance Issue Patterns course, and you're watching module eight where we'll go into application design-related performance issue patterns. In this module we'll cover application design-performance issue patterns, and this includes coverage of slow fetching issues, needless queries, over and under architecture issues, and then also, what I call boil the ocean modules where you're trying to do everything at the data tier, and you end up with at TSQL workload that is next to impossible to optimize.

Plan Quality Patterns
( Silence ) Joe Sack: Hi this is Joe Sack from SQLskills. com, and I'm recording this course for Pluralsight. This is the SQL Server Common Performance Issue Patterns course, and you're watching Module 9, where we'll go into plan quality related performance issue patterns. In this module we'll cover planned quality performance issue patterns, and this includes coverage of cardinality estimate issues, where operator estimates are significantly skewed from the actual row count estimates. And also hidden costs where sometimes your query plan isn't telling you the full story of how your resources are being consumed.