Defragment Indexes

Defragment indexes is not a real monitoring procedure since it doesn’t report any findings. It also doens’t work over Linked Servers. You need to create the Sequenchel database locally on the server where you need to use it. The fllowing information is collected and used during runtime.

  • LinkedServer: The Server where the procedure is run. For now this must be the local server.
  • DatabaseName: The database to process.
  • SchemaName: The schema to which the table belongs.
  • TableObjectID
  • TableName: The table that will be processed.
  • IndexObjectID
  • IndexName: The index that will be processed.
  • IndexRows: The number of rows the index has.
  • IndexLocks: If there are any locks on the index.
  • OfflineColums: If the index has columns that prevent offline re-indexing.
  • FragBefore: The fragmentation level before defragmenting.
  • ProcessStart: The date and time the defragmenting began.

How to use it

Create a scheduled job using the Settings window. This will create a job with most of the required information, but not all. The job will need some tuning before operation.

The LinkedServer parameter must be set to match the name of the local SQL Server.
The Database parameter needs to be set to the name of the local database that needs defragmenting.
The other parameters can be adjusted to suit your personal needs.

If you prefer to create the job manually, execute the stored procedure with the following parameters:

DECLARE @RC int
DECLARE @LinkedServer sysname
DECLARE @Database sysname
DECLARE @MaxTime int
DECLARE @DefragLimit int
DECLARE @ReindexLimit int
DECLARE @IndexOnline int
DECLARE @InventoryOnly int
DECLARE @Exceptionlist nvarchar(2000)
DECLARE @Separator nchar(1)

SET @LinkedServer= ‘LocalServerName’ 
SET @Database= ‘DatabaseToProcess’
SET @MaxTime= 60
SET @DefragLimit= 5
SET @ReindexLimit= 30
SET @IndexOnline= 0
SET @InventoryOnly= 0
SET @ExceptionList = ‘IndexNotToProcess’
SET @Separator = ‘,’

EXECUTE @RC = [dbo].[usp_DefragIndexes] 
   @LinkedServer
  ,@Database
  ,@MaxTime
  ,@DefragLimit
  ,@ReindexLimit
  ,@IndexOnline
  ,@InventoryOnly
  ,@Exceptionlist
  ,@Separator
;

  • LinkedServer: The name of the local server. This is not really required but in a future version we may be able to process a remote server.
  • Database: The database to process. For every database this procedure must be run or scheduled separately.
  • MaxTime: The maximum amount of time in minutes that the procedure is allowed to start re-indexing another index. After this time expires the procedure completes the index currently being processed, but doesn’t move on to the next.
  • DefragLimit: The lower fragmentation limt for defragmentation. Below this limit no action is taken.
  • ReindexLimit: The upper fragmentation limit for defragmentation. Above this limit the index will be re-indexed rather than defragmented.
  • IndexOnline: If set to 1; If possible the index will be re-indexed online.
  • InventoryOnly: Scan all indexes for their fragmentation level, but do not process them. The information is stored in the (local) Sequenchel database.
  • ExceptionList: A comma separated list of indexes that should not be processed.
  • Separator: The separator to be used in the exception list.

Collected Information

  • LinkedServer: The server processed.
  • DatabaseName: The database processed.
  • SchemaName: The schema the table belongs to.
  • TableObjectID
  • TableName: The table where the index belongs to.
  • IndexObjectID
  • IndexName: The index that was processed.
  • IndexRows: The number of rows the index contained at the time of processing.
  • IndexLocks
  • OfflineColumns: Whether the index contacinst columns that prevent online indexing.
  • FragBefore: The fragmentation level before processing.
  • FragAfter: The fragmentation level after processing.
  • DateStart: The first time the index was found.
  • DateStop: The first time the index wasn’t found anymore.
  • ProcessStart: The date and time the processing started.
  • ProcessStop: The date and time processing was completed.