- Ausência de sys.partitions (substituída por sys.dm_db_partition_stats)
- O SQL Azure não tomar SELECT INTO (eu estava usando isso na minha habitual Reconstruir Proc)
Contornando estas limitações, criei uma nova proc:
CREATE PROC [dbo].[SpRebuildAllIndex]
AS
BEGIN
CREATE TABLE #tmpfrag
(
tbname VARCHAR(200)
)
INSERT INTO #tmpfrag
SELECT TOP 20 Schema_name(o.schema_id) + '.'
+ Object_name(ps.object_id) AS TableName
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.indexes i
ON ps.object_id = i.object_id
AND ps.index_id = i.index_id
INNER JOIN sys.objects o
ON ( i.object_id = o.object_id )
CROSS apply sys.Dm_db_index_physical_stats(Db_id(), ps.object_id,
ps.index_id,
NULL,
'LIMITED') ips
GROUP BY Schema_name(o.schema_id) + '.'
+ Object_name(ps.object_id)
ORDER BY Avg(ips.avg_fragmentation_in_percent) DESC
DECLARE @TableName VARCHAR(255)
DECLARE tablecursor CURSOR FOR
(SELECT *
FROM #tmpfrag)
OPEN tablecursor
FETCH next FROM tablecursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT( 'Rebuilding Indexes on ' + @TableName )
BEGIN try
EXEC('ALTER INDEX ALL ON ' + @TableName +
' REBUILD with (ONLINE=ON)')
--PRINT 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD with (ONLINE=ON)'
END try
BEGIN catch
PRINT( 'Rebuild with Online=On ' + @TableName
+ ' UnSuccessful, removing ONLINE' )
EXEC('ALTER INDEX ALL ON ' + @TableName + ' REBUILD')
END catch
FETCH next FROM tablecursor INTO @TableName
END
CLOSE tablecursor
DEALLOCATE tablecursor
DROP TABLE #tmpfrag
INSERT INTO logrebuild
(desclogrebuild)
VALUES ('Sucesso')
SELECT Cast(1 AS INT) AS [Resultado]
END
go
Observem que estou agrupando as tabelas e considerando a fragmentação total/média de todos os índices, pois neste momento não vou fazer o REBUILD índice por índice, mas de todos os índices da tabela.
Check out my new book on MariaDB http://www.amazon.com/dp/B00MQC06HC
Nenhum comentário:
Postar um comentário
Leave your comment here!