Usuário com melhor resposta
Rebuild/Reorder Index

Pergunta
-
Pessoal,
Sou novo nesta área de Banco e estou com uma dúvida onde não sei mais o que fazer.
Me pediram para executar uma rotina de Rebuild/Reorder no nosso banco de dados, eu executei o script conforme orientado, porém as fragmentações dos nosso index ainda continua alta.
Por Exemplo:
Index A (Antes da Execução da Rotina): 94,425...
Index A (Após a Execução da Rotina): 71,4285...
Ou Seja, mesmo executando a rotina de Rebuild/Reorder, vários index continuam com uma alta fragmentação.
Alguém sabe me dizer alguma coisa sobre esse assunto?
Respostas
-
Deleted
- Sugerido como Resposta Junior Galvão - MVPMVP, Moderator quarta-feira, 31 de maio de 2017 00:54
quinta-feira, 25 de maio de 2017 17:00
Todas as Respostas
-
Anderson,
Poderia nos apresentar a maneira que você realizou o Rebuild/Reindex?
Pedro Antonio Galvao Junior [MVP | MCC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]
-
Boa noite,
Anderson, qual o tamanho dos seus índices ?
Talvez tenho alguns posts que possa te ajudar.
https://blogdojamal.wordpress.com/2017/01/08/rebuild-nao-zera-a-fragmentacao/
Para visualizar facilmente os seus índices:
https://blogdojamal.wordpress.com/2017/02/03/sp_showindex-no-sql-azure-e-outras-novidades/
Att
-
Pessoal,
Eu executo o script abaixo para realizar o rebuild/reorder dos index!
/**********************************************************************/
Script
/**********************************************************************/
DBCC UPDATEUSAGE ('Banco')
GO
/**************************************************************************************************/
/* Usando sys.dm_db_index_physical_stats em um script para reconstruir ou reorganizar índices */
/**************************************************************************************************/
-- Ensure a USE <databasename> statement has been executed first.
SET NOCOUNT ON;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(130);
DECLARE @objectname nvarchar(130);
DECLARE @indexname nvarchar(130);
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command nvarchar(4000);
-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
-- and convert object and index IDs to names.
SELECT
object_id AS objectid,
index_id AS indexid,
partition_number AS partitionnum,
avg_fragmentation_in_percent AS frag
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (DB_ID('Banco'), NULL, NULL , NULL, 'DETAILED')
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;
-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;
-- Open the cursor.
OPEN partitions;
-- Loop through the partitions.
WHILE (1=1)
BEGIN;
FETCH NEXT
FROM partitions
INTO @objectid, @indexid, @partitionnum, @frag;
IF @@FETCH_STATUS < 0 BREAK;
SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
FROM sys.objects AS o
JOIN sys.schemas as s ON s.schema_id = o.schema_id
WHERE o.object_id = @objectid;
SELECT @indexname = QUOTENAME(name)
FROM sys.indexes
WHERE object_id = @objectid AND index_id = @indexid;
SELECT @partitioncount = count (*)
FROM sys.partitions
WHERE object_id = @objectid AND index_id = @indexid;
-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
IF @frag < 30.0
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
IF @frag >= 30.0
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
IF @partitioncount > 1
SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));
EXEC (@command);
PRINT N'Executed: ' + @command;
END;
-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;
-- Drop the temporary table.
PRINT N'FIM';
-- Drop a tabela temporária
IF EXISTS (SELECT name FROM sys.objects WHERE name = 'work_to_do')
DROP TABLE work_to_do;
GO
EXEC sp_updatestats
GO- Editado AndersonFurtado sexta-feira, 19 de maio de 2017 16:28
-
Anderson,
Faça o seguinte, utilize ao final deste script o comando Update Statistics com a opção FullScan, a Stored Procedure atualiza as estatísticas das tabelas de sistema, mas não realiza a atualização em todos os níveis, provavelmente esse pode ser um indicador que poderá estar apresentando fragmentação alta.
Veja se este exemplo te ajuda:
/* Sr.Nimbus - OnDemand http://www.srnimbus.com.br */ USE NorthWind GO /* Manutenção das Estatísticas */ -- Problema com estatísticas desatualizadas -- AUTO_UPDATE_STATISTICS ALTER DATABASE NorthWind SET AUTO_UPDATE_STATISTICS OFF WITH NO_WAIT GO UPDATE TOP (50) PERCENT Order_DetailsBig SET Quantity = CHECKSUM(NEWID()) / 10000 GO -- Estimativa incorreta, pois as estatisticas estão desatualizadas SELECT * FROM Order_DetailsBig WHERE Quantity = 100 OPTION (RECOMPILE) GO ALTER DATABASE NorthWind SET AUTO_UPDATE_STATISTICS ON WITH NO_WAIT GO -- Estimativa correta, pois o AUTO_UPDATE_STATISTICS é disparado -- automaticamente SELECT * FROM Order_DetailsBig WHERE Quantity = 100 OPTION (RECOMPILE) /* Quando um auto update statistics é disparado? AUTO_UPDATE_STATISTICS RowModCtr - Se a cardinalidade da tabela é menor que seis e a tabela esta no banco de dados tempdb, auto atualiza a cada seis modificações na tabela - Se a cardinalidade da tabela é maior que seis e menor ou igual a 500, então atualiza as estatísticas a cada 500 modificações na tabela - Se a cardinalidade da tabela é maior que 500, atualiza as estatísticas quando 500 + 20% da tabela for alterada. No Profiler visualizar os evento SP:StmtCompleted e SP:StmtStarting */ -- Exemplo sp_updatestats -- Runs UPDATE STATISTICS against all user-defined and internal tables in -- the current database EXEC sp_updatestats GO -- Linhas modificadas por coluna, antiga rowmodctr IF OBJECT_ID('Tab1') IS NOT NULL DROP TABLE Tab1 GO CREATE TABLE Tab1 (ID Int IDENTITY(1,1) PRIMARY KEY, Col1 Int, Col2 Int, Col3 Int, Col4 Int, Col5 Int) GO CREATE STATISTICS StatsCol1 ON Tab1(Col1) CREATE STATISTICS StatsCol2 ON Tab1(Col2) CREATE STATISTICS StatsCol3 ON Tab1(Col3) CREATE STATISTICS StatsCol4 ON Tab1(Col4) CREATE STATISTICS StatsCol5 ON Tab1(Col5) GO INSERT INTO Tab1(Col1, Col2, Col3, Col4, Col5) VALUES(1, 1, 1, 1, 1) GO 100 CHECKPOINT GO SELECT * FROM Tab1 GO -- Quantidade de modificações na tabela SELECT name, id, rowmodctr FROM sysindexes WHERE id = OBJECT_ID('Tab1') GO -- Zerando rowmodctr UPDATE STATISTICS Tab1 WITH FULLSCAN GO SELECT name, id, rowmodctr FROM sysindexes WHERE id = OBJECT_ID('Tab1') GO -- Atualizando 5 linhas UPDATE TOP (5) Tab1 SET Col1 = 2 GO SELECT name, id, rowmodctr FROM sysindexes WHERE id = OBJECT_ID('Tab1') GO -- E as estatísticas com 2 colunas? CREATE STATISTICS StatsCol1_Col2 ON Tab1(Col1, Col2) GO SELECT name, id, rowmodctr FROM sysindexes WHERE id = OBJECT_ID('Tab1') GO UPDATE TOP (5) Tab1 SET Col2 = 2 GO -- Update na "segunda" coluna não atualiza rowmodctr SELECT name, id, rowmodctr FROM sysindexes WHERE id = OBJECT_ID('Tab1') GO -- Apagando 5 linhas DELETE TOP (5) FROM Tab1 GO SELECT name, id, rowmodctr FROM sysindexes WHERE id = OBJECT_ID('Tab1') GO -- Zerar rowmodctr para seguir os testes UPDATE STATISTICS Tab1 WITH FULLSCAN GO /* "From BOL" In SQL Server 2000 and earlier, the Database Engine maintained row-level modification counters. Such counters are now maintained at the column level. Therefore, the rowmodctr column is calculated and produces results that are similar to the results in earlier versions, but are not exact. */ -- Consultando modificações por coluna -- DMV sys.system_internals_partition_columns mostra modificações por coluna, -- independente de terem índice ou estatística CHECKPOINT SELECT partitions.object_id, partitions.index_id, columns.name, system_internals_partition_columns.partition_column_id, system_internals_partition_columns.modified_count FROM sys.system_internals_partition_columns INNER JOIN sys.partitions ON system_internals_partition_columns.partition_id = partitions.partition_id INNER JOIN sys.columns ON partitions.object_id = columns.object_id AND system_internals_partition_columns.partition_column_id = columns.column_id WHERE partitions.object_id = OBJECT_ID('Tab1') GO -- system_internals_partition_columns só é atualizada depois do checkpoint -- Atualizando algumas colunas UPDATE TOP (10) Tab1 SET Col4 = 5, Col5 = 5 GO CHECKPOINT SELECT OBJECT_NAME(partitions.object_id) AS objName, partitions.object_id, partitions.index_id, columns.name, system_internals_partition_columns.partition_column_id, system_internals_partition_columns.modified_count FROM sys.system_internals_partition_columns INNER JOIN sys.partitions ON system_internals_partition_columns.partition_id = partitions.partition_id INNER JOIN sys.columns ON partitions.object_id = columns.object_id AND system_internals_partition_columns.partition_column_id = columns.column_id WHERE partitions.object_id = OBJECT_ID('Tab1') GO -- Apagando 10 linhas DELETE TOP (10) FROM Tab1 GO CHECKPOINT SELECT OBJECT_NAME(partitions.object_id) AS objName, partitions.object_id, partitions.index_id, columns.name, system_internals_partition_columns.partition_column_id, system_internals_partition_columns.modified_count FROM sys.system_internals_partition_columns INNER JOIN sys.partitions ON system_internals_partition_columns.partition_id = partitions.partition_id INNER JOIN sys.columns ON partitions.object_id = columns.object_id AND system_internals_partition_columns.partition_column_id = columns.column_id WHERE partitions.object_id = OBJECT_ID('Tab1') GO -- AUTO_UPDATE_STATISTICS -- Quanto tempo demora para disparar um auto_update em uma tabela grande? ALTER DATABASE NorthWind SET AUTO_UPDATE_STATISTICS ON GO -- Criando índice para testes CREATE INDEX ixOrderDate ON OrdersBig(OrderDate) GO -- Consultando quantidade de modificações no índice desde sua criação/update SELECT name, id, rowmodctr FROM sysindexes WHERE id = OBJECT_ID('OrdersBig') GO -- Gerando update de várias linhas para disparar auto_update_statistics -- 15 segundos UPDATE TOP (50) PERCENT OrdersBig SET OrderDate = CONVERT(Date, GETDATE() - (CheckSUM(NEWID()) / 100000)) GO -- Consultando quantidade de modificações no índice desde sua criação/update SELECT name, id, rowmodctr FROM sysindexes WHERE id = OBJECT_ID('OrdersBig') GO -- Disparando auto update statsitics SELECT * FROM OrdersBig WHERE OrderDate = '20121221' -- Maias estavam errados! GO -- Mas quanto tempo demorou? -- Profiler SP:StmtCompleted e SP:StmtStarting -- Ou TraceFlags 3604/3605 e 8721 DBCC TRACEON(3605) /* Error Log: Message AUTOSTATS: Tbl: OrdersBig Objid:1637580872 Rows: 1000070.000000 Threshold: 200514 Duration: 1055ms Message AUTOSTATS: UPDATED Stats: OrdersBig..ixOrderDate Dbid = 5 Indid = 3 Rows: 315415 Duration: 1061ms */ DBCC TRACEON(3604) -- Resultado para Console (painel de results do SSMS) DBCC TRACEON(8721) -- AUTO_UPDATE_STATISTICS_ASYNC -- E se eu não quiser esperar pelo update? ALTER DATABASE Northwind SET AUTO_UPDATE_STATISTICS_ASYNC ON GO -- Gerando update de várias linhas para disparar auto_update_statistics -- 15 segundos UPDATE TOP (50) PERCENT OrdersBig SET OrderDate = CONVERT(Date, GETDATE() - (CheckSUM(NEWID()) / 100000)) GO -- Disparando auto update statsitics SELECT * FROM OrdersBig WHERE OrderDate = '20121221' -- Maias estavam errados! GOMessage AUTOSTATS: CREATED Dbid = 5 Tbl: TestAutoUpdateStatistics(Value) Rows: 315481 Dur: 1591ms -- Consultando tempo gasto... SELECT * FROM sys.dm_exec_background_job_queue_stats GO ALTER DATABASE Northwind SET AUTO_UPDATE_STATISTICS_ASYNC OFF GO -- Ou desabilita o auto update/create statistics para a tabela EXEC sp_autostats 'OrdersBig', 'OFF' GO -- AUTO_CREATE_STATISTICS -- Quanto tempo demora para disparar um auto_create em uma tabela grande? -- Criando tabela com 1 milhão de linhas -- DROP TABLE TestAutoUpdateStatistics SELECT TOP 1000000 * INTO TestAutoUpdateStatistics FROM OrdersBig GO -- Gera auto create statistics pois estatística ainda não existe SELECT * FROM TestAutoUpdateStatistics WHERE Value BETWEEN 1000 AND 1100 GO -- Message -- AUTOSTATS: CREATED Dbid = 5 Tbl: TestAutoUpdateStatistics(Value) Rows: 315481 Dur: 1591ms -- TFs para verificar tempo gasto DBCC TRACEON(3605) -- Resultado para Console (painel de results do SSMS) DBCC TRACEON(8721) -- EDGE Cases -- E colunas blob ? IF OBJECT_ID('TestBlobTab') IS NOT NULL DROP TABLE TestBlobTab GO CREATE TABLE TestBlobTab (ID Int IDENTITY(1,1) PRIMARY KEY, Col1 Int, Foto VarBinary(MAX)) GO -- 51 mins e 39 segundos para rodar INSERT INTO TestBlobTab (Col1, Foto) SELECT TOP 30000 CheckSUM(NEWID()) / 1000000, CONVERT(VarBinary(MAX),REPLICATE(CONVERT(VarBinary(MAX), CONVERT(VarChar(250), NEWID())), 5000)) FROM sysobjects a, sysobjects b, sysobjects c, sysobjects d GO INSERT INTO TestBlobTab (Col1, Foto) SELECT CheckSUM(NEWID()) / 1000000, NULL GO 100 -- Consulta quantidade de páginas LOB SELECT t.name, au.* FROM sys.system_internals_allocation_units au INNER JOIN sys.partitions p ON au.container_id = p.partition_id INNER JOIN sys.tables t ON p.object_id = t.object_id WHERE t.name = 'TestBlobTab' GO -- Demora uma eternidade para criar a estatística na coluna Foto... -- 20 mins e 17 segundos para criar a estatística -- Consulta roda em 0 segundos SELECT COUNT(*) FROM TestBlobTab WHERE Foto IS NULL -- Message -- AUTOSTATS: CREATED Dbid = 5 Tbl: Tab1(Foto) Rows: 70100 Dur: 1217604ms -- Mais informações aqui... http://blogs.msdn.com/b/psssql/archive/2009/01/22/how-it-works-statistics-sampling-for-blob-data.aspx -- NO_RECOMPUTE -- Identificando as estatísticas criadas automaticamente SELECT * FROM sys.stats WHERE Object_ID = OBJECT_ID('TestBlobTab') GO DROP STATISTICS TestBlobTab._WA_Sys_00000003_03F0984C GO -- Criando manualmente com clausula NORECOMPUTE -- DROP STATISTICS TestBlobTab.StatsFoto CREATE STATISTICS StatsFoto ON TestBlobTab(Foto) WITH NORECOMPUTE, SAMPLE 0 PERCENT GO SELECT COUNT(*) FROM TestBlobTab WHERE Foto IS NULL OPTION (RECOMPILE)
Pedro Antonio Galvao Junior [MVP | MCC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]
-
Seguem os tamanhos dos Index (TOP 10)
Referencia
Database
Tabela
Indice
Fragmentacao (%)
Qtd Páginas
Fill Factor (%)
Compressão de Dados
2017-05-23 23:00:16
BASE
SRF010
SRF010_PK
99.92
2592
0
Sem Compressão
2017-05-23 23:00:16
BASE
SR9010
SR9010_PK
99.85
1297
0
Sem Compressão
2017-05-23 23:00:00
BASE
SX5010
SX5010_PK
99.77
2191
0
Sem Compressão
2017-05-23 23:00:16
BASE
SD1010
SD10109
99.56
2699
100
Sem Compressão
2017-05-23 23:00:16
BASE
SD3010
IX_SD3010_02
99.49
6897
100
Sem Compressão
2017-05-23 23:00:16
BASE
FK7010
FK7010_UNQ
99.38
6098
100
Sem Compressão
2017-05-23 23:00:16
BASE
SD1010
SD10102
99.38
4198
100
Sem Compressão
2017-05-23 23:00:00
BASE
FK1010
FK10101
99.36
1725
100
Sem Compressão
2017-05-23 23:00:16
BASE
SD1010
SD1010C
99.33
3306
100
Sem Compressão
2017-05-23 23:00:16
BASE
FK7010
FK70101
99.28
5969
100
Sem Compressão