{"id":1789,"date":"2022-08-05T14:36:08","date_gmt":"2022-08-05T06:36:08","guid":{"rendered":"http:\/\/blog.infinity.idv.tw\/?p=1789"},"modified":"2022-08-05T14:36:26","modified_gmt":"2022-08-05T06:36:26","slug":"wsus%e8%b3%87%e6%96%99%e5%ba%ab%e7%b4%a2%e5%bc%95%e9%87%8d%e5%bb%ba-%e6%94%b9%e5%96%84%e6%95%88%e8%83%bd%e9%9a%a8%e8%91%97%e6%99%82%e9%96%93%e8%80%8c%e9%99%8d%e4%bd%8e%e8%88%87%e6%94%b9%e5%96%84","status":"publish","type":"post","link":"http:\/\/blog.infinity.idv.tw\/?p=1789","title":{"rendered":"WSUS\u8cc7\u6599\u5eab\u7d22\u5f15\u91cd\u5efa-\u6539\u5584\u6548\u80fd\u96a8\u8457\u6642\u9593\u800c\u964d\u4f4e\u8207\u6539\u5584\u51fa\u73fe\u91cd\u8a2d\u4f3a\u670d\u5668\u7bc0\u9ede\u7684\u72c0\u6cc1"},"content":{"rendered":"

WSUS Server \u5b89\u88ddSQL Server Management Studio Express
\u53ef\u4e0a\u5fae\u8edf\u5b98\u7db2\u6293\u53d6
SSMS-Setup-CHT_2014.exe<\/font><\/p>\n

<\/p>\n

\u958b\u555fSQL Server Management Studio Express \u9023\u7dda\u4f4d\u7f6e\u8f38\u5165(WSUS\u4f7f\u7528WID\u8cc7\u6599\u5eab\u5b89\u88dd\u6a21\u5f0f)
\\\\.\\pipe\\MICROSOFT##WID\\tsql\\query
<\/a><\/p>\n

<\/font> <\/p>\n

\u5efa\u7acb\u81ea\u8a02\u7d22\u5f15 \u65b0\u589e\u67e5\u8a62<\/font><\/font><\/font><\/p>\n

— Create custom index in tbLocalizedPropertyForRevision
USE [SUSDB]<\/font><\/font> <\/font><\/font><\/p>\n

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]<\/font><\/p>\n

<\/font> <\/p>\n

— 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]<\/font><\/p>\n


<\/font><\/font><\/font><\/p>\n

<\/font><\/p>\n

<\/font> <\/p>\n

<\/font><\/p>\n

\u65b0\u589e\u67e5\u8a62\u57f7\u884c\u7d22\u5f15\u91cd\u5efa 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<\/font><\/p>\n

<\/font> <\/p>\n

 <\/font><\/p>\n","protected":false},"excerpt":{"rendered":"

WSUS Server \u5b89\u88ddSQL Server Management Studio Express \u53ef\u4e0a\u5fae\u8edf\u5b98\u7db2\u6293\u53d6 SSMS-Setup-CHT_2014.exe<\/p>\n<\/p>\n

\u958b\u555fSQL Server Management Studio Express \u9023\u7dda\u4f4d\u7f6e\u8f38\u5165(WSUS\u4f7f\u7528WID\u8cc7\u6599\u5eab\u5b89\u88dd\u6a21\u5f0f) \\\\.\\pipe\\MICROSOFT##WID\\tsql\\query <\/p>\n<\/p>\n

\u5efa\u7acb\u81ea\u8a02\u7d22\u5f15 \u65b0\u589e\u67e5\u8a62<\/p>\n

— Create custom […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"","sticky":false,"template":"","format":"standard","meta":[],"categories":[32],"tags":[698],"_links":{"self":[{"href":"http:\/\/blog.infinity.idv.tw\/index.php?rest_route=\/wp\/v2\/posts\/1789"}],"collection":[{"href":"http:\/\/blog.infinity.idv.tw\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/blog.infinity.idv.tw\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/blog.infinity.idv.tw\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/blog.infinity.idv.tw\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1789"}],"version-history":[{"count":2,"href":"http:\/\/blog.infinity.idv.tw\/index.php?rest_route=\/wp\/v2\/posts\/1789\/revisions"}],"predecessor-version":[{"id":1791,"href":"http:\/\/blog.infinity.idv.tw\/index.php?rest_route=\/wp\/v2\/posts\/1789\/revisions\/1791"}],"wp:attachment":[{"href":"http:\/\/blog.infinity.idv.tw\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1789"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/blog.infinity.idv.tw\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1789"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/blog.infinity.idv.tw\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1789"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}