36

I was looking at the article here Temporary Tables vs. Table Variables and Their Effect on SQL Server Performance and on SQL Server 2008 was able to reproduce similar results to those shown there for 2005.

When executing the stored procedures (definitions below) with only 10 rows the table variable version out performs the temporary table version by more than two times.

I cleared the procedure cache and ran both stored procedures 10,000 times then repeated the process for another 4 runs. Results below (time in ms per batch)

T2_Time     V2_Time
----------- -----------
8578        2718      
6641        2781    
6469        2813   
6766        2797
6156        2719

My question is: What is the reason for the better performance of the table variable version?

I've done some investigation. e.g. Looking at the performance counters with

SELECT cntr_value
from sys.dm_os_performance_counters
where counter_name = 'Temp Tables Creation Rate';

confirms that in both cases the temporary objects are being cached after the first run as expected rather than created from scratch again for every invocation.

Similarly tracing the Auto Stats, SP:Recompile, SQL:StmtRecompileevents in Profiler (screenshot below) shows that these events only occur once (on the first invocation of the #temp table stored procedure) and the other 9,999 executions do not raise any of these events. (The table variable version does not get any of these events)

Trace

The slightly greater overhead of the first run of the stored procedure can in no way account for the big overall difference however as it still only takes a few ms to clear the procedure cache and run both procedures once so I don't believe either statistics or recompiles can be the cause.

Create Required Database Objects

CREATE DATABASE TESTDB_18Feb2012;

GO

USE TESTDB_18Feb2012;

CREATE TABLE NUM 
  ( 
     n INT PRIMARY KEY, 
     s VARCHAR(128) 
  ); 

WITH NUMS(N) 
     AS (SELECT TOP 1000000 ROW_NUMBER() OVER (ORDER BY $/0) 
         FROM   master..spt_values v1, 
                master..spt_values v2) 
INSERT INTO NUM 
SELECT N, 
       'Value: ' + CONVERT(VARCHAR, N) 
FROM   NUMS 

GO

CREATE PROCEDURE [dbo].[T2] @total INT 
AS 
  CREATE TABLE #T 
    ( 
       n INT PRIMARY KEY, 
       s VARCHAR(128) 
    ) 

  INSERT INTO #T 
  SELECT n, 
         s 
  FROM   NUM 
  WHERE  n%100 > 0 
         AND n <= @total 

  DECLARE @res VARCHAR(128) 

  SELECT @res = MAX(s) 
  FROM   NUM 
  WHERE  n <= @total 
         AND NOT EXISTS(SELECT * 
                        FROM   #T 
                        WHERE  #T.n = NUM.n) 
GO

CREATE PROCEDURE [dbo].[V2] @total INT 
AS 
  DECLARE @V TABLE ( 
    n INT PRIMARY KEY, 
    s VARCHAR(128)) 

  INSERT INTO @V 
  SELECT n, 
         s 
  FROM   NUM 
  WHERE  n%100 > 0 
         AND n <= @total 

  DECLARE @res VARCHAR(128) 

  SELECT @res = MAX(s) 
  FROM   NUM 
  WHERE  n <= @total 
         AND NOT EXISTS(SELECT * 
                        FROM   @V V 
                        WHERE  V.n = NUM.n) 


GO

Test Script

SET NOCOUNT ON;

DECLARE @T1 DATETIME2,
        @T2 DATETIME2,
        @T3 DATETIME2,  
        @Counter INT = 0

SET @T1 = SYSDATETIME()

WHILE ( @Counter < 10000)
BEGIN
EXEC dbo.T2 10
SET @Counter += 1
END

SET @T2 = SYSDATETIME()
SET @Counter = 0

WHILE ( @Counter < 10000)
BEGIN
EXEC dbo.V2 10
SET @Counter += 1
END

SET @T3 = SYSDATETIME()

SELECT DATEDIFF(MILLISECOND,@T1,@T2) AS T2_Time,
       DATEDIFF(MILLISECOND,@T2,@T3) AS V2_Time
  • The profiler trace indicates that statistics only get created on the #temp table once despite it getting cleared and re-populated another 9,999 times subsequent to that. – Martin Smith Feb 19 '12 at 12:35
30

The output of SET STATISTICS IO ON for both looks similar

SET STATISTICS IO ON;
PRINT 'V2'
EXEC dbo.V2 10
PRINT 'T2'
EXEC dbo.T2 10

Gives

V2
Table '#58B62A60'. Scan count 0, logical reads 20
Table 'NUM'. Scan count 1, logical reads 3

Table '#58B62A60'. Scan count 10, logical reads 20
Table 'NUM'. Scan count 1, logical reads 3

T2
Table '#T__ ... __00000000E2FE'. Scan count 0, logical reads 20
Table 'NUM'. Scan count 1, logical reads 3

Table '#T__ ... __00000000E2FE'. Scan count 0, logical reads 20
Table 'NUM'. Scan count 1, logical reads 3

And as Aaron points out in the comments the plan for the table variable version is actually less efficient as whilst both have a nested loops plan driven by an index seek on dbo.NUM the #temp table version performs a seek into the index on [#T].n = [dbo].[NUM].[n] with residual predicate [#T].[n]<=[@total] whereas the table variable version performs an index seek on @V.n <= [@total] with residual predicate @V.[n]=[dbo].[NUM].[n] and so processes more rows (which is why this plan performs so poorly for larger number of rows)

Using Extended Events to look at the wait types for the specific spid gives these results for 10,000 executions of EXEC dbo.T2 10

+---------------------+------------+----------------+----------------+----------------+
|                     |            |     Total      | Total Resource |  Total Signal  |
| Wait Type           | Wait Count | Wait Time (ms) | Wait Time (ms) | Wait Time (ms) |
+---------------------+------------+----------------+----------------+----------------+
| SOS_SCHEDULER_YIELD | 16         | 19             | 19             | 0              |
| PAGELATCH_SH        | 39998      | 14             | 0              | 14             |
| PAGELATCH_EX        | 1          | 0              | 0              | 0              |
+---------------------+------------+----------------+----------------+----------------+

and these results for 10,000 executions of EXEC dbo.V2 10

+---------------------+------------+----------------+----------------+----------------+
|                     |            |     Total      | Total Resource |  Total Signal  |
| Wait Type           | Wait Count | Wait Time (ms) | Wait Time (ms) | Wait Time (ms) |
+---------------------+------------+----------------+----------------+----------------+
| PAGELATCH_EX        | 2          | 0              | 0              | 0              |
| PAGELATCH_SH        | 1          | 0              | 0              | 0              |
| SOS_SCHEDULER_YIELD | 676        | 0              | 0              | 0              |
+---------------------+------------+----------------+----------------+----------------+

So it is clear that the number of PAGELATCH_SH waits is much higher in the #temp table case. I'm not aware of any way of adding the wait resource to the extended events trace so to investigate this further I ran

WHILE 1=1
EXEC dbo.T2 10

Whilst in another connection polling sys.dm_os_waiting_tasks

CREATE TABLE #T(resource_description NVARCHAR(2048))

WHILE 1=1
INSERT INTO #T
SELECT resource_description
FROM sys.dm_os_waiting_tasks
WHERE session_id=<spid_of_other_session> and wait_type='PAGELATCH_SH'

After leaving that running for about 15 seconds it had gathered the following results

+-------+----------------------+
| Count | resource_description |
+-------+----------------------+
|  1098 | 2:1:150              |
|  1689 | 2:1:146              |
+-------+----------------------+

Both of these pages being latched belong to (different) non clustered indexes on the tempdb.sys.sysschobjs base table named 'nc1' and 'nc2'.

Querying tempdb.sys.fn_dblog during the runs indicates that the number of log records added by the first execution of each stored procedure was somewhat variable but for subsequent executions the number added by each iteration was very consistent and predictable. Once the procedure plans are cached the number of log entries are about half those needed for the #temp version.

+-----------------+----------------+------------+
|                 | Table Variable | Temp Table |
+-----------------+----------------+------------+
| First Run       |            126 | 72 or 136  |
| Subsequent Runs |             17 | 32         |
+-----------------+----------------+------------+

Looking at the transaction log entries in more detail for the #temp table version of the SP each subsequent invocation of the stored procedure creates three transactions and the table variable one only two.

+---------------------------------+----+---------------------------------+----+
|           #Temp Table                |         @Table Variable              |
+---------------------------------+----+---------------------------------+----+
| CREATE TABLE                    |  9 |                                 |    |
| INSERT                          | 12 | TVQuery                         | 12 |
| FCheckAndCleanupCachedTempTable | 11 | FCheckAndCleanupCachedTempTable |  5 |
+---------------------------------+----+---------------------------------+----+

The INSERT/TVQUERY transactions are identical except for the name. This contains the log records for each of the 10 rows inserted to the temporary table or table variable plus the LOP_BEGIN_XACT/ LOP_COMMIT_XACT entries.

The CREATE TABLE transaction only appears in the #Temp version and looks as follows.

+-----------------+-------------------+---------------------+
|    Operation    |      Context      |    AllocUnitName    |
+-----------------+-------------------+---------------------+
| LOP_BEGIN_XACT  | LCX_NULL          |                     |
| LOP_SHRINK_NOOP | LCX_NULL          |                     |
| LOP_MODIFY_ROW  | LCX_CLUSTERED     | sys.sysschobjs.clst |
| LOP_DELETE_ROWS | LCX_MARK_AS_GHOST | sys.sysschobjs.nc1  |
| LOP_INSERT_ROWS | LCX_INDEX_LEAF    | sys.sysschobjs.nc1  |
| LOP_DELETE_ROWS | LCX_MARK_AS_GHOST | sys.sysschobjs.nc2  |
| LOP_INSERT_ROWS | LCX_INDEX_LEAF    | sys.sysschobjs.nc2  |
| LOP_MODIFY_ROW  | LCX_CLUSTERED     | sys.sysschobjs.clst |
| LOP_COMMIT_XACT | LCX_NULL          |                     |
+-----------------+-------------------+---------------------+

The FCheckAndCleanupCachedTempTable transaction appears in both but has 6 additional entries in the #temp version. These are the 6 rows referring to sys.sysschobjs and they have exactly the same pattern as above.

+-----------------+-------------------+----------------------------------------------+
|    Operation    |      Context      |                AllocUnitName                 |
+-----------------+-------------------+----------------------------------------------+
| LOP_BEGIN_XACT  | LCX_NULL          |                                              |
| LOP_DELETE_ROWS | LCX_NONSYS_SPLIT  | dbo.#7240F239.PK__#T________3BD0199374293AAB |
| LOP_HOBT_DELTA  | LCX_NULL          |                                              |
| LOP_HOBT_DELTA  | LCX_NULL          |                                              |
| LOP_MODIFY_ROW  | LCX_CLUSTERED     | sys.sysschobjs.clst                          |
| LOP_DELETE_ROWS | LCX_MARK_AS_GHOST | sys.sysschobjs.nc1                           |
| LOP_INSERT_ROWS | LCX_INDEX_LEAF    | sys.sysschobjs.nc1                           |
| LOP_DELETE_ROWS | LCX_MARK_AS_GHOST | sys.sysschobjs.nc2                           |
| LOP_INSERT_ROWS | LCX_INDEX_LEAF    | sys.sysschobjs.nc2                           |
| LOP_MODIFY_ROW  | LCX_CLUSTERED     | sys.sysschobjs.clst                          |
| LOP_COMMIT_XACT | LCX_NULL          |                                              |
+-----------------+-------------------+----------------------------------------------+

Looking at these 6 rows in both transactions they correspond to the same operations. The first LOP_MODIFY_ROW, LCX_CLUSTERED is an update to the modify_date column in sys.objects. The remaining five rows are all concerned with object renaming. Because name is a key column of both affected NCIs (nc1 and nc2) this is carried out as a delete/insert for those then it goes back to the clustered index and updates that too.

It appears that for the #temp table version when the stored procedure ends part of the clean up carried out by the FCheckAndCleanupCachedTempTable transaction is to rename the temp table from something like #T__________________________________________________________________________________________________________________00000000E316 to a different internal name such as #2F4A0079 and when it is entered the CREATE TABLE transaction renames it back. This flip flopping name can be seen by in one connection executing dbo.T2 in a loop whilst in another

WHILE 1=1
SELECT name, object_id, create_date, modify_date
FROM tempdb.sys.objects 
WHERE name LIKE '#%'

Example Results

Screenshot

So one potential explanation for the observed performance differential as alluded to by Alex is that it is this additional work maintaining the system tables in tempdb that is responsible.


Running both procedures in a loop the Visual Studio Code profiler reveals the following

+-------------------------------+--------------------+-------+-----------+
|           Function            |    Explanation     | Temp  | Table Var |
+-------------------------------+--------------------+-------+-----------+
| CXStmtDML::XretExecute        | Insert ... Select  | 16.93 | 37.31     |
| CXStmtQuery::ErsqExecuteQuery | Select Max         | 8.77  | 23.19     |
+-------------------------------+--------------------+-------+-----------+
| Total                         |                    | 25.7  | 60.5      |
+-------------------------------+--------------------+-------+-----------+

The table variable version spends about 60% of the time performing the insert statement and the subsequent select whereas the temporary table is less than half that. This is inline with the timings shown in the OP and with the conclusion above that the difference in performance is down to time spent performing ancillary work not due to time spent in the query execution itself.

The most important functions contributing towards the "missing" 75% in the temporary table version are

+------------------------------------+-------------------+
|              Function              | Inclusive Samples |
+------------------------------------+-------------------+
| CXStmtCreateTableDDL::XretExecute  | 26.26%            |
| CXStmtDDL::FinishNormalImp         | 4.17%             |
| TmpObject::Release                 | 27.77%            |
+------------------------------------+-------------------+
| Total                              | 58.20%            |
+------------------------------------+-------------------+

Under both the create and release functions the function CMEDProxyObject::SetName is shown with an inclusive sample value of 19.6%. From which I infer that 39.2% of the time in the temporary table case is taken up with the renaming described earlier.

And the largest ones in the table variable version contributing to the other 40% are

+-----------------------------------+-------------------+
|             Function              | Inclusive Samples |
+-----------------------------------+-------------------+
| CTableCreate::LCreate             | 7.41%             |
| TmpObject::Release                | 12.87%            |
+-----------------------------------+-------------------+
| Total                             | 20.28%            |
+-----------------------------------+-------------------+

Temporary Table Profile

enter image description here

Table Variable Profile

enter image description here

7

Disco Inferno

Since this is an older question, I decided to revisit the issue on newer versions of SQL Server to see if the same performance profile still exists, or if the characteristics have changed at all.

Specifically, the addition of in-memory system tables for SQL Server 2019 seems a worthwhile occasion to re-test.

I'm using a slightly different test harness, since I ran into this issue while working on something else.

Testing, testing

Using the 2013 version of Stack Overflow, I have this index and these two procedures:

Index:

CREATE INDEX ix_whatever 
    ON dbo.Posts(OwnerUserId) INCLUDE(Score);
GO

Temp table:

    CREATE OR ALTER PROCEDURE dbo.TempTableTest(@Id INT)
    AS
    BEGIN
    SET NOCOUNT ON;

        CREATE TABLE #t(i INT NOT NULL);
        DECLARE @i INT;

        INSERT #t ( i )
        SELECT p.Score
        FROM dbo.Posts AS p
        WHERE p.OwnerUserId = @Id;

        SELECT @i = AVG(t.i)
        FROM #t AS t;

    END;
    GO 

Table variable:

    CREATE OR ALTER PROCEDURE dbo.TableVariableTest(@Id INT)
    AS
    BEGIN
    SET NOCOUNT ON;

        DECLARE @t TABLE (i INT NOT NULL);
        DECLARE @i INT;

        INSERT @t ( i )
        SELECT p.Score
        FROM dbo.Posts AS p
        WHERE p.OwnerUserId = @Id;

        SELECT @i = AVG(t.i)
        FROM @t AS t;

    END;
    GO 

To prevent any potential ASYNC_NETWORK_IO waits, I'm using wrapper procedures.

CREATE PROCEDURE #TT AS
SET NOCOUNT ON;
    DECLARE @i INT = 1;
    DECLARE @StartDate DATETIME2(7) = SYSDATETIME();

    WHILE @i <= 50000
        BEGIN
            EXEC dbo.TempTableTest @Id = @i;
            SET @i += 1;
        END;
    SELECT DATEDIFF(MILLISECOND, @StartDate, SYSDATETIME()) AS [ElapsedTimeMilliseconds];
GO

CREATE PROCEDURE #TV AS
SET NOCOUNT ON;
    DECLARE @i INT = 1;
    DECLARE @StartDate DATETIME2(7) = SYSDATETIME();

    WHILE @i <= 50000
        BEGIN
            EXEC dbo.TableVariableTest @Id = @i;
            SET @i += 1;
        END;
    SELECT DATEDIFF(MILLISECOND, @StartDate, SYSDATETIME()) AS [ElapsedTimeMilliseconds];
GO

SQL Server 2017

Since 2014 and 2016 are basically RELICS at this point, I'm starting my testing with 2017. Also, for brevity, I'm jumping right to profiling the code with Perfview. In real life, I looked at waits, latches, spinlocks, crazy trace flags, and other stuff.

Profiling the code is the only thing that revealed anything of interest.

Time difference:

  • Temp Table: 17891 ms
  • Table Variable: 5891 ms

Still a very clear difference, eh? But what's SQL Server hitting now?

NUTS

Looking at the top two increases in the diffed samples, we see sqlmin and sqlsqllang!TCacheStore<CacheClockAlgorithm>::GetNextUserDataInHashBucket are the two biggest offenders.

NUTS

Judging by the names in the call stacks, cleaning up and internally renaming temp tables seems to be the biggest time sucks in the temp table call vs. the table variable call.

Even though table variables are internally backed by temp tables, this doesn't seem to be an issue.

SET STATISTICS IO ON;
DECLARE @t TABLE(id INT);
SELECT * FROM @t AS t;

Table '#B98CE339'. Scan count 1

Looking through the call stacks for the table variable test doesn't show either of the main offenders at all:

NUTS

SQL Server 2019 (Vanilla)

Alright, so this is still an issue in SQL Server 2017, is anything different in 2019 out of the box?

First, to show there's nothing up my sleeve:

SELECT c.name,
       c.value_in_use,
       c.description
FROM sys.configurations AS c
WHERE c.name = 'tempdb metadata memory-optimized';

NUTS

Time difference:

  • Temp table: 15765 ms
  • Table Variable: 7250 ms

Both procedures were different. The temp table call was a couple seconds faster, and the table variable call was about 1.5 seconds slower. The table variable slow down may be partially explained by table variable deferred compilation, a new optimizer choice in 2019.

Looking at the diff in Perfview, it has changed a bit -- sqlmin is no longer there -- but sqllang!TCacheStore<CacheClockAlgorithm>::GetNextUserDataInHashBucket is.

NUTS

SQL Server 2019 (In-Memory Tempdb system tables)

What about this new in memory system table thing? Hm? Sup with that?

Let's turn it on!

EXEC sys.sp_configure @configname = 'advanced', 
                      @configvalue = 1  
RECONFIGURE;

EXEC sys.sp_configure @configname = 'tempdb metadata memory-optimized', 
                      @configvalue = 1 
RECONFIGURE;

Note that this requires a SQL Server restart to kick in, so pardon me while I reboot SQL on this lovely Friday afternoon.

Now things look different:

SELECT c.name,
       c.value_in_use,
       c.description
FROM sys.configurations AS c
WHERE c.name = 'tempdb metadata memory-optimized';

SELECT *, 
       OBJECT_NAME(object_id) AS object_name, 
       @@VERSION AS sql_server_version
FROM tempdb.sys.memory_optimized_tables_internal_attributes;

NUTS

Time difference:

  • Temp table: 11638 ms
  • Table variable: 7403 ms

The temp tables did about 4 seconds better! That's something.

I like something.

This time, the Perfview diff isn't very interesting. Side by side, it's interesting to note how close the times are across the board:

NUTS

One interesting point in the diff are the calls to hkengine!, which may seem obvious since hekaton-ish features are now in use.

NUTS

As far as the top two items in the diff, I can't make much of ntoskrnl!?:

NUTS

Or sqltses!CSqlSortManager_80::GetSortKey, but they're here for Smrtr Ppl™ to look at:

NUTS

Note that there is an undocumented and definitely not safe for production so please don't use it startup trace flag you can use to have additional temp table system objects (sysrowsets, sysallocunits, and sysseobjvalues) included in the in-memory feature, but it didn't make a noticeable difference in execution times in this case.

Roundup

Even in newer versions of SQL server, high frequency calls to table variables are much faster than high frequency calls to temp tables.

Though it's tempting to blame compilations, recompilations, auto stats, latches, spinlocks, caching, or other issues, the issue is clearly still around managing temp table cleanup.

It's a closer call in SQL Server 2019 with in-memory system tables enabled, but table variables still perform better when call frequency is high.

Of course, as a vaping sage once mused: "use table variables when plan choice isn't an issue".

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.