Learn T-SQL Querying - Second Edition: A guide to developing efficient and elegant T-SQL code

Data professionals seeking to excel in Transact-SQL (T-SQL) for Microsoft SQL Server and Azure SQL Database often lack comprehensive resources. This updated second edition of Learn T-SQL Querying focuses on indexing queries and crafting elegant T-SQL code, catering to all data professionals seeking mastery in modern SQL Server versions and Azure SQL Database. Starting with query processing fundamentals, this book lays a solid foundation for writing performant T-SQL queries. You'll explore the mechanics of the Query Optimizer and Query Execution Plans, learning how to analyze execution plans for insights into current performance and scalability. Through dynamic management views (DMVs) and dynamic management functions (DMFs), you'll build diagnostic queries. This book thoroughly covers indexing for T-SQL performance and provides insights into SQL Server's built-in tools for expedited resolution of query performance and scalability issues. Further, hands-on examples will guide you through implementing features such as avoiding UDF pitfalls, understanding predicate SARGability, Query Store, and Query Tuning Assistant. By the end of this book, you'll have developed the ability to identify query performance bottlenecks, recognize anti-patterns, and skillfully avoid such pitfalls. Learn T-SQL Querying Foreword Contributors About the authors About the reviewer Preface Who this book is for What this book covers To get the most out of this book Download the example code files Conventions used Get in touch Share Your Thoughts Download a free PDF copy of this book Part 1: Query Processing Fundamentals 1 Understanding Query Processing Technical requirements Logical statement processing flow Query compilation essentials Query optimization essentials Query execution essentials Plan caching and reuse Stored procedures Ad hoc plan caching Parameterization The sp_executesql procedure Prepared statements How query processing impacts plan reuse The importance of parameters Security Performance Parameter sniffing To cache or not to cache Summary 2 Mechanics of the Query Optimizer Technical requirements Introducing the Cardinality Estimator Understanding the query optimization workflow The Trivial Plan stage The Exploration stage The Transaction Processing phase The Quick Plan phase The Full Optimization phase Knobs for query optimization Summary Part 2: Dos and Don’ts of T-SQL 3 Exploring Query Execution Plans Technical requirements What is a query plan? Accessing a query plan Navigating a query plan Query plan operators of interest Blocking versus non-blocking operators Data access operators Joins Spools Sort and aggregation operators Query plan properties of interest Plan-level properties Operator-level properties Summary 4 Indexing for T-SQL Performance Technical requirements Understanding predicate SARGability Data access using indexes Structure of a rowstore index Data access using rowstore indexes Inserting and updating data in a rowstore index Indexing strategy using rowstore indexes Best practices for clustered indexes Best practices for non-clustered indexes Index maintenance Summary 5 Writing Elegant T-SQL Queries Technical requirements Best practices for T-SQL querying Referencing objects Joining tables Using NOLOCK Using cursors The perils of SELECT * Functions in our predicate Deconstructing table-valued functions Complex expressions Optimizing OR logic NULL means unknown Fuzzy string matching Inequality logic EXECUTE versus sp_executesql Composable logic Summary 6 Discovering T-SQL Anti- Patterns in Depth Technical requirements Implicit conversions Avoiding unnecessary sort operations UNION ALL versus UNION SELECT DISTINCT Avoiding UDF pitfalls Avoiding unnecessary overhead with stored procedures Pitfalls of complex views Pitfalls of correlated sub-queries Properly storing intermediate results Using table variables and temporary tables Using Common Table Expressions (CTEs) Summary Part 3: Assembling Our Query Troubleshooting Toolbox 7 Building Diagnostic Queries Using DMVs and DMFs Technical requirements Introducing DMVs Exploring query execution DMVs sys.dm_exec_sessions sys.dm_exec_requests sys.dm_exec_sql_text sys.dm_os_waiting_tasks Exploring query plan cache DMVs sys.dm_exec_query_stats sys.dm_exec_procedure_stats sys.dm_exec_query_plan sys.dm_exec_cached_plans Troubleshooting common scenarios with DMV queries Investigating blocking Cached query plan issues Single-use plans (query fingerprints) Finding resource-intensive queries Queries with excessive memory grants Mining XML query plans Plans with missing indexes Plans with warnings Plans with implicit conversions Plans with lookups Summary 8 Building XEvent Profiler Traces Technical requirements Introducing XEvents Getting up and running with XEvent Profiler Remote collection with SQL LogScout Analyzing traces with RML Utilities Summary 9 Comparative Analysis of Query Plans Technical requirements Query plan analyzer Summary 10 Tracking Performance History with Query Store Technical requirements Introducing the Query Store Inner workings of the Query Store Configuring the Query Store Tracking expensive queries Fixing regressed queries Features that rely on the Query Store Query Store for readable secondary replicas Query Store hinting Parameter Sensitive Plan Optimization Automatic Plan Correction Degree of parallelism feedback Optimized plan forcing Summary 11 Troubleshooting Live Queries Technical requirements Using Live Query Statistics Understanding the need for lightweight profiling Diagnostics available with Lightweight Profiling Activity Monitor gets new life Summary 12 Managing Optimizer Changes Technical requirements Understanding where QTA and CE Feedback are needed Understanding QTA fundamentals Exploring the QTA workflow Summary Index Why subscribe? Other Books You May Enjoy Packt is searching for authors like you Share Your Thoughts Download a free PDF copy of this book Troubleshoot query performance issues, identify anti-patterns in your code, and write efficient T-SQL queries with this guide for T-SQL developersKey FeaturesA definitive guide to mastering the techniques of writing efficient T-SQL codeLearn query optimization fundamentals, query analysis, and how query structure impacts performanceDiscover insightful solutions to detect, analyze, and tune query performance issuesPurchase of the print or Kindle book includes a free PDF eBookBook DescriptionData professionals seeking to excel in Transact-SQL for Microsoft SQL Server and Azure SQL Database often lack comprehensive resources. Learn T-SQL Querying second edition focuses on indexing queries and crafting elegant T-SQL code enabling data professionals gain mastery in modern SQL Server versions (2022) and Azure SQL Database. The book covers new topics like logical statement processing flow, data access using indexes, and best practices for tuning T-SQL queries. Starting with query processing fundamentals, the book lays a foundation for writing performant T-SQL queries. You'll explore the mechanics of the Query Optimizer and Query Execution Plans, learning to analyze execution plans for insights into current performance and scalability. Using dynamic management views (DMVs) and dynamic management functions (DMFs), you'll build diagnostic queries. The book covers indexing and delves into SQL Server's built-in tools to expedite resolution of T-SQL query performance and scalability issues. Hands-on examples will guide you to avoid UDF pitfalls and understand features like predicate SARGability, Query Store, and Query Tuning Assistant. By the end of this book, you‘ll have developed the ability to identify query performance bottlenecks, recognize anti-patterns, and avoid pitfallsWhat you will learnIdentify opportunities to write well-formed T-SQL statementsFamiliarize yourself with the Cardinality Estimator for query optimizationCreate efficient indexes for your existing workloadsImplement best practices for T-SQL queryingExplore Query Execution Dynamic Management ViewsUtilize the latest performance optimization features in SQL Server 2017, 2019, and 2022Safeguard query performance during upgrades to newer versions of SQL ServerWho this book is forThis book is for database administrators, database developers, data analysts, data scientists and T-SQL practitioners who want to master the art of writing efficient T-SQL code and troubleshooting query performance issues through practical examples. A basic understanding of T-SQL syntax, writing queries in SQL Server, and using the SQL Server Management Studio tool will be helpful to get started.