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

seeing latency of 20ms, then it's not so important; but you can still look at Chapter 4 to see if there's
anything you can do to optimize your existing storage investment.
Using 20ms is a good rule of thumb target on most systems unless SQL Server is spending a lot of
time waiting for I/O requests.
Troubleshooting Space Issues
All the code in this section uses the Ch8_6TempDBSpaceIssues.sql code i le.
It was mentioned at the beginning of this chapter that all the databases on an instance have to share
only one tempdb; this makes it even more important to understand who is doing what in tempdb, so
Microsoft provides three DMVs to enable you to do just that.
sys.dm_db_fi le_space_usage
This DMV provides a view of the number and types of pages that are allocated in tempdb by i le,
allowing you to see the distribution of pages across your data i les.
You can also use this DMV to total the values across all the i les to get a single view of the break-
down of tempdb usage, which can help you narrow down the scope of the problem in the event of
unexpected usage. Here is an example script for this:
SELECT SUM(total_page_count)*8/1024 AS 'tempdb size (MB)',
SUM(total_page_count) AS 'tempdb pages',
SUM(allocated_extent_page_count) AS 'in use pages',
SUM(user_object_reserved_page_count) AS 'user object pages',
SUM(internal_object_reserved_page_count) AS 'internal object pages',
SUM(mixed_extent_page_count) AS 'Total Mixed Extent Pages'
FROM sys.dm_db_file_space_usage ;
Example results from the preceding script are shown in Figure 8-15. Note that user, internal and
version pages are from uniform extents only.
FIGURE 8-15
sys.dm_db_task_space_usage
This DMV provides details of tempdb usage for currently running tasks. The values are set to 0
at the start of the task and deleted when the task completes, so it's useful for troubleshooting live
issues with currently executing tasks. For example, the following script will give you the top i ve
sessions currently using space in tempdb, ordered by the total amount of space in use:
SELECT TOP 5 *
FROM sys.dm_db_task_space_usage
WHERE session_id > 50
ORDER BY user_objects_alloc_page_count + internal_objects_alloc_page_count