November 17, 2008 at 11:38 am
Hi....Everyweek we run DB maintanance plan for CHECKDB and Reindexing for a database.....The database has around 400 tables and it takes 3 hrs to reindex and the log grows too big after the reindexing job. So, the log truncate and backup jobs are failing. we have to manually shrink the log and take a full backup again.
Is there any script like only the tables which really need reindexing should be reindexed?? So, we can save lot of time and space as well. any help would be appreciated.
Thanks in advance....
November 17, 2008 at 12:21 pm
Look up the following in Books Online. you will find an example there.
sys.dm_db_index_physical_stats dynamic management function
November 17, 2008 at 12:38 pm
Thanks a lot for the reply.....so, I used sys.dm_db_index_physical_stats for a particular database and I got result ...for example, in the following row it has avg_fragmentation_percent as 96.56593...... so we should reindex all the tables whose fragmentation_percent is above 30 right??? please let me know.....
1 1083866928 1 1 CLUSTERED INDEX IN_ROW_DATA 3 0 96.5659340659341 709 1.02679830747532 728 NULL NULL NULL NULL NULL NULL NULL NULL
November 17, 2008 at 12:42 pm
It depends.
This is something you will have to determine based on your application and performance requirements, as well as the size of each of the tables. For very small tables, it may not make much sense to defrag the indexes.
November 17, 2008 at 12:51 pm
Thank you very much for your reply...It was very helpful....
November 18, 2008 at 5:38 am
it's also worth noting that under bulk logged and simple recovery models the following are minimally logged
ALTER INDEX
CREATE INDEX
we always set our db recovery models to bulk logged, rebuild indexes then set back to full and take a backup
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 18, 2008 at 7:55 am
It is replicated and we can't change the recovery model....But I m all set now with the dynamic views above....Thank you
November 18, 2008 at 4:42 pm
ssismaddy (11/18/2008)
It is replicated and we can't change the recovery model....But I m all set now with the dynamic views above....Thank you
even if it is replicated you can still change to bulk logged without any problem. Change the recovery, rebuild the indexes then change back
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 18, 2008 at 6:41 pm
>Is there any script like only the tables which really need reindexing should be reindexed?
I have a solution that you're welcome to use.
http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html
Ola Hallengren
November 18, 2008 at 7:09 pm
Log truncate job?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 18, 2008 at 7:38 pm
The script I used is for rebuilding all the tables whose fragmentation is above 30% and for reorganizing the indexes whose fragm is >5% and <30%. Here is the script...
--Variable Declaration
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 > 5.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;
-- between 5 and 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
IF (@frag > 5.0 and @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
November 18, 2008 at 9:57 pm
Following is the scripts which Rebuild and Reorganize as per fragmentation of the idexex for all the tables of all databases in SQL server 2005...
declare @sql nvarchar(max)
DECLARE @dbname VARCHAR(4000)
declare c_db cursor FAST_FORWARD FOR
select name from sys.sysdatabases
OPEN c_db
FETCH NEXT FROM c_db INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql =
'use '+ @dbname +';
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.
declare @dbid bigint;
set @dbid = DB_ID();
print @dbid;
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 (@dbid, 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'';
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;
'
PRINT (@SQL)
EXEC(@SQL)
FETCH NEXT FROM c_db INTO @dbname
END
CLOSE c_db
DEALLOCATE c_db
November 18, 2008 at 11:26 pm
declare @sql nvarchar(max)
DECLARE @dbname VARCHAR(4000)
declare c_db cursor FAST_FORWARD FOR
select name from sys.sysdatabases
OPEN c_db
FETCH NEXT FROM c_db INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql =
'use '+ @dbname +';
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.
declare @dbid bigint;
set @dbid = DB_ID();
print @dbid;
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 (@dbid, 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'';
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;
'
PRINT (@SQL)
EXEC(@SQL)
FETCH NEXT FROM c_db INTO @dbname
END
CLOSE c_db
DEALLOCATE c_db
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply