- Browse
- » Learn T-SQL querying: a guide to developing efficient and elegant T-SQL code
Learn T-SQL querying: a guide to developing efficient and elegant T-SQL code
Average Rating
Author
Publisher
Packt Publishing Ltd
Publication Date
2024.
Edition
Second edition.
Language
English
Description
Loading Description...
Table of Contents
From the eBook - Second edition.
Cover
Title Page
Copyright and Credits
Foreword
Contributors
Table of Contents
Preface
Part 1: Query Processing Fundamentals
Chapter 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
Chapter 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
Chapter 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
Chapter 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.
Chapter 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
Chapter 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
Chapter 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
Chapter 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
Chapter 9: Comparative Analysis of Query Plans
Technical requirements
Query plan analyzer
Summary
Chapter 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
Chapter 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
Chapter 12: Managing Optimizer Changes
Technical requirements
Understanding where QTA and CE feedback are needed
Understanding QTA fundamentals
Exploring the QTA workflow
Summary
Index
Other Books You May Enjoy.
Excerpt
Loading Excerpt...
Author Notes
Loading Author Notes...
More Details
Contributors
Lahoud, Pam author
Staff View
Loading Staff View.