November 24, 2009 at 5:25 pm
I recently ran a DBCC SHOWCONTIG on one of my databases and the SCAN Density was really low, 14%-20% on a bunch of tables. I was wondering what command would re-index all my my tables.
I was planning on using...
EXEC sp_msForEachTable @COMMAND1= 'DBCC DBREINDEX ( "?")'
I know this is a undocumented stored procedure, but it has worked well for me in the past, I have only used it on SQL 2005 databases, but noticed it is there under my SQL 2000 system stored procedure under master.
My other option is to use DBCC DBREINDEX (tablename, '',70) for each table, correct?
Thanks.
November 24, 2009 at 5:41 pm
For 2000 use dbcc dbreindex and for 2005 use rebuild option under alter table command(http://www.mssqltips.com/tip.asp?tip=1367).
MJ
November 24, 2009 at 5:51 pm
Any reason I can not use EXEC sp_msForEachTable @COMMAND1= 'DBCC DBREINDEX ( "?")' ?
After I run SHOWCONTIG they look fine afterwards.
I backed up and restored to a test server. I am not second guessing you, I am asking to learn.
What is the difference between the two? One obviously allows me to select the fill value, anything else?
I am writting the procedure as I type using DBCC DBREINDEX, so I am doing the perferred method. I am just curious what the difference is.
November 30, 2009 at 2:03 am
TechJunky (11/24/2009)
Any reason I can not use EXEC sp_msForEachTable @COMMAND1= 'DBCC DBREINDEX ( "?")' ?After I run SHOWCONTIG they look fine afterwards.
I backed up and restored to a test server. I am not second guessing you, I am asking to learn.
What is the difference between the two? One obviously allows me to select the fill value, anything else?
I am writting the procedure as I type using DBCC DBREINDEX, so I am doing the perferred method. I am just curious what the difference is.
Yes there is a very good reason: sp_msForEachTable is not documented, and may change at any time.
You can write a forward only cursor to loop through the tables and run a DBCC REINDEX on the more fragmented ones. In fact, there is a sample in Books Online to do that.
November 30, 2009 at 4:07 pm
If you look under the scripts section of this site, there are lots of Index Management scripts you can use.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
December 1, 2009 at 12:22 am
Good tip, Steve! I completely forgot about that. I'm going to have a look myself. 🙂
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply