Outil de maintenance et de statistique pour SQL Server

    Voici un article très intéressant autour de la maintenance de SQL Server.

    SQL Server Index and Statistics Maintenance

    IndexOptimize is the SQL Server Maintenance Solution’s stored procedure for rebuilding and reorganizing indexes and updating statistics. IndexOptimize is supported on SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, and SQL Server 2012.

    Download

    Download MaintenanceSolution.sql. This script creates all the objects and jobs that you need. You can also download the objects as separate scripts.

    License

    The SQL Server Maintenance Solution is free.

    Parameters

    Databases

    Select databases. The keywords SYSTEM_DATABASES, USER_DATABASES, and ALL_DATABASES are supported. The hyphen character (-) is used to exclude databases, and the percent character (%) is used for wildcard selection. All of these operations can be combined by using the comma (,).

    ValueDescription
    SYSTEM_DATABASESAll system databases (master, msdb, and model)
    USER_DATABASESAll user databases
    ALL_DATABASESAll databases
    Db1The database Db1
    Db1, Db2The databases Db1 and Db2
    USER_DATABASES, -Db1All user databases, except Db1
    %Db%All databases that have “Db” in the name
    %Db%, -Db1All databases that have “Db” in the name, except Db1
    ALL_DATABASES, -%Db%All databases that do not have “Db” in the name

    FragmentationLow

    Specify index maintenance operations to be performed on a low-fragmented index.

    ValueDescription
    INDEX_REBUILD_ONLINERebuild index online.
    INDEX_REBUILD_OFFLINERebuild index offline.
    INDEX_REORGANIZEReorganize index.
    INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINERebuild index online. Rebuild index offline if online rebuilding is not supported on an index.
    INDEX_REBUILD_ONLINE,INDEX_REORGANIZERebuild index online. Reorganize index if online rebuilding is not supported on an index.
    INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINEReorganize index. Rebuild index online if reorganizing is not supported on an index. Rebuild index offline if reorganizing and online rebuilding are not supported on an index.
    NULLDo not perform index maintenance. This is the default for a low-fragmented index.

    An online index rebuild or an index reorganization is not always possible. Because of this, you can specify multiple index-maintenance operations for each fragmentation group. These operations are prioritized from left to right: If the first operation is supported for the index, then that operation is used; if the first operation is not supported, then the second operation is used (if supported), and so on. If none of the specified operations are supported for an index, then that index is not maintained.

    IndexOptimize uses the SQL Server ALTER INDEX command: REBUILD WITH (ONLINE = ON) to rebuild indexes online, REBUILD WITH (ONLINE = OFF) to rebuild indexes offline, and REORGANIZE to reorganize indexes.

    FragmentationMedium

    Specify index maintenance operations to be performed on a medium-fragmented index.

    ValueDescription
    INDEX_REBUILD_ONLINERebuild index online.
    INDEX_REBUILD_OFFLINERebuild index offline.
    INDEX_REORGANIZEReorganize index.
    INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINERebuild index online. Rebuild index offline if online rebuilding is not supported on an index.
    INDEX_REBUILD_ONLINE,INDEX_REORGANIZERebuild index online. Reorganize index if online rebuilding is not supported on an index.
    INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINEReorganize index. Rebuild index online if reorganizing is not supported on an index. Rebuild index offline if reorganizing and online rebuilding are not supported on an index. This is the default for a medium-fragmented index.
    NULLDo not perform index maintenance.

    An online index rebuild or an index reorganization is not always possible. Because of this, you can specify multiple index-maintenance operations for each fragmentation group. These operations are prioritized from left to right: If the first operation is supported for the index, then that operation is used; if the first operation is not supported, then the second operation is used (if supported), and so on. If none of the specified operations are supported for an index, then that index is not maintained.

    IndexOptimize uses the SQL Server ALTER INDEX command: REBUILD WITH (ONLINE = ON) to rebuild indexes online, REBUILD WITH (ONLINE = OFF) to rebuild indexes offline, and REORGANIZE to reorganize indexes.

    FragmentationHigh

    Specify index maintenance operations to be performed on a high-fragmented index.

    ValueDescription
    INDEX_REBUILD_ONLINERebuild index online.
    INDEX_REBUILD_OFFLINERebuild index offline.
    INDEX_REORGANIZEReorganize index.
    INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINERebuild index online. Rebuild index offline if online rebuilding is not supported on an index.
    This is the default for a high-fragmented index.
    INDEX_REBUILD_ONLINE,INDEX_REORGANIZERebuild index online. Reorganize index if online rebuilding is not supported on an index.
    INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINEReorganize index. Rebuild index online if reorganizing is not supported on an index. Rebuild index offline if reorganizing and online rebuilding are not supported on an index.
    NULLDo not perform index maintenance.

    An online index rebuild or an index reorganization is not always possible. Because of this, you can specify multiple index-maintenance operations for each fragmentation group. These operations are prioritized from left to right: If the first operation is supported for the index, then that operation is used; if the first operation is not supported, then the second operation is used (if supported), and so on. If none of the specified operations are supported for an index, then that index is not maintained.

    IndexOptimize uses the SQL Server ALTER INDEX command: REBUILD WITH (ONLINE = ON) to rebuild indexes online, REBUILD WITH (ONLINE = OFF) to rebuild indexes offline, and REORGANIZE to reorganize indexes.

    FragmentationLevel1

    Set the lower limit, as a percentage, for medium fragmentation. The default is 5 percent. This is based on Microsoft’s recommendation in Books Online.

    IndexOptimize checks avg_fragmentation_in_percent in sys.dm_db_index_physical_stats to determine the fragmentation.

    FragmentationLevel2

    Set the lower limit, as a percentage, for high fragmentation. The default is 30 percent. This is based on Microsoft’s recommendation in Books Online.

    IndexOptimize checks avg_fragmentation_in_percent in sys.dm_db_index_physical_stats to determine the fragmentation.

    PageCountLevel

    Set a size, in pages; indexes with fewer pages are skipped for index maintenance. The default is 1000 pages. This is based on Microsoft’s recommendation in this white paper.

    IndexOptimize checks page_count in sys.dm_db_index_physical_stats to determine the size of the index.

    SortInTempdb

    Use tempdb for sort operations when rebuilding indexes.

    ValueDescription
    YUse tempdb for sort operations when rebuilding indexes.
    NDo not use tempdb for sort operations when rebuilding indexes. This is the default.

    The SortInTempdb option in IndexOptimize uses the SORT_IN_TEMPDB option in the SQL Server ALTER INDEX command.

    MaxDOP

    Specify the number of CPUs to use when rebuilding indexes. If this number is not specified, the global maximum degree of parallelism is used.

    The MaxDOP option in IndexOptimize uses the MAXDOP option in the SQL Server ALTER INDEX command.

    FillFactor

    Indicate, as a percentage, how full the pages should be made when rebuilding indexes. If a percentage is not specified, the fill factor in sys.indexes is used.

    The FillFactor option in IndexOptimize uses the FILLFACTOR option in the SQL Server ALTER INDEX command.

    PadIndex

    Apply the percentage of free space that the fill factor specifies to the intermediate-level pages of the index.

    ValueDescription
    YApply the percentage of free space that the fill factor specifies to the intermediate-level pages of the index.
    NThe intermediate-level pages of the index are filled to near capacity. This is the default.

    The PadIndex option in IndexOptimize uses the PADINDEX option in the SQL Server ALTER INDEX command.

    LOBCompaction

    Compact pages that contain large object (LOB) columns, when reorganizing indexes.

    ValueDescription
    YCompact pages that contain LOB columns, when reorganizing indexes. This is the default.
    NDo not compact pages that contain LOB columns, when reorganizing indexes.

    The LOBCompaction option in IndexOptimize uses the LOB_COMPACTION option in the SQL Server ALTER INDEX command.

    UpdateStatistics

    Update statistics.

    ValueDescription
    ALLUpdate index and column statistics.
    INDEXUpdate index statistics.
    COLUMNSUpdate column statistics.
    NULLDo not perform statistics maintenance. This is the default.

    IndexOptimize uses the SQL Server UPDATE STATISTICS command to update statistics.

    OnlyModifiedStatistics

    Update statistics only if any rows have been modified since the most recent statistics update.

    ValueDescription
    YUpdate statistics only if any rows have been modified since the most recent statistics update.
    NUpdate statistics regardless of whether any rows have been modified.

    IndexOptimize checks modification_counter in sys.dm_db_stats_properties, in SQL Server 2008 R2 starting with Service Pack 2 and in SQL Server 2012 starting with Service Pack 1, to determine whether any rows have been modified since the most recent statistics update. In earlier versions it checks rowmodctr in sys.sysindexes.

    StatisticsSample

    Indicate, as a percentage, how much of a table is gathered when updating statistics. A value of 100 is equivalent to a full scan. If no value is specified, then SQL Server automatically computes the required sample.

    The StatisticsSample option in IndexOptimize uses the SAMPLE and FULLSCAN options in the SQL Server UPDATE STATISTICS command.

    StatisticsResample

    Update statistics with the most recent sample.

    ValueDescription
    YUpdate statistics with the most recent sample.
    NLet SQL Server automatically compute the required sample. This is the default.

    The StatisticsResample option in IndexOptimize uses the RESAMPLE option in the SQL Server UPDATE STATISTICS command.

    You cannot combine the options StatisticsSample and StatisticsResample.

    PartitionLevel

    Maintain partitioned indexes on the partition level. If this parameter is set to Y, the fragmentation level and page count is checked for each partition. The appropriate index maintenance (reorganize or rebuild) is then performed for each partition.

    ValueDescription
    YMaintain partitioned indexes on the partition level. This is the default.
    NMaintain partitioned indexes on the index level.

    MSShippedObjects

    Maintain indexes and statistics on objects that are created by internal SQL Server components.

    ValueDescription
    YMaintain indexes and statistics on objects that are created by internal SQL Server components.
    NDo not maintain indexes and statistics on objects that are created by internal SQL Server components. This is the default.

    IndexOptimize checks is_ms_shipped in sys.objects to determine whether an object was created by an internal SQL Server component.

    Indexes

    Select indexes. If this parameter is not specified, all indexes are selected. The ALL_INDEXES keyword is supported. The hyphen character (-) is used to exclude indexes, and the percent character (%) is used for wildcard selection. All these operations can be combined by using the comma (,).

    ValueDescription
    ALL_INDEXESAll indexes
    Db1.Schema1.Tbl1.Idx1The index Idx1 on the object Schema1.Tbl1 in the database Db1
    Db1.Schema1.Tbl1.Idx1, Db2.Schema2.Tbl2.Idx2The index Idx1 on the object Schema1.Tbl1 in the database Db1 and the index Idx2 on the object Schema2.Tbl2 in the database Db2
    Db1.Schema1.Tbl1All indexes on the object Schema1.Tbl1 in the database Db1
    Db1.Schema1.Tbl1, Db2.Schema2.Tbl2All indexes on the object Schema1.Tbl1 in the database Db1 and all indexes on the object Schema2.Tbl2 in the database Db2
    Db1.Schema1.%All indexes in the schema Schema1 in the database Db1
    %.Schema1.%All indexes in the schema Schema1 in all databases
    ALL_INDEXES, -Db1.Schema1.Tbl1.Idx1All indexes except the index Idx1 on the object Schema1.Tbl1 in the database Db1
    ALL_INDEXES, -Db1.Schema1.Tbl1All indexes except indexes on the object Schema1.Tbl1 in the database Db1

    TimeLimit

    Set the time, in seconds, after which no commands are executed. By default, the time is not limited.

    Delay

    Set the delay, in seconds, between index commands. By default, there is no delay.

    LockTimeout

    Set the time, in seconds, that a command waits for a lock to be released. By default, the time is not limited.

    The LockTimeout option in IndexOptimize uses the SET LOCK_TIMEOUT set statement in SQL Server.

    LogToTable

    Log commands to the table dbo.CommandLog.

    ValueDescription
    YLog commands to the table.
    NDo not log commands to the table. This is the default.

    Execute

    Execute commands. By default, the commands are executed normally. If this parameter is set to N, then the commands are printed only.

    ValueDescription
    YExecute commands. This is the default.
    NOnly print commands.

    Examples

    A. Rebuild or reorganize all indexes with fragmentation on all user databases

    EXECUTE dbo.IndexOptimize
    @Databases = 'USER_DATABASES',
    @FragmentationLow = NULL,
    @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
    @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
    @FragmentationLevel1 = 5,
    @FragmentationLevel2 = 30

    B. Rebuild or reorganize all indexes with fragmentation and update modified statistics on all user databases

    EXECUTE dbo.IndexOptimize
    @Databases = 'USER_DATABASES',
    @FragmentationLow = NULL,
    @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
    @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
    @FragmentationLevel1 = 5,
    @FragmentationLevel2 = 30,
    @UpdateStatistics = 'ALL',
    @OnlyModifiedStatistics = 'Y'

    C. Update statistics on all user databases

    EXECUTE dbo.IndexOptimize
    @Databases = 'USER_DATABASES',
    @FragmentationLow = NULL,
    @FragmentationMedium = NULL,
    @FragmentationHigh = NULL,
    @UpdateStatistics = 'ALL'

    D. Update modified statistics on all user databases

    EXECUTE dbo.IndexOptimize
    @Databases = 'USER_DATABASES',
    @FragmentationLow = NULL,
    @FragmentationMedium = NULL,
    @FragmentationHigh = NULL,
    @UpdateStatistics = 'ALL',
    @OnlyModifiedStatistics = 'Y'

    E. Rebuild or reorganize all indexes with fragmentation on all user databases, performing sort operations in tempdb and using all available CPUs

    EXECUTE dbo.IndexOptimize @Databases = 'USER_DATABASES',
    @FragmentationLow = NULL,
    @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
    @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
    @FragmentationLevel1 = 5,
    @FragmentationLevel2 = 30,
    @SortInTempdb = 'Y',
    @MaxDOP = 0

    F. Rebuild or reorganize all indexes with fragmentation on all user databases, using the option to maintain partitioned indexes on the partition level

    EXECUTE dbo.IndexOptimize
    @Databases = 'USER_DATABASES',
    @FragmentationLow = NULL,
    @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
    @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
    @FragmentationLevel1 = 5,
    @FragmentationLevel2 = 30,
    @PartitionLevel = 'Y'

    G. Rebuild or reorganize all indexes with fragmentation on all user databases, with a time limit so that no commands are executed after 3600 seconds

    EXECUTE dbo.IndexOptimize
    @Databases = 'USER_DATABASES',
    @FragmentationLow = NULL,
    @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
    @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
    @FragmentationLevel1 = 5,
    @FragmentationLevel2 = 30,
    @TimeLimit = 3600

    H. Rebuild or reorganize all indexes with fragmentation on the table Production.Product in the database AdventureWorks

    EXECUTE dbo.IndexOptimize
    @Databases = 'AdventureWorks',
    @FragmentationLow = NULL,
    @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
    @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
    @FragmentationLevel1 = 5,
    @FragmentationLevel2 = 30,
    @Indexes = 'AdventureWorks.Production.Product'

    I. Rebuild or reorganize all indexes with fragmentation except indexes on the table Production.Product in the database AdventureWorks

    EXECUTE dbo.IndexOptimize
    @Databases = 'USER_DATABASES',
    @FragmentationLow = NULL,
    @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
    @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
    @FragmentationLevel1 = 5,
    @FragmentationLevel2 = 30,
    @Indexes = 'ALL_INDEXES, -AdventureWorks.Production.Product'

    J. Rebuild or reorganize all indexes with fragmentation on all user databases and log the results to a table

    EXECUTE dbo.IndexOptimize
    @Databases = 'USER_DATABASES',
    @FragmentationLow = NULL,
    @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
    @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
    @FragmentationLevel1 = 5,
    @FragmentationLevel2 = 30,
    @LogToTable = 'Y'

    Execution

    Execute the stored procedures from SQL Server Agent CmdExec jobs, using sqlcmd and the -b option. Pass the parameters to the stored procedures by name:

    sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE dbo.IndexOptimize @Databases = 'USER_DATABASES'" -b

    Use SQL Server Agent output files to ensure that you always have a full set of information, in case of an error.

    You can use the MaintenanceSolution.sql script to install the SQL Server Maintenance Solution. Then the sqlcmd commands and the output files will be configured for you.

     

    Source

    Joris



    Leave your comments

    Post comment as a guest

    0
    Your comments are subject to administrator's moderation.
      • No comments found

      Menu principal