none
Truncate Table - Alocação física de espaço em disco RRS feed

  • Pergunta

  • Senhores, boa tarde.

    Tenho uma dúvida, gostaria de saber se ao criar um indíce Clustered numa tabela Heap, e em seguida realizar o TRUNCATE da tabela, o espaço físico em disco será liberado para OS, ou ainda assim o espaço ficará alocado no arquivo de dados?

    Att,

    Gabriel David

    domingo, 5 de maio de 2019 15:52

Todas as Respostas

  • Deleted
    domingo, 5 de maio de 2019 19:28
  • José,

    Salvo engano, e eu posso estar enganado, mas já presenciei isso na minha jornada, o Truncate Table realiza o processo de liberação dos dados e espaço em disco deixando de ser alocado e se tornando desalocado, ao contrário do Delete que por padrão requer um tempo de contenção e retenção das áreas marcadas para serem excluídas.

    Ambos são muito similares, mas o Truncate é mais rápido que o Delete e menos impactando no que se refere ao uso do arquivo de log.

    Mas para evitar falar bobagens e manter como você diz a qualidade técnica das respostas, busquei mais informações na documentação oficial Microsoft, que destaca os seguintes pontos em comparação ao comando Delete:

    "Em comparação com a instrução DELETE, o TRUNCATE Table tem as seguintes vantagens:

    Menos espaço de log de transações é usado.

    A instrução DELETE Remove linhas uma de cada vez e registra uma entrada no log de transações para cada linha excluída. TRUNCATE TABLE remove os dados desalocando as páginas de dados usadas para armazenar os dados da tabela e registra somente os desalocações de página no log de transações.

    Menos bloqueios são normalmente usados.

    Quando a instrução DELETE é executada usando um bloqueio de linha, cada linha na tabela é bloqueada para exclusão. TRUNCATE TABLE sempre bloqueia a tabela (incluindo um bloqueio de esquema (SCH-M)) e a página, mas não cada linha.

    Sem exceção, zero páginas são deixadas na tabela.

    Depois que uma instrução DELETE é executada, a tabela ainda pode conter páginas vazias. Por exemplo, páginas vazias em um heap não podem ser desalocadas sem pelo menos um bloqueio de tabela (LCK_M_X) exclusivo. Se a operação de exclusão não usar um bloqueio de tabela, a tabela (heap) conterá muitas páginas vazias. Para índices, a operação de exclusão pode deixar páginas vazias para trás, embora essas páginas serão desalocadas rapidamente por um processo de limpeza em segundo plano.

    TRUNCATE TABLE remove todas as linhas de uma tabela, mas a estrutura da tabela e suas colunas, restrições, índices e assim por diante permanecem. Para remover a definição de tabela, além de seus dados, use a instrução DROP TABLE.

    Se a tabela contiver uma coluna de identidade, o contador dessa coluna será redefinido para o valor de propagação definido para a coluna. Se nenhuma semente foi definida, o valor padrão 1 é usado.

    Para manter o contador de identidade, use DELETE em vez disso."

    https://docs.microsoft.com/en-us/sql/t-sql/statements/truncate-table-transact-sql?view=sql-server-2017


    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]



    terça-feira, 7 de maio de 2019 11:14
    Moderador
  • Deleted
    terça-feira, 7 de maio de 2019 12:12
  • José,

    Ok, compreendi a sua observação, sim eu conheço o método e a forma que o SQL Server trabalha, justamente fazendo o desalocamento das áreas que estão envolvidas na tabela que esta sofrendo o Truncate ou Delete.

    Em relação ao arquivo de banco de dados, aqueles com extensão .MDF ou .NDF, eu também conheço sua estrutura e funcionamento, como também, as estrutura das tabelas, aquelas formadas de páginas de dados, que possuem suas numerações, sei que estão entre aspas espalhadas por toda área que forma o arquivo.

    No que você se refere a reler a dúvida não preciso, somente estou apresentando um outro ponto de post com base no post do Gabriel, destacando comportamento no uso do arquivo de log e também até mesmo argumentando sobre o uso do Delete.

    E quando me refiro a movimentar ou remover espaço em disco, sim isso é um procedimento feito dentro da estrutura do próprio arquivo de dados, algo que deixa de ser entendido como espaço alocado e vira não alocado, talvez a interpretação de liberar espaço em disco eu tenha me expressado mal, mas que a movimentação ocorre isso ocorre, o que vai ao longo de tempo poder ser considerada como uma possível fragmentação.

    Esse tipo de análise e troca de experiência é muito importante.


    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]




    terça-feira, 7 de maio de 2019 12:33
    Moderador
  • José,

    Salvo engano, e eu posso estar enganado, mas já presenciei isso na minha jornada, o Truncate Table realiza o processo de liberação dos dados e espaço em disco deixando de ser alocado e se tornando desalocado, ao contrário do Delete que por padrão requer um tempo de contenção e retenção das áreas marcadas para serem excluídas.

    Ambos são muito similares, mas o Truncate é mais rápido que o Delete e menos impactando no que se refere ao uso do arquivo de log.

    Mas para evitar falar bobagens e manter como você diz a qualidade técnica das respostas, busquei mais informações na documentação oficial Microsoft, que destaca os seguintes pontos em comparação ao comando Delete:

    "Em comparação com a instrução DELETE, o TRUNCATE Table tem as seguintes vantagens:

    Menos espaço de log de transações é usado.

    A instrução DELETE Remove linhas uma de cada vez e registra uma entrada no log de transações para cada linha excluída. TRUNCATE TABLE remove os dados desalocando as páginas de dados usadas para armazenar os dados da tabela e registra somente os desalocações de página no log de transações.

    Menos bloqueios são normalmente usados.

    Quando a instrução DELETE é executada usando um bloqueio de linha, cada linha na tabela é bloqueada para exclusão. TRUNCATE TABLE sempre bloqueia a tabela (incluindo um bloqueio de esquema (SCH-M)) e a página, mas não cada linha.

    Sem exceção, zero páginas são deixadas na tabela.

    Depois que uma instrução DELETE é executada, a tabela ainda pode conter páginas vazias. Por exemplo, páginas vazias em um heap não podem ser desalocadas sem pelo menos um bloqueio de tabela (LCK_M_X) exclusivo. Se a operação de exclusão não usar um bloqueio de tabela, a tabela (heap) conterá muitas páginas vazias. Para índices, a operação de exclusão pode deixar páginas vazias para trás, embora essas páginas serão desalocadas rapidamente por um processo de limpeza em segundo plano.

    TRUNCATE TABLE remove todas as linhas de uma tabela, mas a estrutura da tabela e suas colunas, restrições, índices e assim por diante permanecem. Para remover a definição de tabela, além de seus dados, use a instrução DROP TABLE.

    Se a tabela contiver uma coluna de identidade, o contador dessa coluna será redefinido para o valor de propagação definido para a coluna. Se nenhuma semente foi definida, o valor padrão 1 é usado.

    Para manter o contador de identidade, use DELETE em vez disso."

    https://docs.microsoft.com/en-us/sql/t-sql/statements/truncate-table-transact-sql?view=sql-server-2017


    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]



    Será que o autoshrink não estava habilitado neste cenário?
    terça-feira, 7 de maio de 2019 15:58
  • André,

    Olha, sinceramente falando eu ficaria supreso se a opção AutoShrink venha a estar habilitado, em meus ambientes de estudo, fiz uso dela, justamente para entender as diferenças do Delete em relação ao Truncate.

    O fato importante que ao trabalhar com o AutoShrink ativo, os impactos apresentados nas manipulação de dados se tornam mais perceptíveis.

    Além disso, ao processar o Truncate Table, as movimentações geradas após o procedimento de expurgo físico dos dados fica mais notavél, devido ao truncate ser mais rápido e gerar menos escritas no arquivo de log.


    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    • Sugerido como Resposta IgorFKModerator segunda-feira, 24 de junho de 2019 18:36
    terça-feira, 7 de maio de 2019 22:30
    Moderador