| 14 |
|
|
matrix = {'defragBlob' : ['-- Declare variables\n', 'SET NOCOUNT ON\n', 'DECLARE @tablename VARCHAR(128)\n', 'DECLARE @execstr VARCHAR(255)\n', 'DECLARE @objectid int\n', 'DECLARE @indexid int\n', 'DECLARE @frag decimal\n', 'DECLARE @maxreorg decimal\n', 'DECLARE @maxrebuild decimal\n', 'DECLARE @IdxName varchar(128)\n', 'DECLARE @ViewOnly bit\n', 'DECLARE @ReorgOptions varchar(255)\n', 'DECLARE @RebuildOptions varchar(255)\n', '\n', '-- Set to 1 to view proposed actions, set to 0 to Execute proposed actions:\n', 'SET @ViewOnly=0\n', '\n', '-- Decide on the maximum fragmentation to allow for a reorganize.\n', '-- AVAILABLE OPTIONS: http://technet.microsoft.com/en-us/library/ms188388(SQL.90).aspx\n', 'SET @maxreorg = 20.0\n', "SET @ReorgOptions = 'LOB_COMPACTION=ON'\n", '-- Decide on the maximum fragmentation to allow for a rebuild.\n', 'SET @maxrebuild = 30.0\n', '\n', '\n', '\n', '--NOTE: SQL Server will retain existing options if they are not specified.\n', '--If you are running SQL Server Enterprise or Developer then you may inlude the ONLINE = ON option above.\n', "SET @RebuildOptions = 'SORT_IN_TEMPDB=OFF, STATISTICS_NORECOMPUTE=OFF, ALLOW_ROW_LOCKS=ON, ALLOW_PAGE_LOCKS=ON'\n", '\n', '\n', '-- Declare a cursor.\n', 'DECLARE tables CURSOR FOR\n', "SELECT '[' + CAST(TABLE_SCHEMA AS VARCHAR(100)) + ']'\n", "+'.'+ '[' + CAST(TABLE_NAME AS VARCHAR(100)) + ']'\n", 'AS Table_Name\n', 'FROM INFORMATION_SCHEMA.TABLES\n', "WHERE TABLE_TYPE = 'BASE TABLE'\n", '--You may use the line below to specify a table.\n', "--AND Table_Name = 'Results'\n", '\n', '\n', '-- Create the temporary table.\n', "if exists (select name from tempdb.dbo.sysobjects where name like '#fraglist%')\n", 'drop table #fraglist\n', '\n', 'CREATE TABLE #fraglist (\n', 'ObjectName CHAR(255),\n', 'ObjectId int,\n', 'IndexId int,\n', 'LogicalFrag nvarchar(255),\n', 'IndexName CHAR(255)\n', ')\n', '\n', '-- Open the cursor.\n', 'OPEN tables\n', '\n', '-- Loop through all the tables in the database.\n', 'FETCH NEXT\n', 'FROM tables\n', 'INTO @tablename\n', '\n', 'WHILE @@FETCH_STATUS = 0\n', 'BEGIN\n', '-- Display the dmv info of all indexes of the table\n', 'INSERT INTO #fraglist\n', 'SELECT @tablename, cast(o.Object_Id as numeric) as ObjectId, cast(ips.Index_Id as numeric) as IndexId,\n', 'avg_fragmentation_in_percent as LogicalFrag, i.name as IndexName\n', "FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('' + @tablename + ''), NULL,NULL,NULL)ips\n", 'JOIN sys.objects o on o.object_id = ips.object_id\n', 'JOIN sys.indexes i on ips.index_id = i.index_id and ips.object_id = i.object_id\n', 'ORDER BY ips.index_id\n', '\n', '\n', '\n', '\n', '\n', 'FETCH NEXT\n', 'FROM tables\n', 'INTO @tablename\n', 'END\n', '\n', '-- Close and deallocate the cursor.\n', 'CLOSE tables\n', 'DEALLOCATE tables\n', '\n', '\n', '-- Declare the cursor for the list of indexes to be defragged.\n', 'DECLARE indexes CURSOR FOR\n', 'SELECT ObjectName, ObjectId, IndexId, LogicalFrag, IndexName\n', 'FROM #fraglist\n', 'WHERE ((LogicalFrag >= @maxreorg) OR (LogicalFrag >= @maxrebuild))\n', "AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0\n", '\n', '-- Open the cursor.\n', 'OPEN indexes\n', '\n', '-- Loop through the indexes.\n', 'FETCH NEXT\n', 'FROM indexes\n', 'INTO @tablename, @objectid, @indexid, @frag, @IdxName\n', '\n', 'WHILE @@FETCH_STATUS = 0\n', 'BEGIN\n', 'IF (@frag >= @maxrebuild)\n', 'BEGIN\n', 'IF (@ViewOnly=1)\n', 'BEGIN\n', "PRINT 'Fragmentation at ' + RTRIM(CONVERT(VARCHAR(15),@frag)) + '%' + ' ' + 'WOULD be executing ALTER INDEX ' + '[' + RTRIM(@IdxName) + ']' + ' ON ' + RTRIM(@tablename) + ' REBUILD WITH ( ' + @RebuildOptions + ' )'\n", 'END\n', 'ELSE\n', 'BEGIN\n', "PRINT 'Fragmentation at ' + RTRIM(CONVERT(VARCHAR(15),@frag)) + '%' + ' ' + 'Now executing ALTER INDEX ' + '[' + RTRIM(@IdxName) + ']' + ' ON ' + RTRIM(@tablename) + ' REBUILD WITH ( ' + @RebuildOptions + ' )'\n", "SELECT @execstr = 'ALTER INDEX ' + '[' + RTRIM(@IdxName) + ']' + ' ON ' + RTRIM(@tablename) + ' REBUILD WITH ( ' + @RebuildOptions + ' )'\n", 'EXEC (@execstr)\n', 'END\n', 'END\n', '-- Determine if fragmentation surpasses the defined threshold for reorganizing:\n', 'ELSE IF (@frag >= @maxreorg)\n', 'BEGIN\n', 'IF (@ViewOnly=1)\n', 'BEGIN\n', "PRINT 'Fragmentation at ' + RTRIM(CONVERT(VARCHAR(15),@frag)) + '%' + ' ' + 'WOULD be executing ALTER INDEX ' + '[' + RTRIM(@IdxName)+ ']' + ' ON ' + RTRIM(@tablename) + ' REORGANIZE WITH ( ' + @ReorgOptions + ' )'\n", 'END\n', 'ELSE\n', 'BEGIN\n', "PRINT 'Fragmentation at ' + RTRIM(CONVERT(VARCHAR(15),@frag)) + '%' + ' ' + 'Now executing ALTER INDEX ' + '[' + RTRIM(@IdxName)+ ']' + ' ON ' + RTRIM(@tablename) + ' REORGANIZE WITH ( ' + @ReorgOptions + ' )'\n", "SELECT @execstr = 'ALTER INDEX ' + '[' + RTRIM(@IdxName) + ']' + ' ON ' + RTRIM(@tablename) + ' REORGANIZE WITH ( ' + @ReorgOptions + ' )'\n", 'EXEC (@execstr)\n', 'END\n', 'END\n', '\n', 'FETCH NEXT\n', 'FROM indexes\n', 'INTO @tablename, @objectid, @indexid, @frag, @IdxName\n', 'END\n', '\n', '-- Close and deallocate the cursor.\n', 'CLOSE indexes\n', 'DEALLOCATE indexes\n', '\n', '-- Delete the temporary table.\n', 'DROP TABLE #fraglist\n', 'GO\n'],
|
