August 11, 2006 at 7:16 am
Microsoft recommends avoiding the use of DBCC DBREINDEX as this feature will be removed in a future version of SQL Server. Microsoft recommends using ALTER INDEX w/ REBUILD instead.
Does anyone have a script which will traverse a Database (and all tables) using the ALTER INDEX REBUILD -- retaining the IDX's original definition attributes? (optimally, we'd like to see a script traversing all DB's, all Tb's, and rebuilding all IDX's)
thx in advance.
August 11, 2006 at 1:23 pm
I think in the Maint plan wizard there is an option to do this without coding it all manually.
August 28, 2006 at 2:35 am
Hi there,
I run this SP on our production servers... Create the SP in your master database & just specify the dbname you want to re-index.
ALTER PROCEDURE [dbo].[p_ReIndexing] (
@dbname varchar(50)
)
AS
SET NOCOUNT ON
DECLARE @tblname varchar(200), @execstring nvarchar(4000)
EXEC('
DECLARE defrag_cursor CURSOR FOR
SELECT
''['' + b.name + '']'' + ''.'' + ''['' + a.name + '']'' AS tblname
FROM '
+ @dbname + '..sysobjects a (nolock)
INNER JOIN ' + @dbname + '..sysusers b (nolock)
ON a.uid = b.uid
WHERE a.name NOT LIKE ''sys%''
AND a.name NOT LIKE ''%sys%''
AND a.name NOT LIKE ''%properties%''
AND a.name NOT LIKE ''MS%''
AND a.type = ''U''
GROUP BY ''['' + b.name + '']'' + ''.'' + ''['' + a.name + '']''')
OPEN defrag_cursor
FETCH NEXT FROM defrag_cursor
INTO @tblname
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @execstring = 'ALTER INDEX ALL
ON ' + @dbname + '.' + @tblname + '
REBUILD;'
EXEC sp_executeSQL @execstring
EXEC sp_updatestats
FETCH NEXT FROM defrag_cursor
INTO @tblname
END
CLOSE defrag_cursor
DEALLOCATE defrag_cursor
August 29, 2006 at 12:46 pm
There is an approach in Books on Line for sql 2005, listed at the bottom of the article on sys.dm_db_index_physical_stats.
This approach, which can be adapted to a stored procedure, uses all of the new best practices.
Does not use sysindexes or sysobjects or dbcc reindex -- all of which will be removed in the future.
The following example automatically reorganizes or rebuilds all partitions in a
database that have an average fragmentation over 10 percent.
Executing this query requires the VIEW DATABASE STATE permission.
This example specifies DB_ID as the first parameter without specifying a database name.
An error will be generated if the current database has a compatibility level of 80 or lower.
To resolve the error, replace DB_ID() with a valid database name. For more information
about database compatibility levels, see sp_dbcmptlevel (Transact-SQL).
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 partitions CURSOR FOR SELECT * FROM #work_to_do;
OPEN 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;
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 partitions;
DEALLOCATE partitions;
DROP TABLE #work_to_do;
GO
January 14, 2010 at 2:02 pm
You need Alter permission, not only view database state
January 14, 2010 at 3:33 pm
Here is one of the better scripts available for perform a smart reindex: http://sqlfool.com/tag/indexes/
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply