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

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...

Descripción completa

Detalles Bibliográficos
Otros Autores: Lopes, Pedro, author (author), Lahoud, Pam, author
Formato: Libro electrónico
Idioma:Inglés
Publicado: Birmingham, England : Packt Publishing Ltd [2024]
Edición:Second edition
Materias:
SQL server.
SQL (Computer program language)
Client/server computing.
Ver en Biblioteca Universitat Ramon Llull:https://discovery.url.edu/permalink/34CSUC_URL/1im36ta/alma991009805127306719
Tabla de Contenidos:
  • 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.

Ejemplares similares