What this book covers
Chapter 1, Understanding Query Processing, introduces T-SQL query optimization and execution essentials: how does SQL Server optimize and execute T-SQL? How does SQL Server use parameters? Are parameters an advantage? When and why does SQL Server cache execution plans for certain T-SQL statements but not for others? When is that an advantage and when is it a problem? This is information that any T-SQL practitioner needs to keep as a reference for proactive T-SQL query writing, as well as reactive troubleshooting and optimization purposes. This chapter will be referenced throughout the Execution Plan-related chapters, as we link architectural topics to real-world uses.
Chapter 2, Mechanics of the Query Optimizer, introduces T-SQL query optimization internals and architecture, starting with the infamous Cardinality Estimation process and its building blocks. From there, you will understand how the Query Optimizer uses that information to produce a just-in-time, good-enough execution plan. This chapter will be referenced throughout the Execution Plan-related chapters, as we bridge architectural topics to real-world uses.
Chapter 3, Exploring Query Execution Plans, shows you how to read and analyze a graphical query execution plan, where to look for relevant performance information in the plan, and how to use the plan to troubleshoot query performance issues.
Chapter 4, Indexing for T-SQL Performance, introduces guidelines to keep in mind for writing T-SQL queries that perform and scale well. Some basics of database physical design structure such as indexes will be covered, as well as how the optimizer estimates cost and chooses access methods based on how the query is written.
Chapter 5, Writing Elegant T-SQL Queries, reveals various common T-SQL patterns and anti-patterns, specifically those that should be easily identifiable just by looking at the T-SQL construct. This chapter will have more of a cookbook structure. For each of the patterns, we will show a T-SQL example that contains the pattern, learn how to rewrite the query to avoid the pattern, and examine query execution plans before and after the change to show improved performance.
Chapter 6, Discovering T-SQL Anti-Patterns in Depth, reveals various common T-SQL patterns and anti-patterns that require some more in-depth analysis to be identified – the proverbial elephant in the room. This chapter will also follow the cookbook structure introduced in Chapter 5, Writing Elegant T-SQL Queries.
Chapter 7, Building Diagnostic Queries Using DMVs and DMFs, introduces dynamic management views and functions that expose relevant just-in-time information to unlock the secrets of T-SQL execution. It includes real-world examples of how to use these artifacts to troubleshoot different poor performance scenarios, either leveraging snippets provided in this book or in GitHub, and how to build customized scripts.
Chapter 8, Building XEvent Profiler Traces, introduces Extended Events (XEvents), the lightweight infrastructure that exposes relevant just-in-time information from every component of the SQL Database Engine, focused on those related to T-SQL execution. You will get real-world examples of how to use these XEvents to troubleshoot different poor performance scenarios, leveraging collection and analysis tools such as the XEvent Profiler, LogScout, and Replay Markup Language for event analysis, and dropping a note on the infamously deprecated SQL Server Profiler.
Chapter 9, Comparative Analysis of Query Plans, introduces rich-UI tools that ship with SQL Server Management Studio to enable standalone query plan analysis or compare plans from different points in time. It then moves on to visually pinpoint the interesting parts that may provide the key to improving T-SQL query performance and scalability.
Chapter 10, Tracking Performance History with Query Store, introduces a flagship feature: Query Store. This is a practical approach to leveraging what is effectively a flight recorder for your SQL Database Engine T-SQL execution, for the purpose of trend analysis or T-SQL performance troubleshooting and analysis, through rich UI reports that ship with SQL Server Management Studio. Then, you will see how Query Store integrates with the Query Plan Comparison and Query Plan Analysis functionalities for a complete, UI-driven workflow for query performance insights. Lastly, we’ll review some of the SQL Database Engine features that rely on the data collected by Query Store.
Chapter 11, Troubleshooting Live Queries, introduces the profiling infrastructure that exposes real-time query execution plans, which enable scenarios such as production system troubleshooting. You will see a real-world example of how to leverage rich UI tools: Live Query Statistics as a standalone case or as part of the Activity Monitor functionality of SQL Server Management Studio.
Chapter 12, Managing Optimizer Changes, discusses two features – QTA (client-side) and CE Feedback (server-side) – which aim to address some of the most common causes of cardinality estimation (CE)-related performance regressions that may affect our T-SQL queries after an upgrade from an older version of the SQL Database Engine to a newer version.