5.7 Медленный Rebuild Table Index

9 апреля 2024 г.

16:41

QBM_ZIndexRebuild

8.1.3

USE [OneIM]

/****** Object: StoredProcedure [dbo].[QBM_ZIndexRebuild] Script Date: 09.04.2024 16:48:06 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

ALTER procedure [dbo].[QBM_ZIndexRebuild] ( @SlotNumberDummy int , @TableName varchar(38), @dummy1 varchar(38) = '', @dummy2 varchar(38) = '' ) as begin BEGIN

TRY exec QBM_PIndexRebuild @TableName END TRY BEGIN CATCH declare @ErrorMessage nvarchar(4000) declare @ErrorSeverity int declare @ErrorState int select

@ErrorSeverity = dbo.QBM_FGIErrorSeverity() select @ErrorState = 1 select @ErrorMessage = dbo.QBM_FGIErrorMessage() exec QBM_PRollbackIfAllowed RAISERROR

(@ErrorMessage, @ErrorSeverity, @ErrorState) WITH NOWAIT END CATCH end

8.2

USE [OneIM82]

/****** Object: StoredProcedure [dbo].[QBM_ZIndexRebuild] Script Date: 09.04.2024 16:55:14 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

ALTER procedure [dbo].[QBM_ZIndexRebuild] ( @SlotNumberDummy int , @TableName varchar(38), @dummy1 varchar(38) = '', @GenProcID varchar(38) = '' ) as begin

declare @Message nvarchar(1000) = '#LDS#Reindexing of {0} was not performed due to the size of the table.|' + @tablename + '|' BEGIN TRY if ISNULL(@GenProcID

, '') = '' begin select @GenProcID = dbo.QBM_FGISessionContext('') end if exists (select top 1 1 from DialogTable t where t.TableName = @tableName and

( t.SizeMB > 1024.0 or dbo.QBM_FGITableCountAll(t.TableName) > 1000000 ) ) begin exec QBM_PJournal @Message, @@procid, 'I', 'I', 100 goto endLabel end

if 1=0 begin exec QBM_PIndexRebuild @TableName end else begin declare @SQL33734 nvarchar(max) = concat('exec QBM_PIndexRebuild ''', @TableName, '''' )

exec QBM_PJobCreate_Mnt @SQL33734, @GenProcID end END TRY BEGIN CATCH declare @ErrorMessage nvarchar(4000) declare @ErrorSeverity int declare @ErrorState

int select @ErrorSeverity = dbo.QBM_FGIErrorSeverity() select @ErrorState = 1 select @ErrorMessage = dbo.QBM_FGIErrorMessage() exec QBM_PRollbackIfAllowed

RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState) WITH NOWAIT END CATCH endLabel: return end

QBM_PJobCreate_Mnt

USE [OneIM82]

/****** Object: StoredProcedure [dbo].[QBM_PJobCreate_Mnt] Script Date: 10.04.2024 15:41:09 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

create procedure [dbo].[QBM_PJobCreate_Mnt] (@SQLCmd nvarchar(max) , @GenProcID varchar(38) ) as begin declare @Parameters QBM_YParameterList declare @ComponentClass

nvarchar(256) = 'VI.JobService.JobComponents.SQLComponent' declare @TaskName nvarchar(64) = 'Execute SQL' declare @QueueName nvarchar(128) = case dbo.QBM_FGIWithoutAgentAndBroker

() when 0 then 'QBM_PDBQueueProcess_Mnt' else 'QBM_PWorkMaintenance' end BEGIN TRY insert into @Parameters (Parameter1, ContentFull) values ('SQLStmt'

, @sqlcmd) , ('WithoutTransaction', '1') if not exists (select top 1 1 from JobQueue q with (readpast) where q.ComponentClass = @ComponentClass and q.TaskName

= @TaskName and q.Queue = @QueueName and q.ParamIN like CONCAT('%', replace(@SQLCmd, '''', ''''''), '%') ) begin exec QBM_PJobCreate @ComponentClass

, @TaskName , @Parameters , @GenProcID , @ObjectKeysAffected = default , @QueueName = @QueueName end END TRY BEGIN CATCH declare @ErrorMessage nvarchar

(4000) declare @ErrorSeverity int declare @ErrorState int select @ErrorSeverity = dbo.QBM_FGIErrorSeverity() select @ErrorState = 1 select @ErrorMessage

= dbo.QBM_FGIErrorMessage() exec QBM_PRollbackIfAllowed @GenProcID RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState) WITH NOWAIT END CATCH ende:

return end

QBM_PIndexRebuild

8.1.3

USE [OneIM]

/****** Object: StoredProcedure [dbo].[QBM_PIndexRebuild] Script Date: 09.04.2024 16:49:11 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

ALTER procedure [dbo].[QBM_PIndexRebuild] ( @TableName nvarchar(30) ) as begin declare @SQLcmd nvarchar(max) declare @tableID int declare @SQLcmds QBM_YCursorBuffer

declare @ElementCount int declare @ElementIndex int declare @DebugStarttime datetime = getutcdate() declare @DebugLevel char(1) = 'W' BEGIN TRY if @@TRANCOUNT

> 0 begin goto endlabel end if isnull(@TableName, '') = '' begin goto ende end select @tableID = null select top 1 @tableID = OBJECT_ID from sys.tables

where name = @TableName exec QBM_PTableLockEscalationSet @TableName if @TableName in (select TableName from QBM_VHeavyLoadTables ) begin goto ende end

if not exists (select top 1 1 from information_schema.tables where table_name = @TableName and table_type = 'BASE TABLE' ) or @tableID is null begin

select @SQLcmd = N'No reorganize for Table ' + @TableName + N' Table not exists' exec QBM_PJournal @SQLcmd, @@procid, 'W', @DebugLevel goto ende end insert

into @SQLcmds (ContentFull) SELECT N'ALTER INDEX "' + x.indexname + N'" ON ' + schemaname + N'.' + x.TableName + case when percentFrag \< 20.0 and index_id

> 1 then N' REORGANIZE' else dbo.QBM_FGIDBServerIndexRebuildOpt(x.TableName) end from ( select distinct OBJECT_SCHEMA_NAME(frag.object_id) as schemaname

, OBJECT_NAME(frag.object_id) as TableName, six.name as indexname, frag.avg_fragmentation_in_percent as percentFrag , frag.index_id from ( select fr.object_id

, fr.index_id, sum(convert(float, record_count) * avg_fragmentation_in_percent / 100.0)/ sum(convert(float, record_count)) * 100.0 as avg_fragmentation_in_percent

FROM sys.dm_db_index_physical_stats ( DB_ID(), @tableID, DEFAULT, 0, 'detailed' ) as fr where fr.record_count > 0 group by fr.object_id, fr.index_id

) as frag JOIN sys.indexes six ON frag.object_id = six.object_id AND frag.index_id = six.index_id where six.name is not null and frag.avg_fragmentation_in_percent

> 10.0 and frag.object_ID = @tableID and six.is_hypothetical = 0 ) as x where TableName = @TableName order by x.index_id select @ElementCount = @@rowcount

if @ElementCount > 0 begin select @SQLCmd = N'ALTER INDEX ALL ON ' + @TableName + N' SET (ALLOW_PAGE_LOCKS = ON)' exec QBM_PJournal @SQLcmd, @@PROCID

, 'D', @DebugLevel exec QBM_PExecuteSQL @SQLcmd, @@procid select @ElementIndex = 1 while @ElementIndex \<= @ElementCount begin select top 1 @SQLcmd = bu.ContentFull

from @SQLcmds bu where bu.ElementIndex = @ElementIndex exec QBM_PJournal @sqlcmd, @@PROCID, 'D', @DebugLevel exec QBM_PExecuteSQL @SQLcmd, @@procid select

@ElementIndex += 1 end end select @SQLCmd = N'ALTER INDEX ALL ON ' + @TableName + N' SET (ALLOW_ROW_LOCKS = ON)' exec QBM_PJournal @SQLcmd, @@PROCID

, 'D', @DebugLevel exec QBM_PExecuteSQL @SQLcmd, @@procid select @SQLCmd = N'ALTER INDEX ALL ON ' + @TableName + N' SET (ALLOW_PAGE_LOCKS = OFF)' exec

QBM_PJournal @SQLcmd, @@PROCID, 'D', @DebugLevel exec QBM_PExecuteSQL @SQLcmd, @@procid select @SQLCmd = N'UPDATE STATISTICS ' + @TableName + N' WITH FULLSCAN '

exec QBM_PExecuteSQL @SQLcmd, @@procid ende: return END TRY BEGIN CATCH declare @ErrorMessage nvarchar(4000) declare @ErrorSeverity int declare @ErrorState

int select @ErrorSeverity = dbo.QBM_FGIErrorSeverity() select @ErrorState = 1 select @ErrorMessage = dbo.QBM_FGIErrorMessage() exec QBM_PRollbackIfAllowed

RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState) WITH NOWAIT END CATCH endLabel: return end

8.2

USE [OneIM82]

/****** Object: StoredProcedure [dbo].[QBM_PIndexRebuild] Script Date: 09.04.2024 16:52:38 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

ALTER procedure [dbo].[QBM_PIndexRebuild] ( @TableName nvarchar(30) ) as begin declare @SQLcmd nvarchar(max) declare @tableID int declare @SQLcmds QBM_YCursorBuffer

declare @ElementCount int declare @ElementIndex int declare @DebugLevel char(1) = 'W' declare @GenProcID varchar(38) = dbo.QBM_FGISessionContext('') BEGIN

TRY if @@TRANCOUNT > 0 begin goto endlabel end if isnull(@TableName, '') = '' begin goto ende end select @tableID = null select top 1 @tableID = OBJECT_ID

from sys.tables where name = @TableName exec QBM_PTableLockEscalationSet @TableName if @TableName in (select TableName from QBM_VHeavyLoadTables ) begin

goto ende end if not exists (select top 1 1 from information_schema.tables where table_name = @TableName and table_type = 'BASE TABLE' ) or @tableID

is null begin select @SQLcmd = N'No reorganize for Table ' + @TableName + N' Table not exists' exec QBM_PJournal @SQLcmd, @@procid, 'W', @DebugLevel goto

ende end insert into @SQLcmds (ContentFull) SELECT N'ALTER INDEX "' + x.indexname + N'" ON ' + schemaname + N'.' + x.TableName + case when percentFrag

\< 20.0 and index_id > 1 then N' REORGANIZE' else dbo.QBM_FGIDBServerIndexRebuildOpt(x.TableName) end from ( select distinct OBJECT_SCHEMA_NAME(frag.object_id

) as schemaname, OBJECT_NAME(frag.object_id) as TableName, six.name as indexname, frag.avg_fragmentation_in_percent as percentFrag , frag.index_id from

( select fr.object_id, fr.index_id, sum(convert(float, record_count) * avg_fragmentation_in_percent / 100.0)/ sum(convert(float, record_count)) * 100.0

as avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats ( DB_ID(), @tableID, DEFAULT, 0, 'detailed' ) as fr where fr.record_count > 0

group by fr.object_id, fr.index_id ) as frag JOIN sys.indexes six with (readpast) ON frag.object_id = six.object_id AND frag.index_id = six.index_id where

six.name > ' ' and frag.avg_fragmentation_in_percent > 10.0 and frag.object_ID = @tableID and six.is_hypothetical = 0 ) as x where TableName = @TableName

order by x.index_id select @ElementCount = @@rowcount if @ElementCount > 0 begin select @SQLCmd = N'ALTER INDEX ALL ON ' + @TableName + N' SET (ALLOW_PAGE_LOCKS = ON)'

exec QBM_PJournal @SQLcmd, @@PROCID , 'D', @DebugLevel exec QBM_PExecuteSQL @SQLcmd, @@procid select @ElementIndex = 1 while @ElementIndex \<= @ElementCount

begin select top 1 @SQLcmd = bu.ContentFull from @SQLcmds bu where bu.ElementIndex = @ElementIndex exec QBM_PJournal @sqlcmd, @@PROCID, 'D', @DebugLevel

exec QBM_PExecuteSQL @SQLcmd, @@procid select @ElementIndex += 1 end end exec QBM_PSetRowLockOnly @TableName if not exists (select top 1 1 from DialogDBQueue

q with (readpast) where q.UID_Task = 'QBM-K-UpdateStatistics' ) begin exec QBM_PDBQueueInsert_Single 'QBM-K-UpdateStatistics', '', '', @GenProcID end

ende: return END TRY BEGIN CATCH declare @ErrorMessage nvarchar(4000) declare @ErrorSeverity int declare @ErrorState int select @ErrorSeverity = dbo.QBM_FGIErrorSeverity

() select @ErrorState = 1 select @ErrorMessage = dbo.QBM_FGIErrorMessage() exec QBM_PRollbackIfAllowed RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState

) WITH NOWAIT END CATCH endLabel: return end

PSetRowLockOnly

USE [OneIM82]

/****** Object: StoredProcedure [dbo].[QBM_PSetRowLockOnly] Script Date: 09.04.2024 16:59:09 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

ALTER procedure [dbo].[QBM_PSetRowLockOnly] (@TablePattern nvarchar(64) = '%' ) as begin declare @SQLcmd1 nvarchar(1024) declare @SQLcmd2 nvarchar(1024) declare

@ElementBuffer QBM_YCursorBuffer declare @ElementCount int declare @ElementIndex int declare @DebugSwitch int = 0 BEGIN TRY set nocount on insert into

@ElementBuffer(ContentShort , ContentFull ) select case i.allow_row_locks when 0 then concat('exec sp_indexoption ''' , o.name , '.' , t.name , case when

i.type_desc = 'HEAP' and i.name is null then '' else '.' + i.name end , ''', ''AllowRowLocks'', ''True'' ' ) else '' end , case i.allow_page_locks when

1 then concat('exec sp_indexoption ''' , o.name , '.' , t.name , case when i.type_desc = 'HEAP' and i.name is null then '' else '.' + i.name end , ''', ''AllowPageLocks'', ''False'' '

) else '' end from sys.tables t join sys.schemas o on t.schema_id = o.schema_id and dbo.QBM_FGIObjectIsDropable (o.schema_id)=1 join sys.indexes i on

i.object_id = t.object_id where t.type = 'U' and t.name like @TablePattern and t.is_memory_optimized = 0 and i.is_hypothetical = 0 and t.name not like

'sys%' and t.name not like 'ms%' and t.name not like 'IH%' and t.name not like 'conflict%' and (i.allow_row_locks = 0 or i.allow_page_locks = 1 ) and

i.type_desc in ('CLUSTERED', 'NONCLUSTERED', 'HEAP') order by o.name, t.name, i.name select @ElementCount = @@ROWCOUNT select @ElementIndex = 1 while

@ElementIndex \<= @ElementCount begin select top 1 @SQLcmd1 = bu.ContentShort , @SQLcmd2 = bu.ContentFull from @ElementBuffer bu where bu.ElementIndex =

@ElementIndex if @DebugSwitch > 0 begin print @SQLcmd1 print @SQLcmd2 end if @SQLcmd1 > ' ' begin exec QBM_PExecuteSQL @SQLcmd1, @@procid end if @SQLcmd2

> ' ' begin exec QBM_PExecuteSQL @SQLcmd2, @@procid end select @ElementIndex += 1 end END TRY BEGIN CATCH declare @ErrorMessage nvarchar(4000) declare

@ErrorSeverity int declare @ErrorState int select @ErrorSeverity = dbo.QBM_FGIErrorSeverity() select @ErrorState = 1 select @ErrorMessage = dbo.QBM_FGIErrorMessage

() exec QBM_PRollbackIfAllowed RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState) WITH NOWAIT END CATCH end

--------

Вариант решения 1.

Создаем процедуру - заглушку

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[ClearProc]

AS

BEGIN

SET NOCOUNT ON;

END

GO

Вносим изменения в процедуру QBM_ZIndexRebuild

USE [OneIM]

GO

/****** Object: StoredProcedure [dbo].[QBM_ZIndexRebuild] Script Date: 12.04.2024 11:53:34 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER procedure [dbo].[QBM_ZIndexRebuild] ( @SlotNumberDummy int , @TableName varchar(38), @dummy1 varchar(38) = '', @dummy2 varchar(38) = '' ) as begin BEGIN

TRY exec ClearProc END TRY BEGIN CATCH declare @ErrorMessage nvarchar(4000) declare @ErrorSeverity int declare @ErrorState int select

@ErrorSeverity = dbo.QBM_FGIErrorSeverity() select @ErrorState = 1 select @ErrorMessage = dbo.QBM_FGIErrorMessage() exec QBM_PRollbackIfAllowed RAISERROR

(@ErrorMessage, @ErrorSeverity, @ErrorState) WITH NOWAIT END CATCH end

Вариант решения 2

Вносим изменения в процедуру QBM_ZIndexRebuild для учета размеров БД и кол-ва записей в них

USE [OneIM]

GO

/****** Object: StoredProcedure [dbo].[QBM_ZIndexRebuild] Script Date: 12.04.2024 11:53:34 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[QBM_ZIndexRebuild]

(

@SlotNumberDummy INT,

@TableName VARCHAR(38),

@dummy1 VARCHAR(38) = '',

@dummy2 VARCHAR(38) = ''

)

AS

BEGIN

DECLARE @Message NVARCHAR(1000) = '#LDS#Reindexing of {0} was not performed due to the size of the table.|' + @tablename + '|'

BEGIN TRY

IF EXISTS (

SELECT TOP 1 1

FROM DialogTable t

WHERE t.TableName = @tableName

AND (

t.SizeMB > 1024.0

OR dbo.QBM_FGITableCountAll(t.TableName) > 1000000

)

)

BEGIN

EXEC QBM_PJournal @Message, @@procid, 'I', 'I', 100

GOTO endLabel

END

BEGIN

EXEC QBM_PIndexRebuild @TableName

END

END TRY

BEGIN CATCH

DECLARE @ErrorMessage NVARCHAR(4000)

DECLARE @ErrorSeverity INT

DECLARE @ErrorState INT

SELECT @ErrorSeverity = dbo.QBM_FGIErrorSeverity()

SELECT @ErrorState = 1

SELECT @ErrorMessage = dbo.QBM_FGIErrorMessage()

EXEC QBM_PRollbackIfAllowed

RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState) WITH NOWAIT

END CATCH

endLabel:

RETURN

END

Keys	Action
`?`	Open this help
`n`	Next page
`p`	Previous page
`s`	Search