none
COMPACTAÇÃO DE TABELAS RRS feed

  • Pergunta

  • Boa tarde

    Tenho um dúvidas, se e possível fazer a compactação do banco dados ou mesmo tabela no SQLSERVER, tipo tenho um bancos de dados que esta crescendo muitos ..e o culpado muitos espaços, dai acredito que tenha alguma ferramenta que possa ..fazer uma verificação e compactar o banco .

    Hoje estou fazendo assim, cria uma base vazia e faço a importação dos dados ..normalmente o banco fica menor..e melhora o desempenho  do sistema ..

    Porem e muito trabalhoso ..se alguém sabe uma forma para fazer mais rápido e seguro ..desde ja agradeço..

    at. marcos

    terça-feira, 28 de julho de 2015 19:52

Respostas

  • Boa Noite Marcos,

    Com relação a compressão, o SQL Server trabalha com o Data Compression que ajuda a reduzir o tamanho do banco de dados, assim como, otimizar a performance de I/O em ambientes com intensos workloads. 

    Segue um white paper com todo o detalhamento técnico necessário para implementar essa solução: https://technet.microsoft.com/en-us/library/dd894051(v=sql.100).aspx

    Por acaso seria o arquivo de log do SQL Server que está crescendo mais do que o esperado?


    Att, Bruno Silva.

    quarta-feira, 29 de julho de 2015 00:07
  • Marcos bom dia,

    Segue um script que eu uso para compactar database.

    use AdventureWorks2012
    go

    set nocount on
    DECLARE @Compactacao VARCHAR(MAX),
    @Do_Tables BIT = 1,
    @Do_ClusteredIndexes BIT = 1,
    @Do_NonClusteredIndexes BIT = 1 
    declare @Objetos_Compactacao table (Id int identity(1,1), Comando VARCHAR(MAX))
      
    insert into @Objetos_Compactacao(Comando)
    SELECT DISTINCT 'ALTER TABLE [' + sys.schemas.name + '].[' + sys.tables.name+ '] 
    REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)' AS InjectionSQL
      FROM sys.tables
    INNER JOIN sys.partitions ON sys.partitions.object_id = sys.tables.object_id
    INNER JOIN sys.schemas ON sys.tables.schema_id = sys.schemas.schema_id
    WHERE(sys.partitions.data_compression <> 2)
    AND (index_id = 0)
    -- and sys.tables.name not like '%SPC%'
    UNION
    SELECT DISTINCT  'ALTER INDEX [' + sys.indexes.name + '] ON [' + sys.schemas.name + '].[' + 
    sys.tables.name+ '] REBUILD PARTITION = ALL WITH ( STATISTICS_NORECOMPUTE = OFF, ONLINE = OFF, 
    SORT_IN_TEMPDB = OFF, DATA_COMPRESSION = PAGE )'
      FROM sys.indexes
    INNER JOIN sys.tables ON sys.indexes.object_id = sys.tables.object_id
    INNER JOIN sys.schemas ON sys.tables.schema_id = sys.schemas.schema_id
    INNER JOIN sys.partitions ON sys.partitions.object_id = sys.indexes.object_id AND sys.partitions.index_id = sys.indexes.index_id
      WHERE(sys.partitions.data_compression <> 2)
     AND (sys.partitions.index_id <> 0)
    -- and sys.tables.name not like '%SPC%'
    --  AND ((sys.indexes.type = 1 AND @Do_ClusteredIndexes = 1) OR @Do_ClusteredIndexes = 0)
    --  AND ((sys.indexes.type = 2 AND @Do_NonClusteredIndexes = 1) OR @Do_NonClusteredIndexes = 0)
    --delete from @Objetos_Compactacao where ID > 3

    declare @min int, @n int;

    select @n = MAX(id) from @Objetos_Compactacao

    WHILE exists(select null from @Objetos_Compactacao)
    BEGIN
    select @min = MIN(Id) from @Objetos_Compactacao

      select @Compactacao = comando
      from @Objetos_Compactacao
      where id = @min
      
      EXECUTE (@Compactacao)
      --print @Compactacao
      print 'Objeto '+convert(varchar,@min)+' de '+convert(varchar,@n)+'.';
      
      delete from @Objetos_Compactacao
      where id = @min
      end


    Se a resposta foi útil por favor classifique. Tiago Neves - @tiagolneves - acesse o meu blog http://www.tiagoneves.net

    quarta-feira, 29 de julho de 2015 12:51
  • Marcos,

    Não faça a compressão do seu banco de dados de qualquer forma! Já se perguntou, porque o banco de dados não já vem compactado? Pois é...

    Sugiro a leitura deste artigo https://technet.microsoft.com/en-us/library/dd894051%28v=sql.100%29.aspx?f=255&MSPPError=-2147217396

    Entenda que existem cenários para compressão, além disso, existem modos de compressão ROW x PAGE.

    É muito importante a redução do espaço utilizado, mas também é importante compreender os impactos que podem ser gerados.


    Vithor da Silva e Silva | SQL Server Consultant and Trainer | vithor@vssti.com.br | Blog: http://www.vssti.com.br/blog ** Por favor, lembre-se de “Marcar como Resposta” as respostas que resolveram o seu problema. **

    quarta-feira, 29 de julho de 2015 13:13

Todas as Respostas

  • Boa Noite Marcos,

    Com relação a compressão, o SQL Server trabalha com o Data Compression que ajuda a reduzir o tamanho do banco de dados, assim como, otimizar a performance de I/O em ambientes com intensos workloads. 

    Segue um white paper com todo o detalhamento técnico necessário para implementar essa solução: https://technet.microsoft.com/en-us/library/dd894051(v=sql.100).aspx

    Por acaso seria o arquivo de log do SQL Server que está crescendo mais do que o esperado?


    Att, Bruno Silva.

    quarta-feira, 29 de julho de 2015 00:07
  • Marcos bom dia,

    Segue um script que eu uso para compactar database.

    use AdventureWorks2012
    go

    set nocount on
    DECLARE @Compactacao VARCHAR(MAX),
    @Do_Tables BIT = 1,
    @Do_ClusteredIndexes BIT = 1,
    @Do_NonClusteredIndexes BIT = 1 
    declare @Objetos_Compactacao table (Id int identity(1,1), Comando VARCHAR(MAX))
      
    insert into @Objetos_Compactacao(Comando)
    SELECT DISTINCT 'ALTER TABLE [' + sys.schemas.name + '].[' + sys.tables.name+ '] 
    REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)' AS InjectionSQL
      FROM sys.tables
    INNER JOIN sys.partitions ON sys.partitions.object_id = sys.tables.object_id
    INNER JOIN sys.schemas ON sys.tables.schema_id = sys.schemas.schema_id
    WHERE(sys.partitions.data_compression <> 2)
    AND (index_id = 0)
    -- and sys.tables.name not like '%SPC%'
    UNION
    SELECT DISTINCT  'ALTER INDEX [' + sys.indexes.name + '] ON [' + sys.schemas.name + '].[' + 
    sys.tables.name+ '] REBUILD PARTITION = ALL WITH ( STATISTICS_NORECOMPUTE = OFF, ONLINE = OFF, 
    SORT_IN_TEMPDB = OFF, DATA_COMPRESSION = PAGE )'
      FROM sys.indexes
    INNER JOIN sys.tables ON sys.indexes.object_id = sys.tables.object_id
    INNER JOIN sys.schemas ON sys.tables.schema_id = sys.schemas.schema_id
    INNER JOIN sys.partitions ON sys.partitions.object_id = sys.indexes.object_id AND sys.partitions.index_id = sys.indexes.index_id
      WHERE(sys.partitions.data_compression <> 2)
     AND (sys.partitions.index_id <> 0)
    -- and sys.tables.name not like '%SPC%'
    --  AND ((sys.indexes.type = 1 AND @Do_ClusteredIndexes = 1) OR @Do_ClusteredIndexes = 0)
    --  AND ((sys.indexes.type = 2 AND @Do_NonClusteredIndexes = 1) OR @Do_NonClusteredIndexes = 0)
    --delete from @Objetos_Compactacao where ID > 3

    declare @min int, @n int;

    select @n = MAX(id) from @Objetos_Compactacao

    WHILE exists(select null from @Objetos_Compactacao)
    BEGIN
    select @min = MIN(Id) from @Objetos_Compactacao

      select @Compactacao = comando
      from @Objetos_Compactacao
      where id = @min
      
      EXECUTE (@Compactacao)
      --print @Compactacao
      print 'Objeto '+convert(varchar,@min)+' de '+convert(varchar,@n)+'.';
      
      delete from @Objetos_Compactacao
      where id = @min
      end


    Se a resposta foi útil por favor classifique. Tiago Neves - @tiagolneves - acesse o meu blog http://www.tiagoneves.net

    quarta-feira, 29 de julho de 2015 12:51
  • Marcos,

    Não faça a compressão do seu banco de dados de qualquer forma! Já se perguntou, porque o banco de dados não já vem compactado? Pois é...

    Sugiro a leitura deste artigo https://technet.microsoft.com/en-us/library/dd894051%28v=sql.100%29.aspx?f=255&MSPPError=-2147217396

    Entenda que existem cenários para compressão, além disso, existem modos de compressão ROW x PAGE.

    É muito importante a redução do espaço utilizado, mas também é importante compreender os impactos que podem ser gerados.


    Vithor da Silva e Silva | SQL Server Consultant and Trainer | vithor@vssti.com.br | Blog: http://www.vssti.com.br/blog ** Por favor, lembre-se de “Marcar como Resposta” as respostas que resolveram o seu problema. **

    quarta-feira, 29 de julho de 2015 13:13
  • Marcos,

    Gostaria de sugerir a leitura do meu artigo na revista SQL Server Magazine, justamente sobre compressão de dados no SQL Server, tenho a certeza que vai te ajudar:

    http://www.devmedia.com.br/artigo-sql-magazine-68-compactacao-de-dados-com-o-sql-server-2008/14300

    Além disso, no meu blog, você vai poder encontrar mais informações:

    https://pedrogalvaojunior.wordpress.com/2011/02/26/compressao-de-dados-no-sql-server-2008/

    Vale ressaltar que a compressão de dados no Microsoft SQL Server foi introduzido a partir da versão 2008.


    Pedro Antonio Galvao Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitario | SoroCodigos | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    sexta-feira, 31 de julho de 2015 19:07
    Moderador