Who this lesson is for?
This learning section is for just about anyone responsible for the performance of the system. Database administrators, certainly, are targeted because they’re responsible for setting up the systems, creating the infrastructure, and monitoring it over time. Developers are too, because who else is going to generate all the well-formed and highly performant T-SQL code Database developers, more than anyone, are the target audience, if only because that’s what I do for work. Anyone who has the capability to write T-SQL, design tables, implement indexes, or manipulate server settings on the SQL Server system is going to need this information to one degree or another.
The purpose of this lesson is to use as many “real-looking” queries as possible. To do this, we need a “real” database. we could have created one and forced everyone to track down the download. Instead, we chose to use the sample database created by Microsoft, called AdventureWorks2012. This is available through CodePlex (www.codeplex.com/MSFTDBProdSamples). We suggest keeping a copy of the restore handy and resetting your sample database after you have read a couple of topics from this lesson. Microsoft updates these databases over time, so you might see different sets of data or different behavior with some of the queries than what is listed in this lesson. This time we chose AdventureWorks2012 not because it represents a perfect database design but because it suffers from a number of design flaws and data distribution issues that make it more accurately reflect the real world instead of some flawless test case.
In this lesson you will still receive the most benefit by a sequential reading of from SECTION 1 through SECTION 26.
SECTION 1, “SQL Query Performance Tuning,” introduces the iterative process of performance tuning. You’ll get a first glimpse at establishing a performance baseline, identifying bottlenecks, resolving the problems, and quantifying the improvements.
SECTION 2, “Memory Performance Analysis,” starts the process using Performance Monitor metrics and dynamic management objects as mechanisms for collecting information about memory on your systems
SECTION 3, “Disk Performance Analysis,” continues exploring the system of bottlenecks with a chapter dedicated to understanding how to collect metrics on disk performance. You’ll use Performance Monitor and dynamic management objects again as well as add a number of additional T-SQL queries.
SECTION 4, “CPU Performance Analysis,” concludes the system bottleneck discussions with CPU. We’ll also cover some network monitoring, although that is a fairly rare issue within SQL Server, and there’s little a DBA or developer can do about it usually. The tools used are the same as in the preceding sections.
SECTION 5, “Creating a Baseline,” takes the information from all three of the preceding sections and uses it to define a baseline. A baseline represents a known point in your system from which you can compare to understand how performance is changing over time within your system.
Chapter 6, “Query Performance Metrics,” defines the best ways to look “under the hood” and see what kinds of queries are being run on your system. It provides a detailed look at the new Extended Events tools. Several of the most useful dynamic management views and functions used to monitor queries are first identified in this section.
SECTION 7, “Analyzing Query Performance,” walks you through consuming the metrics gathered in the previous chapter and shows various methods available to analyze query performance. You’re introduced for the first time to query execution plans as well as other utilities available within SQL Server for determining which queries are longest running, most frequently called, or in need of tuning.
SECTION 8, “Index Architecture and Behavior,” explains indexes and index architecture. It defines the differences between clustered and nonclustered indexes. It shows which types of indexes work best with different types of querying. Basic index maintenance is also introduced.
SECTION 9, “Index Analysis,” adds to the information from the preceding chapter and supplies more information about the use and functionality of indexes within SQL Server.
SECTION 10, “Database Engine Tuning Advisor,” covers the Microsoft tool Database Engine Tuning Advisor. The chapter goes over in detail how to use the Database Engine Tuning Advisor; you’re introduced to the various mechanisms for calling the tool and shown how it works under real loads.
SECTION 11, “Key Lookups and Solutions,” takes on the classic performance problem, the key lookup, which is also known as the bookmark lookup. This chapter explores various solutions to the lookup operation.
SECTION 12, “Statistics, Data Distribution, and Cardinality,” introduces the concept of statistics. The optimizer uses statistics to make decisions regarding the execution of the query. Maintaining statistics, understanding how they’re stored, learning how they work, and learning how they affect your queries are all topics covered within this chapter.
SECTION 13, “Index Fragmentation,” shows how indexes fragment over time. You’ll learn how to identify when an index is fragmented. You’ll also see what happens to your queries as indexes fragment, and you’ll learn mechanisms to eliminate index fragmentation.
SECTION 14, “Execution Plan Generation,” presents the mechanisms that SQL Server uses to create execution plans. Plan reuse is an important concept within SQL Server. You’ll learn how to identify whether plans are being reused. You’ll get various mechanisms for looking at the cache. This chapter also introduces dynamic management views that allow excellent access to the cache.
SECTION 15, “Execution Plan Cache Behavior,” covers information about how plans move in and out of cache as well as other details about execution plan behaviors including query and plan hash and your ability to reuse execution plans in cache.
SECTION 16, “Parameter Sniffing,” explains the extremely helpful process running automatically within SQL Server called parameter sniffing. But, parameter sniffing can go bad and cause serious performance issues. The problem, and the solutions, all go back to system statistics.
SECTION 17, “Query Recompilation,” displays how and when SQL Server will recompile plans that were stored in cache. You’ll learn how plan recompiles can hurt or help the performance of your system. You’ll pick up mechanisms for forcing a recompile and for preventing one.
SECTION 18, “Query Design Analysis,” reveals how to write queries that perform well within your system. Common mistakes are explored, and solutions are provided. You’ll learn several best practices to avoid common bottlenecks.
SECTION 19, “Reduce Query Resource Use,” demonstrates various methods to ensure you’re using fewer resources such as CPU and I/O when running your queries. You’ll learn about a number of antipatterns that you should avoid while writing your T-SQL.
SECTION 20, “Blocking and Blocked Processes,” teaches the best ways to recognize when various sessions on your server are in contention for resources. You’ll learn how to monitor for blocking along with methods and techniques to avoid blocked sessions.
SECTION 21, “Causes and Solutions for Deadlocks,” shows how deadlocks occur on your system. You’ll get methods for identifying sessions involved with deadlocks. The chapter also presents best practices for avoiding deadlocks or fixing your code if deadlocks are already occurring.
SECTION 22, “Row-by-Row Processing,” diagrams the inherent costs that cursors present to set-oriented T-SQL code. However, when cursors are unavoidable, you need to understand how they work, what they do, and how best to tune them within your environment if eliminating them outright is not an option.
SECTION 23, “Memory-Optimized OLTP Tables and Procedures,” introduces the new capabilities of in-memory data storage and retrieval. You’ll also see how the in-memory stored procedure can radically change performance in a positive fashion. But, this technology isn’t universally applicable, so I’ll also go over some of the limitations and best practices for applicability.
SECTION 24, “Database Performance Testing,” provides you with mechanisms to replicate the performance of your production system onto test systems in order to help you validate that the changes you’ve introduced to your queries really are helpful. You’ll be using the Distributed Replay utility, introduced in SQL Server 2012, along with all the other tools you’ve been using throughout the book.
SECTION 25, “Database Workload Optimization,” demonstrates how to take the information presented in all the previous chapters and put it to work on a real database workload. You’ll identify the worst-performing procedures and put them through various tuning methods to arrive at better performance.
SECTION 26, “SQL Server Optimization Checklist,” summarizes all the preceding chapters into a set of checklists and best practices. The goal of the chapter is to enable you to have a place for quickly reviewing all you have learned from the rest of the book.