Kalen Delaney : temp tableshttp://sqlblog.com/blogs/kalen_delaney/archive/tags/temp+tables/default.aspxTags: temp tablesenCommunityServer 2.1 SP2 (Build: 61129.1)Geek City: Space Used By Worktableshttp://sqlblog.com/blogs/kalen_delaney/archive/2008/11/26/space-used-by-worktables.aspxWed, 26 Nov 2008 17:08:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:10129Kalen Delaney7http://sqlblog.com/blogs/kalen_delaney/comments/10129.aspxhttp://sqlblog.com/blogs/kalen_delaney/commentrss.aspx?PostID=10129<P>Today, a reader asked me the following:</P>
<BLOCKQUOTE>
<P>"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."</P></BLOCKQUOTE>
<P>What is a worktable?
<P>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":
<BLOCKQUOTE>
<P>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.</P></BLOCKQUOTE>
<P>Then I went to one of my favorite whitepapers, <A href="http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx" target=_blank>"Working with tempdb in SQL Server 2005",</A>&nbsp; which I strongly suggest you take a look at, if you're at all interested in keeping track of your <EM>tempdb</EM> database.&nbsp; It had a slightly different definition:</P>
<BLOCKQUOTE>
<P>Work tables are temporary objects and are used to store results for query spool, LOB variables, and cursors.</P></BLOCKQUOTE>
<P>So there is some overlap, in that both definitions mention spools.
<P>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.
<P>SQL Server 2005 provides us a couple of DMVs that can be helpful.
<P>The first, <EM>sys.dm_db_file_space_usage</EM>, 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).
<P>The second, <EM>sys.dm_db_session_space_usage</EM>, 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:
<P><FONT face="Courier New" size=2>SELECT * FROM sys.dm_db_session_space_usage<BR>WHERE session_id = @@spid;</FONT>
<P>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.
<P>The whitepaper gives you code to create a table called <EM>tempdb_space_usage</EM> and a stored procedure called <EM>sp_sampleTempDbSpaceUsage</EM> to populate the table. It also provides half a dozen queries to examine the data collected.
<P>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.
<P>Have fun!
<P><FONT color=#ff00ff size=4>~Kalen</FONT></P><img src="http://sqlblog.com/aggbug.aspx?PostID=10129" width="1" height="1">DMVsinternalsstoragetemp tablestempdbDid You Know? Estimated vs Actual Planshttp://sqlblog.com/blogs/kalen_delaney/archive/2007/07/30/did-you-know-estimated-vs-actual-plans.aspxMon, 30 Jul 2007 18:41:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:1999Kalen Delaney5http://sqlblog.com/blogs/kalen_delaney/comments/1999.aspxhttp://sqlblog.com/blogs/kalen_delaney/commentrss.aspx?PostID=1999<P>In my previous post, I mentioned that it is important to understand the difference between estimated and actual query plans, so I decided to go into a few more details regarding the differences. </P>
<P>Optimization takes place&nbsp;before execution, so in one sense, any query plan is an estimated plan. But when you request an actual plan, SQL Server will actually execute each statement as it displays the plan, so&nbsp;the plan&nbsp;for subsequent statements can change after the earlier statements are executed.&nbsp; In addition, SQL Server adds additional information to the actual plan, after the execution takes place. As mentioned last time, the actual plan includes the number of processors used to execute the query. It will also include the number of rows returned from each step of the plan as it is executed, and the number of times each step is executed.&nbsp; If you are executing a plan that has already been cached and is now being reused, the actual plan will include both the parameter values the plan was originally compiled with and the parameter values used during execution. </P>
<P>You can request that SQL Server display ESTIMATED plans without executing the query with any of the following options:</P>
<BLOCKQUOTE>
<P>SET SHOWPLAN_TEXT ON </P>
<P>SET SHOWPLAN_ALL ON</P>
<P>SET SHOWPLAN_XML ON</P>
<P>For graphical estimated plans, you can use the Query | Display Estimated Execution Plan menu option. This can be invoked with a toolbar button, or with Cntl-L. </P></BLOCKQUOTE>
<P>&nbsp;</P>
<P>You can request that SQL Server display actual plans with any of the following options:</P>
<BLOCKQUOTE>
<P>SET STATISTICS PROFILE ON </P>
<P>SET STATISTICS XML ON </P>
<P>For graphical actual plans, you can use the Query | Include Actual Execution Plan menu option. This can be invoked with a toolbar button, or with Cntl-K. </P></BLOCKQUOTE>
<P>I'll show you 3 examples of ways in which the actual plan can be useful. They require that you have the AdventureWorks database installed.</P>
<P>Example 1:</P>
<P>First, create a stored procedure called 'testtemp' that will build a temporary table.</P>
<BLOCKQUOTE>
<P>USE AdventureWorks<BR>GO<BR>IF EXISTS (SELECT 1 FROM sys.procedures<BR>WHERE name = 'testtemp')<BR>DROP PROC testtemp<BR>GO<BR>CREATE PROC testtemp<BR>(@p int)<BR>AS<BR>SELECT * INTO #t<BR>FROM Sales.SalesOrderDetail <BR>CREATE INDEX t_index ON #t (ProductID)<BR>SELECT * FROM #t<BR>WHERE ProductID &lt; @p<BR>RETURN <BR>GO</P></BLOCKQUOTE>
<P>Try to look at the estimated plan for the procedure, and you will get an error because the plan for the SELECT cannot be generated when the temp table #t does not exist:
<BLOCKQUOTE>
<P>-- Estimated plan <BR>SET SHOWPLAN_TEXT ON&nbsp;<BR>GO<BR>EXEC testtemp 896<BR>-- error is generated<BR>GO<BR>SET SHOWPLAN_TEXT&nbsp;OFF&nbsp;<BR>GO </P></BLOCKQUOTE>
<P>Now look at the actual plan and you will see plans for the temp table creation and for the SELECT from the temp table.</P>
<BLOCKQUOTE>
<P>SET STATISTICS PROFILE ON&nbsp;<BR>GO<BR>EXEC testtemp 896<BR>GO<BR>SET STATISTICS PROFILE OFF&nbsp;<BR>GO </P>
<P>&nbsp;</P></BLOCKQUOTE>
<P>Example 2:</P>
<P>One of the main reasons that the actual plan may different from the estimated plan is because of data changes to your data. In general (and assuming you have the option 'auto update statistics' enabled) if more than 20% of the data in a table changes, the optimizer will detect stale statistics and update them automatically. The updated statistics will then trigger a recompile. </P>
<P>This example makes a copy of Sales.SalesOrderDetail in the AdventureWorks database and builds an index on the table.&nbsp;</P>
<BLOCKQUOTE>
<P>SELECT * INTO NewOrders <BR>FROM Sales.SalesOrderDetail <BR>GO
<P>CREATE INDEX IX_NewOrders_ProductID on NewOrders(ProductID) <BR>GO
<P>SET SHOWPLAN_ALL ON -- Estimated Plan <BR>GO
<P>-- The estimated plan shows us that a seek is done on the SELECT from NewOrders because at optimization time <BR>--&nbsp;there are only a few rows that have a ProductID value of 897.
<P>BEGIN TRAN <BR>UPDATE NewOrders <BR>SET ProductID = 897 <BR>WHERE ProductID between 800 and 900
<P>SELECT OrderQty, CarrierTrackingNumber<BR>FROM NewOrders <BR>WHERE ProductID = 897 <BR>ROLLBACK TRAN <BR>GO
<P>SET SHOWPLAN_ALL OFF <BR>GO
<P>&nbsp;</P>-- The actual plan shows us that a table scan is done on the SELECT from NewOrders because after the update is executed, <BR>--&nbsp; statistics are updated and the query is recompiled. Now there are lots of rows&nbsp;that have a ProductID value of 897.
<P>SET STATISTICS PROFILE ON -- Actual Plan <BR>GO
<P>BEGIN TRAN <BR>UPDATE NewOrders <BR>SET ProductID = 897 <BR>WHERE ProductID between 800 and 900 </P>
<P>SELECT OrderQty, CarrierTrackingNumber <BR>FROM NewOrders <BR>WHERE ProductID = 897 <BR>ROLLBACK TRAN <BR>GO </P>
<P>SET STATISTICS PROFILE OFF <BR>GO </P></BLOCKQUOTE>
<P>Example 3:
<P>For this example, you can create a stored procedure that uses a parameter to determine which rows from the Sales.SalesOrderDetail table to select.
<BLOCKQUOTE>
<P>USE AdventureWorks<BR>GO<BR>IF EXISTS (SELECT 1 FROM sys.procedures<BR>WHERE name = 'GetProducts')<BR>DROP PROC GetProducts<BR>GO<BR>CREATE PROC GetProducts<BR>(@p int)<BR>AS<BR>SELECT * <BR>FROM Sales.SalesOrderDetail <BR>WHERE ProductID = @p<BR>RETURN <BR>GO</P></BLOCKQUOTE>
<P>The first time you run the procedure, a plan will be built based on the first parameter. The second time you run the procedure, the original plan will be used, and the statistics should show that SQL Server is performing more reads than there are pages in the table. (There are 1238 pages in the table.)
<BLOCKQUOTE>
<P>SET STATISTICS IO ON<BR>GO<BR>EXEC GetProducts 710&nbsp; -- using nc index takes 145 logical reads<BR>GO<BR>EXEC GetProducts 707&nbsp; -- using nc index takes 9458 logical reads. <BR>GO </P></BLOCKQUOTE>
<P>You can look at the actual plan in XML to see the parameters used for compile and execute.</P>
<BLOCKQUOTE>
<P>SET STATISTICS XML ON<BR>GO<BR>EXEC GetProducts 707 <BR>GO</P></BLOCKQUOTE>
<P>Near the bottom of the XML document, you should see the following:</P>
<BLOCKQUOTE>
<P>&lt;ParameterList&gt;</P>
<P>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;ColumnReference Column="@p" ParameterCompiledValue="(710)" ParameterRuntimeValue="(707)" /&gt;</P>
<P>&lt;/ParameterList&gt;</P></BLOCKQUOTE>
<P>Seeing the different values used for compilation and execution can lead you to suspect an issue with parameter sniffing. There are several ways around this problem, but that's the topic for another day. </P>
<P>&nbsp;</P>
<P>Have fun!</P>
<P><FONT color=#ff00ff><STRONG>~Kalen</STRONG></FONT></P><img src="http://sqlblog.com/aggbug.aspx?PostID=1999" width="1" height="1">parameter sniffingshowplantemp tables