Conference Questions Pot-Pourri #3: unexpected index rebuild results and bulk-logged operations

Q1) Why doesn’t performing an index rebuild alter the fragmentation?

A1) Here are the possibilities – all of which I’ve seen happen:

• There isn’t an index – either DBCC DBREINDEX or ALTER INDEX … REBUILD are being run on a table that only has a heap, and so the (extent) fragmentation of the heap isn’t changing because there’s no way to rebuild a heap (except by the heavily NOT recommended method of creating and then dropping a clustered index).
  
• The index is too small. An index with only a handful of pages may not show any changes in fragmentation because all the pages are single, mixed pages (see my previous post on extent types for more info) and so rebuilding the index does nothing.
  
• The workload and schema are such that by the time the rebuild has finished and the fragmentation calculation has been done again, the index is already getting fragmented again.
  
• The Extent Scan Fragmentation result from DBCC SHOWCONTIG is being used to gauge fragmentation for an index stored in a filegroup with multiple files. The Extent Scan Fragmentation in DBCC SHOWCONTIG does not cope with multiple files (as is documented in Books Online) and so the value may even go UP in some cases!

Q2) What operations take advantage of minimal-logging when the recovery mode is BULK_LOGGED?

A2) The list is very small – 4 four classes of operations:

• Index builds, rebuilds, or drop of a clustered index (NOT index defrags with DBCC INDEXDEFRAG or ALTER INDEX … REORGANIZE – this is a common misconception).
  
• Bulk load operations (i.e. BCP, INSERT … SELECT * FROM OPENROWSET (BULK…), and BULK INSERT).
  
• Insert or appends of LOB data (either using WRITETEXT/UPDATETEXT for TEXT/NTEXT/IMAGE data types, or UPDATE with a .WRITE clause).
  
• SELECT INTO operations on permanent tables.

For these operations, only the allocations are logged in the transaction log. Any extents that are allocated and changed through a minimally-logged operation are marked in the ML bitmaps (one for every 4GB of each file) and then the next transaction log backup will also read all those extents and include them in the backup.