0

Using SQL Server, Is there a way to create a temporary table per user session in a user database in the same exact way they are created in tempdb?

Using tempdb is fine in most cases, but some of the operations I've been seeing generate a lot of IO and in some cases end up saturating the templog file.

So here is a typical situation

-- create the table
create table #some_table(t int)

-- Do some work
declare @rownum int = 1

begin transaction
while 1=1
begin
    insert into #some_table values(@rownum)
    if @rownum > 5000000
        break

    set @rownum = @rownum + 1

end

Work continues on until @rownum reaches 5000000 and because its within a transaction, tempdb can't shrink. In the mean time, alerts start showing up and the LOGS disk fills up. Doing a SHRINKFILE is useless because the transaction is left open.

The work has to be done in tempdb because the process that created that temporary table could be run multiple times or there could be multiple processes that use the same table name.

-- show the contents of #some_table
set statistics io on

select * from #some_table

set statistics io off   


-- results
(5000001 row(s) affected)
Table '#some_table_________________________________________________________________________________________________________00000000001F'. Scan count 1, logical reads 9, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

So until I find a way to simulate that temporary table per session ability in tempdb, we have to continue tracking down offending queries and basically play air traffic control with tempdb.

Here is the situation I'm currently dealing with

enter image description here

tempdb's log is currently at 40GB+ and climbing fast.

6
  • For the specific example in your question you could use a table variable. These only prevent the tempdb log being truncated for as long as the statement not the whole transaction. (Though ROLLBACK won't affect these in the same way either. It is not clear why you have this all in a transaction anyway from the snippet shown) Nov 5, 2013 at 15:00
  • The problem with table variables, unless I'm mistaken is that I can't add indexes to them. Nov 5, 2013 at 15:09
  • You can add some indexes. Specifically those backed by a unique/primary key constraint in current versions. (The answer to your main question is "no" btw. There is no way to create a temporary table per user session in a user database in the same exact way they are created in tempdb) Nov 5, 2013 at 15:11
  • Why would you insert 5,000,000 rows, one at a time, in a loop? There are much, much, much more efficient ways to generate sets. Nov 5, 2013 at 15:12
  • The loop was just to be able to fill up tempdb's log file and simplify monitoring the output of DBCC SQLPERF(logspace) . The queries causing us problems aren't necessarily like the example provided. Nov 5, 2013 at 15:16

1 Answer 1

3

There is no direct way to copy temp table behavior into non user databases. This starts with the easy collision preventions and continues to several advantages of temp tables (like reduced logging) that just can't be reproduced.

To create data sets in user databases that avoid collisions you have basically two options:

1. put all the data in one table and deal with the separation using some kind of session id in the table

The most obvious session id you could use for the single table approach is the @@SPID. But you could use other "random" values too. To achieve greater physical separation, you could partition the table on that column. The obvious disadvantage is that all sessions have to use the same table layout.

2. create a random table name and use dynamic sql from then on.

The random-table-name option is going to make your code very hard to read. But as SQL Server does not allow statements to access objects based on names stored in variables, dynamic SQL is your only option.

If you go this way I recommend to include the current session_id and request_start_time (from sys.dm_exec_requests) as well as a guid in the name. That way it is easy to write some kind of garbage collector that goes around and deletes tables that got left behind. Depending on your object life time expectations you might have to use a different time stamp like the connect_time in sys.dm_exec_connections but the idea is the same. Once the request with that start time is gone, the object cannot be of use anymore because the variable holding its name went out of scope too.

1
  • Hello Sebastien, thank you for the answer, the coders went with Martin's answer. Nov 6, 2013 at 8:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.