Indexes

• Igor Micev

  SSC-Dedicated

  Points: 33109

  More actions

  July 23, 2013 at 3:35 pm

  #280552

  Hi All,

  I have a table [BusinessProcesses] with BpId as primary/clustered key/index on it.

  I have the following index on column CorrespondingContractNumber:

  CREATE NONCLUSTERED INDEX [IBPCorrespondingContractNumber] ON [dbo].[BusinessProcesses]

  ([CorrespondingContractNumber] ASC)

  I execute:

  dbcc show_statistics('dbo.BusinessProcesses','IBPCorrespondingContractNumber')

  /*

  All densityAverage LengthColumns

  8.592541E-051.272107CorrespondingContractNumber

  1.788491E-085.272107CorrespondingContractNumber, BPId

  */

  I create the next index:

  CREATE NONCLUSTERED INDEX [IBPCorrespondingContractNumber2] ON [dbo].[BusinessProcesses]

  ([CorrespondingContractNumber] ASC)

  INCLUDE(BpId)

  I execute:

  dbcc show_statistics('dbo.BusinessProcesses','IBPCorrespondingContractNumber2')

  /*

  All densityAverage LengthColumns

  3.906723E-061.311324CorrespondingContractNumber

  */

  The second index is more selective.

  I execute this example query:

  select CorrespondingContractNumber

  from BusinessProcesses

  where CorrespondingContractNumber is not null

  and the second index is being used by the query optimizer. Conclusion: The second index (IBPCorrespondingContractNumber2) is narrower and the optimizer simply uses it.

  Then i create

  CREATE NONCLUSTERED INDEX [IBPCorrespondingContractNumber3] ON [dbo].[BusinessProcesses]

  ([CorrespondingContractNumber] ASC,

  bpid asc)

  dbcc show_statistics('dbo.BusinessProcesses','IBPCorrespondingContractNumber3')

  /*

  All densityAverage LengthColumns

  3.906723E-061.311324CorrespondingContractNumber

  1.788491E-085.311324CorrespondingContractNumber, BPId

  */

  I execute this example query:

  select CorrespondingContractNumber

  from BusinessProcesses

  where CorrespondingContractNumber is not null

  Now the optimizer uses IBPCorrespondingContractNumber3 which is not narrower thatn IBPCorrespondingContractNumber2

  I need someones of you to put here your thinking about this...

  Thank you in advance,

  IgorMi

  Igor Micev,My blog: www.igormicev.com

• Sean Pearce

  SSCoach

  Points: 15999

  More actions

  July 24, 2013 at 3:36 am

  #1635255

  I suspect the index used by the optimizer is smaller, rather than narrower.

  SELECT

  i.name,

  SUM(ips.page_count) AS page_count,

  MAX(ips.index_depth) AS index_depth

  FROM

  sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('BusinessProcesses'), NULL, NULL, 'DETAILED') ips

  INNER JOIN

  sys.indexes i

  ON ips.object_id = i.object_id

  AND ips.index_id = i.index_id

  GROUP BY

  i.name

  ORDER BY

  page_count DESC;

  

  The SQL Guy @ blogspot[/url]

  @SeanPearceSQL

  About Me[/url]

• Igor Micev

  SSC-Dedicated

  Points: 33109

  More actions

  July 24, 2013 at 3:47 am

  #1635262

  Hi

  This is the output from your query:

  namepage_countindex_depth

  IBPCorrespondingContractNumber798454

  IBPCorrespondingContractNumber2795023

  IBPCorrespondingContractNumber3795023

  The clustered index on BpId:

  namepage_countindex_depth

  IBPContentId5763204

  It seems it's not the size, especially for the last two indexes.

  Thank you, anyway.

  Igor Micev,My blog: www.igormicev.com