August 12, 2005 at 9:12 am
I am working on indexes in my database.
This is what I did.
1) Run DBCC SHOWCONTIG to look at the results of the table i.e fragmentation & all that.
2) Dropped the index one by one and created them again using create non-clustered index command on the tables.
3) RUN dbcc showcontig (tablename) again and THE RESULTS ARE THE SAME. NO DIFFERENCE. WHY??
4) Ran dbcc dbreindex(tablename) and got improvement in results...scan density went up, logical frags went down and other changes as well.
My question is my does dropping the index and re-creating them gives the same results for showcontig??????????? What is a better way anyway of reducing fragments & boosting performance?
August 12, 2005 at 9:38 am
Hi There,
I have two questions which will probably help answer your question.
1. Do you have a clustered index on the table you're looking at?
2. Is the table split across more than physical file (mdf)?
I'm guessing the answer to the first may be no, which explains why you're not getting any benefit from dropping and recreating the indexes (but reindexing does seem to help) and the second is a possible yes (but not necessarily) as the results are always skewed when a table spans multiple files.
August 12, 2005 at 9:43 am
Answers to the above questions are :-
1) Do you have a clustered index on the table you're looking at? YES, THERE IS A PRIMARY KEYS ATTACHED TO THOSE TABLES.
2) Is the table split across more than physical file (mdf)? NO.
awaiting for your further response.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply