Creating Index take a long time

• SQLRNNR

  SSC Guru

  Points: 281324

  More actions

  August 7, 2014 at 8:20 am

  #1735994

  Shurgenz (8/4/2014)

  ---

  when creating the new index on a large table that already has some indexes, the new index could take a long time when tehre is alredy index with indexed columns in it that will be in a new index. in such case SQL Server use that index bookmarking to the clustered index taking from there the columns needed for the new, which do not exists in the index.

  for example, table T is declared as T (ID int, A char, B char, C Char)

  it has say, a clustered index CLIX_ID on ID, nonclustered IX_A on A

  if You are creating the new index as INDEX IX_A_inc_B on A include(B), it scans IX_A, taking A from it, and bookmarks CLIX_ID to take B. The good way to create IX_A_inc_B faster is to drop IX_A first, then create IX_A_inc_B and then recreate IX_A

  in this way first it would scan CLIX_ID when creating IX_A_inc_B and then IX_A_inc_B to create IX_A

  in my tests i've got the execution time reduced from 3 hours to 10 minutes

  IN this case, why would you create more duplicate indexes? If the index needed is just a matter of adding a column or columns to the definition of an index, why not alter the existing index? Creating multiple indexes with these kinds of definitions will indeed slow things down. They are duplicate indexes and should be re-examined for better implementation.

  Jason...AKA CirqueDeSQLeil
  _______________________________________________
  I have given a name to my pain...MCM SQL Server, MVP
  SQL RNNR
  Posting Performance Based Questions - Gail Shaw[/url]
  Learn Extended Events