locked
Index Seek,Index Scan, Table scan. RRS feed

  • Question

  • which one is the performance killer for SQL server ? and which one increase the performance of the SQL Server ?

    1) Index Seek.

    2) Index Scan.

    3) Table Scan. ???

    Monday, August 29, 2016 3:01 PM

Answers

  • Index Scan a scan is an efficient strategy if thetable is small or if most of the rows qualify for the predicate.

    seek only touches rows that qualify and pages that contain these qualifying rows, the cost is proportional to the number of qualifying rows and pages rather than to the total number of rows in the table.

    In general query optimizer tries to use an Index Seek which means that the optimizer has found a useful index to retrieve recordset. But if it is not able to do so either because there is no index or no useful indexes on the table, then SQL Server has to scan all the records that satisfy the query condition.

    http://blog.sqlauthority.com/2007/03/30/sql-server-index-seek-vs-index-scan-table-scan/

    By finding and fixing your Index Scans and Table Scans you can drastically improve performance especially for larger tables.  So take the time to identify where your scans may be occurring and create the necessary indexes to solve the problem.  One thing that you should be aware of is thattoo many indexes also causes issues, so make sure you keep a balance on how many indexes you create for a particular table.

    https://www.mssqltips.com/sqlservertutorial/277/index-scans-and-table-scans/


    Please click Mark As Answer if my post helped.


    Monday, August 29, 2016 3:07 PM
  • Hello Kandan,

    Basically it depends on number of rows , type of queries & index types , It depends on the requirements too, I think below link will help you to understand the concept of Index Seek, Index Scan & Table Scan

    Identifying and Solving Index Scan Problems

    Index Scans and Table Scans

    Please Mark me as answer if my post helps you.

    Regards

    ChetanV


    Monday, August 29, 2016 3:33 PM

All replies

  • Index Scan a scan is an efficient strategy if thetable is small or if most of the rows qualify for the predicate.

    seek only touches rows that qualify and pages that contain these qualifying rows, the cost is proportional to the number of qualifying rows and pages rather than to the total number of rows in the table.

    In general query optimizer tries to use an Index Seek which means that the optimizer has found a useful index to retrieve recordset. But if it is not able to do so either because there is no index or no useful indexes on the table, then SQL Server has to scan all the records that satisfy the query condition.

    http://blog.sqlauthority.com/2007/03/30/sql-server-index-seek-vs-index-scan-table-scan/

    By finding and fixing your Index Scans and Table Scans you can drastically improve performance especially for larger tables.  So take the time to identify where your scans may be occurring and create the necessary indexes to solve the problem.  One thing that you should be aware of is thattoo many indexes also causes issues, so make sure you keep a balance on how many indexes you create for a particular table.

    https://www.mssqltips.com/sqlservertutorial/277/index-scans-and-table-scans/


    Please click Mark As Answer if my post helped.


    Monday, August 29, 2016 3:07 PM
  • Hello Kandan,

    Basically it depends on number of rows , type of queries & index types , It depends on the requirements too, I think below link will help you to understand the concept of Index Seek, Index Scan & Table Scan

    Identifying and Solving Index Scan Problems

    Index Scans and Table Scans

    Please Mark me as answer if my post helps you.

    Regards

    ChetanV


    Monday, August 29, 2016 3:33 PM