May 18, 2010 at 12:20 am
Hello Admins
Recently we had some issue in our DB because of I/O on temp DB. Investigating this with Microsoft by our hosting partner, they come back with this answer (this is from Microsoft)
“I have checked the SP definition, and found it creates multiple table variables for each execution, which brings I/O on tempdb as in SQL 2005, table variables are no longer stored in-memory but on tempdb. The key point here is we saw hundreds of connection executing the same Stored Procedure at the same time, which can easily result in tempdb contention and cause SQL to stuck. As you mentioned the SQL server worked fine before, did you notice any change to the system recently? Like workload changed, App updated?”
I thought TABLE VARIABLE is IN-MEMORY processing and TEMP TABLE uses temp DB
because lots of our SP uses table variable (thinking its IN-MEMORY processing).
Cheers
May 18, 2010 at 12:51 am
Table variables have never been in -memory only objects, where this myth originates from im not exactly sure.
Heres a link for sql server 2000 http://support.microsoft.com/kb/305977
which states
Q4: Are table variables memory-only structures that are assured better performance as compared to temporary or permanent tables, because they are maintained in a database that resides on the physical disk?
A4: A table variable is not a memory-only structure. Because a table variable might hold more data than can fit in memory, it has to have a place on disk to store data. Table variables are created in the tempdb database similar to temporary tables. If memory is available, both table variables and temporary tables are created and processed while in memory (data cache).
May 18, 2010 at 1:23 am
Both table variables and temporary tables are created in TempDB. There are few differents between them. When you use table variable the server doesn’t hold statistics on it and it assumes that it has only 1 record in it. When you work with temporary tables, the server does keep statistics on it and causes recompilation (which sometimes might be good thing). If your variables hold a small amount of records, then you can leave it as it is, but if you table variables hold lots of records, then it might be better to use temporary tables instead of table variables (this could help getting a better query plan but won’t help you with the TempDB contention).
As for the TempDB contention – I’ve had this problem in the past when I worked on SQL Server 2000. I’ve checked and found out that the problem was because of the temporaries table’s pages allocation. I’ve created more files for the TempDB (The number of files was the same as the number of CPUs that we had) and enabled trace flag 1118 which caused the TempDB to stop working with mixed extents and give each temporary table a unified extent. This solved the problem. I didn’t have the same problem with SQL Server 2005 but, I’ve read few articles about it. It seems that adding files to TempDB will still help (notice that most places recommended that the number of files should be the same as the number of CPU but I remember reading an article that said the half of this number is good enough). The problem is that there are 2 different opinions about using the trace flag 1118. There are many experts that say that it helps and many that say that it shouldn’t be used. My guess is that you’ll just have to try it and decide for yourself.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply