how can i analyze the results of the sql query : dbcc showcontig to improve the performance of sql
A:
The results from SHOWCONTIG are relevant only if you have a clustered index on the table. Books Online has a nice section about this.
Depending on the fragmentation level (~20% - 30%), you will decide whether to simply defrag the indexes, or rebuild them completely. This also depends on the number of changed rows, the total number of data pages, etc.
If you choose to defrag the indexes, remember to update your statistics as a separate step. This happens automatically when you rebuild.
EDIT: (From Books Online for SQL 2008)
This feature will be removed in the next version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use sys.dm_db_index_physical_stats instead.
Randolph Potter
2010-01-08 11:24:53
send the link to the nice page on msdn please
Microgen
2010-01-08 11:32:56
This is to the SQL 2000 page, but the information is still relevant: http://msdn.microsoft.com/en-us/library/aa258803%28SQL.80,lightweight%29.aspx. Here's a link to Paul Randal's blog for more information about this section: http://www.sqlskills.com/BLOGS/PAUL/post/Where-do-the-Books-Online-index-fragmentation-thresholds-come-from.aspx
Randolph Potter
2010-01-08 12:34:31