So we have a little issue.. well, that is always relative. One of our SQL instances is slow.. and I mean slooooow. It also happens to store our emails and all our archived documents. The primary reason for this is that none of the indexes have been rebuild/reorganise since forever. A wild Fragmented Index appears.. I chose you Adrian Man.. Adrian Man uses awesome l337 SQL skills. It’s SUPER effective. And so, 1 morning later..
/*-*-*-*-*-*-*-*-* ﷽ *-*-*-*-*-*-*-*-*-*-*-*/ SET FMTONLY OFF/*This is for temporary tables in SSRS*/ DECLARE @Database VARCHAR(255), @Table VARCHAR(255), @cmd NVARCHAR(3500),@cmd2 NVARCHAR(3500), @fillfactor INT, @pagemin INT, @fragmin INT, @WaitFor VARCHAR(12),@ActivateWait VARCHAR(1), @JustShowMe VARCHAR(1) SET @WaitFor = '00:00:15' /*hh:mm:ss Average time for a big index is around 2 minutes, and assume around 25% the trans log filled by each rebuild*/ SET @fillfactor = 80 /*Depending or your index growth anywhere between 70-90 should work*/ SET @pagemin = 1000 /*not concerned with small tables, ignore less than 1500 pages index*/ SET @fragmin = 5 /*Ignore fragmentation less than 5%*/ SET @ActivateWait = 0 /*1 will active it*/ SET @JustShowMe = 1 /*0 is off*/ DECLARE @Databases TABLE(id INT IDENTITY(1,1), databasename VARCHAR(250)) INSERT INTO @Databases SELECT name FROM sys.databases db WHERE name NOT IN ('master','msdb','tempdb','model','distribution' ,'MUR Group Views' ,'SAP_test' ,'SAPERION-1' ,'SAP-JHB') AND db.state <> 6 AND db.user_access <> 1 ORDER BY 1 --SELECT * FROM @Databases IF @JustShowMe = 0 BEGIN DECLARE @Outputme TABLE (id INT, type_desc VARCHAR(50), database_id INT, object_id VARCHAR(250),tablename VARCHAR(250),index_id INT,name VARCHAR(250),page_count INT, avg_fragmentation_in_percent DECIMAL(10,3), seconds INT) END DECLARE @i_Count INT, @i_Max INT SET @i_Max = (SELECT MAX(id) FROM @Databases ) SET @i_Count = 1 WHILE @i_Count <= @i_Max BEGIN SET @Database = (SELECT databasename FROM @Databases WHERE id = @i_Count) SET @cmd2 = 'USE ['+@Database+'] DECLARE @IndexBase TABLE(id INT IDENTITY(1,1), type_desc VARCHAR(50), database_id INT, [object_id] INT, [tablename] VARCHAR(250), index_id INT, name VARCHAR(250), page_count INT, avg_fragmentation_in_percent DECIMAL(10,3), seconds INT) INSERT INTO @IndexBase SELECT b.type_desc , a.database_id , b.[object_id],''[''+ isc.TABLE_SCHEMA +''].[''+ t.name+'']'' , a.index_id , b.name , a.page_count , a.avg_fragmentation_in_percent , CASE WHEN a.page_count < 3000 THEN FLOOR((a.page_count/100)*0.3) WHEN a.page_count > 3000 AND a.page_count < 30000 THEN FLOOR((a.page_count/100)*0.4) WHEN a.page_count > 30000 AND a.page_count < 300000 THEN FLOOR((a.page_count/100)*0.5) WHEN a.page_count > 300000 THEN FLOOR((a.page_count/100)*0.6) END Seconds FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N''Production.Product''),NULL, NULL, NULL) AS a JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id LEFT OUTER JOIN sys.tables AS t ON b.[object_id] = t.[object_id] LEFT OUTER JOIN INFORMATION_SCHEMA.TABLES isc ON isc.TABLE_NAME = t.name WHERE page_count > '+CONVERT(VARCHAR(10),@pagemin)+' AND a.avg_fragmentation_in_percent > '+CONVERT(VARCHAR(10),@fragmin)+' ORDER BY b.type_desc ASC, page_count desc IF EXISTS(SELECT * FROM @IndexBase ib WHERE ib.type_desc = ''CLUSTERED'') BEGIN IF ' + @JustShowMe + ' = 1 PRINT 1 ELSE DELETE FROM @IndexBase WHERE type_desc <> ''CLUSTERED'' AND tablename IN (SELECT DISTINCT tablename FROM @IndexBase ib WHERE ib.type_desc = ''CLUSTERED'') END ' SET @cmd = @cmd2 + ' DECLARE @Numbers TABLE(number INT);DECLARE @Index AS INT, @Until INT, @FinalCMD NVARCHAR(3800) SET @Index = 1 DECLARE @ibname VARCHAR(250), @ibtablename VARCHAR(250), @waittime INT, @frags INT SET @Until = (SELECT MAX(id) FROM @IndexBase) WHILE @Index < @Until + 1 BEGIN SET @frags = (SELECT ib.avg_fragmentation_in_percent FROM @IndexBase ib WHERE ib.name IS NOT NULL AND ib.id = @Index) SET @waittime = (SELECT ib.seconds FROM @IndexBase ib WHERE ib.name IS NOT NULL AND ib.id = @Index) SET @ibname = (SELECT ib.name FROM @IndexBase ib WHERE ib.name IS NOT NULL AND ib.id = @Index) SET @ibtablename = (SELECT ib.tablename FROM @IndexBase ib WHERE ib.name IS NOT NULL AND ib.id = @Index) IF @frags > 25 BEGIN SET @FinalCMD = ''USE ['+@Database+']; ALTER INDEX [''+@ibname+''] ON ''+@ibtablename+'' REBUILD PARTITION = ALL WITH ( FILLFACTOR = '+CONVERT(VARCHAR, @fillfactor)+',PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, ONLINE = OFF, SORT_IN_TEMPDB = ON, DATA_COMPRESSION = NONE )'' END ELSE BEGIN SET @FinalCMD = ''USE ['+@Database+']; ALTER INDEX [''+@ibname+''] ON ''+@ibtablename+'' REORGANIZE WITH ( LOB_COMPACTION = ON )'' END IF @FinalCMD IS NOT NULL BEGIN BEGIN TRY DECLARE @Waitforme VARCHAR(15) SET @Waitforme = CONVERT(VARCHAR,DATEADD(SECOND,@waittime,''00:00:00''),108) EXEC sp_executesql @FinalCMD IF '+@ActivateWait+' > 0 BEGIN WAITFOR DELAY @Waitforme END END TRY BEGIN CATCH BEGIN TRY WAITFOR DELAY ''00:00:05'' EXEC sp_executesql @FinalCMD END TRY BEGIN CATCH PRINT ''Error: '' +@FinalCMD END CATCH; END CATCH; END SET @Index = @Index + 1 END ' IF @JustShowMe = 1 BEGIN SET @cmd = @cmd2 + ' SELECT * FROM @IndexBase' INSERT INTO @Outputme EXEC sp_executesql @cmd --PRINT @cmd END IF @JustShowMe = 0 BEGIN EXEC sp_executesql @cmd --PRINT @cmd IF @ActivateWait > 0 BEGIN WAITFOR DELAY @WaitFor END END SET @i_Count = @i_Count + 1 END IF @JustShowMe = 1 BEGIN SELECT om.id, om.type_desc, sdb.name, om.database_id, om.object_id, om.tablename, om.index_id, om.name, om.page_count, om.avg_fragmentation_in_percent, om.seconds FROM @Outputme om LEFT OUTER JOIN MASTER.dbo.sysdatabases sdb ON sdb.dbid = om.database_id END
There you go kids. Note the Exec has been commented out 5 lines from the bottom, see print results before continuing. This little script will look through all your DBs on a server, exclude those you don’t want, then look at all the tables and figure out which needs their indexes rebuild. There are some parameters like @fillfactor,@pagemin and @fragmin which you can specify to your liking.
Edit: I favoured Rebuilding until about 30 minutes ago, but we don’t have Enterprise so we cannot rebuild online, so in comes Reorganise to save the day.
The script rebuilds over 25% and reorganises on fragmentation less than that. In 30 minutes I have decided that online is better and allows for greater agility as to when and how we access our data. So instead of rebuilding every night and killing our sessions in Australia I would rather reorganise when it’s lunch time in Hong Kong, then our tables are still accessible but load is low enough to run a reorganise without anyone interrupting my beauty sleep.
This script only rebuilds indexes, in my opinion reorganizing creates too much transaction log activity to justify using it above a rebuild, as the rebuild gives you so much more.
The script will ignore remaining indexes for a table if a CLUSTERED index needs some rebuilding, but All indexes will show on @JustShowMe = 1
After each rebuild/reorganise the script waits for some time, the calculation is done based on seconds per 1000 pages and is valid on our production environment, of course your mileage might vary. I used this:
Mind you, I was too lazy to factor in fragmentation level, ballpark is good enough for estimates.
There is a @ActivateWait switch, make it 0 to ignore the waiting.. you would want this on massive indexes, but once it runs nicely you can consider using the wait function.
This gives me oodles more control than compared to Maintenance Plans.
Also I don’t want to rebuild/reorganise small tables as they are fragmented again by the time the script finishes, that’s why I have the @pagemin to define the size I would like to defrag.
Hope someone benefits from this..
Edit: Added the @JustShowMe switch, making it 1 will just show you the defragmented indexes based on you parameters.
Increase the fillfactor from 98 to 80, I was filling them up too quickly.
Also added some more logic to the script, just check the switches when you run it
I am working on a PowerShell script to run this for all servers and all instances on the network.. and here it is
One script to rule them all, One script to find them, One script to bring them all and in the index bind them