主页 / dba / 问题 / 52819
Accepted
Craig Efrein
Asked: 2013-11-06 06:55:50 +0800 CST

在 tempdb 以外的数据库中为每个会话创建临时表

  • 772

使用 SQL Server，有没有办法在用户数据库中为每个用户会话创建一个临时表，其方式与在 tempdb 中创建的方式完全相同？

在大多数情况下使用 tempdb 没问题，但我看到的一些操作会产生大量 IO，并且在某些情况下最终会使 templog 文件饱和。

所以这是一个典型的情况

-- 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

工作继续进行，直到@rownum 达到 5000000，并且因为它在事务中，所以 tempdb 不能收缩。与此同时，警报开始出现，LOGS 磁盘已满。执行 SHRINKFILE 是无用的，因为事务处于打开状态。

该工作必须在 tempdb 中完成，因为创建该临时表的进程可能会运行多次，或者可能有多个进程使用相同的表名。

-- 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.

因此，在我找到一种方法来模拟 tempdb 中每个会话的临时表功能之前，我们必须继续跟踪有问题的查询，并基本上使用 tempdb 进行空中交通控制。

这是我目前正在处理的情况

在此处输入图像描述

tempdb 的日志目前在 40GB+ 并且快速攀升。

sql-server tempdb

1 个回答

  1. Best Answer

    没有直接的方法可以将临时表行为复制到非用户数据库中。这从简单的冲突预防开始，并延续了临时表的几个优点（如减少日志记录），这些优点是无法复制的。

    要在用户数据库中创建避免冲突的数据集，您基本上有两种选择：

    1.将所有数据放在一个表中，并使用表中的某种会话ID处理分离

    可以用于单表方法的最明显的会话 ID 是 @@SPID。但是您也可以使用其他“随机”值。为了实现更大的物理分离，您可以在该列上对表进行分区。明显的缺点是所有会话都必须使用相同的表格布局。

    2.创建一个随机表名，从此使用动态sql。

    random-table-name 选项将使您的代码非常难以阅读。但是由于 SQL Server 不允许语句根据存储在变量中的名称访问对象，因此动态 SQL 是您唯一的选择。

    如果您采用这种方式，我建议在名称中包含当前 session_id 和 request_start_time（来自 sys.dm_exec_requests）以及 guid。这样就很容易编写某种垃圾收集器来四处走动并删除留下的表。根据您的对象寿命预期，您可能必须使用不同的时间戳，例如 sys.dm_exec_connections 中的 connect_time，但想法是相同的。一旦具有该开始时间的请求消失，该对象就不能再使用了，因为保存其名称的变量也超出了范围。

    • 3

相关问题