Why is using a table variable more than twice as fast as a #temp table in this specific case?

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)

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() OVE

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= 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 |
| FCheckAndC

StackExchange Database Administrators Q#13392, answer score: 31