Query Performance

Hi Gurus,

I have run DBCC SHOWCONTIG statement against the table X as shown below:

DBCC SHOWCONTIG (X)
GO

and received the following result:

DBCC SHOWCONTIG scanning 'X' table...
Table: 'X' (885578193); index ID: 1, database ID: 10
TABLE level scan performed.
- Pages Scanned........................: 1400
- Extents Scanned.......................: 300
- Extent Switches.......................: 300
- Avg. Pages per Extent..................: 4.7
- Scan Density [Best Count:Actual Count]......: 50.00% [200:400]
- Logical Scan Fragmentation ..............: 21.43%
- Extent Scan Fragmentation ...............: 33.33%
- Avg. Bytes Free per Page................: 2107.1
- Avg. Page Density (full)................: 73.97%

Which one of the following is more recommended way to increase query performance against the table X?

A. Run DBCC DBREINDEX statement with high fill factor value to rebuild the clustered index.
B. Run DBCC DBREINDEX statement with low fill factor value to rebuild the clustered index.
C. Drop and re-create the clustered index by using DROP INDEX and CREATE INDEX statements with low fill factor value.
D. Drop and re-create the clustered index by using DROP INDEX and CREATE INDEX statements with high fill factor value.
E. Set the truncate log on checkpoint option for the database which contain the Employee table.

Thank youIf they are asking for high query performance, then you don't want a low fill factor, you want as much data per data page. That being said, I would choose (A). (D) would be OK too but it is one more process as you would drop the clustered, recreate it, then any non-clustereds would be rebuilt, that's three steps instead of two.

HTH|||HTH,

Thanks very much.