index seek and scan

• PiMané

  SSChampion

  Points: 13965

  More actions

  October 3, 2012 at 5:32 am

  #1544823

  If you create an index with (id, ch, na) and search for ch or na just add Id > 0 if all id values are positive.

  If the 1st column on the index is a string not null just add column IS NOT NULL... you will get index seek.

  ---

  
  If you need to work better, try working less...

• Zeal-DBA

  SSCarpal Tunnel

  Points: 4332

  More actions

  October 3, 2012 at 5:35 am

  #1544824

  hmmm thats what i expected, its a great idea... it definitely shows index seek, but practically it really improves performance or it just shows index seek???

• laurie-789651

  SSCertifiable

  Points: 7680

  More actions

  October 3, 2012 at 5:44 am

  #1544826

  Zeal-DBA (10/3/2012)

  ---

  one last question,

  suppose i am using select queries like this frequently

  select * from T where id =6

  select * from T where ch ='!'

  select * from T where na ='sue'

  so i'll have to create all 3 indexes like

  CREATE NONCLUSTERED INDEX ind1 ON T (id,ch,na)

  CREATE NONCLUSTERED INDEX ind1 ON T (ch,id,na)

  CREATE NONCLUSTERED INDEX ind1 ON T (na,id,ch)

  to get better performance or incex seek for all the queries ??

  If you have an index id, ch, na & you seek on ch, SQL Server will not use the index because it's not in ch order. So you could, as Pedro says, force queries to have a seek value for id first, then whatever you want. As long as the first column in the index is included in the seek, the optimiser can use the index - Then you'd only need one index.

  Be careful about adding too many indexes - will the overhead be worth the benefit?

• laurie-789651

  SSCertifiable

  Points: 7680

  More actions

  October 3, 2012 at 5:45 am

  #1544827

  Zeal-DBA (10/3/2012)

  ---

  hmmm thats what i expected, its a great idea... it definitely shows index seek, but practically it really improves performance or it just shows index seek???

  Generally, index seeks are better than index or table scans because they involve reading less pages into memory, & the physical reads are the slowest part of query execution.

  So it's a real win.

• Gail Shaw

  SSC Guru

  Points: 1004514

  More actions

  October 3, 2012 at 6:41 am

  #1544872

  PiMané (10/3/2012)

  ---

  If you create an index with (id, ch, na) and search for ch or na just add Id > 0 if all id values are positive.

  If the 1st column on the index is a string not null just add column IS NOT NULL... you will get index seek.

  Index seek, yes, but it'll be a useless index seek that returns every row in the table.

  Adding predicates just to turn a scan into a seek is one thing and one thing only, a waste of typing.

  http://sqlinthewild.co.za/index.php/2009/03/05/when-is-a-seek-actually-a-scan/

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• Gail Shaw

  SSC Guru

  Points: 1004514

  More actions

  October 3, 2012 at 6:41 am

  #1544873

  Zeal-DBA (10/3/2012)

  ---

  one last question,

  suppose i am using select queries like this frequently

  select * from T where id =6

  select * from T where ch ='!'

  select * from T where na ='sue'

  so i'll have to create all 3 indexes like

  CREATE NONCLUSTERED INDEX ind1 ON T (id,ch,na)

  CREATE NONCLUSTERED INDEX ind1 ON T (ch,id,na)

  CREATE NONCLUSTERED INDEX ind1 ON T (na,id,ch)

  to get better performance or incex seek for all the queries ??

  Correct, you will need three separate indexes for all three queries to seek optimally.

  http://www.sqlservercentral.com/articles/Indexing/68439/

  http://www.sqlservercentral.com/articles/Indexing/68563/

  http://www.sqlservercentral.com/articles/Indexing/68636/

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• PiMané

  SSChampion

  Points: 13965

  More actions

  October 3, 2012 at 7:09 am

  #1544889

  GilaMonster (10/3/2012)

  ---

  Zeal-DBA (10/3/2012)

  ---

  one last question,

  suppose i am using select queries like this frequently

  select * from T where id =6

  select * from T where ch ='!'

  select * from T where na ='sue'

  so i'll have to create all 3 indexes like

  CREATE NONCLUSTERED INDEX ind1 ON T (id,ch,na)

  CREATE NONCLUSTERED INDEX ind1 ON T (ch,id,na)

  CREATE NONCLUSTERED INDEX ind1 ON T (na,id,ch)

  to get better performance or incex seek for all the queries ??

  Correct, you will need three separate indexes for all three queries to seek optimally.

  http://www.sqlservercentral.com/articles/Indexing/68439/

  http://www.sqlservercentral.com/articles/Indexing/68563/

  http://www.sqlservercentral.com/articles/Indexing/68636/

  If the three fields are the output and the query is only filtered on one of then isn't it better to have ?

  CREATE NONCLUSTERED INDEX ind1 ON T (id) INCLUDE (ch,na)

  CREATE NONCLUSTERED INDEX ind1 ON T (ch) INCLUDE (id,na)

  CREATE NONCLUSTERED INDEX ind1 ON T (na) INCLUDE (id,ch)

  Or is it the same in performance?

  I made a test to check what SQL did to the pages and in both cases, with full index and covered index, there were a LOP_MODIFY_ROW, LOP_DELETE_ROWS and LOP_INSERT_ROWS operations for both indexes, so the impact on read/writes is the same..

  Pedro

  ---

  
  If you need to work better, try working less...

• PiMané

  SSChampion

  Points: 13965

  More actions

  October 3, 2012 at 7:11 am

  #1544891

  GilaMonster (10/3/2012)

  ---

  Adding predicates just to turn a scan into a seek is one thing and one thing only, a waste of typing.

  http://sqlinthewild.co.za/index.php/2009/03/05/when-is-a-seek-actually-a-scan/

  Thanks for the enlightening... 🙂

  ---

  
  If you need to work better, try working less...

• Gail Shaw

  SSC Guru

  Points: 1004514

  More actions

  October 3, 2012 at 7:15 am

  #1544893

  PiMané (10/3/2012)

  ---

  If the three fields are the output and the query is only filtered on one of then isn't it better to have ?

  CREATE NONCLUSTERED INDEX ind1 ON T (id) INCLUDE (ch,na)

  CREATE NONCLUSTERED INDEX ind1 ON T (ch) INCLUDE (id,na)

  CREATE NONCLUSTERED INDEX ind1 ON T (na) INCLUDE (id,ch)

  Maybe, maybe not. Depends on the number of rows the queries return.

  What you've done by creating those three indexes as they are is quadrupled the space required for this table. So if that table was 10GB, by adding those 3 indexes you now need 40GB of space (on disk, in backups, etc)

  It's not the same write impact either. With single column indexes, an update affecting one column has to change the table and one column. With those indexes it'll need to change the table and 3 indexes.

  Personally I'd almost never consider making 3 nonclustered indexes that all have every single column of the table in them.

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• PiMané

  SSChampion

  Points: 13965

  More actions

  October 3, 2012 at 7:21 am

  #1544900

  GilaMonster (10/3/2012)

  ---

  Personally I'd almost never consider making 3 nonclustered indexes that all have every single column of the table in them.

  So, just like you answered before in a post I placed, if a few number of records is returned a Lookup isn't that bad with single column indexes considering the options...

  Pedro

  ---

  
  If you need to work better, try working less...