Weekly DB Maintenance - DBREINDEX

Question

Weekly DB Maintenance - DBREINDEX

Cricketdogger

SSC Veteran

Points: 259
More actions
January 20, 2009 at 10:55 am

#97090

All,
As part of the weekly db maintenance, I run the following:
exec sp_MSforeachtable
@command1 = "Print '?'",
@command2 = "DBCC DBREINDEX ('?')"
Last week, I changed the DBREINDEX to be DBREINDEX ('?',' ',10). Two things happened. 1 - The database went from 20 gb to 128 gb. 2 - Performance has gone south.
I have since changed the DBREINDEX back, but the damage is done. Can someone please tell me what I should do to get back to where I was before I made that horrible decision to change the DBREINDEX. I need my small db back and performace to be back to normal.
Thanks in advance - I will be eternally grateful.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

george sibbald SSC Guru Points: 104210 More actions · Answer 1

you have told the reindex job to only use 10% of each page when it reorganises the data, hence you have loads of wasted space within each page, so the total space used by the data has grown. This will have increased your i/o to retrieve the same amount of data so performance has suffered

rerun you SQL with DBREINDEX ('?',' ',90),

---------------------------------------------------------------------

Cricketdogger SSC Veteran Points: 259 More actions · Answer 2

Thanks George. I got the table counts and re-indexed the smallest tables (up to 2500 rows) at 95, the next group were tables w\up to 5000 rows at 90 then I did the remaining tables at 80.

Then I turned on auto-shrink overnight and was very happy to see that this morning the database was only 56 gb (down from 128 gb). I will re-index again tonight and turn on auto-shrink and see if how close I can get to the original 20 gb.

Thanks for your help!

george sibbald SSC Guru Points: 104210 More actions · Answer 3

thanks for the feedback.

Please don't keep shrinking the file, it will fragment the database each time you do, also auto shrink could kick in at busy times, last thing you want. Get the database file down to a reasonable size that will fit your data plus room for growth and the extra space a reindex temporarily uses, and leave it there. Turn auto shrink off on production databases.

Also optimum fill factor is dependant on amount of inserts and updates which could cause a page split rather than size of table, but you are in a better place than you were!

---------------------------------------------------------------------

Cricketdogger SSC Veteran Points: 259 More actions · Answer 4

Hi George,

Just letting you know the final outcome is that I have the database down to 26 gb now!! I am ecstatic!! And auto shrink is turned off now. But I tell you what, it came through for me when I needed it!!

Thanks again.

george sibbald SSC Guru Points: 104210 More actions · Answer 5

good news. auto shrink (and shrink in general) is not without its uses, thats why its there, but is dangerous if not used with care! 🙂

---------------------------------------------------------------------

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Cricketdogger (1/21/2009)
Then I turned on auto-shrink overnight and was very happy to see that this morning the database was only 56 gb (down from 128 gb). I will re-index again tonight and turn on auto-shrink and see if how close I can get to the original 20 gb.

Do you realise by shrinking you've completely undone the reorganise operation?

Shrinking causes massive fragmentation and will just result in the data file growing again next time data gets added. When that happens, the entire system will slow down as the file is expanded. Also repeated shrinks and grows will cause fragmentation at the file-system level, which is hard to fix.

See - http://sqlinthewild.co.za/index.php/2007/09/08/shrinking-databases/

Turn autoshrink off. Free space inside a database file is not a problem.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

george sibbald SSC Guru Points: 104210 More actions · Answer 7

Gail, thanks for pointing this out, I've covered it with the OP (see previous posts) and I think we have one more DBA educated in the pitfalls of shrinking files. 🙂

---------------------------------------------------------------------

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

I just wanted to emphasise the fragmented indexes that it would cause.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

george sibbald SSC Guru Points: 104210 More actions · Answer 9

no probs,

---------------------------------------------------------------------