Hi, I am a new into sql field. I want to know that when we have to decide to rebuild indexes. What kind of cammands i have to run and how to analyze thier result before going to rebuild index.I ran DBCC SHOWCONTIG (Tablename) anf got the following results. - Pages Scanned................................: 3 - Extents Scanned..............................: 2 - Extent Switches..............................: 1 - Avg. Pages per Extent........................: 1.0 - Scan Density [Best Count:Actual Count].......: 50.00% [2:1] - Logical Scan Fragmentation ..................: 0.00% - Extent Scan Fragmentation ...................: 0.00% - Avg. Bytes Free per Page.....................: 4945.0 - Avg. Page Density (full).....................: 38.91% DBCC execution completed. If DBCC printed error messages, contact your system administrator. I know that Scan Density should be close to 100% and in my case its 50%.I can not go to rebuild my index on on this concolusion. Can some one tell whatelse and where else i have to look to know that these indexes needs to be rebuild or refrag. Thanks in Advance.
Wondered who owns SQL Server 20000 version [], just kidding. In order to get more understanding on defragmentation of indexes refer to following linsk: http://www.sql-server-performance.com/articles/per/detect_fragmentation_sql2000_sql2005_p1.aspx http://www.sql-server-performance.com/articles/per/index_fragmentation_p1.aspx http://sqlserver-qa.net/blogs/tools...server-index-optimization-best-practices.aspx
In SQL Books Online under DBCC SHOWCONTIG - there is a good script , that checks for levels of logical fragmentation - and only when it's a certain level will it do a defrag. You have the option to change the level. Deciding at what percentage defrag to instigate the defrag level , is dependant on your system ie. are there lots of INSERTS , UPDATES etc