SQL procka pro rebuild či reorganizaci indexů tabulky v db schématu.
Pokud je potřeba dělat automatický maintenance sql indexů, tak je zbytečné to dělat ručně. Zejména pokud se jedná o jednoúčelové, jednoduché tabulky kde není žádná chytristika.
Zatím se mi procka osvědčila a nikdo si nestěžoval :-).
Procku pouštím periodicky z ADF pro většinu indexů. A v rámci datového zpracování pro tabulky/indexy kde bývá “těžší” provoz.
/****** Object: StoredProcedure [dbo].[pIndexOptimalization] Script Date: 05.05.2022 14:45:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
+==============================================================================+
CREATED BY: martin.misar DATE: 2021-11-02
DESCRIPTION: rebuil or reorganize for indexes which are too much fragmented
@pSchemaName - name of schema which will be opt
@pTableName
- if is null then all indexes in all tables in schema will be optimized
- if is set then all indexes for specified table will be optimized
@pReorganize - 1 = reorganize, 0 = do nothing
@pRebuild - 1 = rebuild, 0 = do nothing
@pDebug - 1 = only print sql statements, 0 = all will be executed
USAGE: ADF, ADB, SQL
EXAMPLE: EXEC [dbo].[pIndexOptimalization] N'schema', N'table',1,1,0
MODIFIED BY: DATE:
GOAL:
+==============================================================================+
*/
ALTER PROCEDURE [dbo].[pIndexOptimalization] (
@pSchemaName varchar(200)
,@pTableName varchar(200)
,@pReorganize bit = 1
,@pRebuild bit = 1
,@pDebug bit = 0
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE
@vTempVar1 NVARCHAR(max)
,@vStatement NVARCHAR(max)
,@pDatabaseName varchar(100) = DB_NAME();
/* ----------------------------------------------------------------------------*/
drop table if exists #IndexTables;
drop table if exists #IndexBase;
/* ----------------------------------------------------------------------------*/
create table #IndexBase
(
DatabaseName varchar(100),
SchemaName varchar(100),
TableName varchar(100),
IndexName varchar(200),
IndexId int,
ObjectId int,
AvgFrag decimal(18,2),
ReorganizeIndex bit,
RebuildIndex bit
)
/* ----------------------------------------------------------------------------*/
SELECT
[TableName] = tabl.name
into
#IndexTables
FROM
sys.schemas sche
left join sys.tables tabl
on tabl.schema_id = sche.schema_id
WHERE
1=1
and tabl.name is not null
and sche.name = @pSchemaName
and tabl.name = isnull( @pTableName, tabl.name)
/* ----------------------------------------------------------------------------*/
DECLARE IndexBase_cursor CURSOR
LOCAL FAST_FORWARD
FOR
select TableName from #IndexTables
OPEN IndexBase_cursor
FETCH NEXT FROM IndexBase_cursor into @vTempVar1
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @vStatement = 'insert into #IndexBase select '+char(39)+@pDatabaseName+char(39)+','+char(39)+@pSchemaName+char(39)+','+char(39)+@vTempVar1+char(39)+', ind.name,ips.index_id,ips.object_id,ips.avg_fragmentation_in_percent,
case
when avg_fragmentation_in_percent > 5 and avg_fragmentation_in_percent < 30 then 1
else
0
end,
case
when avg_fragmentation_in_percent >= 30 then 1
else
0
end
from
sys.dm_db_index_physical_stats(DB_ID(' +char(39)+ @pDatabaseName +char(39)+ '), OBJECT_ID('+char(39)+ @pDatabaseName + '.'+@pSchemaName+'.'+@vTempVar1+char(39)+'), NULL, NULL , ' + char(39) +'LIMITED' + char(39)+') ips
left join sys.indexes ind
on ind.index_id = ips.index_id and ind.object_id = ips.object_id
where
ind.name is not null' ;
if @pDebug = 1 Print @vStatement;
EXEC sp_executesql @vStatement;
FETCH NEXT FROM IndexBase_cursor into @vTempVar1
END
CLOSE IndexBase_cursor
DEALLOCATE IndexBase_cursor
/* ----------------------------------------------------------------------------*/
-- INDEX REORGANIZE --
/* ----------------------------------------------------------------------------*/
if @pReorganize = 1
begin
DECLARE IndexReorganize_cursor CURSOR
LOCAL FAST_FORWARD
FOR
select 'ALTER INDEX ' + IndexName + ' ON ' + SchemaName + '.' + TableName + ' REORGANIZE' from #IndexBase where ReorganizeIndex = 1
OPEN IndexReorganize_cursor
FETCH NEXT FROM IndexReorganize_cursor into @vTempVar1
WHILE @@FETCH_STATUS = 0
BEGIN
if @pDebug = 1 Print @vTempVar1;
if @pDebug = 0 EXEC sp_executesql @vTempVar1;
FETCH NEXT FROM IndexReorganize_cursor into @vTempVar1
END
CLOSE IndexReorganize_cursor
DEALLOCATE IndexReorganize_cursor
end
/* ----------------------------------------------------------------------------*/
-- INDEX REBUILD
/* ----------------------------------------------------------------------------*/
if @pRebuild = 1
begin
DECLARE IndexRebuild_cursor CURSOR
LOCAL FAST_FORWARD
FOR
select 'ALTER INDEX ' + IndexName + ' ON ' + SchemaName + '.' + TableName + ' REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON,ONLINE = ON)' from #IndexBase where RebuildIndex = 1
OPEN IndexRebuild_cursor
FETCH NEXT FROM IndexRebuild_cursor into @vTempVar1
WHILE @@FETCH_STATUS = 0
BEGIN
if @pDebug = 1 Print @vTempVar1;
if @pDebug = 0 EXEC sp_executesql @vTempVar1;
FETCH NEXT FROM IndexRebuild_cursor into @vTempVar1
END
CLOSE IndexRebuild_cursor
DEALLOCATE IndexRebuild_cursor
end
/* ----------------------------------------------------------------------------*/
select * from #IndexBase;
return 1;
/* ----------------------------------------------------------------------------*/
drop table if exists #IndexTables;
drop table if exists #IndexBase;
/* ----------------------------------------------------------------------------*/
END
