Table and Index fragmentation | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Table and Index fragmentation

Hello, I was wondering, how can we find out Table and Index fragmentation on SQL Server databases? Any help will be greatly apprecaited. Thanks,

You can use DBCC SHOWCONTIG (table_name, index_name) Check BOL for details
http://www.sql-server-performance.com/dt_dbcc_showcontig.asp to understand the output. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
See if this helps:http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)

My 2c,
http://www.sql-server-performance.com/tp_automatic_reindexing.asp Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided “AS IS” with no warranties for accuracy.
Thank you guys for replying to my question. I’ve another question now. Step 1: When I ran DBCC SHOWCONTIG (‘MYTABLE’). I see a output generated. Should I assume this detail output whats generated is only for "Mytable" or this includes all the indexes details aswell? In simple, Is there a way, I just see what tables are fragmented and not indexes and vice-versa? But just curious about the step 1 results. Apprecaited if someone please share the answers. Thanks in advance.

From Book Online: The DBCC SHOWCONTIG statement traverses the page chain at the leaf level of the specified index when index_id is specified. If only table_id is specified, or if index_id is 0, the data pages of the specified table are scanned.
Regards, Chetan Jain
DBA_user
Check the link provided in this website about SHOWCONTIG output. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
Thanks for the answers. Still not clear on this.. Sorry. This is my first time exposure to SQL Server. But Satya this is for you and would greatly apprecaite, If you please answer this. When i run this command: DBCC DBREINDEX (‘MYTABLE’,”,90) — Should i assume this will rebuild my table and all of the indexes on it OR just the table not indexes? How can i rebuild a table (fragmented table) and all of its indexes on it with one command? I assume the above command does that. But just want to make sure. appreciate if you share the answer. Thanks,
About "DBCC DBREINDEX (‘MYTABLE’,”,90)", yes all indexes. About "and all of its indexes on it with one command?", I suggest to use link I write before, because that store procedure only defrag indexes according fragmentation you choose. Said when any index has below 85 ( you can put any %), that or those indexes will reindex.
Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided “AS IS” with no warranties for accuracy.
True that rebuilds all indexes on the table using a fillfactor value and ensure to run during less traffic hours. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
Guys, I’m missing the answers completely. When I say "DBCC DBREINDEX (‘MYTABLE’,”,90)" Does this command rebuilds only indexes on a table or it rebuilds table + indexes? Which one does it do? Can you guys be little clear here please? If you please list all the steps to rebuild a table + all of the indexes on it, that will be great. In the earlier post, most of them replied, when i run the above command, it will only rebuilds the indexes. My question is, how can i rebuild a table aswell? Finally is there a way, We could rebuild a fragmented table and all of its indexes in one command? If yes, could you please share that with me????
Another question. When i run DBCC SHOWCONTIG (tblNames), Systems takes a while and displays the results. I was wondering, what results are these? I.E, Are these Table or Index fragmentation results or just either one of them? Could anyone please clear this mystery? Appreciated all of your help so far. Thanks again,
Hi,
My understanding is that if your table has a clustered index on it, then yes, the table will be rebuilt as well. From BOL:
When you create a clustered index, the table is copied, the data in the table is sorted, and then the original table is deleted. Therefore, enough empty space must exist in the database to hold a copy of the data. So if you rebuild the clustered index, i think it is fair to infer that the table will be sorted and rebuilt too Ben ‘I reject your reality and substitute my own’ – Adam Savage
Yes and if there is no clustered index then it will rebuild all non-clustered indexes, as a good chance you can create clustered index and drop it if disk space is a concern. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
Make sure you read closely through
http://support.microsoft.com/default.aspx?scid=kb;en-us;304519 and the link I’ve posted. Especiallyhttp://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx#EEAA should provide the answer you ask for. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)

]]>