Learn T-SQL Querying
A guide to developing efficient and elegant T-SQL code
- 출간 정보
- 2019.05.03. 전자책 출간
- 파일 정보
<Learn T-SQL Querying> ▶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
- 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
▶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, through the help of practical examples. Previous knowledge of T-SQL querying is not required to get started on this book.
▶What this book covers
- Chapter 1, Anatomy of a Query, shows you how to write solid, performant T-SQL. Users will become familiar with how SQL Server runs T-SQL syntax to deliver the intended result sets in a scalable fashion. In this chapter, we'll cover the basic building blocks that make up a T-SQL statement, as well as how SQL Server interprets those blocks to begin the process of executing our queries. The concepts introduced in this chapter will be used throughout the remaining sections of the book to explain most patterns and anti-patterns, as well as mitigation strategies.
- Chapter 2, Understanding Query Processing, introduces the fact that the way a T-SQL query is written and submitted to the server influences how it is interpreted and executed by SQL Server. Even before a single T-SQL query is written, the choice of development style (for example, using stored procedures versus ad hoc statements) can have a direct impact on the performance of the application.
- Chapter 3, Mechanics of the Query Optimizer, explores the internals of SQL Server query optimization and defines many important concepts that any database professional who writes T-SQL queries will keep coming back to, especially when troubleshooting query performance issues. The Cardinality Estimator (CE) is a fundamental part of SQL Server's Query Optimizer; knowing how it uses statistics, and the importance of keeping updated and relevant statistics for the overall query optimization process, empowers database professionals to write good queries—queries that both drive and leverage good database schema designs.
- Chapter 4, Exploring Query Execution Plans, will leave the reader with a good understanding of the various elements that make up a query execution plan in SQL Server. Nearly everything we need to understand and troubleshoot the performance of our T-SQL queries can be found somewhere in the plan, either in the visible part of the plan, or in the properties windows, which we can access by right-clicking the operators.
- Chapter 5, Writing Elegant T-SQL Queries, will leave the reader with a better understanding of some of the aspects that database professionals need to keep in mind to write good queries, and how to identify some of the inefficiencies that may surface if the predicates expressed in queries are not supported by a suitable index design. These are all but a part of the intricacies of writing good, scalable T-SQL code.
- Chapter 6, Easily-Identified T-SQL Anti-Patterns, covers a few T-SQL anti-patterns, such as SELECT * syntax, OR logic, and functions in our predicates, that are relatively easy to find simply by looking at our T-SQL code and how it is written. The scenarios covered in this chapter are some of the most common examples of patterns that prevent our T-SQL queries from scaling well and maintaining the expected level of performance throughout the lifetime of applications. All are easy to detect, and most have simple workarounds. Therefore, when writing queries, try to avoid these anti-patterns by leveraging some of the techniques we outline here.
- Chapter 7, Discovering T-SQL Anti-Patterns in Depth, covers some performance pitfalls that are not always obvious when writing T-SQL queries. Using the knowledge and tools covered in earlier chapters, together with the anti-patterns discussed in this chapter, we should now be able to dig deeper into our query execution plans and uncover issues that have the potential to impact performance and scalability before they reach production.
- Chapter 8, Building Diagnostic Queries Using DMVs and DMFs, covers examples of how to use Dynamic Management Views (DMVs) and Dynamic Management Functions (DMFs), which can be a powerful troubleshooting tool when it comes to diagnosing query performance issues. They are lightweight, easy to use, and provide a breadth of information that is useful for zeroing in on performance issues.
- Chapter 9, Building XEvent Profiler Traces, introduces the Extended Events (XEvents) engine in SQL Server and how you can leverage XEvent traces to gather detailed data about query execution and performance. While DMVs are great for point in time and cumulative analysis, there are some issues that can only be diagnosed by catching queries and related data in real time. This is where tracing with XEvents is useful. We'll also discuss the various free tools from Microsoft that can be used to quickly and easily configure, capture, and analyze XEvent traces. Together with DMVs, we now have several tools in our toolbelt that can be used to diagnose and troubleshoot the various issues covered in the book.
- Chapter 10, Comparative Analysis of Query Plans, covers the rich UI features available in SQL Server Management Studio (SSMS) to make query plan analysis easier. First, Query Plan Comparison that allows us to quickly and easily compare query plans to determine what differences may help explain what changed between two plans. Next, Query Plan Analyzer which allows us to zero in on problem areas in the query plan such as inaccurate cardinality estimates with the click of a button.
- Chapter 11, Tracking Performance History with Query Store, covers the important topic of storing query performance statistics in the flight recorder that is the Query Store, which allows us to access query plans and their runtime statistics, along with how they change over time. We can now more easily find resolutions for performance problems. We can easily identify plans that must be tuned, or for quick mitigation, just return to a good known plan that has been stored in Query Store. We'll cover how to use either system views or SSMS to uncover the highest resource-consuming queries executing in our databases and help us quickly find and fix query performance issues that are related to plan changes, which greatly simplifies query performance troubleshooting.
- Chapter 12, Troubleshooting Live Queries, covers how Lightweight Profiling together with tools such as Live Query Statistics and Activity Monitor are invaluable tools for troubleshooting and solving query performance issues, namely those queries that take hours to complete, or never do.
- Chapter 13, Managing Optimizer Changes with the Query Tuning Assistant, introduces a feature: the Query Tuning Assistant (QTA). QTA aims to address some of the most common causes of cardinality estimation related performance regressions that may affect our T-SQL queries after an upgrade from an older version of SQL Server to a newer version, namely SQL Server 2016 and above.
Transact-SQL (T-SQL) is Microsoft's proprietary extension to the SQL language, which is used with Microsoft SQL Server and Azure SQL Database. This book is a useful guide to learning the art of writing efficient T-SQL code in modern SQL Server versions, as well as Azure SQL Database.
This 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 understand how to leverage them for troubleshooting. In the later chapters, you will learn how to identify various T-SQL patterns and anti-patterns. This will help you analyze execution plans to gain insights into current performance, as well as to determine whether or not a query is scalable. You will also learn to build diagnostic queries using Dynamic Management Views (DMVs) and Dynamic Management Functions (DMFs) to address various challenges in T-SQL execution. Next, you will study how to leverage the built-in tools of SQL Server to shorten the time taken to address query performance and scalability issues. In the concluding chapters, the book will guide you through implementing various features such as Extended Events, Query Store, and Query Tuning Assistant using handson examples.
By the end of this book, you will have the skills to determine query performance bottlenecks, avoid pitfalls, and discover anti-patterns in use in your existing T-SQL code.
▶About the Author
- Pedro Lopes
Pedro Lopes is a Program Manager in the Database Systems group, based in Redmond, WA, USA. He has over 19 years of industry experience and has been with Microsoft for 9 years. He is currently responsible for program management of Database Engine features for in-market and vNext versions of SQL Server, with a special focus on the Relational Engine. He has extensive experience with query performance troubleshooting and is a regular speaker at numerous conferences such as SQLBits, PASS Summit, SQLIntersection, Microsoft Ignite, and Microsoft Build. He blogs about SQL on the SQL Server Team blog. He has authored several tools in the Tiger toolbox on GitHub: AdaptiveIndexDefrag maintenance solution, BPCheck, and usp_WhatsUp.
- Pam Lahoud
Pam Lahoud is a Program Manager in the Database Systems group, based in Redmond, WA, USA. She has been with Microsoft for 13 years and is currently responsible for program management of Database Engine features for in-market and vNext versions of SQL Server, with a special focus on the Storage Engine area. She is passionate about SQL Server performance and has focused on performance tuning and optimization, particularly from the developer's perspective, throughout her career. She is a SQL Server 2008 Microsoft Certified Master (MCM) with over 20 years of experience working with SQL Server.
▶TABLE of CONTENTS
1. Anatomy of a Query
2. Understanding Query Processing
3. Mechanics of the Query Optimizer
4. Exploring Query Execution Plans
5. Writing Elegant T-SQL Queries
6. Easily-Identified T-SQL Anti-Patterns
7. Discovering T-SQL Anti-Patterns in Depth
8. Building Diagnostic Queries Using DMVs and DMFs
9. Building XEvent Profiler Traces
10. Comparative Analysis of Query Plans
11. Tracking Performance History with Query Store
12. Troubleshooting Live Queries
13. Managing Optimizer Changes with the Query Tuning Assistant
내가 남긴 별점 0.0
'구매자' 표시는 리디북스에서 유료도서 결제 후 다운로드 하시거나 리디셀렉트 도서를 다운로드하신 경우에만 표시됩니다.
본문 끝 최상단으로 돌아가기
사용 가능 : 개
<>부터 총 화
총 화 대여 완료했습니다.
남은 작품 : 총 화 (원)
Learn T-SQL Querying
대여 기간 : 일
결제 금액 : 원
결제 가능한 리디캐시, 포인트가 없습니다.
리디캐시를 충전하시면 자동으로 결제됩니다.
최대 5% 리디포인트 적립 혜택도 놓치지 마세요!
이미 구매한 작품입니다.
원하는 결제 방법을 선택해주세요.
대여 기간이 만료되었습니다.