Chapter 1, Anatomy of a Query, shows you how to write solid, performant T-SQL. Users will become familiar with how SQL Server runs T-SQL syntax to deliver the intended result sets in a scalable fashion. In this chapter, we'll cover the basic building blocks that make up a T-SQL statement, as well as how SQL Server interprets those blocks to begin the process of executing our queries. The concepts introduced in this chapter will be used throughout the remaining sections of the book to explain most patterns and anti-patterns, as well as mitigation strategies.
Chapter 2, Understanding Query Processing, introduces the fact that the way a T-SQL query is written and submitted to the server influences how it is interpreted and executed by SQL Server. Even before a single T-SQL query is written, the choice of development style (for example, using stored procedures versus ad hoc statements) can have a direct impact on the performance of the application.
Chapter 3, Mechanics of the Query Optimizer, explores the internals of SQL Server query optimization and defines many important concepts that any database professional who writes T-SQL queries will keep coming back to, especially when troubleshooting query performance issues. The Cardinality Estimator (CE) is a fundamental part of SQL Server's Query Optimizer; knowing how it uses statistics, and the importance of keeping updated and relevant statistics for the overall query optimization process, empowers database professionals to write good queries—queries that both drive and leverage good database schema designs.
Chapter 4, Exploring Query Execution Plans, will leave the reader with a good understanding of the various elements that make up a query execution plan in SQL Server. Nearly everything we need to understand and troubleshoot the performance of our T-SQL queries can be found somewhere in the plan, either in the visible part of the plan, or in the properties windows, which we can access by right-clicking the operators.
Chapter 5, Writing Elegant T-SQL Queries, will leave the reader with a better understanding of some of the aspects that database professionals need to keep in mind to write good queries, and how to identify some of the inefficiencies that may surface if the predicates expressed in queries are not supported by a suitable index design. These are all but a part of the intricacies of writing good, scalable T-SQL code.
Chapter 6, Easily-Identified T-SQL Anti-Patterns, covers a few T-SQL anti-patterns, such as SELECT * syntax, OR logic, and functions in our predicates, that are relatively easy to find simply by looking at our T-SQL code and how it is written. The scenarios covered in this chapter are some of the most common examples of patterns that prevent our T-SQL queries from scaling well and maintaining the expected level of performance throughout the lifetime of applications. All are easy to detect, and most have simple workarounds. Therefore, when writing queries, try to avoid these anti-patterns by leveraging some of the techniques we outline here.
Chapter 7, Discovering T-SQL Anti-Patterns in Depth, covers some performance pitfalls that are not always obvious when writing T-SQL queries. Using the knowledge and tools covered in earlier chapters, together with the anti-patterns discussed in this chapter, we should now be able to dig deeper into our query execution plans and uncover issues that have the potential to impact performance and scalability before they reach production.
Chapter 8, Building Diagnostic Queries Using DMVs and DMFs, covers examples of how to use Dynamic Management Views (DMVs) and Dynamic Management Functions (DMFs), which can be a powerful troubleshooting tool when it comes to diagnosing query performance issues. They are lightweight, easy to use, and provide a breadth of information that is useful for zeroing in on performance issues.
Chapter 9, Building XEvent Profiler Traces, introduces the Extended Events (XEvents) engine in SQL Server and how you can leverage XEvent traces to gather detailed data about query execution and performance. While DMVs are great for point in time and cumulative analysis, there are some issues that can only be diagnosed by catching queries and related data in real time. This is where tracing with XEvents is useful. We'll also discuss the various free tools from Microsoft that can be used to quickly and easily configure, capture, and analyze XEvent traces. Together with DMVs, we now have several tools in our toolbelt that can be used to diagnose and troubleshoot the various issues covered in the book.
Chapter 10, Comparative Analysis of Query Plans, covers the rich UI features available in SQL Server Management Studio (SSMS) to make query plan analysis easier. First, Query Plan Comparison that allows us to quickly and easily compare query plans to determine what differences may help explain what changed between two plans. Next, Query Plan Analyzer which allows us to zero in on problem areas in the query plan such as inaccurate cardinality estimates with the click of a button.
Chapter 11, Tracking Performance History with Query Store, covers the important topic of storing query performance statistics in the flight recorder that is the Query Store, which allows us to access query plans and their runtime statistics, along with how they change over time. We can now more easily find resolutions for performance problems. We can easily identify plans that must be tuned, or for quick mitigation, just return to a good known plan that has been stored in Query Store. We'll cover how to use either system views or SSMS to uncover the highest resource-consuming queries executing in our databases and help us quickly find and fix query performance issues that are related to plan changes, which greatly simplifies query performance troubleshooting.
Chapter 12, Troubleshooting Live Queries, covers how Lightweight Profiling together with tools such as Live Query Statistics and Activity Monitor are invaluable tools for troubleshooting and solving query performance issues, namely those queries that take hours to complete, or never do.
Chapter 13, Managing Optimizer Changes with the Query Tuning Assistant, introduces a feature: the Query Tuning Assistant (QTA). QTA aims to address some of the most common causes of cardinality estimation related performance regressions that may affect our T-SQL queries after an upgrade from an older version of SQL Server to a newer version, namely SQL Server 2016 and above.