Boa Noite a Todos!
Hoje desenvolvi uma SP onde identifico quais indices necessitam de Rebuild (maior que 30.0) e Reorganize (entre 5 e 30), mas existem alguns casos onde mesmo executando a rotina existem indices que não sofreram nenhuma modificação conforme
esse exemplo (85.7143 - Executado: ALTER INDEX [IDX_codigo] ON [IMAC_ATERS_ADM].[dbo].[tbCidade] REBUILD).
O que percebi que as Pk's tambem não sofreram nenhum ação com o comando. Caso alguem queira dar uma olhada nessa SP me avise onde postar.
Segue abaixo o codigo da SP:
DECLARE
@vobjectid
int,
@vindexid
int,
@vpartitioncount
bigint,
@vschemaname
varchar(130),
@vobjectname
varchar(130),
@vindexname
varchar(130),
@vpartitionnum
bigint,
@vpartitions
bigint,
@vfrag
float,
@vcommand
varchar(2000),
@vSql1
nvarchar(2000),
@vParam
nvarchar(2000),
@vSql2
nvarchar(2000),
@vParam2
nvarchar(2000),
@vSql3
nvarchar(2000),
@vParam3
nvarchar(2000)
-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
-- and convert object and index IDs to names.
begin
SELECT
object_id AS objectid,
index_id
AS indexid,
partition_number
AS partitionnum,
avg_fragmentation_in_percent
AS frag
INTO #Temp
FROM
sys.dm_db_index_physical_stats
(DB_ID(@pDb),
NULL, NULL
, NULL, 'LIMITED')
WHERE
avg_fragmentation_in_percent
> 10.0
AND index_id
> 0;
print @pDb
-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions
CURSOR FOR
SELECT * FROM #Temp;
-- Open the cursor.
OPEN partitions;
-- Loop through the partitions.
WHILE (1=1)
BEGIN
FETCH NEXT
FROM partitions
INTO @vobjectid, @vindexid, @vpartitionnum, @vfrag;
IF (@@FETCH_STATUS
< 0)
begin
break
end
Set @vSql1 =
N' SELECT @vobjectnameOut = QUOTENAME(o.name), @vschemanameOut = QUOTENAME(s.name)
FROM
'+@pDb+'.sys.objects AS o
JOIN '+@pDb+'.sys.schemas
as s ON (s.schema_id = o.schema_id)
WHERE
o.object_id = @vobjid'
Set @vParam =
N'@vobjectnameOut varchar(130) OUTPUT, @vschemanameOut varchar(130) OUTPUT, @vobjid int'
execute sp_executesql
@vSql1,
@vParam,
@vobjid = @vobjectid,
@vobjectnameOut = @vobjectname
OUTPUT,
@vschemanameOut = @vschemaname
OUTPUT;
Set @vSql2 =
N'SELECT @vindexnameOut = QUOTENAME(name)
FROM
'+@pDb+'.sys.indexes
WHERE
object_id = @vobjid
AND index_id = @vindid '
Set @vParam2
= N'@vindexnameOut varchar(130) OUTPUT, @vobjid int, @vindid int '
execute sp_executesql
@vSql2,
@vParam2,
@vobjid = @vobjectid,
@vindid = @vindexid,
@vindexnameOut = @vindexname
OUTPUT;
Set @vSql3 =
N'SELECT @vpartitioncountOut = count (*)
FROM
'+@pDb+'.sys.partitions
WHERE
object_id = @vobjid
AND index_id = @vidxid '
Set @vParam3 =
N'@vpartitioncountOut bigint OUTPUT, @vobjid int, @vidxid int '
execute sp_executesql
@vSql3,
@vParam3,
@vobjid = @vobjectid,
@vidxid = @vindexid,
@vpartitioncountOut = @vpartitioncount
OUTPUT;
print @vfrag
-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
IF (@vfrag
between 5.0 and 30.0)
begin
SET @vcommand =
'ALTER INDEX ' + @vindexname
+ ' ON ['+@pDb+'].'
+ @vschemaname +
'.' + @vobjectname
+ ' REORGANIZE';
end
IF (@vfrag
>= 30.0)
begin
SET @vcommand =
'ALTER INDEX ' + @vindexname
+ ' ON ['+@pDb+'].'
+ @vschemaname
+ '.'
+ @vobjectname + ' REBUILD';
end
IF (@vpartitioncount
> 1)
begin
SET @vcommand = @vcommand
+ ' PARTITION='
+ CAST(@vpartitionnum
AS nvarchar(10));
end
EXEC (@vcommand);
PRINT 'Executado: '
+ @vcommand;
end
end
END
CLOSE
partitions;
DEALLOCATE
partitions;
DROP
TABLE #Temp;
GO
Um grande abraço
Anderson
