-- Ensure a USE
statement has been executed first.
use
[WSS_Content_GUID]
go
SET NOCOUNT ON
-- adapted from
"Rebuild or reorganize indexes (with configuration)" from MSDN Books
Online
--
(http://msdn.microsoft.com/en-us/library/ms188917.aspx)
-- =======================================================
-- ||
Configuration variables:
-- || - 10 is an
arbitrary decision point at which to
-- || reorganize
indexes.
-- || - 30 is an
arbitrary decision point at which to
-- || switch
from reorganizing, to rebuilding.
-- || - 0 is the
default fill factor. Set this to a
-- || a value
from 1 to 99, if needed.
--
=======================================================
DECLARE
@reorg_frag_thresh float SET @reorg_frag_thresh = 10.0
DECLARE
@rebuild_frag_thresh float SET
@rebuild_frag_thresh = 30.0
DECLARE
@fill_factor tinyint SET @fill_factor = 80
DECLARE
@report_only bit SET @report_only = 1
-- added (DS) :
page_count_thresh is used to check how many pages the current table uses
DECLARE
@page_count_thresh smallint SET @page_count_thresh = 1000
-- Variables
required for processing.
DECLARE
@objectid int
DECLARE
@indexid int
DECLARE
@partitioncount bigint
DECLARE
@schemaname nvarchar(130)
DECLARE
@objectname nvarchar(130)
DECLARE
@indexname nvarchar(130)
DECLARE
@partitionnum bigint
DECLARE
@partitions bigint
DECLARE @frag float
DECLARE
@page_count int
DECLARE
@command nvarchar(4000)
DECLARE
@intentions nvarchar(4000)
DECLARE
@table_var TABLE(
objectid int,
indexid int,
partitionnum int,
frag float,
page_count
int
)
-- Conditionally
select tables and indexes from the
--
sys.dm_db_index_physical_stats function and
-- convert
object and index IDs to names.
INSERT INTO
@table_var
SELECT
[object_id] AS
objectid,
[index_id] AS
indexid,
[partition_number] AS
partitionnum,
[avg_fragmentation_in_percent] AS frag,
[page_count] AS
page_count
FROM
sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
WHERE
[avg_fragmentation_in_percent] > @reorg_frag_thresh
AND
page_count >
@page_count_thresh
AND
index_id >
0
-- Declare the
cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR
SELECT * FROM @table_var
-- Open the
cursor.
OPEN partitions
-- Loop through
the partitions.
WHILE (1=1) BEGIN
FETCH NEXT
FROM partitions
INTO
@objectid, @indexid,
@partitionnum, @frag,
@page_count
IF @@FETCH_STATUS <
0 BREAK
SELECT
@objectname =
QUOTENAME(o.[name]),
@schemaname =
QUOTENAME(s.[name])
FROM
sys.objects AS o WITH (NOLOCK)
JOIN sys.schemas as s WITH (NOLOCK)
ON s.[schema_id] = o.[schema_id]
WHERE
o.[object_id]
= @objectid
SELECT
@indexname =
QUOTENAME([name])
FROM
sys.indexes WITH (NOLOCK)
WHERE
[object_id] =
@objectid AND
[index_id] =
@indexid
SELECT
@partitioncount = count
(*)
FROM
sys.partitions WITH (NOLOCK)
WHERE
[object_id] =
@objectid AND
[index_id] =
@indexid
-- Build the
required statement dynamically based on options and index stats.
SET
@intentions =
@schemaname +
N'.' +
@objectname +
N'.' +
@indexname +
N':' + CHAR(13) + CHAR(10)
SET
@intentions =
REPLACE(SPACE(LEN(@intentions)), ' ', '=') + CHAR(13) + CHAR(10) +
@intentions
SET
@intentions = @intentions +
N'
FRAGMENTATION: ' + CAST(@frag AS nvarchar) + N'%' + CHAR(13) + CHAR(10) +
N' PAGE COUNT:
' +
CAST(@page_count
AS nvarchar) + CHAR(13) + CHAR(10)
IF @frag < @rebuild_frag_thresh BEGIN
SET
@intentions = @intentions +
N'
OPERATION: REORGANIZE' + CHAR(13) + CHAR(10)
SET
@command =
N'ALTER
INDEX ' + @indexname +
N' ON '
+ @schemaname +
N'.' +
@objectname +
N'
REORGANIZE; ' +
N' UPDATE
STATISTICS ' + @schemaname + N'.' + @objectname +
N' '
+ @indexname + ';'
END
IF @frag >= @rebuild_frag_thresh BEGIN
SET
@intentions = @intentions +
N'
OPERATION: REBUILD' + CHAR(13) + CHAR(10)
SET
@command =
N'ALTER
INDEX ' + @indexname +
N' ON '
+ @schemaname +
N'.' + @objectname +
N' REBUILD'
END
IF
@partitioncount > 1 BEGIN
SET
@intentions = @intentions +
N'
PARTITION: ' + CAST(@partitionnum AS nvarchar(10)) + CHAR(13) + CHAR(10)
SET
@command = @command +
N'
PARTITION=' + CAST(@partitionnum AS nvarchar(10))
END
IF @frag >= @rebuild_frag_thresh AND
@fill_factor > 0 AND
@fill_factor < 100 BEGIN
SET
@intentions = @intentions +
N' FILL
FACTOR: ' + CAST(@fill_factor AS nvarchar) + CHAR(13) + CHAR(10)
SET
@command = @command +
N' WITH
(FILLFACTOR = ' + CAST(@fill_factor AS nvarchar) + ')'
END
-- Execute
determined operation, or report intentions
IF
@report_only = 0 BEGIN
SET
@intentions = @intentions + N' EXECUTING: ' + @command
PRINT
@intentions
EXEC (@command)
END ELSE BEGIN
PRINT
@intentions
END
PRINT @command
END
-- Close and
deallocate the cursor.
CLOSE partitions
DEALLOCATE partitions
GO
--===========================================
--[dbo].[AllDocStreams].[AllDocStreams_CI]:
--
FRAGMENTATION: 19.7798%
-- PAGE COUNT:
557169
-- OPERATION:
REORGANIZE
--ALTER INDEX
[AllDocStreams_CI] ON [dbo].[AllDocStreams] REORGANIZE; UPDATE STATISTICS [dbo].[AllDocStreams]
[AllDocStreams_CI];
--=============================================
--[dbo].[AllDocVersions].[AllDocVersions_PK]:
--
FRAGMENTATION: 12.833%
-- PAGE COUNT:
21959
-- OPERATION:
REORGANIZE
--ALTER INDEX
[AllDocVersions_PK] ON [dbo].[AllDocVersions] REORGANIZE; UPDATE STATISTICS [dbo].[AllDocVersions]
[AllDocVersions_PK];
--===================================
--[dbo].[AllLinks].[Links_Forward]:
--
FRAGMENTATION: 26.2066%
-- PAGE COUNT:
48793
-- OPERATION: REORGANIZE
--ALTER INDEX
[Links_Forward] ON [dbo].[AllLinks] REORGANIZE;
UPDATE STATISTICS [dbo].[AllLinks] [Links_Forward];
--====================================
--[dbo].[AllLinks].[Links_Backward]:
--
FRAGMENTATION: 25.3887%
-- PAGE COUNT:
44059
-- OPERATION:
REORGANIZE
--ALTER INDEX
[Links_Backward] ON [dbo].[AllLinks] REORGANIZE; UPDATE STATISTICS [dbo].[AllLinks]
[Links_Backward];
--=======================================
--[dbo].[EventCache].[EventCache_Time]:
--
FRAGMENTATION: 25.687%
-- PAGE COUNT:
1674
-- OPERATION:
REORGANIZE
--ALTER INDEX
[EventCache_Time] ON [dbo].[EventCache] REORGANIZE; UPDATE STATISTICS [dbo].[EventCache]
[EventCache_Time];
--=========================================
--[dbo].[EventCache].[EventCache_ListId]:
-- FRAGMENTATION:
22.176%
-- PAGE COUNT:
3603
-- OPERATION:
REORGANIZE
--ALTER INDEX
[EventCache_ListId] ON [dbo].[EventCache] REORGANIZE; UPDATE STATISTICS [dbo].[EventCache]
[EventCache_ListId];
--=================================
--[dbo].[EventLog].[EventLog_Id]:
--
FRAGMENTATION: 32.5723%
-- PAGE COUNT:
16207
-- OPERATION:
REBUILD
-- FILL FACTOR:
80
--ALTER INDEX
[EventLog_Id] ON [dbo].[EventLog] REBUILD WITH (FILLFACTOR = 80)
--=============================================
--[dbo].[AllUserData].[AllUserData_ParentId]:
--
FRAGMENTATION: 13.7654%
-- PAGE COUNT:
1006816
-- OPERATION:
REORGANIZE
--ALTER INDEX
[AllUserData_ParentId] ON [dbo].[AllUserData] REORGANIZE; UPDATE STATISTICS [dbo].[AllUserData]
[AllUserData_ParentId];
--=================================
--[dbo].[Workflow].[Workflow_CI]:
--
FRAGMENTATION: 15.8641%
-- PAGE COUNT:
3385
-- OPERATION:
REORGANIZE
--ALTER INDEX
[Workflow_CI] ON [dbo].[Workflow] REORGANIZE;
UPDATE STATISTICS [dbo].[Workflow] [Workflow_CI];
--=======================================
--[dbo].[AuditData].[AuditData_OnItem]:
--
FRAGMENTATION: 12.085%
-- PAGE COUNT:
243318
-- OPERATION:
REORGANIZE
--ALTER INDEX
[AuditData_OnItem] ON [dbo].[AuditData] REORGANIZE; UPDATE STATISTICS [dbo].[AuditData]
[AuditData_OnItem];
Bueno eso es todo por ahora, con esto terminamos la serie que tiene que ver con bases de datos y la granja de SharePoint en sus versiones 2010,2013 y 2016.
SharePoint4fun!,
Juan Manuel Herrera Ocheita
