Knowing Tempdb - SQL Server 2012 Internals and Troubleshooting - page 232

sys.dm_db_session_space_usage
When a task completes, the values from sys . dm _ db _ task _ usage are aggregated by session, and these
aggregated values are viewable using sys . dm _ db _ session _ space _ usage .

The following example code demonstrates how to use this DMV, showing you all the sessions in
order of total tempdb usage:

SELECT *
FROM sys.dm_db_session_space_usage
WHERE session_id > 50
ORDER BY user_objects_alloc_page_count + internal_objects_alloc_page_count DESC ;

The output won't include any currently executing tasks, so it's not very useful for a live issue; but
you can look up the session _ id in sys . dm _ exec _ requests to gather information about who's using
that session _ id , such as their login details; the server they're connecting from; and the application
they are using.

CONFIGURATION BEST PRACTICES

Because several of the issues addressed in this chapter have required coni guration changes, this
section consolidates all the best practices for coni guring tempdb. You won't just i nd prescriptive
rules here, but also the background to the recommendations and guidance on how to choose the
best coni guration for any particular environment. In particular this section covers the following:

➤

Where to place tempdb

➤

Initial sizing and autogrowth

➤

Coni guring multiple i les

Tempdb File Placement
It's quite a well-known best practice to separate data, transaction logs, and tempdb, and if you knew
that already, are you sure you know why? The origin of this recommendation lies with the
separation of types of workload between different physical storage, i.e. separate physical disks.

This is still a valid recommendation for environments where you can guarantee that separation, but
more commonly we see customers deploying SQL Server in a shared storage environment, where
physical separation is much harder to achieve and usually isn't even necessary for performance
reasons.

It is still a good idea however to maintain separation to help with manageability so that potential
problems are easier to isolate. For example, separating tempdb onto its own logical disk means that
you can pre-size it to i ll the disk (see tempdb sizing later in the chapter) without worrying about
space requirements for other i les, and the more separation you implement the easier it is to correlate
logical disk performance to specii c database i les.

At the very minimum you should aim to have one logical disk for data i les, one for transaction log
i les, and one for tempdb data i les. I prefer to keep the tempdb data i les on their own drive so they