SQL SERVER – Index Seek Vs. Index Scan (Table Scan)

SQL SERVER - Index Seek Vs. Index Scan (Table Scan) indexfinger Index Scan retrieves all the rows from the table. Index Seek retrieves selective rows from the table.

Index Scan:
Since a scan touches every row in the table, whether or not it qualifies, the cost is proportional to the total number of rows in the table. Thus, a scan is an efficient strategy if the table is small or if most of the rows qualify for the predicate.

Index Seek:
Since a 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.

Index Scan is nothing but scanning on the data pages from the first page to the last page. If there is an index on a table, and if the query is touching a larger amount of data, which means the query is retrieving more than 50 percent or 90 percent of the data, and then the optimizer would just scan all the data pages to retrieve the data rows. If there is no index, then you might see a Table Scan (Index Scan) in the execution plan.

Index seeks are generally preferred for the highly selective queries. What that means is that the query is just requesting a fewer number of rows or just retrieving the other 10 (some documents says 15 percent) of the rows of 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.

Reference : Pinal Dave (https://blog.sqlauthority.com)

SQL Index, SQL Joins, SQL Scripts, SQL Server
Previous Post
SQL SERVER – Difference between DISTINCT and GROUP BY – Distinct vs Group By
Next Post
SQL SERVER – 2005 Best Practices Analyzer (February 2007 CTP)

Related Posts

107 Comments. Leave new

  • Hi Pinal Dave,

    I am trying to implement index by using substring function but its throwing an error so could u suggest me for better solution.

    Generally, implementing this substring function in index for getting the index seek on the query.

    Below is the syntax.

    create nonclustered index IX_CreatedBy on TransDetails
    (substring(createdby, charindex(‘\’,CreatedBy) + 1, len(CreatedBy)))

    in the db having the value like \
    at this moment cannot change the table structure for this query alone.

    Regards,
    Mohanraj Jayaraman

    Reply
  • Pinal,

    Your explainations are really amazing.

    I have seen couple of times with SQL Server 2000 that for some reason, query optimizer do not use index on its own and query finishes in about 8 min while if I give hint(explicit) then it returns output in less than a min. I checked index frag and update stats.. all are looking fine.

    Can you please advice ?

    Thanks,
    sunil

    Reply
  • Pinal,

    Here’s the query i was talking about.. I tested it in SQL 2000 and SQL 2005 both…

    DECLARE @StartDate DATETIME
    DECLARE @EndDate DATETIME
    SET @StartDate = ‘2008-09-11 16:00:00’
    SET @EndDate = ‘2008-09-12 00:00:00’
    SELECT * FROM PCHDotCom.dbo.BillMeEntries WITH (NOLOCK, INDEX=IX_BME_CREATED)
    WHERE
    Created >= @StartDate AND
    Created < @EndDate
    ORDER BY
    1 DESC

    If I remove the index hint, the query plan will decide to do clustered index scan and the query takes drastically longer

    Please help ASAP.

    Thanks,
    Sunil

    Reply
    • I will try with this, in WHERE ClusterdIndexColumn>=’min possible value’ and ClusterdIndexColumn= @StartDate AND Created = @StartDate AND
      Created =’min possible value’ and ClusterdIndexColumn<='max possible value'
      will replace
      select * from PCHDotCom.dbo.BillMeEntries
      and instead of SCAN it will use SEEK of ClusteredIndex
      and if you have additional columns in WHERE which belong to NonClusteredIndex it will improve NonClusterdIndex if you have all columns of Include in SELECT list, but still if in select list is column which is not part of INCLUDE, then it will use SEEK (not SCAN) of ClusteredIndex

      Reply
  • Hi,

    Here is my Select statement

    UPDATE FCE
    SET
    –FCE.RECAT_CREDIT_CMT = RIID.CREDIT_CMT,
    FCE.RECAT_DEAL_ID = RIID.DEAL_ID,
    FCE.RECAT_STATUS = RIID.STATUS,
    FCE.RECAT_STATUS_DT = RIID.CLM_STATUS_DT,
    FCE.RECAT_EUV_FLAG_ITEM = RIID.EUV_LINE_FLAG,
    FCE.RECAT_EUV_FLAG = RIID.EUV_FLAG,
    FCE.M_CODE = RIID.MCODE,
    FCE.RECAT_EUV_SUBMIT_DT =RIID.EUV_SUBMIT_DT,
    FCE.RECAT_EUV_CONTROL_DT =RIID.EUV_CONTROL_DT,
    FCE.STAKO_DEAL=RIID.STAKO_DEAL,
    FCE.MMP_FLAG=RIID.MMP_FLAG
    FROM FinClaimEntries FCE, RECAT_ITEMS RIID
    where FCE.CLAIM_ID = RIID.ECLAIM_ID
    AND (FCE.recat_prod_item_id = RIID.ECLM_ITEM_ID
    OR FCE.recat_bdl_item_id = RIID.ECLM_ITEM_ID
    OR FCE.CLAIM_ITEM_ID = RIID.ECLM_ITEM_ID
    )
    AND RIID.CLM_ID = (SELECT MAX(CLM_ID) FROM RECAT_ITEMS RIID2 where FCE.CLAIM_ID = RIID2.ECLAIM_ID )

    Here is Number of Records Details

    FinClaimEntries – 78,589,25
    RECAT_ITEMS – 97,256,86

    Please let me know yours suggestion for Index Creation & Joins

    Thanks,
    Sunil

    Reply
  • Johnny - Greece
    October 20, 2008 2:17 pm

    Hi to all,

    I should optimize a very simple query (select) where the actual execution plan presents a clustered index cost of 56 % due to estimated cost operator. how can i reduce this cost??

    thanks

    Reply
  • Hi Pinal,
    Could you explain the Previous syntax:
    SELECT * FROM PCHDotCom.dbo.BillMeEntries WITH (NOLOCK, INDEX=IX_BME_CREATED)
    WHERE
    Created >= @StartDate AND
    Created < @EndDate
    ORDER BY
    1 DESC
    =====
    Is the Inde = index name a Valid syntax if so how does it help performance wise??

    Reply
  • Hi Pinal,

    I have a table with abt 200K records. a “where clause” on a non-indexed field causes a “clustered index scan”. It works fine and fast, but perf degrades when concurrent users increase.

    do you think a clustered index scan can be a good reason for perf hit with increase in concurrent users even in a table of this size. OR maybe i should keep looking further for other causes.

    rgds

    Ajay

    Reply
  • Imran Mohammed
    January 10, 2009 2:10 am

    @Ajay,

    If you have sql server 2000 then
    1. Why dont you run Index Tuning Wizard, analyze your query, result of which recommends any improvements can be done on indexes for that query.

    If you have SQL Server 2005,
    1. Use Database Engine Tuning Advisor. This does the same thing, it will give you recommendations about indexes for that particular query.

    Regards,
    IM.

    Reply
  • Hi Pinal,
    I wanted to ask if there is something similar to the horizontal partitioning mechanism(automatic physical distribution of partitioned table in ms SQL 2005 SE).
    Would be very useful if you could suggest something similar to this mechanism(if the same is not available in MS SQL 2005 SE)

    Reply
  • Is there a way to make mssql aware that it needs to do an index seek rather than a table scan? The proper indexes exist, and the statistics have been updated to my knowledge. On one system, a join between two tables results in a table scan in one table. One a separate database with the same schema/indexes, it does an index seek on both tables in the join. How do I make mssql aware it needs to do an index seek on the one system?

    Reply
  • i have a query the where clause which is convered in non-clustered index and partial non-key column which is convered in the non-clustered index as well. But the execution plan show me the clustered index scan = 98 %, can i have any solution to tunning this scenerio ?

    Reply
    • if in SELECT you have * which mean all columns it will use clustered index.
      If you want to be used non-clustered index then in SELECT list should be columns of non-clustered index or INCLUDED columns of non-clustered index.
      If in SELECT is column which is not part of INCLUDE list of non-clustered index then query will use SCAN of Clustered Index. If you want SEEK of Clustered Index then in WHERE add this condition ClusterdIndexColumn>=minposiblevalue (and ClusterdIndexColumn<=maxposiblevalue)

      Reply
  • There is some difference between Index scan and Table scan, that is, when we have index scan sql engine locks only PK not whole ROW, and because of that is faster than table scan

    Reply
  • Pinal,

    An Index Scan is not the same as a table scan. A “CLUSTERED” index scan is the same as a table scan.

    Reply
  • This is a wonderful topic and I’m glad I found it as it’s the exact answer to a quest that I had about a table today. It helped reduce my query time from 14 seconds to less than 1!

    Great work!
    Tim

    Reply
  • Hi,

    I have a select statement. How do I know if it is using the index which i created on one of the columns (and this column is included in the select list)

    Reply
  • how tune any query

    Reply
  • hi Pinal ,

    i have 10 ten queries on three tables with 10 where conditions on one table.in that 10 ten queries 3 queries uses
    where condition below

    where city= and state=? and country=?

    how indexes i need to create on these table(one or more).

    this table getting truncate abd loaded everyday.

    Please advise.

    Reply
    • Marko Parkkola
      April 27, 2010 11:06 am

      Hi,

      Have you tried Sql Server Database Tuning Advisor? It gives you nice list of indexes it thinks would be good. You can even tell it to suggest indexed views.

      It’s hard to say which columns you should index just by your where-statement. You could try to index all of those three columns, index per column. After that check Execution Plan to see if indexes are being used or not.

      Reply
  • Hi Kumar,

    I think you can create a composite Index on the three columns instead of creating indexes on each and every column to reduce space and to optimize the query performance. Correct me any body if i am wrong.

    Reply
    • Marko Parkkola
      April 30, 2010 8:57 pm

      Yes, you can. But if the columns aren’t selective enough you risk that your composite index isn’t used at all. Also the order of columns inside the index is significant. You have to test and test and test again to see if the index is used or not.

      I would suggest you create the index and check execution plan to see if it’s used. Then wait couple of days so that database can collect enough statistics and check it again. Finally sys.dm_db_index_physical_stats function tells a lot what’s going on with your indexes.

      For example I created four composite index in a single table on one of your databases. All of them were used initially but eventually two of them was rendered useless so I dropped them and DB is running like charm.

      Reply
  • Why would sql suddenly change from an index seek configuration to an index scan configuration? We went from screaming fast to no go in one afternoon. What would trigger the change? Since there were no changes made to the database other than additional data, what would automatically trigger this change in sql?

    Reply
  • Hi Pinal,
    I have a base view on top of which there are several views.
    One of the views which is based on the base view gives timeout errors.

    In the execution plan it shows one index seek takes Cost 80%

    There are appropriate indexes on the tables.
    Can you suggest any more optimization.

    Reply

Leave a Reply