marrowyung
asked on
Rebulit index for all table in all SQL server 2005 database
Dear all,
Any script for me to rebuilt index for all index of all table in all database?
I want to do it all in one file.
I am using SQL server 2005.
DBA100.
Any script for me to rebuilt index for all index of all table in all database?
I want to do it all in one file.
I am using SQL server 2005.
DBA100.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
The one i posted
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
acperkins,
"EXECUTE dbo.IndexOptimize
@Databases = 'ALL_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_RE BUILD_ONLI NE,INDEX_R EBUILD_OFF LINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDE X_REBUILD_ OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30 "
Yeah, that one is listed as the example. It works for you, right?
"EXECUTE dbo.IndexOptimize
@Databases = 'ALL_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_RE
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDE
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30 "
Yeah, that one is listed as the example. It works for you, right?
ASKER
it seems that running this:
use <userdatabase>
EXECUTE dbo.IndexOptimize
@Databases = 'ALL_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_RE BUILD_ONLI NE,INDEX_R EBUILD_OFF LINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDE X_REBUILD_ OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30
the avg fragmentation_in_precent still the same, checking by this:
SELECT DB_NAME(a.database_id) as [Database Name]
,OBJECT_NAME(SO.id) as [Table Name]
,a.object_id as [Table Object ID]
,b.name as [Index Name]
,a.index_id
,a.avg_fragmentation_in_pe rcent
FROM sys.dm_db_index_physical_s tats (DB_ID(), NULL, NULL, NULL, NULL) AS a
JOIN sys.indexes AS b
ON a.object_id = b.object_id
JOIN sys.sysobjects AS SO
on b.object_id = SO.id
AND a.index_id = b.index_id
where a.database_id = db_id() and SO.xtype = 'U'
order by a.avg_fragmentation_in_per cent DESC
GO
any reason ?
use <userdatabase>
EXECUTE dbo.IndexOptimize
@Databases = 'ALL_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_RE
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDE
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30
the avg fragmentation_in_precent still the same, checking by this:
SELECT DB_NAME(a.database_id) as [Database Name]
,OBJECT_NAME(SO.id) as [Table Name]
,a.object_id as [Table Object ID]
,b.name as [Index Name]
,a.index_id
,a.avg_fragmentation_in_pe
FROM sys.dm_db_index_physical_s
JOIN sys.indexes AS b
ON a.object_id = b.object_id
JOIN sys.sysobjects AS SO
on b.object_id = SO.id
AND a.index_id = b.index_id
where a.database_id = db_id() and SO.xtype = 'U'
order by a.avg_fragmentation_in_per
GO
any reason ?
See my response in your new question:
https://www.experts-exchange.com/questions/28054129/optimizing-index.html?anchorAnswerId=38960264#a38960264
https://www.experts-exchange.com/questions/28054129/optimizing-index.html?anchorAnswerId=38960264#a38960264
ASKER
Any one script can do the job for all user 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_s
-- and convert object and index IDs to names.
SELECT
object_id AS objectid,
index_id AS indexid,
partition_number AS partitionnum,
avg_fragmentation_in_perce
INTO #work_to_do
FROM sys.dm_db_index_physical_s
WHERE avg_fragmentation_in_perce
-- 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';
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