SQL Server : Index Seek with very high Scan Count [closed]

Posted on

Question :

I have a query that joins 2 tables:

  • Documents table, that has DocID as Unique Clustered Index
  • DocumentsRows table, that has DocID, RowID as Unique Clustered Index

When I join those 2 tables, I usually do:

SELECT <somefields>
FROM Documents
INNER JOIN DocumentsRows ON Documents.DocID = DocumentsRows.DocID
WHERE <something>

If I run the query with STATISTICS IO ON and SHOWPLAN, I see that the engine choose Index Seek on DocumentsRows but the Number of Executions is equal to the Scan Count for the same table.

Table ‘DocumentsRows’. Scan count 2277, logical reads 12591, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Documents’. Scan count 5, logical reads 17526, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

I know that since I’m not joining on the entire index, the seek does not retrieve only 1 value (in fact it retrieves all the rows for a specific document), then why does SQL Server choose to seek on that index instead of just scan it?

Why should an Index Seek have a so high scan count? How can I solve this?

Answer :

I believe this has already been answered and would be helpful for you if you can refer to below discussion:Also, what action where clause is performing needs to be looked into:

Why so many logical reads?

Since Logical Reads are from the data cache (memory) I would think the fact that there is a lot of reads will make little difference and it would seem that the second query is more efficient when reading a lot of data in small chunks while the first query reads the data in large chunks.

Overall, the query performance significantly can be improved using below several aspects as well:

  1. Only the correct subset of rows from the * table to be retrieved, potentially reducing physical IO and locking contention
  2. The CPU intensive hash functions to be eliminated
  3. The memory footprint for both data and hash buckets to be reduced.

Index Seek will be used only if conditions from WHERE or ON clauses are the first in the list of columns of the Index. Also, this fields must be selective which means that the condition must filter just a little percent or rows from a table.

Also, you don’t need to include column that is part of the clustered index into list of columns of a nonclustered index, because Nonclustered indexes always contain the clustered index columns if a clustered index is defined on the table.

Actually, this is very complex subject. To know more about using indexes in Sql Server you can read this article https://technet.microsoft.com/en-us/library/jj835095.aspx

Leave a Reply

Your email address will not be published.