SQL to Rebuild Indexes, Update Statistics, and Shrink Databases
Here is SQL to Rebuild Indexes, Update Statistics, and Shrink Databases on SQL Server. This code works on SQL Server 2008 – 2016. It should help keeping your databases running at their peak performance. Just alter the settings as appropriate and away you go! Older legacy SQL2000 kept in for reference.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 |
--SQL to Rebuild Indexes, Update Statistics, and Shrink Databases on SQL Server --Code from http://cc.davelozinski.com -- This SQL Code will do the following: -- 1) get the names of all the databases on the current SQL server and loop through them -- 2) shrinking each database (if enabled. See <a href="https://msdn.microsoft.com/en-au/library/ms190488.aspx" target="_blank">DBCC SHRINKDATABASE</a>) -- 3) selecting all the tables in the current database -- 4) rebuilding all the indexes on each table (See <a href="https://msdn.microsoft.com/en-AU/library/ms188388.aspx" target="_blank">ALTER INDEX</a>) -- 5) rebuilding all the statistics on each table (See <a href="https://msdn.microsoft.com/en-us/library/ms187348.aspx" target="_blank">UPDATE STATISTICS</a>) -- Adjust the values for the @indexFillFactor, @freeSpaceAfterShrink, @shrinkDatabases as appropriate for your needs. DECLARE @indexFillFactor INT DECLARE @freeSpaceAfterShrink INT DECLARE @shrinkDatabases BIT SET @indexFillFactor = 95 -- the percentage fill factor when rebuilding indexes SET @freeSpaceAfterShrink = 10 -- how much free space to leave after shrinking the database SET @shrinkDatabases = 1 -- 0 = false; 1 = true. ------- nothing below here needs to be configured except maybe the databases to exclude --------------------------------------- DECLARE @Database VARCHAR(255) DECLARE @TableName VARCHAR(512) DECLARE @IndexName VARCHAR(512) DECLARE @IsColumnStoreIndex BIT DECLARE @cmd NVARCHAR(1024) DECLARE DatabaseCursor CURSOR FOR SELECT name FROM [master].[dbo].[sysdatabases] WHERE name NOT IN ('master','msdb','tempdb','model','distribution') ORDER BY 1 OPEN DatabaseCursor FETCH NEXT FROM DatabaseCursor INTO @Database WHILE @@FETCH_STATUS = 0 BEGIN -- shrink the database if you want -------------------------- IF (@shrinkDatabases = 1) BEGIN print 'Shrinking database: ' + @Database DBCC SHRINKDATABASE (@Database, @freeSpaceAfterShrink) END --create the cursor for looping over the indexes by each table SET @cmd = 'DECLARE IndexCursor CURSOR FOR ' + 'SELECT ''['' + table_catalog + ''].['' + table_schema + ''].['' + table_name + '']'' as tableName ' + ',i.name as IndexName ' + ',CASE WHEN i.type_desc like ''%COLUMNSTORE%'' THEN 1 ' + ' ELSE 0 ' + ' END as IsColumnStoreIndex ' + 'FROM [' + @Database + '].[sys].[indexes] i ' + 'INNER JOIN [' + @Database + '].[sys].[all_objects] o on i.object_id = o.object_id ' + 'INNER JOIN [' + @Database + '].[INFORMATION_SCHEMA].[TABLES] t on o.name = t.TABLE_NAME ' + ' AND t.table_type = ''BASE TABLE'' ' + 'INNER JOIN [' + @Database + '].[sys].[schemas] AS s ON o.[schema_id] = s.[schema_id] ' + ' AND SCHEMA_NAME(o.[schema_id]) = t.TABLE_SCHEMA ' + 'ORDER BY o.name' --print (@cmd) EXECUTE sp_executesql @cmd OPEN IndexCursor FETCH NEXT FROM IndexCursor INTO @TableName, @IndexName, @IsColumnStoreIndex WHILE @@FETCH_STATUS = 0 BEGIN --Update indexes -------------------------- -- IF (@@MICROSOFTVERSION / POWER(2, 24) >= 9) --only uncomment these lines if this code used on an SQL2000 database -- BEGIN -- -- SQL 2005 or higher command print 'Started rebuilding Index: [' + @IndexName + '] on table: ' + @TableName + ' at ' + CAST(GetDate() as varchar) IF (@IsColumnStoreIndex = 0) --non columnstore index. Can use FILLFACTOR SET @cmd = 'ALTER INDEX [' + @IndexName + '] ON ' + @TableName + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@indexFillFactor) + ')' ELSE --ColumnStoreIndex, cannot use FILLFACTOR SET @cmd = 'ALTER INDEX [' + @IndexName + '] ON ' + @TableName + ' REBUILD' --print @cmd EXEC (@cmd) print 'Finished Rebuilding Index: [' + @IndexName + '] on table: ' + @TableName + ' at ' + CAST(GetDate() as varchar) print ' ' -- END -- ELSE -- BEGIN -- -- SQL 2000 command kept just in case -- DBCC DBREINDEX(@Table,' ',@indexFillFactor) -- END FETCH NEXT FROM IndexCursor INTO @TableName, @IndexName, @IsColumnStoreIndex END CLOSE IndexCursor DEALLOCATE IndexCursor -- Update statistics -------------------------- print 'Start updating statistics on DB: ' + @Database + ' at ' + CAST(GetDate() as varchar) SET @cmd = 'USE [' + @Database +']' + CHAR(13) + 'EXEC sp_updatestats' + CHAR(13) --print @cmd EXEC (@cmd) print 'Finished updating statistics on DB: ' + @Database + ' at ' + CAST(GetDate() as varchar) print ' ' FETCH NEXT FROM DatabaseCursor INTO @Database END CLOSE DatabaseCursor DEALLOCATE DatabaseCursor |
