Sql-server – Differences Between Two Different Create Index Commands

OK, enough brain cells are dead.

SQL Fiddle

WITH cte AS
(
  SELECT 
    [ICFilterID], 
    [ParentID],
    [FilterDesc],
    [Active],
    CAST(0 AS varbinary(max)) AS Level
  FROM [dbo].[ICFilters]
  WHERE [ParentID] = 0
  UNION ALL
  SELECT 
    i.[ICFilterID], 
    i.[ParentID],
    i.[FilterDesc],
    i.[Active],  
    Level + CAST(i.[ICFilterID] AS varbinary(max)) AS Level
  FROM [dbo].[ICFilters] i
  INNER JOIN cte c
    ON c.[ICFilterID] = i.[ParentID]
)

SELECT 
  [ICFilterID], 
  [ParentID],
  [FilterDesc],
  [Active]
FROM cte
ORDER BY [Level];

Achieving optimal import performance in this scenario requires three things:

1. Minimally-logged base table inserts
2. Minimally-logged nonclustered index builds
3. Avoiding physical reads

Minimal Logging

Achieving minimally-logged inserts to an empty clustered table without nonclustered indexes requires:

1. Using either the SIMPLE or BULK_LOGGED database recovery models
2. Specifying a table lock and ordered input (e.g. TABLOCK and ORDER hints)

Side note:

It is also possible to achieve minimally-logged inserts to a clustered table that has nonclustered indexes provided trace flag 610 is enabled. Whether nonclustered index inserts are minimally-logged or not depends on the query plan selected by the query optimizer.

If the query plan uses a separate iterator for the nonclustered index, and the iterator has the DMLRequestSort property set to true, the nonclustered index inserts will be minimally logged, provided the other conditions mentioned previously are met.

Building nonclustered indexes separately

The advantages of doing this are:

1. Clustered index inserts can be minimally-logged without enabling TF 610
2. CREATE INDEX is minimally logged if the recovery model is not FULL

Avoiding physical reads

Ideally, the data to be imported will be stored on a separate machine, or at least on separate physical storage from that used to host the database.

The database server should have enough memory to hold the largest base table in cache, with enough left over for sorting operations necessary when building nonclustered indexes.

A good pattern is to fast-load the base table (minimally-logged clustered index load) and then to build all nonclustered indexes for that table while its data pages are still cached.

The question outlines a process whereby base tables are loaded first, and then nonclustered indexes built. The cursor definition does not use an ORDER BY clause to at least group nonclustered index builds on the same table together.

The likely result is that data pages for different tables are repeatedly read into cache and then discarded as nonclustered indexes are built in a non-deterministic order.

The cost of repeated physical reads completely dominates the benefits of minimal logging gained by building nonclustered indexes separately. This explains why you found that loading tables with existing indexes is faster (because all nonclustered indexes for a given table are maintained before moving on to the next table).

Summary

The import process should be reworked to bulk-load one table at a time. This means loading the table and building all nonclustered indexes before moving on to the next one. The SQL Server instance should have enough memory available to hold the largest table and perform the largest nonclustered index sort at the same time.

You could also try enabling TF 610 before loading the data into tables with nonclustered indexes already in place. This is not usually as fast as the previous method, but it may be fast enough.

See the following for more information:

The Data Loading Performance Guide

Operations That Can Be Minimally Logged