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.
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.
What is your main criteria of running CREATE and DROP INDEX on this table? How to Detect Table Fragmentation in SQL Server 2000 and 2005 is the method you can find the fragmentation and method of reduce it so.