October 19, 2007 at 3:51 am
I get some information about index using dm_db_index_physical_stats.
On the basis of this information I rebuild or reorganize some indexs.
Problem is when after that operation i get again some information about indexs, it tells me that I should rebuild it again.
October 19, 2007 at 4:11 am
How large are your tables? If the table size is small, like only a few pages, then the rebuild will not only result little or no speed improvement, it will also not change fragmentation much.
Another thing, is your table clustered?
Regards,
Andras
October 19, 2007 at 4:20 am
Take a look of this information, which I think might be useful to you. Please read and try to implement with the same methodology listed overhere.
October 19, 2007 at 5:50 am
For example i have table with 31721 rows this table takes 250MB.
This table has clustered index and 5 non-clustered.
dm_db_index_physical_stats show me that
avg_fragmentation_in_percent = 66.6666666666667
avg_page_space_used_in_percent = 73.9189523103533
October 19, 2007 at 11:38 am
Can you post the before and after (removing fragmentation) results from the DMV for each index?
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
October 19, 2007 at 3:20 pm
tklimczak (10/19/2007)
For example i have table with 31721 rows this table takes 250MB.This table has clustered index and 5 non-clustered.
dm_db_index_physical_stats show me that
avg_fragmentation_in_percent = 66.6666666666667
avg_page_space_used_in_percent = 73.9189523103533
You are *not* shrinking the database after the index rebuild, right?
* Noel
October 22, 2007 at 5:39 am
it works now, tx 🙂
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply