No Money No Honey

WSUS Server 安裝SQL Server Management Studio Express
可上微軟官網抓取
SSMS-Setup-CHT_2014.exe

建立自訂索引 新增查詢

-- Create custom index in tbLocalizedPropertyForRevision
USE [SUSDB]

CREATE NONCLUSTERED INDEX [nclLocalizedPropertyID] ON [dbo].[tbLocalizedPropertyForRevision]
(
      [LocalizedPropertyID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

-- Create custom index in tbRevisionSupersedesUpdate
CREATE NONCLUSTERED INDEX [nclSupercededUpdateID] ON [dbo].[tbRevisionSupersedesUpdate]
(
      [SupersededUpdateID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

新增查詢執行索引重建 T-SQL
USE SUSDB;
GO
SET NOCOUNT ON;
 
-- Rebuild or reorganize indexes based on their fragmentation levels
DECLARE @work_to_do TABLE (
     objectid int
     , indexid int
     , pagedensity float
     , fragmentation float
     , numrows int
)
 
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @schemaname nvarchar(130); 
DECLARE @objectname nvarchar(130); 
DECLARE @indexname nvarchar(130); 
DECLARE @numrows int
DECLARE @density float;
DECLARE @fragmentation float;
DECLARE @command nvarchar(4000); 
DECLARE @fillfactorset bit
DECLARE @numpages int
 
-- Select indexes that need to be defragmented based on the following
-- * Page density is low
-- * External fragmentation is high in relation to index size
PRINT 'Estimating fragmentation: Begin. ' + convert(nvarchar, getdate(), 121) 
INSERT @work_to_do
SELECT
     f.object_id
     , index_id
     , avg_page_space_used_in_percent
     , avg_fragmentation_in_percent
     , record_count
FROM 
     sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'SAMPLED') AS f
WHERE
     (f.avg_page_space_used_in_percent < 85.0 and f.avg_page_space_used_in_percent/100.0 * page_count < page_count - 1)
     or (f.page_count > 50 and f.avg_fragmentation_in_percent > 15.0)
     or (f.page_count > 10 and f.avg_fragmentation_in_percent > 80.0)
 
PRINT 'Number of indexes to rebuild: ' + cast(@@ROWCOUNT as nvarchar(20))
 
PRINT 'Estimating fragmentation: End. ' + convert(nvarchar, getdate(), 121)
 
SELECT @numpages = sum(ps.used_page_count)
FROM
     @work_to_do AS fi
     INNER JOIN sys.indexes AS i ON fi.objectid = i.object_id and fi.indexid = i.index_id
     INNER JOIN sys.dm_db_partition_stats AS ps on i.object_id = ps.object_id and i.index_id = ps.index_id
 
-- Declare the cursor for the list of indexes to be processed.
DECLARE curIndexes CURSOR FOR SELECT * FROM @work_to_do
 
-- Open the cursor.
OPEN curIndexes
 
-- Loop through the indexes
WHILE (1=1)
BEGIN
     FETCH NEXT FROM curIndexes
     INTO @objectid, @indexid, @density, @fragmentation, @numrows;
     IF @@FETCH_STATUS < 0 BREAK;
 
     SELECT 
         @objectname = QUOTENAME(o.name)
         , @schemaname = QUOTENAME(s.name)
     FROM 
         sys.objects AS o
         INNER JOIN sys.schemas as s ON s.schema_id = o.schema_id
     WHERE 
         o.object_id = @objectid;
 
     SELECT 
         @indexname = QUOTENAME(name)
         , @fillfactorset = CASE fill_factor WHEN 0 THEN 0 ELSE 1 END
     FROM 
         sys.indexes
     WHERE
         object_id = @objectid AND index_id = @indexid;
 
     IF ((@density BETWEEN 75.0 AND 85.0) AND @fillfactorset = 1) OR (@fragmentation < 30.0)
         SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
     ELSE IF @numrows >= 5000 AND @fillfactorset = 0
         SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD WITH (FILLFACTOR = 90)';
     ELSE
         SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
     PRINT convert(nvarchar, getdate(), 121) + N' Executing: ' + @command;
     EXEC (@command);
     PRINT convert(nvarchar, getdate(), 121) + N' Done.';
END
 
-- Close and deallocate the cursor.
CLOSE curIndexes;
DEALLOCATE curIndexes;
 
 
IF EXISTS (SELECT * FROM @work_to_do)
BEGIN
     PRINT 'Estimated number of pages in fragmented indexes: ' + cast(@numpages as nvarchar(20))
     SELECT @numpages = @numpages - sum(ps.used_page_count)
     FROM
         @work_to_do AS fi
         INNER JOIN sys.indexes AS i ON fi.objectid = i.object_id and fi.indexid = i.index_id
         INNER JOIN sys.dm_db_partition_stats AS ps on i.object_id = ps.object_id and i.index_id = ps.index_id
 
     PRINT 'Estimated number of pages freed: ' + cast(@numpages as nvarchar(20))
END
GO
 
 
--Update all statistics
PRINT 'Updating all statistics.' + convert(nvarchar, getdate(), 121) 
EXEC sp_updatestats
PRINT 'Done updating statistics.' + convert(nvarchar, getdate(), 121) 
GO