Dynamic Management Views and Dynamic Management Functions - Microsoft SQL Server 2012 Performance Tuning - page 159

Following diagram depicts the logical concept of the page, extent, uniform extent, and
mixed extent:

8KB
Page

8KB
Page

Table-1

Extent Uniform Extent

8KBPage 8KBPage

Table-1 Table-5

8KBPage 8KBPage

Table-2 Table-6

8KBPage 8KBPage

Table-3 Table-7

8KBPage 8KBPage

Table-4 Table-8

Mixed Extent

In this recipe, we will learn how to monitor the performance of TempDB database. We
will learn how to identify the sessions and tasks that cause an increase in space usage
on the TempDB database.

Getting ready

In this recipe, we will run a sample query that generates 10 million rows and stores them in a
local temporary table in TempDB database. We will record and notice the difference in page
allocation and deallocation statistics for TempDB database, before and after running our
sample query.

Following is the prerequisite for this recipe:

F An instance of SQL Server 2012 Developer or Enterprise Evaluation edition

How to do it...

Follow the steps provided here to perform this example practically:

1. Open SQL Server Management Studio and connect to the instance of SQL Server.