Search icon CANCEL
Subscription
0
Cart icon
Cart
Close icon
You have no products in your basket yet
Save more on your purchases!
Savings automatically calculated. No voucher code required
Arrow left icon
All Products
Best Sellers
New Releases
Books
Videos
Audiobooks
Learning Hub
Newsletters
Free Learning
Arrow right icon
Arrow up icon
GO TO TOP
Learn T-SQL Querying

You're reading from  Learn T-SQL Querying

Product type Book
Published in May 2019
Publisher Packt
ISBN-13 9781789348811
Pages 484 pages
Edition 1st Edition
Languages
Authors (2):
Pedro Lopes Pedro Lopes
Profile icon Pedro Lopes
Pam Lahoud Pam Lahoud
Profile icon Pam Lahoud
View More author details
Toc

Table of Contents (18) Chapters close

Preface 1. Section 1: Query Processing Fundamentals
2. Anatomy of a Query 3. Understanding Query Processing 4. Mechanics of the Query Optimizer 5. Section 2: Dos and Donts of T-SQL
6. Exploring Query Execution Plans 7. Writing Elegant T-SQL Queries 8. Easily-Identified T-SQL Anti-Patterns 9. Discovering T-SQL Anti-Patterns in Depth 10. Section 3: Assemble Your Query Troubleshooting Toolbox
11. Building Diagnostic Queries Using DMVs and DMFs 12. Building XEvent Profiler Traces 13. Comparative Analysis of Query Plans 14. Tracking Performance History with Query Store 15. Troubleshooting Live Queries 16. Managing Optimizer Changes with the Query Tuning Assistant 17. Other Books You May Enjoy

Exploring query plan cache DMVs

Another set of DMVs that are helpful when troubleshooting T-SQL query performance are the query plan cache related DMVs. While the execution DMVs we discussed in the previous section contain point-in-time information that changes frequently, these DMVs contain information about queries that are currently in the plan cache, which can contain information all the way back to when the server was last restarted, depending on how long query plans remain in the cache.

The amount of time a plan remains in the cache depends on several factors, such as memory pressure, recompilation, schema changes, and so on. Provided that the server has been online for some time and no cache-flushing events have occurred, such as changing MAXDOP, or manually clearing the plan cache by running ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE, these plan cache DMVs...
lock icon The rest of the chapter is locked
Register for a free Packt account to unlock a world of extra content!
A free Packt account unlocks extra newsletters, articles, discounted offers, and much more. Start advancing your knowledge today.
Unlock this book and the full library FREE for 7 days
Get unlimited access to 7000+ expert-authored eBooks and videos courses covering every tech area you can think of
Renews at $15.99/month. Cancel anytime