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
Source Click Here.
Post a Comment