Automation of dbcc dbreindex()

This stored procedure automates dbcc dbreindex() from defragmenting indexes in a database. It could also be used for dbcc indexdefrag() with simple modification. This SP is based on sample code from Books Online, which I have taken, made some amendments to and formed into a stored procedure.<br /><br />You should create this in the master database. Then to run it, you simply run it in the database you wish to defragment. You pass the SP the threshold of scan density below which you wish to run dbcc dbreindex(). For example, if you wish to run it in a database called TESTDB and you want to defragment all indexes with a scan density below 80%, do the following:- <br /><br /><font face="Courier New">USE TESTDB <br />GO <br />exec sp_defragment_indexes 80.00 </font id="Courier New"><br /><br />The output will show you which indexes it is defragmenting, and show you how fragmented the indexes are. <br /><br />If you run this as a scheduled task, you can set the job to output to text file, then you can review what the SP has done each time it's run.<br /><br /><br /><br />-- SP code starts here<br /><br /><font face="Courier New">CREATE PROCEDURE sp_defragment_indexes @maxfrag DECIMAL<br /><br />AS<br /><br />/* T.Pullen 29.11.2001<br /><br />This stored procedure checks index fragmentation in a database and defragments<br />indexes whose scan densities fall below a specified threshold, @magfrag, which<br />is passed to the SP.<br /><br />Must be run in the database to be defragmented.<br />*/<br />-- Declare variables<br /><br />SET NOCOUNT ON<br />DECLARE @tablename VARCHAR (12<img src='/community/emoticons/emotion-11.gif' alt='8)' /><br />DECLARE @execstr VARCHAR (255)<br />DECLARE @objectid INT<br />DECLARE @indexid INT<br />DECLARE @frag DECIMAL<br />DECLARE @indexname CHAR(255)<br />DECLARE @dbname sysname<br /><br />SELECT @dbname = db_name()<br />IF @dbname IN ('master', 'msdb', 'model', 'tempdb')<br />BEGIN<br />PRINT 'This procedure should not be run in system databases.'<br />RETURN<br />END<br /><br />-- Declare cursor<br />DECLARE tables CURSOR FOR<br /> SELECT so.name<br /> FROM sysobjects so<br /> JOIN sysindexes si<br /> ON so.id = si.id<br /> WHERE so.type ='U'<br /> AND si.indid &lt; 2<br /> AND si.rows &gt; 0<br /><br /><br />-- Create the table<br />CREATE TABLE #fraglist (<br /> ObjectName CHAR (255),<br /> ObjectId INT,<br /> IndexName CHAR (255),<br /> IndexId INT,<br /> Lvl INT,<br /> CountPages INT,<br /> CountRows INT,<br /> MinRecSize INT,<br /> MaxRecSize INT,<br /> AvgRecSize INT,<br /> ForRecCount INT,<br /> Extents INT,<br /> ExtentSwitches INT,<br /> AvgFreeBytes INT,<br /> AvgPageDensity INT,<br /> ScanDensity DECIMAL,<br /> BestCount INT,<br /> ActualCount INT,<br /> LogicalFrag DECIMAL,<br /> ExtentFrag DECIMAL)<br /><br />-- Open the cursor<br />OPEN tables<br /><br />-- Loop through all the tables in the database<br />FETCH NEXT<br /> FROM tables<br /> INTO @tablename<br /><br />WHILE @@FETCH_STATUS = 0<br />BEGIN<br />-- Do the showcontig of all indexes of the table<br /> INSERT INTO #fraglist <br /> EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''') <br /> WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')<br /> FETCH NEXT<br /> FROM tables<br /> INTO @tablename<br />END<br /><br />-- Close and deallocate the cursor<br />CLOSE tables<br />DEALLOCATE tables<br /><br />SELECT * FROM #fraglist<br /><br />-- Declare cursor for list of indexes to be defragged<br />DECLARE indexes CURSOR FOR<br /> SELECT ObjectName, ObjectId, IndexName, ScanDensity<br /> FROM #fraglist<br /> WHERE ScanDensity &lt;= @maxfrag<br /> AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') &gt; 0<br /><br />SELECT 'Started defragmenting indexes at ' + CONVERT(VARCHAR,GETDATE())<br /><br />-- Open the cursor<br />OPEN indexes<br /><br />-- loop through the indexes<br />FETCH NEXT<br /> FROM indexes<br /> INTO @tablename, @objectid, @indexname, @frag<br /><br />WHILE @@FETCH_STATUS = 0<br />BEGIN<br /> SELECT @execstr = 'DBCC DBREINDEX (' + RTRIM(@tablename) + ',<br /> ' + RTRIM(@indexname) + ') WITH NO_INFOMSGS'<br /> SELECT (@execstr)<br /> EXEC (@execstr)<br /><br /> FETCH NEXT<br /> FROM indexes<br /> INTO @tablename, @objectid, @indexname, @frag<br />END<br /><br />-- Close and deallocate the cursor<br />CLOSE indexes<br />DEALLOCATE indexes<br /><br />SELECT 'Finished defragmenting indexes at ' + CONVERT(VARCHAR,GETDATE())<br /><br />-- Delete the temporary table<br />DROP TABLE #fraglist<br /><br /><br />GO</font id="Courier New"><br /><br />Tom Pullen<br />DBA, Oxfam GB

Thanks Tom for this script. I use parts of it to improve my procedure that rebuilds <br />indexes. <img src='/community/emoticons/emotion-1.gif' alt='' /><br /><br />Bambola.<br />

Yep - it's the WITH TABLERESULTS of DBCC SHOWCONTIG that make all the difference, make it so much easier to do this conditionally.

My corresponding 6.5 script is a mess - you have to keep xp_cmdshelling out to read text file output!

Tom Pullen
DBA, Oxfam GB

We want to use your stored procedure to reindex database but we are a bit confused by the output.

We have about 35 indexes that show up in the grid with all the statistics. What we are confused about are these last three entries. Why does it show the PxUser, PK_User index??? Is that the only one it reindexed???


To verify I only included the bottom of the results as the first part of the results did show all my indexes. I did this on a test database only before running it on a live database.

Thanks

Doug

---------------------------------------------------------------
Started defragmenting indexes at Oct 16 2003 3:21PM


---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DBCC DBREINDEX (PxUser,
PK_User) WITH NO_INFOMSGS


----------------------------------------------------------------
Finished defragmenting indexes at Oct 16 2003 3:21PM

Here is a simmillar script I've posted a while ago in this forum:
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=1389

This script will also check the extent of fragmentation and will defrag only those indexes that are fragmented beyond a specific limit.

You should be able to use the procedure above with sql7. it is using DBCC DBREINDEX and not INDEXDEFRAG (which was only introduces in sql 2000), and it is not using table datatype or anything else I can see to prevent you from using it. Or am I missing something...

Bambola.

I don't think my script will work in SQL Server 7.0, because it uses DBCC SHOWCONTIG()WITH TABLERESULTS which was introduced in 2000.

This makes a big difference, because without it, you are reduced to outputting to text file, and the re-reading the results back in from text file, using xp_cmdshell for example, which is cumbersome.

I do not have an equivalent for 7.0, but I do have something for 6.5 which automates the SHOWCONTIG part, but not the REINDEX - but it could easily be adapted to do so. Let me know if you would like me to post that.

Tom Pullen
DBA, Oxfam GB

In reply to dcohn - yes, that does mean it has only reindexed your PK index. This procedure will only reindex indexes which are fragmented - it will not reindex entire tables if that is unnecessary.

Remember that if the index being defragged is the clustered index, the entire table will be reindexed along with it, because clustered indexes contain the data, and if they are rebuilt, all nonclustered indexes will be rebuilt too.

Tom Pullen
DBA, Oxfam GB

> I don't think my script will work in SQL Server 7.0, because it uses DBCC SHOWCONTIG()WITH TABLERESULTS which was introduced in 2000.

I didn't know that...

Bambola.

Okay, here it is. I must admit that this is shamelessly stolen from someone else who wrote it originally, it was a woman, but I can't remember who she was or where I got it from .. so if she's reading this, which I hope she isn't, I'm very sorry.

--sp code starts here

if exists (select * from sysobjects where id = object_id('dbo.sp_get_fragmentation') and sysstat & 0xf = 4)
drop procedure dbo.sp_get_fragmentation
GO

create procedure sp_get_fragmentation @login_name varchar(30) = NULL, @pwd varchar(30) = NULL,
@dbname varchar(30), @target decimal(5,2) = 85.00,@file varchar(255) = 'c: emp',
@rebuild_ind char(1) = 'N'

as

/* sp_get_fragmentation
T.Pullen 28.8.2001
This SP reports all index fragmentation and optionally reindexes tables below a certain threshold. Suitable for SQL Server 6.5 & potentially 7.0.
*/

begin

set nocount on

declare @obj_name sysname
declare @idint
declare @indidsmallint
declare @ind_namesysname
declare @ownersysname
declare @where_clausevarchar(255)
declare @cmdvarchar(255)
declare @scan_dnstydecimal(5,2)
declare @rowcntint


if @dbname = 'master' or @dbname = 'model' -- or @dbname = 'pubs'
or @dbname = 'tempdb' or @dbname = 'distribution' or @dbname = 'msdb'
return 1

exec('declare obj_cursor cursor for
select o.name, o.id, i.indid, i.name, u.name
from ' + @dbname + '.dbo.sysobjects o, ' + @dbname + '.dbo.sysindexes i, ' + @dbname + '.dbo.sysusers u
where o.id = i.id and i.id > 99 and i.rows > 0
and o.uid = u.uid order by o.name FOR READ ONLY')

create table #iw_report
(dbname varchar(30) not null,
obj_name varchar(30) not null,
idint not null,
indidsmallint not null,
ind_name varchar(30) not null,
ownervarchar(30) not null,
density decimal(5,2) null)

create table #iw_density
(density_str varchar(255) null)


open obj_cursor

fetch next from obj_cursor into @obj_name, @id, @indid, @ind_name, @owner



while @@fetch_status = 0
begin
-- use trusted connection if password and login name are NULL
if @pwd is NULL and @login_name is NULL
select @cmd = 'isql /Q"DBCC SHOWCONTIG(' + convert(varchar(30),@id) + ',' + convert(varchar(5),@indid)
+ ')" /E /d' + @dbname +
' /o ' + @file + 'showcontig.out'
else
select @cmd = 'isql /Q"DBCC SHOWCONTIG(' + convert(varchar(30),@id) + ',' + convert(varchar(5),@indid)
+ ')" /U' + @login_name + ' /P' + @pwd + ' /d' + @dbname +
' /o ' + @file + 'showcontig.out'

execute master..xp_cmdshell @cmd

select @cmd = 'findstr /c:"Scan Density" ' +
@file + 'showcontig.out'


truncate table #iw_density


insert into #iw_density(density_str)
exec master..xp_cmdshell @cmd



select @scan_dnsty = convert(decimal(5,2),RTRIM(substring(density_str, charindex('%',density_str) - 6, 6)))
from #iw_density

-- select @scan_dnsty

if @scan_dnsty < @target
insert into #iw_report (dbname, obj_name, id, indid, ind_name, owner, density)
values(@dbname, @obj_name, @id, @indid, @ind_name, @owner, @scan_dnsty)



fetch next from obj_cursor into @obj_name, @id, @indid, @ind_name, @owner
end

close obj_cursor
deallocate obj_cursor

-- delete output file
select @cmd = 'if exist ' + @file + 'showcontig.out del ' + @file + 'showcontig.out'
execute master..xp_cmdshell @cmd

select @rowcnt = count(*) from #iw_report

if @rowcnt > 0
begin
print ' '
print 'Fragmented tables:'
print @dbname
select * from #iw_report
print ' '

-- rebuild indexes
if @rebuild_ind = 'Y'
begin
declare rebuild_cur cursor for
select distinct obj_name, id, owner
from #iw_report

open rebuild_cur

fetch next from rebuild_cur into @obj_name, @id, @owner

while @@fetch_status = 0
begin
-- make sure that table has at least one index

select @where_clause = 'where id = ' + convert(varchar(15),@id)

exec ('use ' + @dbname + ' if exists (select * from sysindexes ' + @where_clause +
' and indid > 0 ) DBCC DBREINDEX(''' + @dbname + '.' + @owner + '.'+ @obj_name + ''') WITH NO_INFOMSGS')


fetch next from rebuild_cur into @obj_name, @id, @owner
end
close rebuild_cur
deallocate rebuild_cur
end


end
else
select @dbname + ' - No fragmented tables have been found'


end

GO

--sp code ends here.

Hope this is useful.

Tom Pullen
DBA, Oxfam GB

Yes, if there are no indexes on the table, it shows you the fragmentation of the table. It looks like there are 2 pages in your table and each page is in its own extent. Well, it's not a big problem with a table with 2 pages, but if you still want to see 100%, you may delete and re-create the table <img src='/community/emoticons/emotion-1.gif' alt='' />

DBREINDEX on each index runs as an implicit transaction. So, if for some reason the process that started the DBREINDEX fails, then it always rollback. But, if the server itself crashes then there are heavy possibilities of the index getting corrupted.

INDEXDEFRAG is less prone to corruptions in such instances.

I am comparitively new to this SQL server. I did not understand the following part of code

-----

SELECT so.name
FROM sysobjects so
JOIN sysindexes si
ON so.id = si.id
WHERE so.type ='U'
AND si.indid < 2
AND si.rows > 0
------------
It looks like it is taking all tables with indid=0 and indid=1..
There may be tables in database that do not have any indexes on them.. So what is the use of running dbcc dbreindex on these tables.
Following sql query lists the tables that have
******************************************************************************
select distinct(so.name),so.id from sysobjects so,sysindexes si where so.type='u' and so.id in (select distinct(si.id) from sysindexes si,sysobjects so where si.id = so.id and si.indid > 0 and si.indid <> 255 and so.type='u') order by so.name
************************************************************************************

Secondly, what about tables that nonclustered index but no clustered index on them.

Thanks for your patience and time in answering this novice user.

Regards,
PRashanth

Hi, I have recently come across a 3rd-party app (*sigh*) which uses non-dbo-owned tables and realised that this script does not work for these. I have re-written it so it should work for all tables, regardless of the owner.<br /><br />I hope you find this of some use.<br /><br /><br />--procedure code starts here<br /><br /><br /><font face="Courier New">CREATE PROCEDURE sp_defragment_indexes @maxfrag DECIMAL<br /><br />AS<br /><br />/* T.Pullen 29.11.2001<br /><br />This stored procedure checks index fragmentation in a database and defragments<br />indexes whose scan densities fall below a specified threshold, @magfrag, which<br />is passed to the SP.<br /><br />Must be run in the database to be defragmented.<br /><br />Modification history<br />Changed by T.Pullen 3.3.2004 to cope with non-dbo owned tables<br /><br />*/<br />-- Declare variables<br /><br />SET NOCOUNT ON<br />DECLARE @tablename VARCHAR (12<img src='/community/emoticons/emotion-11.gif' alt='8)' /><br />DECLARE @execstr VARCHAR (255)<br />DECLARE @objectid INT<br />DECLARE @objectowner VARCHAR(255)<br />DECLARE @indexid INT<br />DECLARE @frag DECIMAL<br />DECLARE @indexname CHAR(255)<br />DECLARE @dbname sysname<br />DECLARE @tableid INT<br />DECLARE @tableidchar VARCHAR(255)<br /><br /><br /><br /><br />SELECT @dbname = db_name()<br />IF @dbname IN ('master', 'msdb', 'model', 'tempdb')<br />BEGIN<br />PRINT 'This procedure should not be run in system databases.'<br />RETURN<br />END<br /><br />-- Declare cursor<br />DECLARE tables CURSOR FOR<br /> SELECT so.id<br /> FROM sysobjects so<br /> JOIN sysindexes si<br /> ON so.id = si.id<br /> WHERE so.type ='U'<br /> AND si.indid &lt; 2<br /> AND si.rows &gt; 0<br /><br /><br />-- Create the table<br />CREATE TABLE #fraglist (<br /> ObjectName CHAR (255),<br /> ObjectId INT,<br /> IndexName CHAR (255),<br /> IndexId INT,<br /> Lvl INT,<br /> CountPages INT,<br /> CountRows INT,<br /> MinRecSize INT,<br /> MaxRecSize INT,<br /> AvgRecSize INT,<br /> ForRecCount INT,<br /> Extents INT,<br /> ExtentSwitches INT,<br /> AvgFreeBytes INT,<br /> AvgPageDensity INT,<br /> ScanDensity DECIMAL,<br /> BestCount INT,<br /> ActualCount INT,<br /> LogicalFrag DECIMAL,<br /> ExtentFrag DECIMAL)<br /><br /><br />-- Open the cursor<br />OPEN tables<br /><br />-- Loop through all the tables in the database<br />FETCH NEXT<br /> FROM tables<br /> INTO @tableid<br /><br />WHILE @@FETCH_STATUS = 0<br />BEGIN<br />-- Do the showcontig of all indexes of the table<br /> SELECT @tableidchar = CONVERT(varchar,@tableid)<br /> INSERT INTO #fraglist <br /> EXEC ('DBCC SHOWCONTIG (' + @tableidchar + ') <br /> WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')<br /> FETCH NEXT<br /> FROM tables<br /> INTO @tableid<br />END<br /><br />-- Close and deallocate the cursor<br />CLOSE tables<br />DEALLOCATE tables<br /><br />SELECT * FROM #fraglist<br /><br />-- Declare cursor for list of indexes to be defragged<br />DECLARE indexes CURSOR FOR<br /> SELECT ObjectName, ObjectOwner = user_name(so.uid), ObjectId, IndexName, ScanDensity<br /> FROM #fraglist f<br /> JOIN sysobjects so ON f.ObjectId=so.id<br /> WHERE ScanDensity &lt;= @maxfrag<br /> AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') &gt; 0<br /><br />SELECT 'Started defragmenting indexes at ' + CONVERT(VARCHAR,GETDATE())<br /><br />-- Open the cursor<br />OPEN indexes<br /><br />-- loop through the indexes<br />FETCH NEXT<br /> FROM indexes<br /> INTO @tablename, @objectowner, @objectid, @indexname, @frag<br /><br />WHILE @@FETCH_STATUS = 0<br />BEGIN<br /> SELECT @execstr = 'DBCC DBREINDEX (' + RTRIM(@objectowner) + '.' + RTRIM(@tablename) + ',<br /> ' + RTRIM(@indexname) + ') WITH NO_INFOMSGS'<br /> EXEC (@execstr)<br /><br /> FETCH NEXT<br /> FROM indexes<br /> INTO @tablename, @objectowner, @objectid, @indexname, @frag<br />END<br /><br />-- Close and deallocate the cursor<br />CLOSE indexes<br />DEALLOCATE indexes<br /><br />SELECT 'Finished defragmenting indexes at ' + CONVERT(VARCHAR,GETDATE())<br /><br />-- Delete the temporary table<br />DROP TABLE #fraglist<br /><br />GO</font id="Courier New"><br /><br />--Procedure code ends here<br /><br />Tom Pullen<br />DBA, Oxfam GB

Luis, there isn't at present but I will have a look at if I get a chance and maybe I can change it. I'm sure it'd just be a case of adding a relevant WHERE clause to one of the cursor selects.


Tom Pullen
DBA, Oxfam GB

quote:Originally posted by LuisMartin

Tom:
I try to use the last sp. I have the following problem:
When sp find a non dba table, job stop and give some message about that table.
Is there a chance to filter and defrag only dba tables?

Thanks,

Luis Martin
Moderator
SQL-Server-Performance.com

The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.

I had the same problem and this was my solution:-
select so.[name]
from sysobjects so,
sysindexes si,
sysusers su
where
so.[id]=si.[id] and
so.[uid]=su.[uid] and
so.type='U' and
si.indid < 2 and
si.[rows] > 0 and
su.uid = 1 -- where owner is 'dbo'
order by so.[name]

I have been running this on the QA server which has dbs with non dbo tables and it seems to work OK so far.

BTW, thanks for the code Thomas. [8D]

Regards,
Andy Ong

'sapere aude.'

quote:Originally posted by LuisMartin

Thanks, Andy I'll try.


Luis Martin
Moderator
SQL-Server-Performance.com

All postings are provided “AS IS” with no warranties for accuracy.

Hi, I'd just found out another vendor's database we have here contains tables with different owners. So I tweaked the select statement to do this instead:-

select so.[name]
from sysobjects so,
sysindexes si,
sysusers su
where
so.[id]=si.[id] and
so.[uid]=su.[uid] and
so.type='U' and
si.indid < 2 and
si.[rows] > 0 and
su.[uid] < 16384 -- where 16384 and above represents db roles
order by so.[name]

So far I have look at other SQL Servers at our sites at random and in sysusers.uid, the int 16384 to 16393 (for SQL Server 7.0 and above) always represents db roles. Can you guys confirm this is always the case?

Regards,
Andy Ong

'sapere aude.'

i think it's always over 16,000 or so for any groups if memory serves.


Tom Pullen
DBA, Oxfam GB