SELECT @SQL = @SQL + 'Select ' + quotename(NAME, '''') + ' as [DB_Name],
ps.object_id AS objectid, object_Name(PS.Object_ID,'+ convert(VARCHAR(10), database_id) + ') as object_name ,
ps.index_id AS indexid, ind.name as IndexName,sch.name as SchemaName,ps.partition_number AS partitionnum, ps.avg_fragmentation_in_pe rcent AS frag, ps.page_count
from ' + quotename(NAME) + '.sys.dm_db_index_physical_stats(' + convert(VARCHAR(10), database_id) + ', NULL, NULL, NULL, NULL) ps
inner join ' + NAME + '.sys.tables as tbl
on ps.object_id = tbl.object_id
inner join ' + NAME + '.sys.schemas as sch
on tbl.schema_id = sch.schema_id
inner join ' + NAME + '.sys.indexes as ind
on ps.index_id = ind.index_id and
ps.object_id = ind.object_id
WHERE ps.avg_fragmentation_in_percent > 10.0
AND ps.index_id > 0
AND ps.page_count > 50
ORDER BY [DB_Name] asc , ps.avg_fragmentation_in_percent DESC;
' + CHAR(13)
FROM sys.databases
WHERE state_desc = 'ONLINE'
AND NAME NOT IN ('master', 'tempdb', 'model', 'msdb', 'ReportServer', 'ReportServerTempDB')
declare @SQL nvarchar(max)
set @SQL = ''
select @SQL = @SQL +
'Select ' + quotename(name,'''') + ' as [DB_Name],
ps.object_id AS objectid, object_Name(PS.Object_ID,' + convert(varchar(10),database_id) + ') as object_name ,
ps.index_id AS indexid, ind.name as IndexName,sch.name as SchemaName,ps.partition_number AS partitionnum, ps.avg_fragmentation_in_percent AS frag, ps.page_count
from ' + quotename(name) + '.sys.dm_db_index_physical_stats(' +
convert(varchar(10),database_id) + ', NULL, NULL, NULL, NULL) ps
inner join ' + name + '.sys.tables as tbl
on ps.object_id = tbl.object_id
inner join ' + name + '.sys.schemas as sch
on tbl.schema_id = sch.schema_id
inner join ' + name + '.sys.indexes as ind
on ps.index_id = ind.index_id and
ps.object_id = ind.object_id
WHERE ps.avg_fragmentation_in_percent > 10.0
AND ps.index_id > 0
AND ps.page_count > 1
ORDER BY [DB_Name] asc , ps.avg_fragmentation_in_percent DESC;
'
+ CHAR(13) +'---------------------------------------------' + CHAR(13)
from sys.databases where state_desc = 'ONLINE'
and name not in ('master','tempdb','model','msdb', 'ReportServer','ReportServerTempDB')
print @SQL
-- Ensure a USE <databasename> statement has been executed first.
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 @page_count BIGINT;
DECLARE @command NVARCHAR(4000);
DECLARE @updateStatsThreshold INT;
DECLARE @StartTime DATETIME;
DECLARE @EndTime DATETIME;
-----------------------------------------------------------
DECLARE @Work_To_Do TABLE (
[DATABASE] VARCHAR(50)
,ObjectID NUMERIC
,IndeID INT
,PartitionNum INT
,Frag FLOAT
,Page_Count INT
)
DECLARE @Databases TABLE (
RowNumber INT
,Name VARCHAR(50)
,[dbID] INT
)
DECLARE @x INT
DECLARE @y INT
DECLARE @dbID INT
-----------------------------------------------------------
INSERT INTO
@Databases
SELECT
ROW_NUMBER() OVER (ORDER BY dbID) AS 'RowNumber'
,Name
,dbID
FROM
master..sysdatabases
--SELECT * FROM #Databases
-----------------------------------------------------------
SET @x = 1
SELECT @y = COUNT(*) FROM @Databases
-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
-- and convert object and index IDs to names.
WHILE @x < @y
BEGIN
SELECT @dbID = dbID FROM @Databases WHERE RowNumber = @x
INSERT INTO
@Work_To_Do
SELECT
(SELECT Name FROM @Databases WHERE RowNumber = @x) AS 'Database',
OBJECT_ID AS ObjectID,
index_id AS IndexID,
partition_number AS PartitionNum,
avg_fragmentation_in_percent AS Frag,
Page_Count
FROM
sys.dm_db_index_physical_stats (@dbID, NULL, NULL , NULL, 'LIMITED')
WHERE
avg_fragmentation_in_percent > 10.0
AND index_id > 0
--AND page_count > 50
ORDER BY
avg_fragmentation_in_percent DESC;
PRINT @x
SET @x = @x + 1
END
-----------------------------------------------------------
--stats threshold
SET @updateStatsThreshold = 7
-- 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;
--write header
PRINT N'"Date", "Page_Count", "Frag", "Command"';
SET @StartTime = (SELECT GETDATE())
-- 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
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 < 90.0
-- SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE WITH (MaxDop=8)';
--SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
--IF @frag >= 90.0
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD WITH (MaxDop=8)';
IF @partitioncount > 1
SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS NVARCHAR(10));
PRINT N'"' + CAST(GETDATE() AS VARCHAR(20)) + '", "' + CAST(@page_count AS VARCHAR(30)) + '", "' + CAST(@frag AS VARCHAR(20)) + '", "' + @command + '"';
EXEC (@command);
--stats
IF STATS_DATE(@objectid, @indexid) < DATEADD(dd, -@updateStatsThreshold, GETDATE())
BEGIN
SELECT @command = 'UPDATE STATISTICS ' + @schemaname + '.' + OBJECT_NAME(@objectid) + ' ' + @indexname +' WITH RESAMPLE'
PRINT N'"' + CAST(GETDATE() AS VARCHAR(20)) + '", "' + CAST(@page_count AS VARCHAR(30)) + '", "' + CAST(@frag AS VARCHAR(20)) + '", "' + @command + '"';
EXEC (@command)
END
--End loop
END;
-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;
SET @EndTime = (SELECT GETDATE())
SELECT LTRIM(STR(DATEDIFF(mi,@starttime,@endtime) % 60 )) + '.' + LTRIM(STR(DATEDIFF(ss,@starttime,@endtime) % 60 )) AS 'CompletionTime'
SELECT * FROM @Work_To_Do
-----------------------------------------------------------
DECLARE @Work_To_Do TABLE (
[DATABASE] VARCHAR(50)
,ObjectID NUMERIC
,IndeID INT
,PartitionNum INT
,Frag FLOAT
,Page_Count INT
)
DECLARE @Databases TABLE (
RowNumber INT
,Name VARCHAR(50)
,[dbID] INT
)
DECLARE @x INT
DECLARE @y INT
DECLARE @dbID INT
-----------------------------------------------------------
INSERT INTO
@Databases
SELECT
ROW_NUMBER() OVER (ORDER BY dbID) AS 'RowNumber'
,Name
,dbID
FROM
master..sysdatabases
--SELECT * FROM #Databases
-----------------------------------------------------------
SET @x = 1
SELECT @y = COUNT(*) FROM @Databases
WHILE @x < @y
BEGIN
SELECT @dbID = dbID FROM @Databases WHERE RowNumber = @x
INSERT INTO
@Work_To_Do
SELECT
(SELECT Name FROM @Databases WHERE RowNumber = @x) AS 'Database',
OBJECT_ID AS ObjectID,
index_id AS IndexID,
partition_number AS PartitionNum,
avg_fragmentation_in_percent AS Frag,
Page_Count
FROM
sys.dm_db_index_physical_stats (@dbID, NULL, NULL , NULL, 'LIMITED')
WHERE
avg_fragmentation_in_percent > 10.0
AND index_id > 0
AND page_count > 50
ORDER BY
avg_fragmentation_in_percent DESC;
PRINT @x
SET @x = @x + 1
END
-----------------------------------------------------------
SELECT
*
FROM
@Work_To_Do