January 27, 2009 at 5:03 am
Hi All,
I am testing the script to rebuild the indexes based on fragmentation level.
In my current database there is no indexes having logical fragmentation level more than 20.
How do I update the logical fragmentation level of the index.
Is there any script to update
Please help me on this.
Rajesh Kasturi
January 27, 2009 at 5:16 am
Rajesh kasturi (1/27/2009)
How do I update the logical fragmentation level of the index.
You can't. The logical fragmentation is calculated from the way the pages are arranged on disk. It's not just a value.
If you want to really fragment a database (providing it's a test database), shrink it (use the no_truncate option on the shrinkfile). That should send the fragmentation of some indexes to 90% or higher. Don't do that on a production DB.
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
January 27, 2009 at 8:59 am
Thanks for the reply, I will try and let you know.
Could you please explain me how no_truncate will increase the fragmentaion level.
Rajesh Kasturi
January 27, 2009 at 9:06 am
It's not the notruncate that will do it. I just put that in place so that the file size won't reduce and force grows. It's the shrink itself. Shrink works by moving pages from the end of the file to the beginning. In doing so, it can completely reverse the order of the index. Since fragmentation is the % of pages out of order, an index that's been reversed is highly fragmented.
See - http://sqlinthewild.co.za/index.php/2007/09/08/shrinking-databases/
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply