looking for some scripts for index maintenance

espanolanthony
espanolanthony used Ask the Experts™
on
Hello friends, just looking for some online rebuild/reorganise in  the form of Stored Procedures for one or more databases.what i was looking for is the script should
-- Reorganizes indexes with fragmentation between 10 and 30%
-- Rebuilds indexes with more than 30% fragmentation

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Database Consultant
Top Expert 2009
Commented:
Hello espanolanthony,



Regards,

Aneesh
SET NOCOUNT ON

-- Declare variables
DECLARE @database_name NVARCHAR(128)
DECLARE @schema_name NVARCHAR(128)
DECLARE @table_or_view_name NVARCHAR(128)
DECLARE @index_name NVARCHAR(128)
DECLARE @avg_fragmentation_in_percent FLOAT
DECLARE @start_time CHAR(23)
DECLARE @finish_time CHAR(23)
DECLARE @command NVARCHAR(4000)
DECLARE @table_variable TABLE
                        ([database_name] NVARCHAR(128)
                        ,[schema_name] NVARCHAR(128)
                        ,[table_or_view_name] NVARCHAR(128)
                        ,[index_name] NVARCHAR(128)
                        ,[avg_fragmentation_in_percent] FLOAT
                        )

-- Log time stamp for the beginning of the script
PRINT 'START: ' + CONVERT(CHAR(23), CURRENT_TIMESTAMP, 126)

INSERT INTO
    @table_variable
    SELECT
        DB_NAME(DDIPS.[database_id]) AS [database_name]
        ,S.[name] AS [schema_name]
        ,O.[name] AS [table_or_view_name]
        ,I.[name] AS [index_name]
        ,DDIPS.[avg_fragmentation_in_percent]
    FROM
        sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') DDIPS
                        INNER JOIN sys.sysdatabases SD 
                                ON DDIPS.[database_id] = SD.[dbid]
                        INNER JOIN sys.objects O 
                                ON DDIPS.[object_id] = O.[object_id] 
                        INNER JOIN sys.indexes I 
                                ON DDIPS.[index_id] = I.[index_id] 
                                        AND I.[object_id] = O.[object_id] 
                        INNER JOIN sys.schemas S 
                                ON S.[schema_id] = O.[schema_id] 
                        INNER JOIN sys.partitions P 
                                ON DDIPS.[object_id] = P.[object_id] 
                                        AND I.[index_id] = P.[index_id]
        WHERE
                DDIPS.[page_count] > 8 -- Ignore tables < 64K (1 extent)
                AND DDIPS.[avg_fragmentation_in_percent] > 5 -- Ignore indexes that have < = 5% fragmentation
                AND DDIPS.[index_type_desc] IN ('CLUSTERED INDEX','NONCLUSTERED INDEX') -- Only get clustered and nonclustered indexes
                AND I.[is_hypothetical] = 0 -- Only real indexes
                AND O.[type_desc] = 'USER_TABLE' -- Restrict to user tables

SELECT TOP 1 @database_name=[database_name],@schema_name=[schema_name],@table_or_view_name=[table_or_view_name],@index_name=[index_name],@avg_fragmentation_in_percent=[avg_fragmentation_in_percent] FROM @table_variable
WHILE( @@rowcount <> 0 )
    BEGIN
                -- Grab time stamp for the beginning of this index
                SET @start_time=CONVERT(CHAR(23), CURRENT_TIMESTAMP, 126)
                IF @avg_fragmentation_in_percent <= 30 -- REORGANIZE
                        SET @command='ALTER INDEX [' + @index_name + '] ON [' + @database_name + '].[' + @schema_name + '].[' + @table_or_view_name + '] REORGANIZE;'
                ELSE -- REBUILD
                        SET @command='ALTER INDEX [' + @index_name + '] ON [' + @database_name + '].[' + @schema_name + '].[' + @table_or_view_name + '] REBUILD WITH (SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON, ONLINE = ON, MAXDOP = 0);'
                EXECUTE (@command);
                -- Grab time stamp for the ending of this index
                SET @finish_time=CONVERT(CHAR(23), CURRENT_TIMESTAMP, 126)
                PRINT @start_time + ' | ' + @finish_time + ' | ' + CAST(@avg_fragmentation_in_percent AS CHAR(7)) + ' | ' + @command 

                DELETE FROM @table_variable WHERE [database_name]=@database_name AND [schema_name]=@schema_name AND [table_or_view_name]=@table_or_view_name AND [index_name]=@index_name AND [avg_fragmentation_in_percent]=@avg_fragmentation_in_percent
                SELECT TOP 1 @database_name=[database_name],@schema_name=[schema_name],@table_or_view_name=[table_or_view_name],@index_name=[index_name],@avg_fragmentation_in_percent=[avg_fragmentation_in_percent] FROM @table_variable
    END

-- Log time stamp for the ending of the script
PRINT 'STOP: ' + CONVERT(CHAR(23), CURRENT_TIMESTAMP, 126)

Open in new window

Nathan RileyFounder

Commented:
Here is the one I use:


SET NOCOUNT ON
	DECLARE @tablename VARCHAR (128)
	DECLARE @execstr   VARCHAR (255)
	DECLARE @objectid  INT
	DECLARE @indexid   INT
	DECLARE @frag      DECIMAL
	DECLARE @maxfrag   DECIMAL

	-- Decide on the maximum fragmentation to allow
	SELECT @maxfrag = 10.0

	-- Declare cursor
	DECLARE tables CURSOR FOR
	   SELECT TABLE_NAME
	   FROM INFORMATION_SCHEMA.TABLES
	   WHERE TABLE_TYPE = 'BASE TABLE'

	-- Create the table
	CREATE TABLE #fraglist (
	   ObjectName CHAR (255),
	   ObjectId INT,
	   IndexName CHAR (255),
	   IndexId INT,
	   Lvl INT,
	   CountPages INT,
	   CountRows INT,
	   MinRecSize INT,
	   MaxRecSize INT,
	   AvgRecSize INT,
	   ForRecCount INT,
	   Extents INT,
	   ExtentSwitches INT,
	   AvgFreeBytes INT,
	   AvgPageDensity INT,
	   ScanDensity DECIMAL,
	   BestCount INT,
	   ActualCount INT,
	   LogicalFrag DECIMAL,
	   ExtentFrag DECIMAL)

	-- Open the cursor
	OPEN tables

	-- Loop through all the tables in the database
	FETCH NEXT
	   FROM tables
	   INTO @tablename

	WHILE @@FETCH_STATUS = 0
	BEGIN
	-- Do the showcontig of all indexes of the table
	   INSERT INTO #fraglist 
	   EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''') 
		  WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
	   FETCH NEXT
		  FROM tables
		  INTO @tablename
	END

	-- Close and deallocate the cursor
	CLOSE tables
	DEALLOCATE tables

	-- Declare cursor for list of indexes to be defragged
	DECLARE indexes CURSOR FOR
	   SELECT ObjectName, ObjectId, IndexId, LogicalFrag
	   FROM #fraglist
	   WHERE LogicalFrag >= @maxfrag
		  AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0

	-- Open the cursor
	OPEN indexes

	-- loop through the indexes
	FETCH NEXT
	   FROM indexes
	   INTO @tablename, @objectid, @indexid, @frag

	WHILE @@FETCH_STATUS = 0
	BEGIN
	   PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ',
		  ' + RTRIM(@indexid) + ') - fragmentation currently '
		   + RTRIM(CONVERT(varchar(15),@frag)) + '%'
	   SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ',
		   ' + RTRIM(@indexid) + ')'
	   EXEC (@execstr)

	   FETCH NEXT
		  FROM indexes
		  INTO @tablename, @objectid, @indexid, @frag
	END

	-- Close and deallocate the cursor
	CLOSE indexes
	DEALLOCATE indexes

	-- Delete the temporary table
	DROP TABLE #fraglist
	GO

Open in new window

Author

Commented:
need a stored procedure so that i can create a job and run at midnight. Please let me know if you have something like that. and that script should select certain databases not all.

Thanks again.
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Nathan RileyFounder
Commented:
Well it's easy just create a new SP with that code and name it whatever you wish, or you just execute it in a "Execute SQL Task" within SSIS and drop that exact code in there.  As far as selecting a DB maybe aneeshattingal can help you with that as mine that I use doesn't have any spot to specify the DB name that I see.

Author

Commented:
can you help me out aneesh please
AneeshDatabase Consultant
Top Expert 2009

Commented:
You just need to put a CREATE Procedure statement at the beginning



Aneesh
CREATE PROCEDURE dbo.sp_reindex 
AS
SET NOCOUNT ON
-- Declare variables
DECLARE @database_name NVARCHAR(128)
DECLARE @schema_name NVARCHAR(128)
DECLARE @table_or_view_name NVARCHAR(128)
DECLARE @index_name NVARCHAR(128)
DECLARE @avg_fragmentation_in_percent FLOAT
DECLARE @start_time CHAR(23)
DECLARE @finish_time CHAR(23)
DECLARE @command NVARCHAR(4000)
DECLARE @table_variable TABLE
                        ([database_name] NVARCHAR(128)
                        ,[schema_name] NVARCHAR(128)
                        ,[table_or_view_name] NVARCHAR(128)
                        ,[index_name] NVARCHAR(128)
                        ,[avg_fragmentation_in_percent] FLOAT
                        )
-- Log time stamp for the beginning of the script
PRINT 'START: ' + CONVERT(CHAR(23), CURRENT_TIMESTAMP, 126)
INSERT INTO
    @table_variable
    SELECT
        DB_NAME(DDIPS.[database_id]) AS [database_name]
        ,S.[name] AS [schema_name]
        ,O.[name] AS [table_or_view_name]
        ,I.[name] AS [index_name]
        ,DDIPS.[avg_fragmentation_in_percent]
    FROM
        sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') DDIPS
                        INNER JOIN sys.sysdatabases SD 
                                ON DDIPS.[database_id] = SD.[dbid]
                        INNER JOIN sys.objects O 
                                ON DDIPS.[object_id] = O.[object_id] 
                        INNER JOIN sys.indexes I 
                                ON DDIPS.[index_id] = I.[index_id] 
                                        AND I.[object_id] = O.[object_id] 
                        INNER JOIN sys.schemas S 
                                ON S.[schema_id] = O.[schema_id] 
                        INNER JOIN sys.partitions P 
                                ON DDIPS.[object_id] = P.[object_id] 
                                        AND I.[index_id] = P.[index_id]
        WHERE
                DDIPS.[page_count] > 8 -- Ignore tables < 64K (1 extent)
                AND DDIPS.[avg_fragmentation_in_percent] > 5 -- Ignore indexes that have < = 5% fragmentation
                AND DDIPS.[index_type_desc] IN ('CLUSTERED INDEX','NONCLUSTERED INDEX') -- Only get clustered and nonclustered indexes
                AND I.[is_hypothetical] = 0 -- Only real indexes
                AND O.[type_desc] = 'USER_TABLE' -- Restrict to user tables
SELECT TOP 1 @database_name=[database_name],@schema_name=[schema_name],@table_or_view_name=[table_or_view_name],@index_name=[index_name],@avg_fragmentation_in_percent=[avg_fragmentation_in_percent] FROM @table_variable
WHILE( @@rowcount <> 0 )
    BEGIN
                -- Grab time stamp for the beginning of this index
                SET @start_time=CONVERT(CHAR(23), CURRENT_TIMESTAMP, 126)
                IF @avg_fragmentation_in_percent <= 30 -- REORGANIZE
                        SET @command='ALTER INDEX [' + @index_name + '] ON [' + @database_name + '].[' + @schema_name + '].[' + @table_or_view_name + '] REORGANIZE;'
                ELSE -- REBUILD
                        SET @command='ALTER INDEX [' + @index_name + '] ON [' + @database_name + '].[' + @schema_name + '].[' + @table_or_view_name + '] REBUILD WITH (SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON, ONLINE = ON, MAXDOP = 0);'
                EXECUTE (@command);
                -- Grab time stamp for the ending of this index
                SET @finish_time=CONVERT(CHAR(23), CURRENT_TIMESTAMP, 126)
                PRINT @start_time + ' | ' + @finish_time + ' | ' + CAST(@avg_fragmentation_in_percent AS CHAR(7)) + ' | ' + @command 
                DELETE FROM @table_variable WHERE [database_name]=@database_name AND [schema_name]=@schema_name AND [table_or_view_name]=@table_or_view_name AND [index_name]=@index_name AND [avg_fragmentation_in_percent]=@avg_fragmentation_in_percent
                SELECT TOP 1 @database_name=[database_name],@schema_name=[schema_name],@table_or_view_name=[table_or_view_name],@index_name=[index_name],@avg_fragmentation_in_percent=[avg_fragmentation_in_percent] FROM @table_variable
    END
-- Log time stamp for the ending of the script
PRINT 'STOP: ' + CONVERT(CHAR(23), CURRENT_TIMESTAMP, 126)
GO



You can now schedule a job at midnight using the SQL Server agent 

Open in new window

Author

Commented:
so where is the option to select particular databases. As far as i think this will rebuild index for all the databases in the instance right?
AneeshDatabase Consultant
Top Expert 2009

Commented:
you have two options either create the above sp on all the user databases or in the master database and set it as system sp
While creating the job , you will be able to choose the Database where it should run the script

Author

Commented:
yes that's right but what if i need to run this script for more than one Databases. and if i set

Author

Commented:
sorry missed
  if i set ONLINE=OFF option that would be ok too right?
AneeshDatabase Consultant
Top Expert 2009

Commented:
>  if i set ONLINE=OFF option that would be ok too right?
yup, then that will be an offline reindexing
> but what if i need to run this script for more than one Databases
you can scedule mutiple jobs / job steps , or modify the sp to accept many database names

Author

Commented:
i know that sir, but i do not know how to modify the SP to select certain databases for rebuilding indexes for one or more databases. I would appreciate if you could let me know please...............

Thanks a lot in advance
AneeshDatabase Consultant
Top Expert 2009

Commented:
oops sorry, that will reindex all the objects on all the user databases

Author

Commented:
so any thing that will choose certain databases?

Author

Commented:
thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial