Usando o SQL Server, existe uma maneira de criar uma tabela temporária por sessão de usuário em um banco de dados do usuário exatamente da mesma maneira que são criados no tempdb?
Usar o tempdb é bom na maioria dos casos, mas algumas das operações que tenho visto geram muitos IO e, em alguns casos, acabam saturando o arquivo templog.
Aqui está uma situação típica
-- 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
O trabalho continua até que @rownum atinja 5.000.000 e, como está dentro de uma transação, o tempdb não pode ser reduzido. Enquanto isso, os alertas começam a aparecer e o disco LOGS fica cheio. Fazer um SHRINKFILE é inútil porque a transação fica aberta.
O trabalho deve ser feito em tempdb porque o processo que criou essa tabela temporária pode ser executado várias vezes ou pode haver vários processos que usam o mesmo nome de tabela.
-- 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.
Portanto, até encontrar uma maneira de simular essa capacidade de tabela temporária por sessão no tempdb, temos que continuar rastreando as consultas ofensivas e basicamente jogar o controle de tráfego aéreo com o tempdb.
Aqui está a situação com a qual estou lidando atualmente
O log do tempdb está atualmente em 40 GB+ e subindo rapidamente.
Não há uma maneira direta de copiar o comportamento da tabela temporária em bancos de dados que não sejam do usuário. Isso começa com as fáceis prevenções de colisão e continua com várias vantagens das tabelas temporárias (como registro reduzido) que simplesmente não podem ser reproduzidas.
Para criar conjuntos de dados em bancos de dados de usuários que evitem colisões você tem basicamente duas opções:
1. coloque todos os dados em uma tabela e lide com a separação usando algum tipo de id de sessão na tabela
O ID de sessão mais óbvio que você pode usar para a abordagem de tabela única é o @@SPID. Mas você também pode usar outros valores "aleatórios". Para obter maior separação física, você pode particionar a tabela nessa coluna. A desvantagem óbvia é que todas as sessões precisam usar o mesmo layout de tabela.
2. crie um nome de tabela aleatório e use sql dinâmico a partir de então.
A opção random-table-name tornará seu código muito difícil de ler. Mas como o SQL Server não permite que instruções acessem objetos com base em nomes armazenados em variáveis, o SQL dinâmico é sua única opção.
Se você seguir esse caminho, recomendo incluir o session_id atual e o request_start_time (de sys.dm_exec_requests), bem como um guid no nome. Dessa forma, é fácil escrever algum tipo de coletor de lixo que circula e exclui tabelas que foram deixadas para trás. Dependendo das expectativas de tempo de vida do objeto, você pode ter que usar um registro de data e hora diferente, como connect_time em sys.dm_exec_connections, mas a ideia é a mesma. Depois que a solicitação com esse horário de início acabar, o objeto não poderá mais ser usado porque a variável que contém seu nome também saiu do escopo.