Learn T-SQL Querying

Learn T-SQL Querying

by Pedro Lopes, Pam Lahoud
Released May 2019
Publisher(s): Packt Publishing
ISBN: 9781789348811

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

Troubleshoot query performance issues, identify anti-patterns in code, and write efficient T-SQL queries

Key Features

  • Discover T-SQL functionalities and services that help you interact with relational databases
  • Understand the roles, tasks, and responsibilities of a T-SQL developer
  • Explore solutions for carrying out database querying tasks, database administration, and troubleshooting

Book Description

Transact-SQL (T-SQL) is Microsoft's proprietary extension to the SQL language used with Microsoft SQL Server and Azure SQL Database. This book will be a usefu to learning the art of writing efficient T-SQL code in modern SQL Server versions as well as the Azure SQL Database.

The book will get you started with query processing fundamentals to help you write powerful, performant T-SQL queries. You will then focus on query execution plans and leverage them for troubleshooting. In later chapters, you will explain how to identify various T-SQL patterns and anti-patterns. This will help you analyze execution plans to gain insights into current performance, and determine whether or not a query is scalable. You will also build diagnostic queries using dynamic management views (DMVs) and dynamic management functions (DMFs) to address various challenges in T-SQL execution. Next, you will work with the built-in tools of SQL Server to shorten the time taken to address query performance and scalability issues. In the concluding chapters, this will guide you through implementing various features, such as Extended Events, Query Store, and Query Tuning Assistant, using hands-on examples.

By the end of the book, you will have developed the skills to determine query performance bottlenecks, avoid pitfalls, and discover the anti-patterns in use.

What you will learn

  • Use Query Store to understand and easily change query performance
  • Recognize and eliminate bottlenecks that lead to slow performance
  • Deploy quick fixes and long-term solutions to improve query performance
  • Implement best practices to minimize performance risk using T-SQL
  • Achieve optimal performance by ensuring careful query and index design
  • Use the latest performance optimization features in SQL Server 2017 and SQL Server 2019
  • Protect query performance during upgrades to newer versions of SQL Server

Who this book is for

This book is for database administrators, database developers, data analysts, data scientists, and T-SQL practitioners who want to get started with writing T-SQL code and troubleshooting query performance issues with the help of practical examples. Previous knowledge of T-SQL querying is not required to get started with this book.

Table of contents

  1. Title Page
  2. Copyright and Credits
    1. Learn T-SQL Querying
  3. About Packt
    1. Why subscribe?
    2. Packt.com
  4. Foreword
  5. Contributors
    1. About the authors
    2. About the reviewers
    3. Packt is searching for authors like you
  6. Preface
    1. Who this book is for
    2. What this book covers
    3. To get the most out of this book
      1. Download the example code files
      2. Download the color images
      3. Conventions used
    4. Get in touch
      1. Reviews
  7. Section 1: Query Processing Fundamentals
  8. Anatomy of a Query
    1. Building blocks of a T-SQL statement
      1. SELECT
        1. DISTINCT
        2. TOP
      2. FROM
        1. INNER JOIN
        2. OUTER JOIN
        3. CROSS JOIN
        4. APPLY
      3. WHERE
      4. ORDER BY
      5. GROUP BY
      6. HAVING
    2. Logical statement processing flow
    3. Summary
  9. Understanding Query Processing
    1. Query compilation essentials
    2. Query optimization essentials
    3. Query execution essentials
    4. Plan caching and reuse
      1. Stored procedures
      2. Ad hoc plan caching
      3. Parameterization
        1. Simple parameterization
        2. Forced parameterization
      4. The sp_executesql procedure
      5. Prepared statements
      6. How query processing impacts plan reuse
    5. The importance of parameters
      1. Security
      2. Performance
      3. Parameter sniffing
      4. To cache or not to cache
    6. Summary
  10. Mechanics of the Query Optimizer
    1. Introducing the Cardinality Estimator
    2. Understanding the query optimization workflow
      1. The Trivial Plan stage
      2. The Exploration stage
        1. Transaction Processing
        2. Quick plan
        3. Full optimization
      3. Knobs for query optimization
    3. Summary
  11. Section 2: Dos and Donts of T-SQL
  12. Exploring Query Execution Plans
    1. Accessing a query plan
    2. Navigating a query plan
    3. Query plan operators of interest
      1. Blocking versus non-blocking operators
      2. Data-access operators
        1. Table Scan
        2. Clustered Index Scan
        3. NonClustered Index Scan
        4. NonClustered Index Seek
        5. Clustered Index Seek
        6. Lookups
          1. RID Lookups
          2. Key Lookups
        7. Columnstore Index Scan
      3. Joins
        1. Nested Loops joins
        2. Merge Joins
        3. Hash Match joins
        4. Adaptive Joins
      4. Spools
      5. Sorts and aggregation
        1. Sorts
        2. Stream aggregation
        3. Hash aggregation
    4. Query plan properties of interest
      1. Plan-level properties
        1. Cardinality estimation model version
        2. Degree of Parallelism*
        3. Memory Grant*
        4. MemoryGrantInfo
        5. Optimization Level
        6. OptimizerHardwareDependentProperties
        7. OptimizerStatsUsage
        8. QueryPlanHash
        9. QueryHash
        10. Set options
        11. Statement
        12. TraceFlags
        13. WaitStats
        14. QueryTimeStats*
        15. MissingIndexes
        16. Parameter List
        17. Warnings*
          1. PlanAffectingConvert
          2. WaitForMemoryGrant*
          3. MemoryGrantWarning*
          4. SpatialGuess*
          5. UnmatchedIndexes*
          6. FullUpdateForOnlineIndexBuild
      2. Operator-level properties
        1. RunTimeCountersPerThread
          1. Actual I/O Statistics*
          2. Actual Number of Rows
          3. Actual Time Statistics
        2. Estimated rows
        3. EstimateRowsWithoutRowGoal
        4. Warnings*
          1. Columns With No Statistics*
          2. Spill To TempDb
          3. No Join Predicate
    5. Summary
  13. Writing Elegant T-SQL Queries
    1. Understanding predicate SARGability
    2. Basic index guidelines
      1. Clustered indexes
      2. Non-clustered indexes
        1. INCLUDE columns
        2. Filtered indexes
        3. Unique versus non-unique
      3. Columnstore indexes
      4. Indexing strategy
        1. Data structure considerations
        2. Database usage considerations
        3. Query considerations
    3. Best practices for T-SQL querying
      1. Referencing objects
      2. Joining tables
      3. Using NOLOCK
      4. Using cursors
    4. Summary
  14. Easily-Identified T-SQL Anti-Patterns
    1. The perils of SELECT *
    2. Functions in our predicate
    3. Deconstructing table-valued functions
    4. Complex expressions
    5. Optimizing OR logic
    6. NULL means unknown
    7. Fuzzy string matching
    8. Inequality logic
    9. EXECUTE versus sp_executesql
    10. Composable logic
    11. Summary
  15. Discovering T-SQL Anti-Patterns in Depth
    1. Implicit conversions
    2. Avoiding unnecessary sort operations
      1. UNION ALL versus UNION
      2. SELECT DISTINCT
      3. SELECT TOP 1 with ORDER BY
    3. Avoiding UDF pitfalls
    4. Avoiding unnecessary overhead with stored procedures
    5. Pitfalls of complex views
    6. Pitfalls of correlated sub-queries
    7. Properly storing intermediate results
      1. Using table variables and temporary tables
      2. Using Common Table Expressions
    8. Summary
  16. Section 3: Assemble Your Query Troubleshooting Toolbox
  17. Building Diagnostic Queries Using DMVs and DMFs
    1. Introducing Dynamic Management Views
    2. Exploring query execution DMVs
      1. sys.dm_exec_sessions
      2. sys.dm_exec_requests
      3. sys.dm_exec_sql_text
      4. sys.dm_os_waiting_tasks
    3. Exploring query plan cache DMVs
      1. sys.dm_exec_query_stats
      2. sys.dm_exec_procedure_stats
      3. sys.dm_exec_query_plan
      4. sys.dm_exec_cached_plans
    4. Troubleshooting common scenarios with DMV queries
      1. Investigating blocking
      2. Cached query plan issues
        1. Single-use plans (query fingerprints)
        2. Finding resource intensive queries
        3. Queries with excessive memory grants
      3. Mining XML query plans
        1. Plans with missing indexes
        2. Plans with warnings
        3. Plans with implicit conversions
        4. Plans with lookups
    5. Summary
  18. Building XEvent Profiler Traces
    1. Introducing Extended Events
    2. SQL Server Profiler – deprecated but not forgotten
    3. Getting up and running with XEvent Profiler
    4. Remote collection with PSSDiag and SQLDiag
    5. Analyzing traces with RML Utilities
    6. Summary
  19. Comparative Analysis of Query Plans
    1. Query Plan Comparison
    2. Query Plan Analyzer
    3. Summary
  20. Tracking Performance History with Query Store
    1. The Query Store
      1. Inner workings of the Query Store
      2. Configuring the Query Store
    2. Tracking expensive queries
    3. Fixing regressed queries
    4. Summary
  21. Troubleshooting Live Queries
    1. Using Live Query Statistics
    2. Understanding the need for Lightweight Profiling
      1. Diagnostics available with Lightweight Profiling
        1. The query_thread_profile XEvent
        2. The query_plan_profile XEvent
        3. The query_post_execution_plan_profile XEvent
        4. The sys.dm_exec_query_statistics_xml DMF
        5. The sys.dm_exec_query_plan_stats DMF
    3. Activity Monitor gets a new life
    4. Summary
  22. Managing Optimizer Changes with the Query Tuning Assistant
    1. Understanding where QTA is needed
    2. Understanding QTA fundamentals
    3. Exploring the QTA workflow
    4. Summary
  23. Other Books You May Enjoy
    1. Leave a review - let other readers know what you think

Product information

  • Title: Learn T-SQL Querying
  • Author(s): Pedro Lopes, Pam Lahoud
  • Release date: May 2019
  • Publisher(s): Packt Publishing
  • ISBN: 9781789348811