Can temp tables get screwed up by multiple users running the same process at the same time?

Question

We have an instance of SQL server with multiple occurrences of the same DB. 

Additionally each instance has multiple users working on it at the same time.

We have one very complicated process that uses temp tables to pass data between stored procedures.  Lately (it had been running for years just fine) we have been getting some errors during that process that have been very difficult to pin down and so far not able to replicate - even with a data restore and rerun.

The only pattern I have found so far is that all off the corrupt data occurred when the process was run on multiple databases at the same time.  Since it is the same code on each database the temp tables have the same name and since all are being run on the same instance of SQL server they will all be in the tempdb.  Is it possible that the temp tables from one instance of the process are conflicting with the temp tables from another instance of the process?  

Answer 1

If they are #temp tables then no - unless your users are somehow all using the same spid/connection

If they are ##temp tables then yes - those are global

---

Chuck

Answer 2

Hi

nope. if you use # tables or @ tables, the tables are visible just for the session

global temporary tables (##) are visible from all the sessions

---

Javier Villegas | @javier_vill | http://sql-javier-villegas.blogspot.com/

Please click "Propose As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

Answer 3

Nope, i doubt you are getting issues with different using temp tables. your stored procedure is  giving strange errors, that could be parameter sniffing. do sp_recomplie and hopefully it would be better

Read here :

http://www.simple-talk.com/sql/t-sql-programming/parameter-sniffing/

---

Nadia Azmi

Answer 4

We have one very complicated process that uses temp tables to pass data between stored procedures.

The only pattern I have found so far is that all off the corrupt data occurred when the process was run on multiple databases at the same time.  Since it is the same code on each database the temp tables have the same name and since all are being run on the same instance of SQL server they will all be in the tempdb.

The text in bold implies that the code is using global temp tables (with '##' as a prefix to the table name). I'm sure the database sizes have grown over time and the queries are getting slower thus trying to hold on to the global temp table(s) a little longer. This is introducing conflicts with other processes running in parallel.

You have answered your own question in the subsequent paragraph :-)

I guess it is time to consider performance optimization of that code or to re-architect that process. 

---

Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers.
Thanks!
Aalam | Blog (http://aalamrangi.wordpress.com)

Answer 5

I would agree with all of the helpful posts here that tell you the difference between local & global temporary tables. So can you look at the procs and tell us if its using a local or global temp table ? if its local temp tables, then you have nothing to worry about as its scope is limited to the session.

When you say that the only pattern you have found is that corrupt data occurred and you suspect its because of temp table - this is your interpretation of the cause of the issue. Can you give us an unbiased description of the actual error? Is the user or any batch process getting any error messages ? Or you are seeing unexpected data and that is the issue ? If you can some details about the errors you are seeing, we can try to analyze this better.

Hope this helps !

---

Sanil Mhatre | Database Developer | MCTS | If you find my reply useful in any way, please vote it as helpful. If it has helped answer your question, please mark it as Answer. http://sqlwithsanil.com