A customer is running in a SQL Server 2000 environment, 32-bit AWE mode, on 64-bit Windows 2003 R2.
The server has 16GB of RAM and 8 logical CPU cores. It’s running under VMWare. Storage is provided via a (slow) SAN. SQL Server has ~12GB of RAM assigned to it.
During monitoring of long-running queries, we discovered that a frequently-accessed stored procedure (known for being a beast: it contains a WHILE loop and several JOINs) was doing worse than usual.
After adding some indexes, we put in a change to convert the table variable in this stored procedure to a temp table. That was on Tuesday night.
On Wednesday, the wait stat RESOURCE_SEMAPHORE was in the top three (behind OLEDB and CXPACKET). Memory pressure, and so much of it? I had never seen that wait stat get so high.
This morning it was the same: RESOURCE_SEMAPHORE was climbing by the second, and the CPU and I/O was thrashing. Looking in sysprocesses was a vision of CXPACKET and RESOURCE_SEMAPHORE.
While it’s obvious now what caused it, I was completely stumped for the whole of yesterday.
This morning, we rolled back to the table variable version of the stored procedure, because it was causing blocking on the database server.
One minute after rolling back, RESOURCE_SEMAPHORE stopped dead in its tracks. It’s been two and a half hours and it hasn’t changed its value.
So tempdb contention caused memory pressure. Well, sure, it happens, and this is hardly the first time I’ve heard of that, but it demonstrated a significant difference between using a table variable and a temp table.
So while I will stick with recommending a temp table over a table variable in most cases, this is a fine example of “it depends”.