SQL Server Performance

DBCC SHOWCONTIG v& DROP INDEX followed by CREATE INDEX?

Discussion in 'Performance Tuning for DBAs' started by Jelly0228, May 8, 2008.

  1. Jelly0228 New Member

    I met a strange problem when defragmenting indexes by re-creating indexes.
    If I re-create indexes in SQL Server Enterprise Manager and then check fragmentation with DBCC SHOWCONTIG, value of SCAN DENSITY will be increased ,which means fragmentation situation has been improved. But if I try to defragment by running statement DROP INDEX followed by CREATE INDEX in SQL Query Analyzer, value of SCAN DENSITY of DBCC SOWCONG has no change. It seems defragmentation doesn't benefit from DROP/CREATE INDEX. DBCC DBREINDEX and DBCC INDEXDEFRG don't work too.
    Have you ever met such problems? Any suggestion? Thanks.
  2. satya Moderator

    Do you have any text/image type of columns on this table?
  3. Jelly0228 New Member

    Hi satya, thank you for you attention. There is no any text/image type of columns on the table. Since I met the same problem on all the SQL Server I take charge of, could you help me on you SQL Server to run DROP INDEX AND CREATE INDEX and check the DBCC SHOWCONTIG reusult please? I suspect that it's a weakness of SQL Server. Thanks.
  4. satya Moderator

Share This Page