Nesta página, será demonstrado como mover índices não cluster do filegroup (grupo de arquivos) “Primary” para o filegroup “Secondary”, e também mover tabelas de um arquivo físico (datafile) para outros dois arquivos.
O que notamos em vários ambientes ERP, atualmente, é que a criação do banco de dados foi realizada com somente um grupo de arquivos e um datafile no banco de dados. Quando um banco de dados é criado nesse formato, conforme o tempo de uso e maior quantidade de conexões, o ambiente possivelmente irá começar a degradar sua performance, mesmo que inicialmente não ocorram problemas.
A fabricante, Microsoft, indica que ao criar o índice não-cluster em um grupo de arquivos diferente, você pode obter ganhos de desempenho se os grupos de arquivos estiverem usando unidades físicas diferentes com seus próprios controladores.
Primeiro, vamos entender melhor o que são arquivos de dados, grupos de arquivos e tipos de grupos de arquivos.
Segundo a documentação oficial do MSSQL Server, esse SGBD possui três tipos de arquivos:
Sendo assim, a indicação da TOTVS e da Microsoft é manter um Grupo de Arquivos (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.
Configuração de Demonstração:
Primeiro realizamos a criação de um Banco de Dados: “TPPRD” com somente um datafile: “TPPRD_Data01” contido no filegroup: “PRIMARY”, conforme o print a seguir:
No DBAccess, o ambiente está configurado da seguinte forma:
Premissas:
Os passos desta demonstração serão:
ALTERAR BANCO DE DADOS PARA MODO SIMPLE
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:
USE master GO ALTER DATABASE [TPPRD] SET RECOVERY SIMPLE GO |
ADICIONAR ARQUIVOS AO GRUPO DE ARQUIVOS PRIMARY
Crie usando T-SQL (linha de comando) ou na área gráfica do SQL Server Management Studio (SSMS).
Ou rode o seguinte comando:
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 |
CRIAR 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 e selecione Filegroups, logo após adicione um Filegroup, uma linha será adicionada logo abaixo de Primary, nomear esse Filegroup para Secondary, clique em OK:
Ou rode o seguinte comando:
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).
Ou rode o seguinte comando:
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, vamos realizar a distribuição dos índices para o datafile "SECONDARY".
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:
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 |
O script acima irá gerar o DML do move dos índices:
Copiar os DMLs e rodar em uma nova janela do SSMS, movendo assim os índices para o novo filegroup Secondary.
ALTERAR BANCO DE DADOS PARA MODO FULL
Vamos voltar o banco de dados em modo Full do qual foi alterado no começo deste artigo.
Ou rode o seguinte comando:
USE master GO ALTER DATABASE [TPPRD] SET RECOVERY FULL GO |
CONFIGURAÇÃO DBACCESS
Após a criação do filegroup Secondary e move dos índices, abrir o Monitor do DBAccess, na aba Configurações, Microsoft SQL, Tablespace Índices e incluir a chave Secondary, logo após 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:
https://tdn.totvs.com/pages/releaseview.action?pageId=564330500