Chapter 2. Common patterns

The core of this book will supply many ready-to-run code snippets that will give you useful information about the cause of your performance problems, show how to improve your SQL queries, and also give you a better understanding of your SQL Server.

Many of the code snippets in this book have approaches (patterns) that are used over and over again. Rather than explain the patterns repeatedly for each of the code snippets that use them, the purpose of this chapter is to explain these patterns once, in detail. Please feel free to refer back to this section if you’re unsure of some of the common code that you’ll see repeatedly in the snippets throughout this book.

We’ll kick off with a look at how you can reduce any blocking the DMV code snippets might cause or encounter.

2.1. Reducing blocking

When SQL queries run concurrently, locks are necessary to ensure the integrity of any changes. For example, a SELECT statement will issue locks that may prevent an UPDATE statement from running, because the latter will want exclusive access to the relevant rows. These locks can result in blocking, which causes queries to run more slowly, and potentially locks can lead to client timeouts or, at its extreme, deadlocks.

2.2. Using CROSS APPLY

2.3. Restricting output to a given database

2.4. Restricting output by using the TOP command

2.5. Creating an empty temporary table structure

2.6. Looping over the databases on a server

2.7. Retrieving a query’s cached plan and SQL text

2.8. Extracting the Individual Query from the Parent Query

2.9. Identifying the database used by ad hoc queries

2.10. Calculating DMV changes

2.11. Reading cached plans

2.12. Building dynamic SQL

2.13. Printing the content of large variables

2.14. Common terms and acronyms

2.15. Known problems that may affect the scripts

2.16. Summary