THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Kalen Delaney

Geek City: Space Used By Worktables

Today, a reader asked me the following:

"How can I find the amount of space occupied by a worktable?. Using SET STATISTICS IO ON, I can only see the number of reads using the worktable, not the amount of space taken."

What is a worktable?

I always like to think of it as a temp table that SQL Server builds without being asked. While preparing to write this post, I decided to see if I could find a formal definition. Books Online for SQL Server 2005 gives the following definition in the topic "Worktables":

Worktables are generated for certain GROUP BY, ORDER BY, or UNION queries. For example, if an ORDER BY clause references columns that are not covered by any indexes, the relational engine may need to generate a worktable to sort the result set into the order requested. Worktables are also sometimes used as spools that temporarily hold the result of executing a part of a query plan.

Then I went to one of my favorite whitepapers, "Working with tempdb in SQL Server 2005",  which I strongly suggest you take a look at, if you're at all interested in keeping track of your tempdb database.  It had a slightly different definition:

Work tables are temporary objects and are used to store results for query spool, LOB variables, and cursors.

So there is some overlap, in that both definitions mention spools.

Prior to SQL Server 2005, the best we could do was watch the STATISTICS IO value, and look at the page reads for any worktables created in the query, but, as my reader mentions, those values show us the number of reads, not the total size of the tables . There were/are some Performance Monitor counters that let us see how many worktables were created, but they don't mention the size.

SQL Server 2005 provides us a couple of DMVs that can be helpful.

The first, sys.dm_db_file_space_usage, has a name that seems like it will provide information about all your databases, but it turns out it just provides information for tempdb. I usually use this view to keep track of the version store space, but it also tells me how much space is used for user objects (explicit temp tables) and internal objects (which include worktables).

The second, sys.dm_db_session_space_usage, reports information for each session, so you can filter it by the session_id you are interested in . For you current session, you can look at @@spid:

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

During testing, the above can be useful, to look at the values before you run a test, and then look at the values afterwards, and compute the difference. This still doesn't give you the exact size of your worktables, but it can give you some ideas. In fact, the above mentioned whitepaper states that there is no way to get the number of pages used by any specific internal object in tempdb.

The whitepaper gives you code to create a table called tempdb_space_usage and a stored procedure called sp_sampleTempDbSpaceUsage to populate the table. It also provides half a dozen queries to examine the data collected.

You should be able to get a much better handle on what is using your tempdb space by following the guidelines in the whitepaper and running some of the provided queries.

Have fun!

~Kalen

Published Wednesday, November 26, 2008 10:08 AM by Kalen Delaney

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Peso said:

Thank you.

Now I know where to begin investigating worktable space usage.

November 26, 2008 5:37 PM
 

James Luetkehoelter said:

Great post Kalen! I find that a lot of the "new" features often do a "looky at the shiny button" and then throw the work at tempdb. I've run into more situation where tempdb was completely taxed because the customer was unaware of it.

November 26, 2008 5:51 PM
 

M A Srinivas said:

I am getting the error

SELECT * FROM sys.dm_db_file_session_space_usage

Msg 208, Level 16, State 1, Line 1

Invalid object name 'sys.dm_db_file_session_space_usage'.

December 1, 2008 5:01 AM
 

Kalen Delaney said:

Hi Srinivas

Thanks for catching that! I had the view name correct in the description right above the code, but in the code I merged it with the other view. It has been corrected now.

~Kalen

December 1, 2008 11:42 AM
 

Jeremy said:

To M A Srinivas

what returns select @@version  for your server?

this DMV is available only starting from sql server 2005

December 5, 2008 9:20 AM
 

Kalen Delaney said:

Jeremy

ALL DMVs are only available starting in SQL Server 2005, so since the complaint was only about one DVM and it was about one that was incorrect in my code, I assume he has 2005 or 2008.

Thanks!

Kalen

December 5, 2008 12:05 PM
 

Yucer said:

Thank you.

March 5, 2014 5:52 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Favorite Non-technical Sites or Blogs

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement