Chapter 9. Database-level DMVs

 

This chapter covers

  • Using tempdb to identify and fix space problems
  • Index operations to identify and improve SQL query performance

This chapter covers a miscellany of functionality that collectively belongs to the database DMVs. We’ll examine and discuss the importance of tempdb, how its space is used by different objects, how much space is used by individual sessions and running tasks, and why it’s important to monitor. If tempdb has problems, it can affect all the databases on SQL Server. Typically tempdb problems are reported in the SQL Server error log. We’ll examine how to check tempdb for potential problems and how to fix them, as we’ll look at a list of tempdb best practices.

We’ll investigate various aspects of index contention and explore solutions. Topics include indexes under locking pressure, lock escalations, indexes with page splits, and metrics about how tables and indexes are specifically used. All these scripts will provide opportunities to identify problem areas and optimize the performance of your tables and indexes.

First, let’s look at why tempdb is such an important database, what it’s used for, and how it’s used.

9.1. Space usage in tempdb

Here we’ll examine and discuss the importance of tempdb, see how to determine the amount of total and free space it has, and drill down into the amount of space used by the different types of objects it contains.

9.2. Session usage in tempdb

9.3. Task usage in tempdb

9.4. Tempdb recommendations

9.5. Index contention

9.6. Summary