Table Variable degrades performance? | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Table Variable degrades performance?

We are using Table Variable in our heavly used storedprocedure. In the storedprocedure, we have 4-6 separate inserts/deletes/updates to table variable using select statements. Usually, the storedprocedure takes 0 – 4 secs for each run. My questions are..
1) I think Table variables sits in RAM. But, we can see it writes in the tempdb. if there is no sufficient RAM then does it write to disk? which degrades the performance?
2) when concurrent connections are more than 1000 connections then we hit 100% CPU & more disk reads in sql server. Also Same stored procedure it takes more than 2 mins to run.
3) There is any configuration in sql server where to keep Table variable (in memory or in disk) Thanks in advance. Let me know if you need more detail.
KBA & Articles:
http://support.microsoft.com/default.aspx/kb/305977
http://www.sql-server-performance.com/temp_tables.asp
http://www.odetocode.com/Articles/365.aspx
http://databases.aspfaq.com/database/should-i-use-a-temp-table-or-a-table-variable.html Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.
See
http://toponewithties.blogspot.com/2004/08/difference-between-table-variable-and.html Roji. P. Thomas
http://toponewithties.blogspot.com

Also read
http://www.aspfaq.com/show.asp?id=2475 Madhivanan Failing to plan is Planning to fail
quote:Originally posted by sgkgops We are using Table Variable in our heavly used storedprocedure. In the storedprocedure, we have 4-6 separate inserts/deletes/updates to table variable using select statements. Usually, the storedprocedure takes 0 – 4 secs for each run. My questions are..
1) I think Table variables sits in RAM. But, we can see it writes in the tempdb. if there is no sufficient RAM then does it write to disk? which degrades the performance?
If you have read articles from links posted you already know the answer.
quote:
2) when concurrent connections are more than 1000 connections then we hit 100% CPU & more disk reads in sql server. Also Same stored procedure it takes more than 2 mins to run.
Make sure that you fully qualify stored procedure for each call. That may significantly reduce cpu usage.
quote:
Thanks in advance. Let me know if you need more detail.
See if fully qualifying stored procedure name helps. If not, then post the code. Hopefully it is not too long.

]]>