**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 0 (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 0 (output fragmentation information after run completes).
- **@debugMode** defaults to 0 (do not display debug comments) or optionally 0 (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. 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).
- **@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.
