The Multifunctioning DBA

Aug 23 2012   3:17PM GMT

Index Fragmenting on Purpose

Colin Smith Colin Smith Profile: Colin Smith

In most cases we would like to reduce fragmentation and try to come up with ways, like reducing fill factor, to minimize the amount of fragmentation that occurs when doing inserts and updates. I , however, need to create fragmentation. I am sure that you are asking yourself why I might need to do this. That is a great question and the answer is because I need to test some re-indexing scripts and get timings on how long they are going to run. To accomplish this, I am going to use another feature of SQL Server that I try to NEVER use. The dreaded Shrink Database. The reason I am using shrink database is that it, by its nature, causes fragmentation.  So today is just a day of worst practices for me. I am doing this is a sandbox testing environment and I would not recommend ever using Shrink Database Unless you absolutely have to. If you do run Shrink Database for any reason, just know that you are going to need to clean up your indexes after and that is going to grow your database again.

 Comment on this Post

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

Share this item with your network: