I don't know where these scripts originated... I've tried to search it out to give appropriate credit, but it's on MSDN blogs, TechNet blogs, SQLServerCentral, and other sql related sites. I first came across them on the
Dynamics Communities site. If I learn of the origin I will come back and include it. For now, I'm including it here for my own reference. Consider changing the recovery mode to simple temporarily if there are going to be massive changes.
USE [DATABASE]
--Check Fragmentation
SELECT s.DATABASE_ID,
s.OBJECT_ID,
s.INDEX_ID,
b.NAME,
s.AVG_FRAGMENTATION_IN_PERCENT
FROM
sys.DM_DB_INDEX_PHYSICAL_STATS (DB_ID(), NULL, NULL, NULL, NULL) AS s
INNER JOIN sys.INDEXES AS b
ON
s.OBJECT_ID = b.OBJECT_ID
AND
s.INDEX_ID = b.INDEX_ID
WHERE
s.DATABASE_ID =
DB_ID()
ORDER BY s.AVG_FRAGMENTATION_IN_PERCENT
DESC
/**Fragmentation is also covered in the Performance Analyzer for Microsoft
Dynamics AX (also known as
DynamicsPerf). When running an sp_capturestats,
pass the parameter
@INDEX_PHYSICAL_STATS =’Y’. Note that this places a
heavier load on SQL when capturing,
so you should consider executing this
during non-peak hours. Once the
index fragmentation statistics have been
captured you can view them with
this script.
**/
USE [DATABASE]
SELECT DATABASE_NAME,
TABLE_NAME,
INDEX_NAME,
AVG_FRAGMENTATION_IN_PERCENT,
FRAGMENT_COUNT
FROM
INDEX_STATS_CURR_VW
ORDER BY AVG_FRAGMENTATION_IN_PERCENT DESC
--This script will reorganize or rebuild indexes based on the
fragmentation. It's set at 30% in this
script.
-- Ensure a USE statement has been
executed first.
USE [DATABASE]
SET nocount
ON;
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 @command NVARCHAR(4000);
-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function and convert object and index IDs to names.
SELECT OBJECT_ID AS
objectid,
INDEX_ID AS
indexid,
PARTITION_NUMBER AS
partitionnum,
AVG_FRAGMENTATION_IN_PERCENT AS frag
INTO
#work_to_do
FROM
sys.DM_DB_INDEX_PHYSICAL_STATS (DB_ID(), NULL, NULL, NULL, 'LIMITED')
WHERE
AVG_FRAGMENTATION_IN_PERCENT > 10.0
AND
INDEX_ID > 0;
-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions
CURSOR FOR
SELECT *
FROM #work_to_do;
-- Open the cursor.
OPEN partitions;
-- Loop through the partitions.
WHILE ( 1 = 1 )
BEGIN;
FETCH next FROM partitions INTO
@objectid, @indexid,
@partitionnum, @frag;
IF @@FETCH_STATUS <
0
BREAK;
SELECT
@objectname = QUOTENAME(o.NAME),
@schemaname = QUOTENAME(s.NAME)
FROM sys.OBJECTS AS o
JOIN
sys.SCHEMAS AS s
ON
s.SCHEMA_ID = o.SCHEMA_ID
WHERE o.OBJECT_ID =
@objectid;
SELECT
@indexname = QUOTENAME(NAME)
FROM sys.INDEXES
WHERE OBJECT_ID = @objectid
AND
INDEX_ID = @indexid;
SELECT
@partitioncount = COUNT (*)
FROM sys.PARTITIONS
WHERE OBJECT_ID = @objectid
AND
INDEX_ID = @indexid;
-- 30 is an
arbitrary decision point at which to switch between reorganizing and
rebuilding.
IF @frag < 30.0
SET
@command = N'ALTER
INDEX ' + @indexname + N' ON '
+
@schemaname + N'.'
+ @objectname +
N' REORGANIZE';
IF @frag >= 30.0
SET
@command = N'ALTER
INDEX ' + @indexname + N' ON '
+
@schemaname + N'.'
+ @objectname
+
N' REBUILD WITH( MAXDOP = 8)';
--Enterprise version:
+ N' REBUILD WITH(ONLINE = ON, MAXDOP = 8)';
IF
@partitioncount > 1
SET
@command = @command +
N' PARTITION='
+
CAST(@partitionnum
AS NVARCHAR(10));
EXEC (@command);
PRINT N'Executed: ' +
@command;
END;
-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;
-- Drop the temporary table.
DROP TABLE
#work_to_do;
GO
