Árvore de páginas

Versões comparadas

Chave

  • Esta linha foi adicionada.
  • Esta linha foi removida.
  • A formatação mudou.
Dica
titleDica: Links relacionados

Os links descritos a seguir trazem informações referentes ao assunto abordado nesta página.

(Microsoft) Datafiles e Filegrous no banco de dados(Microsoft) Guia de arquitetura e design de índices: posição do índice em grupos de arquivos ou esquemas de partição(Microsoft) Modelos de Recuperação do SQL Server • (TDN) Compactação de dados: SQL Server 

Informações
titleO que encontrarei nesta página?

Nesta página, você visualizará informações sobre datafiles, filegroups e as recomendações da TOTVS, alinhada com as recomendações da fabricante do banco de dados, Microsoft. Em seguida, há uma demonstração, onde um banco de dados é criado com um datafile, um filegroup é criado, como adicionar datafiles ao filegroup criado, como mover os índices entre os datafiles e como configurar a conexão no DBAccess com um segundo filegroup.

Índice

Ambientes Protheus e o banco de dados MSSQL Server

Ao criar um banco de dados para uso do Protheus, muitos ambientes foram montados com apenas um datafile (arquivo de dados) e um filegroup (grupo de arquivos) no banco de dados MSSQL Server. Com o decorrer do uso do produto, este cenário pode decorrer em perda de performance, já que o arquivo crescerá conforme novos dados são inseridos; consequentemente, o banco levará mais tempo para inserir registros em tabelas, ou para retornar informações necessárias. 

Esta página foi elaborada com o intuito de garantir a melhor experiência de uso do ERP Protheus e a adoção de boas práticas de mercado ao se configurar um banco de dados. Aqui, será demonstrado como mover índices não cluster do filegroup (grupo de arquivos) “Primary” para o filegroupSecondary”, e como mover tabelas de um datafile (arquivo físico) para outros dois arquivos.

A fabricante (Microsoft) indica que ao criar o índice não-cluster em um filegroup diferente, você pode obter ganhos de desempenho se os filegroups estiverem usando unidades físicas diferentes com seus próprios controladores. 

Expandir
titleTabelas informativas: arquivos e grupos de arquivos

Segundo a documentação oficial do MSSQL Server, o SGBD possui três tipos de arquivos:

ArquivoDescrição
PrimáriaContém informações de inicialização do banco de dados e aponta para os outros arquivos no banco de dados. Todo banco de dados possui um arquivo de dados primário. A extensão de nome de arquivo indicada para arquivos de dados primários é .mdf.
SecundárioArquivos de dados opcionais definidos pelo usuário. Os dados podem ser distribuídos em vários discos, colocando cada arquivo em uma unidade de disco diferente. A extensão de nome de arquivo indicada para arquivos de dados secundários é .ndf.
Log de TransaçõesO log contém informações usadas para recuperar o banco de dados. Deve haver, no mínimo, um arquivo de log para cada banco de dados. A extensão de nome de arquivo indicada para arquivos de transação é .ldf.

E também possui diferentes grupos de arquivos:

Grupo de arquivosDescrição
PrimáriaO grupo de arquivos que contém o arquivo primário. Todas as tabelas do sistema fazem parte do grupo de arquivos primário.
Dados otimizados para memóriaUm grupo de arquivos com otimização de memória baseia-se no grupo de arquivos do fluxo de arquivos
Fluxo de arquivos
Definido pelo usuárioQualquer grupo de arquivos que seja criado pelo usuário quando o usuário cria o banco de dados pela primeira vez ou quando o modifica posteriormente.
Informações
titleDatafiles (arquivos de dados), filegroups (grupos de arquivos) e tipos de filegroups

Os datafiles (arquivos de dados) e logfiles (arquivos de log) são arquivos do banco de dados; datafiles armazenam dados e objetos, e logfiles armazenam informações sobre as transações no banco de dados. Os filegroups (grupos de arquivos) são separadores dos datafiles, e podem ser utilizados para facilitar a administração e alocação de recursos. Mais informações podem ser encontradas no site da fabricante.  

A indicação da TOTVS, assim como da Microsoft, é manter um filegroup específico para índices não cluster

Para exemplificar a separação dos índices e dos dados, criamos uma demonstração com o Database “TPPRD”, exibindo a mudança de índices para outro filegroup e distribuição das tabelas em mais de um datafile.

Preparo do ambiente de exemplo

  • Banco de dados criado: TPPRD
  • Datafile: “TPPRD_Data01”
  • Filegroup: “PRIMARY”

Print de exemplo do banco de dados criado:

No DBAccess, o ambiente está configurado informando apenas os dados de conexão com o banco de dados, conforme print:

Antes de começar...

  1. Realize e garanta que o backup da base seja realizado com sucesso antes de iniciar o processo;
  2. Realize e garanta que o backup seja extraído após o término do processo;
  3. Aplique todas as recomendações em ambientes de desenvolvimento e de homologação antes de aplicar em ambiente de produção;
  4. Faça a validação e marcação de tempo para comparativo das principais rotinas do ambiente, antes e depois de realizar o procedimento de mudança de índices;
  5. Garanta que o Protheus e serviços do DBAccess estejam parados durante a atividade;
  6. A atividade deve ser acompanhada por um profissional especializado em Banco de Dados.

Alterando o modo de recuperação do banco de dados

Coloque o banco de dados em modo Simple para evitar o crescimento excessivo do arquivo de log do SQL Server durante esta manutenção. O print a seguir mostra como selecionar a opção por meio do SQL Server Management Studio:

Alternativamente, execute o seguinte comando:

Bloco de código
languagesql
titleScript para alteração do modo de recuperação da database
USE master
GO
ALTER DATABASE [TPPRD]
SET RECOVERY SIMPLE
GO

Adicionando arquivos ao filegroup Primary

Utilizando a área gráfica do SQL Server Management Studio (SSMS):

  1. Clique com o botão direito no banco de dados, abra propriedades;
  2. Selecione Files;
  3. Adicione dois datafiles, clicando em Add;
  4. Duas linhas serão adicionadas logo abaixo do arquivo de Log. Atribua o nome lógico destes datafiles;
  5. Altere o tamanho inicial dos Filegroups para 1024MB (variável dependo do tamanho do banco de dados);
  6. Nos três pontinhos (...), altere o tamanho de Autogrowth para 128MB (variável dependo do tamanho do banco de dados). Clique em Ok;
  7. Especifique o path (caminho físico dos datafiles; recomenda-se utilizar uma unidade diferente que os datafiles Primary);
  8. Altere o nome físico dos dois arquivos.

Print de exemplo:

Caso queira utilizar T-SQL, execute o seguinte comando, alterando o nome da database para o nome de sua database:

Bloco de código
languagesql
titleScript para adicionar datafiles ao banco de dados
USE [master]
GO
ALTER DATABASE [TPPRD] ADD FILE 
( NAME = N'TPPRD_Data02', FILENAME = N'H:\MSSQLSERVER\TPPRD\TPPRD_Data02.ndf' , SIZE = 1048576KB , FILEGROWTH = 131072KB ),
( NAME = N'TPPRD_Data03', FILENAME = N'H:\MSSQLSERVER\TPPRD\TPPRD_Data03.ndf' , SIZE = 1048576KB , FILEGROWTH = 131072KB )
TO FILEGROUP [PRIMARY]
GO

Criando filegroup SECONDARY

É possível criar o filegroup usando T-SQL (linha de comando) ou na área gráfica do SQL Server Management Studio (SSMS). 

Clique com o botão direito no banco de dados, abra propriedades e selecione Filegroups, logo após adicione um Filegroup, uma linha será adicionada logo abaixo de Primary. Nomeie este Filegroup para Secondary, e clique em OK:

Ou execute o seguinte comando:

Bloco de código
languagesql
titleScript para adicionar filegroup ao banco de dados
USE [master] 
GO 
ALTER DATABASE [TPPRD] ADD FILEGROUP [SECONDARY] 
GO

Adicionando arquivos ao filegroup secondary

Crie usando T-SQL (linha de comando) ou na área gráfica do SQL Server Management Studio (SSMS). 

  1. Clique com o botão direito no banco de dados, abra propriedades;
  2. Selecione Files;
  3. Adicione três datafiles, clicando em Add;
  4. Três linhas serão adicionadas logo abaixo do arquivo de Log. Atribua o nome lógico para esses Datafiles;
  5. Altere o Filegroup dos três arquivos para SECONDARY;
  6. Altere o tamanho inicial dos Filegroups para 1024MB (valor variável, dependo do tamanho do banco de dados);
  7. Nos três pontinhos (...), altere o tamanho de Autogrowth para 128MB (valor variável, dependo do tamanho do banco de dados). Clique em Ok;
  8. Especifique o path (caminho físico dos datafiles; alinhando-se à boas práticas de mercado, utilize uma unidade diferente dos datafiles Primary);
  9. Altere o nome físico dos três arquivos.

Ou execute o seguinte comando:

Bloco de código
languagesql
titleScript para adicionar os datafiles ao filegroup
USE [master]
GO
ALTER DATABASE [TPPRD] ADD FILE
( NAME = N'TPPRD_Index01', FILENAME =N'F:\MSSQLSERVER\TPPRD\TPPRD_Index01.ndf' , SIZE = 1048576KB , FILEGROWTH = 131072KB ),
( NAME = N'TPPRD_Index02', FILENAME =N'F:\MSSQLSERVER\TPPRD\TPPRD_Index02.ndf' , SIZE = 1048576KB , FILEGROWTH = 131072KB ),
( NAME = N'TPPRD_Index03', FILENAME =N'F:\MSSQLSERVER\TPPRD\TPPRD_Index03.ndf' , SIZE = 1048576KB , FILEGROWTH = 131072KB ) 
TO FILEGROUP [SECONDARY]
GO

Após criar três arquivos para índices, prossiga para realizar a distribuição dos índices para o datafile "SECONDARY".

Movendo índices não cluster para filegroup secondary

Após criar o filegroup e datafiles de índices, os índices não cluster serão movidos para o novo filegroup criado.

O script a seguir, ao ser executado, gera os comandos para a operação de move dos índices:

Bloco de código
languagesql
titleScript para gerar o DML dos índices
declare @SchemaName varchar(100)declare @TableName varchar(256)
declare @IndexName varchar(256)
declare @ColumnName varchar(100)
declare @is_unique varchar(100)
declare @IndexTypeDesc varchar(100)
declare @FileGroupName varchar(100)
declare @is_disabled varchar(100)
declare @IndexOptions varchar(max)
declare @IndexColumnId int
declare @IsDescendingKey int 
declare @IsIncludedColumn int
declare @TSQLScripCreationIndex varchar(max)
declare @TSQLScripDisableIndex varchar(max)
declare CursorIndex cursor for
 select schema_name(t.schema_id) [schema_name], t.name, ix.name,
 case when ix.is_unique = 1 then 'UNIQUE ' else '' END 
 , ix.type_desc,
 case when ix.is_padded=1 then 'PAD_INDEX = ON, ' else 'PAD_INDEX = OFF, ' end
 + case when ix.allow_page_locks=1 then 'ALLOW_PAGE_LOCKS = ON, ' else 'ALLOW_PAGE_LOCKS = OFF, ' end
 + case when ix.allow_row_locks=1 then  'ALLOW_ROW_LOCKS = ON, ' else 'ALLOW_ROW_LOCKS = OFF, ' end
 + case when INDEXPROPERTY(t.object_id, ix.name, 'IsStatistics') = 1 then 'STATISTICS_NORECOMPUTE = ON, ' else 'STATISTICS_NORECOMPUTE = OFF, ' end
 + case when ix.ignore_dup_key=1 then 'IGNORE_DUP_KEY = ON, ' else 'IGNORE_DUP_KEY = OFF, ' end
 + 'SORT_IN_TEMPDB = OFF, FILLFACTOR =' + '90'
 , ix.is_disabled , 'SECONDARY'
 from sys.tables t 
 inner join sys.indexes ix on t.object_id=ix.object_id
 where ix.type>0 and ix.is_primary_key=0 and ix.is_unique_constraint=0 -- and schema_name(tb.schema_id)= @SchemaName and tb.name=@TableName
 and t.is_ms_shipped=0 and t.name<>'sysdiagrams' and ix.type_desc NOT LIKE 'CLUSTERED' and ix.name NOT LIKE '%UNQ'
 order by schema_name(t.schema_id), t.name, ix.name
open CursorIndex
fetch next from CursorIndex into  @SchemaName, @TableName, @IndexName, @is_unique, @IndexTypeDesc, @IndexOptions,@is_disabled, @FileGroupName
while (@@fetch_status=0)
begin
 declare @IndexColumns varchar(max)
 declare @IncludedColumns varchar(max)
 
 set @IndexColumns=''
 set @IncludedColumns=''

 declare CursorIndexColumn cursor for 
  select col.name, ixc.is_descending_key, ixc.is_included_column
  from sys.tables tb 
  inner join sys.indexes ix on tb.object_id=ix.object_id
  inner join sys.index_columns ixc on ix.object_id=ixc.object_id and ix.index_id= ixc.index_id
  inner join sys.columns col on ixc.object_id =col.object_id  and ixc.column_id=col.column_id
  where ix.type>0 and (ix.is_primary_key=0 or ix.is_unique_constraint=0)
  and schema_name(tb.schema_id)=@SchemaName and tb.name=@TableName and ix.name=@IndexName
  order by ixc.index_column_id

 open CursorIndexColumn 
 fetch next from CursorIndexColumn into  @ColumnName, @IsDescendingKey, @IsIncludedColumn

 while (@@fetch_status=0)
 begin
  if @IsIncludedColumn=0 
   set @IndexColumns=@IndexColumns + @ColumnName  + case when @IsDescendingKey=1  then ' DESC, ' else  ' ASC, ' end
  else 
   set @IncludedColumns=@IncludedColumns  + @ColumnName  +', ' 
  fetch next from CursorIndexColumn into @ColumnName, @IsDescendingKey, @IsIncludedColumn
 end
 close CursorIndexColumn
 deallocate CursorIndexColumn

 set @IndexColumns = substring(@IndexColumns, 1, len(@IndexColumns)-1)
 set @IncludedColumns = case when len(@IncludedColumns) >0 then substring(@IncludedColumns, 1, len(@IncludedColumns)-1) else '' end

 --  print @IndexColumns

 --  print @IncludedColumns

 set @TSQLScripCreationIndex =''
 set @TSQLScripDisableIndex =''
 set @TSQLScripCreationIndex='CREATE '+ @is_unique  +@IndexTypeDesc + ' INDEX ' +QUOTENAME(@IndexName)+' ON ' + QUOTENAME(@SchemaName) +'.'+ QUOTENAME(@TableName)+ '('+@IndexColumns+') '+ 
  case when len(@IncludedColumns)>0 then CHAR(13) +'INCLUDE (' + @IncludedColumns+ ')' else '' end + CHAR(13)+'WITH ( DROP_EXISTING = ON,' + @IndexOptions+ ') ON ' + QUOTENAME(@FileGroupName) + ';'  
 if @is_disabled=1 
  set  @TSQLScripDisableIndex=  CHAR(13) +'ALTER INDEX ' +QUOTENAME(@IndexName) + ' ON ' + QUOTENAME(@SchemaName) +'.'+ QUOTENAME(@TableName) + ' DISABLE;' + CHAR(13) 
 print @TSQLScripCreationIndex
 print @TSQLScripDisableIndex
 fetch next from CursorIndex into  @SchemaName, @TableName, @IndexName, @is_unique, @IndexTypeDesc, @IndexOptions,@is_disabled, @FileGroupName
end

close CursorIndex
deallocate CursorIndex

Print de exemplo de resultado da geração do DML: 

Copie os DMLs gerados como resultado do script anterior e execute em uma nova janela do SSMS. Esta operação moverá os índices para o novo filegroup Secondary.

Até aqui, apenas as informações (dados e índices) que já existiam no banco de dados foram distribuídas entre filegroups

Alterar banco de dados para modo full

Reconfigure o banco de dados para modo Full, revertendo a alteração sugerida do início.

Ou execute o comando:

Bloco de código
languagesql
titleRevertendo o modo de recuperação do banco de dados
USE master
GO
ALTER DATABASE [TPPRD]
SET RECOVERY FULL
GO

Configuração do DBAccess

Após a criação do filegroup Secondary e a operação de move dos índices, abra o Monitor do DBAccess. Na aba Configurações, em Microsoft SQL, há a seção de Tablespaces. Em índices, inclua a chave Secondary (referente ao filegroup criado para índices), conforme print. Clique em salvar:

A partir deste momento, o DBAccess irá gerenciar os novos dados que forem inseridos, ou dados que sejam alterados, de maneira separada das informações relativas a índices. 

Manutenção na base de dados

Aviso

Não é necessário executar o Shrink com alta frequência. Este procedimento de manutenção foi realizado, neste caso, para liberar mais de 70% de espaço na base de dados, conforme evidências apresentadas.

Após a manutenção dos índices, datafiles e filegroups, foi executado o shrink do datafile Primário da seguinte forma:

Após liberar o espaço do datafile Primário, realizamos a Foi executada a compactação de dados e índices, conforme indicação para boa performance do Ambiente ERP.   O print a seguir mostra o consumo da database após o procedimento.