Make those SQL Table Indexes pretty

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:

SecondsFactor100.3450.41500.510800.6Pages3000300003000001500000

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..

Adrian

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