Back to OIM Explorer

dbo.QBM_PIndexRebuild

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure. Single DBQueue insert -> QBM-K-UpdateStatistics / QBM_ZUpdateStatistics at line 19; References QBM_PDBQueueInsert_Single

Source: sandbox-db sys.sql_modules

Source size: 3.250 characters

Interpretation

  • Database routine. Review parameters, called procedures, DBQueue inserts, and QBM_PJobCreate helper calls before assuming side effects.
  • DBQueue relation detected. Follow the task procedure and referenced-by list for async processing.

Relations

  • Single DBQueue insert -> QBM-K-UpdateStatistics / QBM_ZUpdateStatistics at line 19
  • References QBM_PDBQueueInsert_Single

Typed Edges

  • queues DBQueue task QBM_ZUpdateStatistics at line 19 Single DBQueue insert -> QBM-K-UpdateStatistics / QBM_ZUpdateStatistics at line 19
  • references source dbo.QBM_FTIndexFragmentation source text reference
  • references source dbo.QBM_FGISessionContext source text reference
  • references source dbo.QBM_PDBQueueInsert_Single source text reference
  • references source dbo.QBM_PExecuteSQLWithRetry_LLP source text reference
  • references source dbo.QBM_PGIDBServerIndexRebuildOpt source text reference
  • references source dbo.QBM_PJournal source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference
  • references source dbo.QBM_PSetRowLockOnly source text reference
  • references source dbo.QBM_PTableLockEscalationSet source text reference
  • queues DBQueue task QBM-K-UpdateStatistics -> QBM_ZUpdateStatistics QBM_PDBQueueInsert_Single 'QBM-K-UpdateStatistics', '', '', @GenProcID end ende: return END TRY BEGIN CATCH exec QBM_PSetRowLockOnly @TableName exec QBM_PSessionErrorAdd default raiserror ('', 18, 1) END CATCH endLabel: return end

References

Referenced By

Complete Source

SQL157 lines
1CREATE PROCEDURE QBM_PIndexRebuild(2  @TableName varchar(30)3)4AS5BEGIN6  DECLARE @SQLcmd nvarchar(max)7  DECLARE @tableID int8  DECLARE @SQLcmds QBM_YCursorBuffer9  DECLARE @ElementCount int10  DECLARE @ElementIndex int11  DECLARE @DebugLevel char(1) = 'W'12  DECLARE @GenProcID varchar(38) = dbo.QBM_FGISessionContext('')13  DECLARE @RebuildOptions nvarchar(max)14  DECLARE @ErrorBuffer QBM_YSessionError15  SET XACT_ABORT OFF16  BEGIN TRY17    IF @@TRANCOUNT > 018    BEGIN19      GOTO endlabel20    END21    IF isnull(@TableName,22    '') = ''23    BEGIN24      GOTO ende25    END26    SELECT @tableID = NULL27    SELECT TOP 1 @tableID = OBJECT_ID28    FROM sys.tables29    WHERE30      name = @TableName31    EXEC QBM_PTableLockEscalationSet @TableName32    IF @TableName IN(33      SELECT TableName34      FROM QBM_VHeavyLoadTables)35    BEGIN36      GOTO ende37    END38    IF NOT EXISTS(39      SELECT TOP 1 140      FROM information_schema.tables41      WHERE42        table_name = @TableName AND table_type = 'BASE TABLE') OR @tableID IS NULL43    BEGIN44      SELECT @SQLcmd = N 'No reorganize for Table ' + @TableName + N ' Table not exists'45      EXEC QBM_PJournal @SQLcmd,46        @@procid,47      'W',48        @DebugLevel49      GOTO ende50    END51    EXEC QBM_PGIDBServerIndexRebuildOpt @TableName,52      @RebuildOptions OUTPUT53    SET lock_timeout 500054    SELECT @ElementCount = 055    BEGIN TRY56      INSERT INTO @SQLcmds(ContentFull)57      SELECT58        N 'ALTER INDEX "' + x.indexname + N '" ON ' + schemaname + N '.' + @TableName + CASE59        WHEN x.PercentFrag < 20.0 AND x.index_id > 1 THEN60        N ' REORGANIZE'61      ELSE @RebuildOptions62      END63      FROM dbo.QBM_FTIndexFragmentation(@TableName,64      5) x65      ORDER BY x.index_id66      SELECT @ElementCount = @@rowcount67    END TRY68    BEGIN CATCH69      EXEC QBM_PSessionErrorAdd DEFAULT70      INSERT INTO @ErrorBuffer(ErrorMessage,71      ErrorSeverity,72      ErrorState,73      ErrorNumber,74      ProcedureName,75      ProcedureLine,76      MessageDate,77      GenProcID,78      RepeatCounter,79      IsReThrow,80      SourceCode)81      SELECT82        ERROR_MESSAGE(),83        ERROR_SEVERITY(),84        ERROR_STATE(),85        50000,86        ERROR_PROCEDURE(),87        ERROR_LINE(),88        GETUTCDATE(),89        NULL,90        0,91        0,92        'select fr.object_id, fr.index_id ...FROM sys.dm_db_index_physical_stats ...'93      EXEC QBM_PSessionErrorAdd @ErrorBuffer94      RAISERROR('',95      18,96      1)97    END CATCH98    IF @ElementCount > 099    BEGIN100      SELECT101        @SQLCmd = N 'ALTER INDEX ALL ON ' + @TableName + N ' SET (ALLOW_PAGE_LOCKS = ON)'102      EXEC QBM_PJournal @SQLcmd,103        @@PROCID,104      'D',105        @DebugLevel106      EXEC QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @SQLcmd,107        @LockTimeout_ms = DEFAULT,108        @MaxWaitTimeForLock_s = DEFAULT,109        @ProcIDForJournal = @@procid,110        @HandleErrorSilent = 0,111        @ExecuteWithTransact = 0112      SELECT @ElementIndex = 1113      WHILE @ElementIndex <= @ElementCount114      BEGIN115        SELECT TOP 1 @SQLcmd = bu.ContentFull116        FROM @SQLcmds bu117        WHERE118          bu.ElementIndex = @ElementIndex119        EXEC QBM_PJournal @sqlcmd,120          @@PROCID,121        'D',122          @DebugLevel123        EXEC QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @SQLcmd,124          @LockTimeout_ms = DEFAULT,125          @MaxWaitTimeForLock_s = 10.0,126          @ProcIDForJournal = @@procid,127          @HandleErrorSilent = 0,128          @ExecuteWithTransact = 0129        SELECT @ElementIndex += 1130      END131    END132    EXEC QBM_PSetRowLockOnly @TableName133    IF NOT EXISTS(134      SELECT TOP 1 1135      FROM DialogDBQueue q136      WITH(readpast)137    WHERE138      q.UID_Task = 'QBM-K-UpdateStatistics')139    BEGIN140      EXEC QBM_PDBQueueInsert_Single 'QBM-K-UpdateStatistics',141      '',142      '',143        @GenProcID144    END145    ende:146    RETURN147  END TRY148  BEGIN CATCH149    EXEC QBM_PSetRowLockOnly @TableName150    EXEC QBM_PSessionErrorAdd DEFAULT151    RAISERROR('',152    18,153    1)154  END CATCH155  endLabel:156  RETURN157END
Open raw exported source
SQL ยท Raw22 lines
1   create   procedure QBM_PIndexRebuild ( @TableName varchar(30) ) as begin declare @SQLcmd nvarchar(max) declare @tableID int declare @SQLcmds 2QBM_YCursorBuffer declare @ElementCount int declare @ElementIndex int declare @DebugLevel char(1) = 'W' declare @GenProcID varchar(38) = dbo.QBM_FGISessionContext3('') declare @RebuildOptions nvarchar(max) declare @ErrorBuffer QBM_YSessionError SET XACT_ABORT OFF BEGIN TRY if @@TRANCOUNT > 0 begin  goto endlabel 4end if isnull(@TableName, '') = '' begin goto ende end select @tableID = null select top 1 @tableID = OBJECT_ID from sys.tables where name = @TableName5 exec QBM_PTableLockEscalationSet @TableName  if @TableName in (select TableName from QBM_VHeavyLoadTables  ) begin goto ende end  if not exists (select6 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 '7 + @TableName + N' Table not exists' exec QBM_PJournal @SQLcmd, @@procid, 'W', @DebugLevel goto ende end  exec QBM_PGIDBServerIndexRebuildOpt @TableName8, @RebuildOptions output  set lock_timeout 5000 select @ElementCount = 0 BEGIN TRY insert into @SQLcmds (ContentFull) SELECT N'ALTER INDEX "' + x.indexname9 + N'" ON ' + schemaname + N'.' + @TableName + case when x.PercentFrag < 20.0 and x.index_id > 1 then N' REORGANIZE'   else @RebuildOptions end from dbo.QBM_FTIndexFragmentation10(@TableName, 5) x order by x.index_id select @ElementCount = @@rowcount END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default insert into @ErrorBuffer 11(ErrorMessage, ErrorSeverity, ErrorState, ErrorNumber , ProcedureName, ProcedureLine, MessageDate , GenProcID, RepeatCounter, IsReThrow, SourceCode) select12 ERROR_MESSAGE(),ERROR_SEVERITY(), ERROR_STATE(), 50000 , ERROR_PROCEDURE() , ERROR_LINE(), GETUTCDATE() , null, 0, 0, 'select fr.object_id, fr.index_id ...FROM sys.dm_db_index_physical_stats ...'13 exec QBM_PSessionErrorAdd @ErrorBuffer raiserror ('', 18, 1) END CATCH if @ElementCount > 0 begin  select @SQLCmd = N'ALTER INDEX ALL ON ' + @TableName14 + N' SET (ALLOW_PAGE_LOCKS = ON)' exec QBM_PJournal @SQLcmd, @@PROCID , 'D', @DebugLevel exec QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @SQLcmd , @LockTimeout_ms15 = default  , @MaxWaitTimeForLock_s = default , @ProcIDForJournal = @@procid , @HandleErrorSilent = 0 , @ExecuteWithTransact = 0 select @ElementIndex =16 1 while @ElementIndex <= @ElementCount begin select top 1 @SQLcmd = bu.ContentFull from @SQLcmds bu where bu.ElementIndex = @ElementIndex exec QBM_PJournal17 @sqlcmd, @@PROCID, 'D', @DebugLevel exec QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @SQLcmd , @LockTimeout_ms = default , @MaxWaitTimeForLock_s = 10.018  , @ProcIDForJournal = @@procid , @HandleErrorSilent = 0 , @ExecuteWithTransact = 0 select @ElementIndex += 1 end  end  exec QBM_PSetRowLockOnly @TableName19 if not exists (select top 1 1 from DialogDBQueue q with (readpast) where q.UID_Task = 'QBM-K-UpdateStatistics' ) begin exec QBM_PDBQueueInsert_Single 20'QBM-K-UpdateStatistics', '', '', @GenProcID end ende: return END TRY BEGIN CATCH  exec QBM_PSetRowLockOnly @TableName exec QBM_PSessionErrorAdd default21 raiserror ('', 18, 1) END CATCH endLabel: return end 22