Chapter 9. Database-level DMVs
This chapter covers
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.
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.