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)
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
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
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
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
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
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
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??
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
@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.
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)
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?
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 ?
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)
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
Pinal,
An Index Scan is not the same as a table scan. A “CLUSTERED” index scan is the same as a table scan.
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
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)
Easy. Insert the select statement to Sql Server Management Studio and press CTRL+L. Check Execution Plan if it’s using Index Seek or Index Scan.
Check this MSDN article also:
https://docs.microsoft.com/en-us/sql/relational-databases/performance/display-an-actual-execution-plan?view=sql-server-2017
These articles and their subarticles might help also:
https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms191227(v=sql.105)
https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms345417(v=sql.105)
how tune any query
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.
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.
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.
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.
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?
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.