meta données pour cette page
  •  

Différences

Ci-dessous, les différences entre deux révisions de la page.

Lien vers cette vue comparative

Les deux révisions précédentesRévision précédente
Prochaine révision		Révision précédente
sql_server:database:index [23/05/2024 13:53] – mdlsql_server:database:index [05/11/2025 09:25] (Version actuelle) – mdl
Ligne 1: Ligne 1:
-====== INDEX ======+====== Index ====== 
 + 
 +  * rebuild en cours 
 +<code TSQL> 
 +SELECT r.session_id,r.command,CONVERT(NUMERIC(6,2),r.percent_complete) 
 +AS [Percent Complete],CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GetDate()),20) AS [ETA Completion Time], 
 +CONVERT(NUMERIC(10,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed Min], 
 +CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0) AS [ETA Min], 
 +CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours], 
 +CONVERT(VARCHAR(1000),(SELECT SUBSTRING(text,r.statement_start_offset/2, 
 +CASE WHEN r.statement_end_offset = -1 THEN 1000 ELSE (r.statement_end_offset-r.statement_start_offset)/2 END) 
 +FROM sys.dm_exec_sql_text(sql_handle))) 
 +FROM sys.dm_exec_requests r WHERE command IN ('Alter Index') 
 +</code>
  
   * Fragmentation   * Fragmentation
Ligne 19: Ligne 32:
 </code> </code>
  
-  * Rebuild+  * entre 10% et 30% de défragmentation => REORGANIZE 
 +  * au dessus de 30% de défragmentation => REBUILD 
 + 
 +  * Rebuild (drop et recréé l'index, donc risque de LOCK, sauf en mode ONLINE)
 <code TSQL> <code TSQL>
 --Basic Rebuild Command --Basic Rebuild Command
 ALTER INDEX Index_Name ON Table_Name REBUILD ALTER INDEX Index_Name ON Table_Name REBUILD
    
---REBUILD Index with ONLINE OPTION (enterprise) +--REBUILD Index with ONLINE OPTION (enterprise edition) 
-ALTER INDEX Index_Name ON Table_Name REBUILD WITH(ONLINE=ON) | WITH(ONLINE=ON)+ALTER INDEX Index_Name ON Table_Name REBUILD WITH(ONLINE=ON) 
 +</code> 
 + 
 +  * Reorganized (ne drop pas l'index, restructure, ne touche pas aux statistiques, se fait toujours ONLINE) 
 +<code TSQL> 
 +ALTER INDEX IX_OrderTracking_SalesOrderID ON Sales.OrderTracking REORGANIZE 
 +</code> 
 + 
 + 
 +---- 
 + 
 +  * Script:  
 + 
 +<code TSQL> 
 +---- 
 +-- Script that reorganizes or rebuilds all indexes having an average fragmentation  
 +-- percentage above a given threshold. It also works in the case 
 +-- where Availability Groups are enabled as it determines if the 
 +-- relevant databases are the primary replicas. 
 +-- 
 +-- This script supports only SQL Server 2005 or later. 
 +-- Also, if you execute this script in a SQL Server 2005 instance  
 +-- or later, any databases with compatibility level 2000 (80) or earlier 
 +-- will be automatically excluded from the index reorganization/rebuild process. 
 +---- 
 + 
 +--Initial check - You must be SysAdmin 
 +DECLARE @isSysAdmin INT 
 +SET @isSysAdmin=(SELECT IS_SRVROLEMEMBER ('sysadmin')); 
 + 
 +--Initial check - You must be using SQL Server 2005 or later 
 +DECLARE @SQLServerVersion INT 
 +SET @SQLServerVersion=(SELECT CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(50)),CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(50)))-1) AS INT)); 
 + 
 + 
 +IF @isSysAdmin=1 AND @SQLServerVersion >= 9 
 +BEGIN  
 + 
 +-- 
 +-- Variable/parameters Declaration 
 +-- 
 +DECLARE @dbname NVARCHAR(128); 
 +DECLARE @ReorganizeOrRebuildCommand NVARCHAR(MAX); 
 +DECLARE @dbid INT; 
 +DECLARE @indexFillFactor VARCHAR(5);  
 +DECLARE @fragmentationThreshold VARCHAR(10); 
 +DECLARE @indexStatisticsScanningMode VARCHAR(20); 
 +DECLARE @verboseMode BIT; 
 +DECLARE @reportOnly BIT; 
 +DECLARE @sortInTempdb VARCHAR(3); 
 +DECLARE @isHadrEnabled BIT; 
 +DECLARE @databaseToCheck VARCHAR(250) 
 +DECLARE @dynamic_command NVARCHAR(1024); 
 +DECLARE @dynamic_command_get_tables NVARCHAR(MAX); 
 + 
 +--Initializations - Do not change 
 +SET @databaseToCheck=NULL; 
 +SET @dynamic_command = NULL; 
 +SET @dynamic_command_get_tables = NULL; 
 +SET @isHadrEnabled=0; 
 + 
 +SET NOCOUNT ON; 
 + 
 +--------------------------------------------------------- 
 +--Set Parameter Values: You can change these (optional) - 
 +--Note: The script has default parameters set   - 
 +--------------------------------------------------------- 
 +--if set to 1: it will just generate a report with the index reorganization/rebuild statements 
 +--if set to 0: it will reorganize or rebuild the fragmented indexes 
 +SET @reportOnly = 0; 
 + 
 +--optional: if not set (NULL), it will scann all databases 
 +--If name is set (i.e. 'testDB') it will just scan the given database 
 +SET @databaseToCheck = NULL; 
 + 
 +--maintains only the indexes that have average fragmentation percentage equal or higher from the given value 
 +SET @fragmentationThreshold = 15;  
 + 
 +--fill factor - the percentage of the data page to be filled up with index data 
 +SET @indexFillFactor = 90;  
 + 
 +--sets the scanning mode for index statistics  
 +--available values: 'DEFAULT', NULL, 'LIMITED', 'SAMPLED', or 'DETAILED' 
 +SET @indexStatisticsScanningMode='SAMPLED'; 
 + 
 +--if set to ON: sorts intermediate index results in TempDB  
 +--if set to OFF: sorts intermediate index results in user database's log file 
 +SET @sortInTempdb='ON';  
 + 
 +--if set to 0: Does not output additional information about the index reorganization/rebuild process 
 +--if set to 1: Outputs additional information about the index reorganization/rebuild process 
 +SET @verboseMode = 0;  
 +------------------------------ 
 +--End Parameter Values Setup - 
 +------------------------------ 
 + 
 +-- check if given database exists and if compatibility level >= SQL 2005 (90) 
 +IF @verboseMode=1 
 + PRINT 'Checking if database '+@databaseToCheck+' exists and if compatibility level equals or greater 2005 (90)'; 
 + 
 + -- if given database does not exist, raise error with severity 20 
 + -- in order to terminate script's execution 
 +IF @databaseToCheck IS NOT NULL 
 +BEGIN 
 + DECLARE @checkResult INT 
 + SET @checkResult=(SELECT COUNT(*) FROM master.sys.databases WHERE [name]=RTRIM(@databaseToCheck)); 
 + IF @checkResult<1 
 +  RAISERROR('Error executing index reorganization/rebuild script: Database does not exist' , 20, 1) WITH LOG; 
 + 
 + DECLARE @checkResult2 INT 
 + SET @checkResult=(SELECT [compatibility_level] FROM master.sys.databases WHERE [name]=RTRIM(@databaseToCheck)); 
 + IF @checkResult<90 
 +  RAISERROR('Error executing index reorganization/rebuild script: Only databases with SQL Server 2005 or later compatibility level are supported' , 20, 1) WITH LOG;   
 +END 
 + 
 +IF @verboseMode=1 
 + PRINT 'Initial checks completed with no errors.'; 
 + 
 +-- Temporary table for storing index fragmentation details 
 +IF OBJECT_ID('tempdb..#tmpFragmentedIndexes') IS NULL 
 +BEGIN 
 +CREATE TABLE #tmpFragmentedIndexes 
 +    ( 
 +      [dbName] sysname, 
 +      [tableName] sysname, 
 +   [schemaName] sysname, 
 +      [indexName] sysname, 
 +      [databaseID] SMALLINT , 
 +      [objectID] INT , 
 +      [indexID] INT , 
 +      [AvgFragmentationPercentage] FLOAT, 
 +   [reorganizationOrRebuildCommand] NVARCHAR(MAX) 
 +    ); 
 +END  
 + 
 +-- Initialize temporary table 
 +DELETE FROM #tmpFragmentedIndexes; 
 + 
 +-- Validate parameters/set defaults 
 +IF @sortInTempdb NOT IN ('ON','OFF') 
 +SET @sortInTempdb='ON'; 
 + 
 +-- Check if instance has AlwaysOn AGs enabled 
 +SET @isHadrEnabled=CAST((SELECT ISNULL(SERVERPROPERTY('IsHadrEnabled'),0)) AS BIT); 
 + 
 +-- if database not specified scan all databases 
 +IF @databaseToCheck IS NULL 
 +BEGIN 
 +DECLARE dbNames_cursor CURSOR 
 +FOR 
 +    SELECT  s.[name] AS dbName , 
 +            s.database_id 
 +    FROM    master.sys.databases s             
 +    WHERE   s.state_desc = 'ONLINE' 
 +            AND s.is_read_only != 1             
 +            AND s.[name] NOT IN ( 'master', 'model', 'tempdb' ) 
 +   AND s.[compatibility_level]>=90 
 +    ORDER BY s.database_id;     
 +END  
 +ELSE 
 +-- if database specified, scan only that database 
 +BEGIN 
 +DECLARE dbNames_cursor CURSOR  
 +FOR 
 +    SELECT  s.[name] AS dbName , 
 +            s.database_id 
 +    FROM    master.sys.databases s             
 +    WHERE   s.state_desc = 'ONLINE' 
 +            AND s.is_read_only != 1                         
 +   AND s.[name]=RTRIM(@databaseToCheck)     
 +END  
 + 
 +-- if Always On Availability Groups are enabled, check for primary databases 
 +-- (thus exclude secondary databases) 
 +IF @isHadrEnabled=1 
 +BEGIN 
 + 
 +DEALLOCATE dbNames_cursor; 
 + 
 +-- if database not specified scan all databases 
 +IF @databaseToCheck IS NULL 
 +BEGIN 
 + DECLARE dbNames_cursor CURSOR 
 + FOR 
 +  SELECT  s.[name] AS dbName , 
 +    s.database_id 
 +  FROM    master.sys.databases s 
 +    LEFT JOIN master.sys.dm_hadr_availability_replica_states r ON s.replica_id = r.replica_id 
 +  WHERE   s.state_desc = 'ONLINE' 
 +    AND s.is_read_only != 1 
 +    AND UPPER(ISNULL(r.role_desc, 'NonHadrEnabled')) NOT LIKE 'SECONDARY' 
 +    AND s.[name] NOT IN ( 'master', 'model', 'tempdb' ) 
 +    AND s.[compatibility_level]>=90  
 +  ORDER BY s.database_id;     
 +END 
 +ELSE 
 +-- if database specified, scan only that database 
 +BEGIN 
 + DECLARE dbNames_cursor CURSOR 
 + FOR 
 +  SELECT  s.[name] AS dbName , 
 +    s.database_id 
 +  FROM    master.sys.databases s 
 +    LEFT JOIN master.sys.dm_hadr_availability_replica_states r ON s.replica_id = r.replica_id 
 +  WHERE   s.state_desc = 'ONLINE' 
 +    AND s.is_read_only != 1 
 +    AND UPPER(ISNULL(r.role_desc, 'NonHadrEnabled')) NOT LIKE 'SECONDARY'     
 +    AND s.[name]=RTRIM(@databaseToCheck);   
 +END  
 +END  
 + 
 + 
 +-- 
 +-- For each database included in the cursor,  
 +-- gather all tables that have indexes with  
 +-- average fragmentation percentage equal or above @fragmentationThreshold 
 +-- 
 +OPEN dbNames_cursor; 
 +FETCH NEXT FROM dbNames_cursor INTO @dbname, @dbid; 
 +WHILE @@fetch_status = 0 
 +    BEGIN    
 +  
 + --If verbose mode is enabled, print logs 
 +        IF @verboseMode = 1 
 +            BEGIN 
 +    PRINT '' 
 +                PRINT 'Gathering index fragmentation statistics for database: ['+ @dbname + '] with id: ' + CAST(@dbid AS VARCHAR(10));     
 +            END; 
 +                    
 +        SET @dynamic_command_get_tables = N' 
 + USE [' + @dbname+ N']; 
 + INSERT INTO #tmpFragmentedIndexes ( 
 +  [dbName], 
 +  [tableName], 
 +  [schemaName], 
 +  [indexName], 
 +  [databaseID], 
 +  [objectID], 
 +  [indexID], 
 +  [AvgFragmentationPercentage], 
 +  [reorganizationOrRebuildCommand]   
 +  ) 
 +  SELECT 
 +     DB_NAME() as [dbName],  
 +     tbl.name as [tableName], 
 +     SCHEMA_NAME (tbl.schema_id) as schemaName,  
 +     idx.Name as [indexName],  
 +     pst.database_id as [databaseID],  
 +     pst.object_id as [objectID],  
 +     pst.index_id as [indexID],  
 +     pst.avg_fragmentation_in_percent as [AvgFragmentationPercentage], 
 +     CASE WHEN pst.avg_fragmentation_in_percent > 30 THEN  
 +     ''ALTER INDEX [''+idx.Name+''] ON [''+DB_NAME()+''].[''+SCHEMA_NAME (tbl.schema_id)+''].[''+tbl.name+''] REBUILD WITH (FILLFACTOR = '+@indexFillFactor+', SORT_IN_TEMPDB = '+@sortInTempdb+', STATISTICS_NORECOMPUTE = OFF);'' 
 +     WHEN pst.avg_fragmentation_in_percent > 5 AND pst.avg_fragmentation_in_percent <= 30 THEN  
 +     ''ALTER INDEX [''+idx.Name+''] ON [''+DB_NAME()+''].[''+SCHEMA_NAME (tbl.schema_id)+''].[''+tbl.name+''] REORGANIZE;''      
 +     ELSE 
 +     NULL 
 +     END 
 +  FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL , '''+@indexStatisticsScanningMode+''') as pst 
 +   INNER JOIN sys.tables as tbl ON pst.object_id = tbl.object_id 
 +   INNER JOIN sys.indexes idx ON pst.object_id = idx.object_id AND pst.index_id = idx.index_id 
 +  WHERE pst.index_id != 0   
 +   AND pst.alloc_unit_type_desc IN ( N''IN_ROW_DATA'', N''ROW_OVERFLOW_DATA'') 
 +   AND pst.avg_fragmentation_in_percent >= '+ @fragmentationThreshold + ''; 
 +         
 +  -- if verbose  mode is enabled, print logs     
 +  IF @verboseMode=1 
 +   BEGIN 
 +    PRINT 'Index fragmentation statistics script: ';     
 +    PRINT @dynamic_command_get_tables; 
 +  END 
 + 
 +  -- gather index fragmentation statistics 
 +        EXEC (@dynamic_command_get_tables); 
 +        
 +     -- bring next record from the cursor 
 +        FETCH NEXT FROM dbNames_cursor INTO @dbname, @dbid; 
 +    END; 
 + 
 +CLOSE dbNames_cursor; 
 +DEALLOCATE dbNames_cursor; 
 + 
 +------------------------------------------------------------ 
 + 
 +-- if 'report only' mode is enabled 
 +IF @reportOnly=1 
 +BEGIN  
 + SELECT  dbName , 
 +            tableName , 
 +            schemaName , 
 +            indexName ,             
 +            AvgFragmentationPercentage , 
 +            reorganizationOrRebuildCommand 
 + FROM    #tmpFragmentedIndexes 
 + ORDER BY AvgFragmentationPercentage DESC; 
 +END 
 +ELSE  
 +-- if 'report only' mode is disabled, then execute  
 +-- index reorganize/rebuild statements 
 +BEGIN  
 + DECLARE reorganizeOrRebuildCommands_cursor CURSOR 
 + FOR 
 +    SELECT  reorganizationOrRebuildCommand 
 +  FROM #tmpFragmentedIndexes 
 +  WHERE reorganizationOrRebuildCommand IS NOT NULL 
 +  ORDER BY AvgFragmentationPercentage DESC; 
 + 
 + OPEN reorganizeOrRebuildCommands_cursor; 
 + FETCH NEXT FROM reorganizeOrRebuildCommands_cursor INTO @ReorganizeOrRebuildCommand; 
 + WHILE @@fetch_status = 0 
 +  BEGIN    
 +          
 +   IF @verboseMode = 1 
 +   BEGIN 
 +     PRINT '' 
 +     PRINT 'Executing script:'      
 +     PRINT @ReorganizeOrRebuildCommand 
 +   END 
 +           
 +   EXEC (@ReorganizeOrRebuildCommand);           
 +   FETCH NEXT FROM reorganizeOrRebuildCommands_cursor INTO @ReorganizeOrRebuildCommand; 
 +  END; 
 + 
 + CLOSE reorganizeOrRebuildCommands_cursor; 
 + DEALLOCATE reorganizeOrRebuildCommands_cursor; 
 + 
 + PRINT '' 
 + PRINT 'All fragmented indexes have been reorganized/rebuilt.' 
 + PRINT '' 
 +END 
 +END  
 +ELSE 
 +BEGIN 
 + PRINT ''; 
 + PRINT 'Error: You need to be SysAdmin and use SQL Server 2005 or later in order to use this script.'; 
 + PRINT ''; 
 +END 
 +--End of Script
 </code> </code>