3

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?

closed as off-topic by Martin Smith, Colin 't Hart, Paul White, Daniel Vérité, ypercubeᵀᴹ Apr 5 '15 at 19:42

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Martin Smith, Colin 't Hart, Paul White, Daniel Vérité, ypercubeᵀᴹ
If this question can be reworded to fit the rules in the help center, please edit the question.

  • Can you include the WHERE filter? – LowlyDBA Jan 27 '15 at 14:28
  • Is it relevant? This thing happens in a lot of queries that involves this kind of join, my example was just one of them. If needed I can provide the real query. – mordack550 Jan 27 '15 at 14:29
  • Yes, it's generally a lot easier to assess a query if you know what it is. Providing the full query & table schema(s) is best if you can. – LowlyDBA Jan 27 '15 at 14:31
  • Well, is the index seek on the clustered index or a non-clustered index? How many rows are returned? A seek is really just a range scan, and it may have high executions because the query needs to return other columns via a lookup - those are executed once per row. Also, what are you "solving"? Is the query slow, or are you just trying to get a prettier number in the scan count? – Aaron Bertrand Jan 27 '15 at 14:34
  • 2
    The question is 2 months old... The problem is already solved. – mordack550 Apr 5 '15 at 12:03
0

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

  • Well i don't think this does address my issue. My question was based on "why does an index seek do so many scans, and how can i fix it?" – mordack550 Jan 27 '15 at 14:40
  • @mordack550 Just edited my answer – KASQLDBA Jan 27 '15 at 14:52

Not the answer you're looking for? Browse other questions tagged or ask your own question.