Variável de Tabela adquire ou não locks?

Ola Pessoal,

Na semana passada surgiu uma questão na hashtag sqlhelp no twitter, bom se você ainda não utiliza twitter esta perdendo uma ótima oportunidade de estar conectado com os experts em SQL Server mundo afora. Mas voltando ao assunto uma pessoa perguntou porque ele devia utilizar variável de tabela ao invés de usar tabela temporária. Varias pessoas responderam e eu claro também respondi que tabela variável é uma boa alternativa pois a mesma não gerava lock overhead. Com essa minha resposta Kevin Boles(Twitter) veio me pedindo para explicar porque não gerava lock uma variável de tabela. Eu falei para ele que tinha lido sobre isso no livro SQL Server 2008 Query Performance Tuning Distilled de Grant Frichey(Blog|Twitter), porem não sabia ao certo pois nunca tinha testado. Vou colocar aqui para vocês o trecho do livro citando o assunto:

“No lock overhead: Since table variables are treated like local variables (not database objects), the locking overhead associated with regular tables and temporary tables does not exist.”

Kevin me explicou que uma variável de tabela  é armazenada mesmo que em nivel de sessão no TempDB igual a uma tabela temporária então por sua vez gera um lock sobre o recurso sendo utilizado.

Vou realizar uma demo e mostrar como o lock acontece.

begin tran commit

declare @i int = 100000

declare @ponteiro int = 0

declare @table table (id int identity(1,1), assunto varchar(50))

while (@i > @ponteiro)

begin

insert into @table values(‘Table Variable’)

set @ponteiro = @ponteiro + 1

end

select * from @table

Em uma outra aba rode a seguinte query:

select resource_type,resource_subtype, DB_NAME(resource_database_id) as DBNAME,

request_mode,request_type

from sys.dm_tran_locks

where resource_database_id = 2

Você terá o seguinte resultado:

A primeira ocorrência é essa, um bloqueio exclusivo em um objeto nesse caso nossa variável de tabela.

Após o termino da operação de insert um select é executado contra essa variável. Abra uma outra seção no Management Studio e execute um select na DMV sys.dm_tran_locks novamente  e você verá o bloqueio que foi inserido novamente na variável de tabela.

Como vocês podem ver dois locks foram colocados, o primeiro foi um lock no objeto que no nosso caso é a variável de tabela e o modo de lock é o de esquema (Sch-S). O segundo é lock compartilhado (S) do tipo HOBT que é um lock em uma Heap ou uma B-Tree, que é o caminho mais básico para acesso a estrutura dos dados.

Podemos notar também que esse segundo registro nos trouxe um valor a mais na coluna resource_subtype que foi o valor :”BULK_OPERATION”, se verificarmos no books online o que nos diz o valor HOBT. BULK_OPERATION a mesma nos retorna.

HOBT. BULK_OPERATION: É uma carga de dados em uma Heap-otimizada juntamente com um scan concorrente.

Aqui nossa heap-otimizada é a nossa variável de tabela que é realmente otimizada para quando se trata de armazenar poucos dados, ou seja, como no fim da minha inserção eu já estava lendo os dados, esse foi o sub recurso utilizado em meu lock do tipo HOBT.

Depois desses testes, conversei novamente com Grant o autor do livro sobre esse problema e após um tempo ele avaliou que o livro realmente estava não errado, mas estava faltando informações, e me garantiu que o livro será ajustado.

É isso ai pessoal, hoje também aprendi bastante com isso, principalmente em sempre testar aquilo que se esta estudando, pois quando se trata de SQL Server ninguém pode saber tudo.

Até mais.

Marcos Freccia
MCP | MCTS SQL Server 2008

Sobre Marcos Freccia

MVP em SQL Server (Data Plataform) , especialista em SQL Server, e atualmente trabalhando com Microsoft Azure!

Publicado em maio 27, 2011, em Tuning, VirtualPass e marcado como , . Adicione o link aos favoritos. 2 Comentários.

  1. Galera estou com a seguinte dúvida.
    performance

    Tenho um processo de carga e faço um insert de 1000 registros e estou com problema de timeout no meu banco de dados porque eu envio linha a linha do List para a procedure de insert e assim vai inserindo linha a linha,o problema é que o timeout da conexão com o banco atinge e para na 200 linha.

    Para resolver isso eu converti o List em Datable e para fazer o processo via BulkCopy ou seja a minha procedure manda um DataTable para procedure de insert eu criei uma variavel do tipo @Table e funcionou perfeito rápido.

    Minha pergunta é em termos de performance do banco de dados o que compromete ? Usar a Variável do tipo @Table não é performático ? Popular usa variável com 1000 linhas com 5 colunas é destruir a performance do banco de dados e os DBA não iram gostar nada disso ? Sendo que este processo será executa 1 vez ao dia ?

    Aguardo comentários de todos.

  1. Pingback: Performance começa na modelagem « Alex Souza

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s

%d blogueiros gostam disto: