Skip to content

Latest commit

 

History

History
41 lines (41 loc) · 10.4 KB

OPTIONS.md

File metadata and controls

41 lines (41 loc) · 10.4 KB

Available parameters for AdaptiveIndexDefrag

  • @Exec_Print defaults to 1 (that is to execute the SQL code generated by this SP) or optionally 0 (just print the commands).
  • @printCmds defaults to 0 (do not print all commands to screen) or optionally 1 (print all commands to screen). Useful if you just want to see what commands would be executed.
  • @outputResults defaults to 0 (does not output fragmentation information) or optionally 1 (output fragmentation information after run completes).
  • @debugMode defaults to 0 (do not display debug comments) or optionally 1 (display debug comments).
  • @timeLimit limits how much time can be spent performing index defrags and is expressed in minutes. Note that the time limit is checked BEFORE an index defrag begins, thus a long index defrag can exceed the time limit. Defaults to 480m (8h).
  • @dbScope specifies a database name to defrag. If not specified, all non-system databases plus msdb and model will be defragmented.
  • @tblName specifies if you only want to defrag indexes for a specific table. The input format is schema.table_name. If not specified, all tables will be defragmented.
  • @defragOrderColumn defines how to prioritize the order of defrags and is used only if @Exec_Print is set to 1. The default is to order by range_scan_count (count of range and table scans on the index because these can benefit the most from defragmentation), other option are fragmentation (amount of fragmentation in the index) or page_count (number of pages in the index).
  • @defragSortOrder defines the sort order of the ORDER BY clause on the above query on how to prioritize the order of defrags. Options are ASC (ascending) or DESC (descending), which is the default.
  • @forceRescan defaults to 0, where a rescan will not occur until all indexes have been defragmented (this allows for a defrag run to span multiple executions over several periods of time). Other option is 1 to force a rescan.
  • @defragDelay specifies the time to wait between defrag commands and defaults to 5s. Refer to Reorganizing and Rebuilding Indexes for documentation on the following parameters.
  • @ixtypeOption defaults to NULL (all indexes will be defragmented). Other options are 1 (only Clustered indexes) or 0 (only Non-Clustered indexes, including XML and Spatial Indexes).
  • @minFragmentation defaults to 5%, will not defrag if fragmentation is less.
  • @rebuildThreshold defaults to 30%. Higher than 30% will result in a rebuild operations instead of reorganize.
  • @rebuildThreshold_cs defaults to 10%. Greater than 10% will result in columnstore rebuild.
  • @minPageCount specifies how many pages must exist in an index in order to be considered for a defrag. Defaults to one extent (8 pages).
  • @maxPageCount specifies the maximum number of pages that can exist in an index and still be considered for a defrag run. Useful for scheduling small indexes during business hours and large indexes for non-business hours.
  • @fillfactor defaults to 1 (the original FF from when the index was created or last defragmented) or optional 0 (uses the default FF of 0).
  • @scanMode specifies which scan mode to use to determine fragmentation levels. LIMITED mode is the default. Scans the smallest number of pages. For an index, only the parent-level pages of the B-tree (that is, the pages above the leaf level) are scanned. For a heap, only the associated PFS and IAM pages are examined. The data pages of the heap are not scanned. Other options include SAMPLED (returns statistics based on a 1 percent sample of all the pages in the index or heap) or DETAILED (scans all pages and returns all statistics. Can cause performance issues). If the index or heap has fewer than 10,000 pages, DETAILED mode is automatically used instead of SAMPLED.
  • @onlineRebuild defaults to 0 (offline rebuild) or optionally 1 (online rebuild if possible).
  • @sortInTempDB defaults to 0 (perform sort operation in the index’s database) or optionally 1 (perform sort operation in TempDB). If a sort operation is not required, or if the sort can be performed in memory, SORT_IN_TEMPDB is ignored. Setting this option to 1 can result in faster defrags and prevent database file size inflation. The caveat is you have to monitor TempDB closely.
  • @maxDopRestriction specifies a processor limit for index rebuilds and statistics updates. If not specified, defrag operations will use the system MaxDOP setting, up to a limit of 8.
  • @updateStats defaults to 1 (updates stats when reorganizing) or optionally 0 (does not update stats when reorganizing).
  • @updateStatsWhere defaults to 0 (updates all stats in entire table) or optionally 1 (updates only index related stats). Even if you choose to update stats, and if the @scanMode option was NOT set in LIMITED mode, only those within certain thresholds will be updated. Refer to Statistical maintenance functionality (autostats) in SQL Server to check the defaults for auto-update statistics.
  • @statsSample defaults to NULL (performs a sample scan on the target table or indexed view where the database engine automatically computes the required sample size), or optionally FULLSCAN (all rows in table or view should be read to gather the statistics) or RESAMPLE (statistics will be gathered using an inherited sampling ratio for all existing statistics including indexes).
  • @persistStatsSample defaults to NULL (does nothing). Other options are 1 (turns ON fixed sampling rate) or 0 (turns OFF fixed sampling rate).
  • @statsThreshold defaults to NULL (use default stats sample method same as TF2371). Any float number greater or equal to 0.001 and less than 100 uses custom stats sample.
  • @statsMinRows defaults to NULL, or optionally integer number that sets the min number of rows a table has to have to be considered for @statsThreshold use.
  • @ix_statsnorecomp defaults to 0 (run with STATISTICS_NORECOMPUTE OFF). Refer to ALTER INDEX (Transact-SQL) for information on the option STATISTICS_NORECOMPUTE. Optionally use 1 (run with STATISTICS_NORECOMPUTE ON will disable the auto update statistics on index related statistics). If you are dealing with stats update with a custom job (or even with this code by updating statistics), you may use this option.
  • @statsIncremental defaults to NULL. When Incremental is ON, the statistics created are per partition statistics. When OFF, the statistics tree is dropped and SQL Server re-computes the statistics. This setting overrides the database level INCREMENTAL property.
  • @dealMaxPartition specifies whether to exclude the right-most populated partition (if an index is partitioned), or act only on that same partition, excluding all others. Typically, this is the partition that is currently being written to in a sliding-window scenario. Enabling this feature may reduce contention. This may not be applicable in other types of partitioning scenarios. Non-partitioned indexes are unaffected by this option. This parameter defaults to 0 (only right-most populated partition is defragmented). If the partition is smaller than @minPageCount, it won’t be considered. Other options are 1 (to exclude the right-most populated partition) or NULL (all partitions are defragmented).
  • @dealLOB specifies if all pages that contain large object (LOB) data are compacted or not. The LOB data types are image, text, ntext, varchar(max), nvarchar(max), varbinary(max), and xml. Compacting this data can improve disk space use. Reorganizing a specified clustered index compacts all LOB columns that are contained in the clustered index. Reorganizing a non-clustered index compacts all LOB columns that are non-key (included) columns in the index. Default is 0 (compact LOBs when reorganizing) and optional 1 (does not compact LOBs when reorganizing).
  • @ignoreDropObj specifies if a table or index is dropped after the defrag cycle has begun, you can choose to ignore those errors in the overall outcome, thus not showing a job as failed if the only errors present refer to dropped database objects. Default is 0 (includes errors about objects that have been dropped since the defrag cycle began) and optional 1 (for error reporting purposes, ignores the fact that objects have been dropped since the defrag cycle began).
  • @disableNCIX specifies if non-clustered indexes are to be disabled before a rebuild. If disk space is limited, it may be helpful to disable the non-clustered index before rebuilding it. When a non-clustered index is not disabled, the rebuild operation requires enough temporary disk space to store both the old and new index. However, by disabling and rebuilding a non-clustered index in separate transactions, the disk space made available by disabling the index can be reused by the subsequent rebuild or any other operation. Hence, no additional space is required except for temporary disk space for sorting (this is typically 20 percent of the index size according to BOL). Note that it does not disable indexes on partitioned tables when defragging a subset of existing partitions. Also, the procedure keeps track of whatever indexes were disabled by the defrag cycle. In case the defrag is canceled, it will account for these disabled indexes in the next run. Default is 0 (does NOT disable non-clustered indexes prior to a rebuild) and optional 1 (disables non-clustered indexes prior to a rebuild).
  • @offlinelocktimeout specifies a lock timeout period in seconds when doing offline index rebuilds.
  • @onlinelocktimeout specifies a time-out period for locks to wait at low priority, expressed in minutes; this is valid from SQL Server 2014 onwards.
  • @abortAfterwait sets the action of @onlinelocktimeout. This parameter defaults to NULL (After lock timeout occurs, continue waiting for the lock with normal (regular) priority) and optionals are 0 (Kill all user transactions that block the online index rebuild DDL operation so that the operation can continue) and 1 (Exit the online index rebuild DDL operation currently being executed without taking any action).
  • @dealROWG sets the Columnstore reorg option to compress all rowgroups, and not just closed ones.
  • @getBlobfrag sets blob handling behavior. This parameter defaults to 0 to exclude blobs from fragmentation scan, but can optionally include blobs and off-row data when scanning for fragmentation.
  • @dataCompression sets the compression option to use on all indexes. This parameter defaults to NULL, to keep whatever compression setting exists for the object and partition.