Issues with abusing Table Variables

I been thinking about writing about performance tuning issues I been dealing with. Every DBA has their own stories that they have ran into. In my cases it is easier to resolve these issues by creating proper indexes. But in some cases the actual code executing has to be re-written because of wide select. Recently while trouble shooting excessive use of TempDB in a system I look after I found an interesting use of Table Variables.

The code is dynamically builds a SQL statement batch and executes it against SQL Server every time someone accessed the page on the website. The code is not complicated but every time they are doing the following items:

1. Create Table Variables (TV) to hold the data temporally for the dynamic batch statement generated.
2. Take full contents of the main data tables and transfer it TV created in step 1.
3. Create another TV table and insert into it all the filtered list of primary key ID fields from the first TV.
4. Finally return the results to the user by taking first TV in step 2 and filtering the data based on information in second TV created in step 3.

What is wrong with this approach?
First brief information what TV are…

• They were introduced in SQL Server 2000.
• They work like variables and have well defined scope.
• They are temporary tables; that get created in TempDB.
• Their definition gets created in TempDB as random number and is not visible outside the scope of the SQL Batch the variable was declared.
• They get created as soon as you use DECLARE statement and destroyed as soon as your batch completes.

So dumping data into a TV is not bad for small data set. But when you are working with thousands if not tens of thousands records it can cause excessive locking and heavy TempDB usage. I am taking a guess they might have used it because books online state table variable “cause fewer recompilations” then temporary tables. But that is for stored procedures and not ad-hoc SQL Statement. In addition these tables have no indexes, no statistics; so all access to the table happen by full table scan.

I created a test database, it contains 3 tables with all the proper indexes that these tables should have and I populated MainTable with 1000 Rows, SubTable with 1000 Rows, and PageTypeTable with 100 Rows.

Example:
So let’s look at an example of what I noticed and the issues with this approach (this is a simplified example):

DECLARE @rowData TABLE
(MainTableID INT,
PageName VARCHAR(50),
PageData1 VARCHAR(50),
PageData2 VARCHAR(50),
PageTypeID INT,
PageType VARCHAR(50))

INSERT INTO @rowData
SELECT main.MainTableID,
main.PageName,
main.PageData1,
main.PageData2,
ptype.PageTypeID,
ptype.Description AS PageType
FROM (     SELECT M.MainTableID, M.PageName, S.PageData1, S.PageData2, M.PageTypeID
   FROM MainTable AS M
INNER JOIN SubTable  AS S ON M.MainTableID = S.MainTableID) AS main
LEFT JOIN PageTypeTable AS ptype ON main.PageTypeID = ptype.PageTypeID

DECLARE @FilteredData TABLE
(MainTableID INT PRIMARY KEY)

INSERT INTO @FilteredData
SELECT DISTINCT MainTableID
FROM @rowData AS R
WHERE R.PageTypeID = 89


SELECT *
FROM @rowData AS R
WHERE R.MainTableID IN (SELECT MainTableID
            FROM @FilteredData)

So if we look at the above example; first comes to mind is why is the code doing SQL’s job? Why not let SQL filter the data and return it in one select? Why make three selects out of it? And work with variable tables?

I ran this code 5 times and here are the SQL profiler results (statement level tracking):

Batch #	CPU	Duration	Reads	Writes	RowCounts
1	16	10	2368	3	1026
2	16	16	2371	3	1026
3	16	10	2365	3	1026
4	15	11	2368	3	1026
5	0	13	2372	3	1026

So what’s interesting here? We see that every time I execute it SQL Server has to re-read the records and write it out; and it is working with the full set table 1000 rows + 13 filtered rows + 13 final rows in the data set. Now if I rewrite the SQL to following …

Refined Query (Taken from the first Query):

   SELECT main.MainTableID,
    main.PageName,
    main.PageData1,
    main.PageData2,
    ptype.PageTypeID,
    ptype.Description AS PageType
FROM (     SELECT M.MainTableID, M.PageName, S.PageData1, S.PageData2, M.PageTypeID
            FROM MainTable AS M
      INNER JOIN SubTable  AS S ON M.MainTableID = S.MainTableID) AS main
LEFT JOIN PageTypeTable AS ptype ON main.PageTypeID = ptype.PageTypeID
WHERE ptype.PageTypeID = 89

This time in the SQL Profiler I get very different results:

Batch #	CPU	Duration	Reads	Writes	RowCounts
1	0	6	32	0	13
2	0	2	18	0	13
3	0	1	18	0	13
4	16	1	18	0	13
5	0	1	18	0	13

So key difference between the two:

• Reads went down from 2372 to 18; logical reads.
• Writes went down from 3 to 0 (no more written to temp tables, so no writing).
• Row Count went from 1026 to 13, the only rows that we actual cared about.

So think about working on data set that has ten thousands or twenty thousands, or hundred thousand rows? How you think your server will fair? Now on top of it add your traffic, 10 people executing the same query, 100 people, 1000 people? The affects of writing code like in original example can be disastrous and is unacceptable; it will not matter how much hardware you throw at it it will not fix the problem.

So question is does the performance impacts end here?
No! Why? Because think about full table scans, what is the SELECT going to do to the table? It is going to create a shared lock on the table; now if this is OLTP system where there are selects and updates all the time. If we lock the entire table shared locks, what is going to happen to request for EXCLUSIVE lock? It will cause blocking. SQL Server is going to put it on because it cannot access the table. How can we test that? I can’t simulate the 1000’s of inbound connections; but using SQL Server profiler we see what kind of locks SQL Server takes out when we run the first batch of statements. You will see that SQL Server takes out locks on all the tables involved in the batch. You will also notice the locks being distributed among your database and the tempdb database. Because it acquiring shared locks on your databases but exclusive locks in tempdb to do the bulk load operation. Executing that batch generated over 5000 events for (Locks Acquired, Locks Releases, Lock Timeouts, and Statement Completed). When I run the second code, it generated about 2000 events. Less then half the locks acquired and released. So you are able to save additional processing time. This also saves on memory usage, as now we don’t have to read the entire table into memory. This will allow SQL to take selective page lock only; if table is large enough it should not cause blocking with other transactions wanting to update single rows else where in the table.

Conclusion:
It is general recommendation to avoid selecting rows and columns you shouldn’t select. This wastes I/O, Memory and CPU time. Yes hardware is improving very quickly; however there at one time or another you will have to deal with the code. Throwing more hardware at it is just patching the problem not fixing the problem.

References:
MSDN Online, Tables, Link.
SQL Server Central, TSQL – Tempdb Question of the day, Link.
SQL Storage Engine Engineers Blog, Lock Escalation, Link.

Updates:
May 15, 2009: I was mistaken on the introduction of TV into SQL Servers (thanks Chris!).