Histórico da Página
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 filegroup “Secondary”, 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
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 filegroup “Secondary”, 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 | ||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| ||||||||||||||||||
Segundo a documentação oficial do MSSQL Server, o SGBD possui três tipos de arquivos:
E também possui diferentes grupos de arquivos:
|
Informações | ||
---|---|---|
| ||
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 e , 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:
Premissas:
Antes de começar...
- Realize e garanta Realizar e garantir que o backup da base seja realizada realizado com sucesso antes de iniciar o processo;
- Realizar Realize e garantir garanta que o backup seja extraído após o término do processo;
- Aplicar Aplique todas as recomendações em ambientes de desenvolvimento e de homologação antes de aplicar em ambiente de produção;
- Indicamos Faça a validação e marcação de tempo para efeito comparativo das principais rotinas do ambiente, antes e depois de realizar o procedimento de mudança de índices;
- Garantir Garanta que o Protheus e serviços do DBAccess estejam parados durante a atividade;
- A atividade deve ser acompanhada por um profissional especializado em Banco de Dados.
Os passos desta demonstração serão:
- Colocar o Banco de Dados em modo Simple;
- Adicionar arquivos de dados ao Grupo de Arquivos Primary;
- Criar um Filegroup: “SECONDARY”;
- Adicionar arquivos de dados ao Grupo de Arquivos Secondary;
- Mover os índices não cluster do Grupo de Arquivos Primary para Secondary;
- Colocar o Banco de Dados em Modo Full.
Alterando o modo de backup do banco de dados
Vamos colocar o banco de dados em modo Simple para evitar o aumento excessivo do tamanho do arquivo de log no SQL Server.
Ou rode o seguinte comando:
Bloco de código | ||
---|---|---|
| ||
USE master
GO
ALTER DATABASE [TPPRD]
SET RECOVERY SIMPLE
GO |
Adicionando arquivos ao filegroup Primary
Crie 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;
- Selecione Files;
- Adicione dois datafiles, clicando em Add;
- Duas linhas serão adicionadas logo abaixo do arquivo de Log, criar o nome “Lógico” para esses Datafiles;
- Alterar o tamanho inicial dos Filegroups para 1024MB (pode ser maior ou menor dependo do tamanho do banco de dados);
- Nos três pontinhos (), alterar o tamanho de Autogrowth para 128MB (pode ser maior ou menor dependo do tamanho do banco de dados) clique em Ok;
- Especificar o path (caminho físico dos datafiles, é indicado unidade diferente que os datafiles Primary);
- Alterar o nome "Físico" dos dois arquivos.
Ou rode o seguinte comando:
Bloco de código | ||
---|---|---|
| ||
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 |
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 | ||||
---|---|---|---|---|
| ||||
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):
- Clique com o botão direito no banco de dados, abra propriedades;
- Selecione Files;
- Adicione dois datafiles, clicando em Add;
- Duas linhas serão adicionadas logo abaixo do arquivo de Log. Atribua o nome lógico destes datafiles;
- Altere o tamanho inicial dos Filegroups para 1024MB (variável dependo do tamanho do banco de dados);
- Nos três pontinhos (...), altere o tamanho de Autogrowth para 128MB (variável dependo do tamanho do banco de dados). Clique em Ok;
- Especifique o path (caminho físico dos datafiles; recomenda-se utilizar uma unidade diferente que os datafiles Primary);
- 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 | ||||
---|---|---|---|---|
| ||||
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 | ||||
---|---|---|---|---|
| ||||
USE [master]
GO
ALTER DATABASE [TPPRD] ADD FILEGROUP [SECONDARY]
GO |
Adicionando arquivos ao filegroup secondary
Crie usando T-SQL (linha de comando) ou
Criando filegroup SECONDARY
Podemos 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
Ou rode o seguinte comando:
- ;
- Selecione Files;
- Adicione três datafiles, clicando em Add;
- Três linhas serão adicionadas logo abaixo do arquivo de Log. Atribua o nome lógico para esses Datafiles;
- Altere o Filegroup dos três arquivos para SECONDARY;
- Altere o tamanho inicial dos Filegroups para 1024MB (valor variável, dependo do tamanho do banco de dados);
- Nos três pontinhos (...), altere o tamanho de Autogrowth para 128MB (valor variável, dependo do tamanho do banco de dados). Clique em Ok;
- Especifique o path (caminho físico dos datafiles; alinhando-se à boas práticas de mercado, utilize uma unidade diferente dos datafiles Primary);
- Altere o nome físico dos três arquivos.
Ou execute o seguinte comando:
Bloco de código | ||||
---|---|---|---|---|
| ||||
USE [master]
GO | ||||
Bloco de código | ||||
| ||||
USE [master] GO ALTER DATABASE [TPPRD] ADD FILEGROUP [SECONDARY] GO |
ADICIONAR ARQUIVOS AO GRUPO DE ARQUIVOS SECONDARY
Crie 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;
- Selecione Files;
- Adicione três datafiles, clicando em Add;
- Três linhas serão adicionadas logo abaixo do arquivo de Log, criar o nome “Lógico” para esses Datafiles;
- Alterar o Filegroup dos três arquivos para SECONDARY;
- Alterar o tamanho inicial dos Filegroups para 1024MB (pode ser maior ou menor dependo do tamanho do banco de dados);
- Nos três pontinhos (), alterar o tamanho de Autogrowth para 128MB (pode ser maior ou menor dependo do tamanho do banco de dados) clique em Ok;
- Especificar o path (caminho físico dos datafiles, é indicado unidade diferente que os datafiles Primary);
- Alterar o nome "Físico" dos três arquivos.
Ou rode o seguinte comando:
Bloco de código | ||
---|---|---|
| ||
USE [master] GO ALTER DATABASE [TPPRD] ADD FILE ( NAME = N'TPPRD_Index01FILE ( 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_Index01Index03.ndf' , SIZE = 1048576KB , FILEGROWTH = 131072KB ), (TO 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, vamos realizar a distribuição dos índices para o datafile "SECONDARY".
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:
Expandir | |||||||
---|---|---|---|---|---|---|---|
| |||||||
Print de exemplo de resultado da geração do DML: |
Copie os DMLs gerados como resultado do script anterior e salve em um arquivo.
Após isto, realize a deleção dos índices que serão criados novamente.
Expandir | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
| ||||||||||
|
MOVER ÍNDICES NÃO CLUSTER PARA FILEGROUP SECONDARY
Após criar o filegroup e datafiles de índices, vamos realizar o move dos índices não cluster para o novo filegroup criado.
Com o script a seguir, ele irá gerar os comandos para movimentar os índices:
Bloco de código | |||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| |||||||||||||
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) +'.'
|
O script acima irá gerar o DML do move dos índices:
|
Copie os DMLs gerados para o drop dos índices e execute em uma nova janela do SSMS. Esta operação deletará os índices do filegroup primary.
Após isto, execute os DMLs salvos como resultado do script de criação Copiar os DMLs e rodar em uma nova janela do SSMS, movendo assim . Esta operação criará os índices para o no novo filegroup Secondary.
Até aqui, apenas as informações de índices que já existiam no banco de dados foram distribuídas entre filegroups.
Alterar banco de dados para modo full
Reconfigure
ALTERAR BANCO DE DADOS PARA MODO FULL
Vamos voltar o banco de dados em para modo Full do qual foi alterado no começo deste artigo., revertendo a alteração sugerida do início.
Ou execute o Ou rode o seguinte comando:
Bloco de código | ||||
---|---|---|---|---|
| ||||
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, abrir abra o Monitor do DBAccess, na . Na aba Configurações, em Microsoft SQL, Tablespace Índices e incluir , há a seção de Tablespaces. Em índices, inclua a chave Secondary, logo após clique (referente ao filegroup criado para índices), conforme print. Clique em salvar:
DATABASE
Antes da Manutenção:
Antes da manutenção podemos verificar um único arquivo com 85GB reservado e 74GB efetivamente utilizado:
SHRINK DATAFILE:
Após a manutenção realizamos a criação de dois novos arquivos de dados, e a distribuição dos índices nos datafiles do filegroup Secondary, realizamos o shrink do datafile Primário da seguinte forma:
Após liberar o espaço do datafile Primário, realizamos a manutenção de Compress de dados e índices conforme indicação para boa performance do Ambiente ERP.
Links de Referência:
Database Files e Filegroups
Recovery Model
Compress de Dados e Índices:
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:
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.
https://tdn.totvs.com/pages/releaseview.action?pageId=564330500