June 22, 2009 at 6:49 pm
Hi, how often do i need to maintain my indexes (executing dbcc dbreindex)?
Thanks
June 22, 2009 at 8:00 pm
Only as often as is required to maintain performance. Anything more is essentially using additional resources, which might be fine if you can spare them, but certainly not necessary.
Some reference tables (largely read-only) might never need to be maintained. In general, the more updates to the table, the more often you will need to maintain indexes (though that may not be true on a column by column basis).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 22, 2009 at 10:51 pm
generally as a rule of thumb we rebuild indexes having fragmentation more than 30% and and reorganize if the fragmentation is below 30% .
to find the fragmentation > 30% for all the indexes in all the databases :
sp_msforeachdb 'SELECT object_name(a.object_id)[Table Name],b.name [Index Name],a.index_type_desc [Index type],partition_number [Partition number],a.index_id [Index id],a.index_level [Index Level],a.avg_fragmentation_in_percent [Percent fragmentation]FROM ?.sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, "DETAILED") a JOIN ?.sys.indexes b ON a.object_id = b.object_id AND a.index_id = b.index_id WHERE a.index_type_desc "HEAP" AND a.index_level = 0 and a.avg_fragmentation_in_percent >=30 '
to find the fragmentation < 30% for all the indexes in all the databases :
sp_msforeachdb 'SELECT object_name(a.object_id)[Table Name],b.name [Index Name],a.index_type_desc [Index type],partition_number [Partition number],a.index_id [Index id],a.index_level [Index Level],a.avg_fragmentation_in_percent [Percent fragmentation]FROM ?.sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, "DETAILED") a JOIN ?.sys.indexes b ON a.object_id = b.object_id AND a.index_id = b.index_id WHERE a.index_type_desc "HEAP" AND a.index_level = 0 and a.avg_fragmentation_in_percent >=30 '
Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)
June 22, 2009 at 11:20 pm
To rebuild and reorganize all the indexes in all the databases :
1) create the SP attached in all the databases
2) execute this command :
sp_msforeachdb "exec ?.dbo.Rebuild_reorg_index"
Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)
June 23, 2009 at 12:36 am
hi_abhay78 (6/22/2009)
generally as a rule of thumb we rebuild indexes having fragmentation more than 30% and and reorganize if the fragmentation is below 30% .to find the fragmentation > 30% for all the indexes in all the databases :
sp_msforeachdb 'SELECT object_name(a.object_id)[Table Name],b.name [Index Name],a.index_type_desc [Index type],partition_number [Partition number],a.index_id [Index id],a.index_level [Index Level],a.avg_fragmentation_in_percent [Percent fragmentation]FROM ?.sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, "DETAILED") a JOIN ?.sys.indexes b ON a.object_id = b.object_id AND a.index_id = b.index_id WHERE a.index_type_desc "HEAP" AND a.index_level = 0 and a.avg_fragmentation_in_percent >=30 '
to find the fragmentation < 30% for all the indexes in all the databases :
sp_msforeachdb 'SELECT object_name(a.object_id)[Table Name],b.name [Index Name],a.index_type_desc [Index type],partition_number [Partition number],a.index_id [Index id],a.index_level [Index Level],a.avg_fragmentation_in_percent [Percent fragmentation]FROM ?.sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, "DETAILED") a JOIN ?.sys.indexes b ON a.object_id = b.object_id AND a.index_id = b.index_id WHERE a.index_type_desc "HEAP" AND a.index_level = 0 and a.avg_fragmentation_in_percent >=30 '
Im getting the error: while running the TSQL.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '-'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '.
Tanx 😀
June 23, 2009 at 2:31 am
Its running perfectly fine in 2005 .
Run it in single line and not in multiple lines .Secondly , while copying it might change the single quote to something else (a different kind of single quote). just double click the error line and it will take you to the line in the code .
But execute it in a single line of code (copy it first in notepad as word wrap not checked )
Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)
June 23, 2009 at 11:44 pm
What about in 2000.
My server has both 2000 and 2005 databases.
Tanx 😀
June 24, 2009 at 1:00 am
For 2000
----------
1) create this SP attached in all the databases you want
2) execute this :sp_msforeachdb "exec ?.dbo.Rebuild_reorg_index"
so there is no need to find and then rebuild/reorg .this SP will do both
Regards
Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)
June 24, 2009 at 2:47 am
My practice,
Rebuild index : per 1 month,
Reorganize index : per 1 week.
June 24, 2009 at 6:36 am
isabel (6/22/2009)
Hi, how often do i need to maintain my indexes (executing dbcc dbreindex)?
Use ALTER INDEX ... REBUILD | REORGANIZE instead of DBCC DBREINDEX (it is deprecated and will be removed from the next version of SQL Server).
Just for completeness: Fragmentation is only a concern for range seeks or scans. It is irrelevant to single-row operations. If your database is exclusively OLTP you may not care about fragmentation at all - beyond the page fullness/space issues.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 24, 2009 at 6:46 am
Maybe a second thought:
select db_name(sind.database_id) as dbname,object_name(sind.object_id,sind.database_id) as objname,sind.index_id,sind.index_type_desc,sind.avg_fragmentation_in_percent,'ALTER INDEX ON '+db_name(sind.database_id)+'.dbo.'+object_name(sind.object_id,sind.database_id)+'.'+si.name+' REBUILD WITH (FILLFACTOR = 85, SORT_IN_TEMPDB = ON STATISTICS_NORECOMPUTE = ON) ' as executethsestatemetns
from sys.dm_db_index_physical_stats(NULL,NULL,NULL,NULL,DEFAULT) sind join sys.indexes si
on si.index_id = sind.index_id and si.object_id = sind.object_id
where sind.database_id>4 and sind.avg_fragmentation_in_percent >=30
and sind.index_id>=1
copy and paste the last column results in new query windows or your can save it in table and execute as needed.
Best practise is to create a baseline and monitor your indexes to check the fragmetnation levels and rebuild accordingly.. there is no definate answer.. but the general run of thm is as simple : if you have lots of inserts/updates/deletes you ought to look at your indexing strategy.
Maninder
www.dbanation.com
June 24, 2009 at 7:02 am
[font="Tahoma"]There are (currently) 99 different index maintenance scripts on this site already:
http://www.sqlservercentral.com/Scripts/Index+Management/[/font]
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply