List of Listings

Chapter 1. The Dynamic Management Views gold mine

Listing 1.1. A simple monitor

Listing 1.2. Find your slowest queries

Listing 1.3. Find those missing indexes

Listing 1.4. Identify what SQL is running now

Listing 1.5. Quickly find a cached plan

Listing 1.6. Missing index details

Chapter 2. Common patterns

Listing 2.1. Restricting output to a given database

Listing 2.2. Top 10 longest-running queries on server

Listing 2.3. Creating a temporary table WHERE 1 = 2

Listing 2.4. Looping over all databases on a server pattern

Listing 2.5. Quickly find the most-used cached plans—simple version

Listing 2.6. Extracting the Individual Query from the Parent Query

Listing 2.7. Identify the database of ad hoc queries and stored procedures

Listing 2.8. Determine query effect via differential between snapshots

Listing 2.9. Example of building dynamic SQL

Listing 2.10. Example of printing the content of large variables

Chapter 3. Index DMVs

Listing 3.1. Identifying the most important missing indexes

Listing 3.2. The most-costly unused indexes

Listing 3.3. The top high-maintenance indexes

Listing 3.4. The most-used indexes

Listing 3.5. The most-fragmented indexes

Listing 3.6. Identifying indexes used by a given routine

Listing 3.7. The databases with the most missing indexes

Listing 3.8. Indexes that aren’t used at all

Listing 3.9. What is the state of your statistics?

Chapter 4. Improving poor query performance

Listing 4.1. How to find a cached plan

Listing 4.2. Finding where a query is used