Measuring Temporary Disk Space Usage

When the temporary space is used from the tempdb database, you can measure the amount of temporary space used by an index operation by using the dynamic management views provided in SQL Server 2005. There are three views that report the temporary disk space used by any operation in tempdb:
· sys.dm_db_task_space_usage
· sys.dm_db_session_space_usage
· sys.dm_db_file_space_usage
While these views only pertain to the tempdb database, you can set the SORT_IN_TEMPDB option to ON when testing for disk space usage requirements and then plan for the same space allocation in your user database.
The sys.dm_db_task_space_usage dynamic management view provides tempdb usage information for each task. As a task (such as an index rebuild) progresses, you can monitor how much temporary space the task is using. However, as soon as the task completes, the counters in the view are reset to zero. So, unless you happen to query this view just at the moment before the task completes, you can’t get the total amount of tempdb space used by a given task. However, when the task is completed these values are aggregated at the session level and stored in the sys.dm_db_session_space_usage view.
The sys.dm_db_session_space_usage provides tempdb usage information for each session. The easiest way to measure the tempdb space used by a given operation is to query sys.dm_db_session_space_usage for your session before and after the operation. However, there is a catch. The data in sys.dm_db_session_space_usage is not updated until the completion of the batch; therefore, you must execute these statements as three separate batches. Essentially, all you really need is three GO statements, as shown in the following example:
SELECT * FROM sys.dm_db_session_space_usage WHERE session_id = @@spid;
GO

GO
SELECT * FROM sys.dm_db_session_space_usage WHERE session_id = @@spid;

GO
When you query the sys.dm_db_session_space_usage view, pay attention to the following two columns in the result set:
· internal_objects_alloc_page_count: This column represents the space used by the sort runs while creating or rebuilding an index.
· user_objects_alloc_page_count: This column represents the tempdb space used by the temporary mapping index. The temporary mapping index is created only when an online index operation creates, drops, or rebuilds a clustered index.
To measure the size of the version store, you can query the version_store_reserved_page_count column in the sys.dm_db_file_space_usage view. The version store size can also be monitored by using the System Monitor (perfmon) counter Version Store Size (KB) in the Transactions performance object. The amount of space required for the version store depends on the size and duration of the transactions that change the data in the underlying table.