Olá,
o tema que trago aqui hoje, embora estranho para alguns, é de enorme importância na manutenção da performance aos dados no SQL Server. Trata-se da fragmentação que os índices das tabelas sofrem provocando lentidão no acesso aos dados.
A fragmentação dos índices é provocada por diversos factores, em tabelas onde existem muitos deletes, updates ou inserts o assunto é uma constante. Outra ação que leva à fragmentação é a compactação das DBs, sendo o mundo ideal nunca fazer shrink às mesmas.
O facto é que todos temos de realizar estas ações mais cedo ou mais tarde, porque a DB cresceu exponencialmente com alguma transação, ou porque o disco de repente ficou cheio e urge compactar a DB, ou simplesmente porque se eliminam dados e se inserem novos num contexto permanente. A solução estará na desfragmentação dos índices, quer seja através da reorganização (Reorganize) ou da reconstrução (Rebuild).
O script que vos trago permite realizar a verificação da fragmentação em determinada (s) DBs e, caso a percentagem de fragmentação seja superior a 5%, realizar uma das duas ações:
* Reorganize, caso a fragmentação esteja entre 5% e 30%
* Rebuil, caso a fragmentação seja superior a 30%
Os scripts são baseados em Cursores percorrendo todas as DBs necessárias. Por acaso apenas especifico uma DB por questão de rapidez.
O código utiliza a função Rownumber() para escolher as linhas de índices únicas. Podem existir diversos índices para a mesma tabela, mas como faço o Reorganize ALL/Rebuild ALL devo apelar à unicidade – caso contrário estaria a tratar mais que uma vez o mesmo índice.
/*--------------------------------------------
INDEX REORGANIZE
fragmentation between 5 and 30
----------------------------------------------*/
DECLARE @Database NVARCHAR(255)
DECLARE @Table NVARCHAR(4000)
DECLARE @cmd NVARCHAR(1000)
DECLARE DatabaseCursor CURSOR READ_ONLY FOR
SELECT name FROM master.sys.databases
--WHERE name NOT IN ('master','msdb','tempdb','model','Testes') -- databases to exclude
WHERE name IN ('DB_User') -- specific databases
AND state = 0 -- database is online
AND is_in_standby = 0 -- database is not read only for log shipping
ORDER BY 1
OPEN DatabaseCursor
FETCH NEXT FROM DatabaseCursor INTO @Database
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmd = 'DECLARE TableCursor CURSOR READ_ONLY FOR
select tableName from (
SELECT ''['' + table_catalog + ''].['' + table_schema + ''].['' + table_name + '']'' as tableName
--,I.name as ''IndexName'',DDIPS.avg_fragmentation_in_percent
, row_number() over (partition by T.name order by I.name) As nlinha
FROM [' + @Database + '].INFORMATION_SCHEMA.TABLES A
INNER JOIN sys.tables T ON A.TABLE_NAME = T.NAME
INNER JOIN sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) DDIPS ON T.object_id = DDIPS.object_id
INNER JOIN sys.schemas S ON T.schema_id = S.schema_id
INNER JOIN sys.indexes I ON I.object_id = DDIPS.object_id AND DDIPS.index_id = I.index_id
WHERE
DDIPS.database_id = DB_ID()
AND I.name is not null
AND DDIPS.avg_fragmentation_in_percent between 5 and 30
AND A.table_type = ''BASE TABLE''
) kk
where nlinha=1'
-- create table cursor
EXEC (@cmd)
--PRINT(@cmd)
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @Table
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY
SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REORGANIZE'
PRINT @cmd
EXEC (@cmd)
END TRY
BEGIN CATCH
PRINT '---'
PRINT @cmd
PRINT ERROR_MESSAGE()
PRINT '---'
END CATCH
FETCH NEXT FROM TableCursor INTO @Table
END
CLOSE TableCursor
DEALLOCATE TableCursor
FETCH NEXT FROM DatabaseCursor INTO @Database
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor
Agora o código para o REBUILD dos índices:
/*--------------------------------------------
INDEX REBUILD
case fragmentation > 30
----------------------------------------------*/
DECLARE @Database NVARCHAR(255)
DECLARE @Table NVARCHAR(4000)
DECLARE @cmd NVARCHAR(1000)
DECLARE DatabaseCursor CURSOR READ_ONLY FOR
SELECT name FROM master.sys.databases
--WHERE name NOT IN ('master','msdb','tempdb','model','Testes') -- databases to exclude
WHERE name IN ('DB_User') -- specific databases
AND state = 0 -- database is online
AND is_in_standby = 0 -- database is not read only for log shipping
ORDER BY 1
OPEN DatabaseCursor
FETCH NEXT FROM DatabaseCursor INTO @Database
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmd = 'DECLARE TableCursor CURSOR READ_ONLY FOR
select tableName from (
SELECT ''['' + table_catalog + ''].['' + table_schema + ''].['' + table_name + '']'' as tableName
--,I.name as ''IndexName'',DDIPS.avg_fragmentation_in_percent
, row_number() over (partition by T.name order by I.name) As nlinha
FROM [' + @Database + '].INFORMATION_SCHEMA.TABLES A
INNER JOIN sys.tables T ON A.TABLE_NAME = T.NAME
INNER JOIN sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) DDIPS ON T.object_id = DDIPS.object_id
INNER JOIN sys.schemas S ON T.schema_id = S.schema_id
INNER JOIN sys.indexes I ON I.object_id = DDIPS.object_id AND DDIPS.index_id = I.index_id
WHERE
DDIPS.database_id = DB_ID()
AND I.name is not null
AND DDIPS.avg_fragmentation_in_percent > 30 --or T.name = ''STOCK''
AND A.table_type = ''BASE TABLE''
) kk
where nlinha=1'
-- create table cursor
EXEC (@cmd)
--PRINT(@cmd)
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @Table
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY
SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD'
PRINT @cmd
EXEC (@cmd)
END TRY
BEGIN CATCH
PRINT '---'
PRINT @cmd
PRINT ERROR_MESSAGE()
PRINT '---'
END CATCH
FETCH NEXT FROM TableCursor INTO @Table
END
CLOSE TableCursor
DEALLOCATE TableCursor
FETCH NEXT FROM DatabaseCursor INTO @Database
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor
Por fim, estes dois scripts podem ser inseridos em dois steps num único job SQL e feito o agendamento para ser executado durante a noite. E se durante a noite existirem processos de alteração de dados este script deverá correr após isso por forma a manter a boa performance para o próximo dia de trabalho.
Espero que vos seja útil,
JG
JGInformática - Jorge Gomes 