How can I monitor who uses SQL Server's tempdb space?

From: http://blogs.msdn.com/ B /apgcdsd/archive/2011/02/11/sql-server-tempdb.aspx

The system database is a global resource for all users connected to the SQL server instance. In the current SQL Server, the frequency of use may exceed the user's imagination. If the tempdb space is exhausted, many operations cannot be completed.

As a support engineer, I am often asked why my tempdb is so big ?" "Who used up my tempdb space ?" It is difficult to answer this question when SQL 2000 is used. Fortunately, after SQL 2005, a new management view is introduced: SYS. dm_db_file_space_usage. By querying this view, you can understand the space usage of tempdb and the space used by tempdb. It is a user object (user_object_reserved_page_count field ), system Object (internal_object_reserved_page_count field) or version storage area (version_store_reserved_page_count field ).

Before discussing the use of tempdb space, let's briefly introduce what operations will commonly use tempdb in large quantities. In SQL 2005 and SQL 2008, tempdb space is far more than temporary tables. Common application objects include:

User object(User_object_reserved_page_count)

The user object is explicitly created by the user. These objects can be in the scope of the user session, or in the scope of the routine used to create the object. It can be stored procedures, triggers, or user-defined functions. A user object can be one of the following items:

• User-Defined tables and Indexes
• System tables and Indexes
• Global temporary table and Index
• Local temporary tables and Indexes
• Table variable
• Table returned by the table Value Function

Internal object(Internal_object_reserved_page_count)

Internal objects are created by the SQL Server database engine as needed to process SQL Server statements. Internal objects can be created and deleted in the statement scope. An internal object can be one of the following items:

• Used for cursor.
• Used for querying hash join or hash aggregation operations.
• The intermediate sorting results of some group by, order by, or union queries.

Version Storage(Version_store_reserved_page_count)

The version storage area is mainly used to support the snapshot transaction isolation level, and some other new features released after SQL 2005 to improve database concurrency.

As you can see,It is difficult to determine whether the connection operation will be used by statements sent by users.Tempdb.A typical example is some queries. If the table supports good indexes and SQL server does not need hash join, tempdb is not used for this query. If the table is large and there is no good index, the tempdb usage may be small.

A major feature of tempdb space usage is that there are only a few objects, such as temporary tables and table variables created by users, which can be managed using management views such as SYS. allocation_units and SYS. partitions. Many internal objects and versions are not stored in these management views. Therefore, the results of sp_spaceused are significantly different from those of actual use,TempdbCannot Use sp_spaceused. SYS. dm_db_file_space_usage must be usedSuch a management view and management function can see the full picture.

 

The following uses an example to describe how to use the DBCC command, DMV, and DMF to monitor which statements are using tempdb.

To make the results simple, we need to restart SQL server before testing.

Then we establish a connection (connection A) in management studio and input the following statement. These statements use the space of tempdb.

 

Select @ spid

Go

Use adventureworks

Go

Select getdate ()

Go

Select * into # mysalesorderdetail

From sales. salesorderdetail

-- Create a temp table

-- This operation should apply for user objects page

Go

Waitfor delay '0: 0: 2'

Select getdate ()

Go

Drop table # mysalesorderdetail

-- Delete a temp table

-- After this operation, the number of user object pages should decrease.

Go

Waitfor delay '0: 0: 2'

Select getdate ()

Go

Select top 100000 * from

[Sales]. [salesorderdetail]

Inner join [sales]. [salesorderheader]

On [sales]. [salesorderheader]. [salesorderid] = [sales]. [salesorderheader]. [salesorderid];

-- A large join is made here.

-- There should be an internal objects application.

Go

Select getdate ()

-- The number of internal objects pages should be decreased after the join statement is completed.

Go

 

So what script can be used to monitor the above behavior?The following script can monitor and discover the currentTempdbUser.This script must start before the tempdb statement starts to run. (You can modify the script based on your preferences .)

The script first uses the "DBCC showfilestats" statement to query the overall usage of the current tempdb. Query the SYS. dm_db_file_space_usage view to obtain the total number of user objects, internal objects, and version storage in tempdb. Query SYS. dm_db_session_space_usage and SYS. dm_exec_sessions, and find all connections currently using tempdb. Finally, use SYS. dm_exec_ SQL _text to find the running statements of these connections.

 

Use tempdb

-- Run the script once every one second until the user manually terminates the script.

While 1 = 1

Begin

Select getdate ()

-- View tempdb usage at the file level

DBCC showfilestats

-- Query 1

-- Returns all session information that has been applied for a space.

Select 'tempdb' As DB, getdate () as time,

Sum (user_object_reserved_page_count) * 8 as user_objects_kb,

Sum (internal_object_reserved_page_count) * 8 as internal_objects_kb,

Sum (version_store_reserved_page_count) * 8 as version_store_kb,

Sum (unallocated_extent_page_count) * 8 as freespace_kb

From SYS. dm_db_file_space_usage

Where database_id = 2

-- Query 2

-- This management view can reflect the total space allocation of tempdb at that time.

Select t1.session _ id,

T1.internal _ objects_alloc_page_count, t1.user _ objects_alloc_page_count,

T1.internal _ objects_dealloc_page_count, t1.user _ objects_dealloc_page_count,

T3 .*

From SYS. dm_db_session_space_usage T1,

-- Reflect the accumulative space application for each session

SYS. dm_exec_sessions as T3

-- Information of each session

Where

T1.session _ id = t3.session _ id

And (t1.internal _ objects_alloc_page_count> 0

Or t1.user _ objects_alloc_page_count> 0

Or t1.internal _ objects_dealloc_page_count> 0

Or t1.user _ objects_dealloc_page_count> 0)

-- Query 3

-- Returns the statement that the session is running and has been applied for space.

Select t1.session _ id,

St. Text

From SYS. dm_db_session_space_usage as T1,

SYS. dm_exec_requests as T4

Cross apply SYS. dm_exec_ SQL _text (t4. SQL _ HANDLE) as St

Where t1.session _ id = t4.session _ id

And t1.session _ id> 50

And (t1.internal _ objects_alloc_page_count> 0

Or t1.user _ objects_alloc_page_count> 0

Or t1.internal _ objects_dealloc_page_count> 0

Or t1.user _ objects_dealloc_page_count> 0)

Waitfor delay '0: 0: 1'

End

 

Figure 1

 

In the connection (Connection B) that runs this script (Figure 1), select "result to file ". Run it first and specify the output file path. Then, run the connection A (figure 2 ). After connection a stops running, manually stop connection B.

 

Figure 2

 

In the result of connecting to a (), we can get four times. The example on the image is:

11:39:36. 513 -- start creating temp table

11:39:38. 920-start to delete temp table

11:39:40. 937-start Query

11:39:45. 733-query ended

 

Connection B generates a text file. You can use some editor tools with the "list all containing a specific string" function to pick out each command result.

From the results of DBCC showfilestats in the text file generated by connection B (figure 3), we can see that the space used by tempdb has increased twice (from 23 to 210, from 47 to 118 ), there was a decline in the middle (from 210 to 47 ).

 

 

Figure 3

From the results of all query 1 in the text file generated by connection B (figure 3), we can see that there are three sections of time, and the user object and internal object space have application and release actions. They are 11:39:36-11:39:37 (user_objects_kb increased), 11:39:40-11:39:41 (user_objects_kb decreased), and 11:39:40-11:39:43 (internal_objects_kb increased ).

 

 

Figure 4

From the results of query 2 (figure 4), we can see that connection a is running in these three periods.

 

Figure 5

According to the time, you can find the statement that connection a is currently running from the result of query 3 (figure 5. For example, during the period from 11:39:40 to 11:39:43 (internal_objects_kb increased), the following statement is always run:

 

 

Figure 6

From the above results, we can see that in the statement connecting to a, the most time point in tempdb is between 11:39:41 and 11:39:42, and the connection is making the query statement in figure 6. SQL Server needs to store some internal objects to complete inner join.