I would like to ask for some help to create correct indexes for this query:

FROM dbo.Sizes AS S
        SELECT 1
        FROM #PermissionsTable AS PT
        WHERE PT.ProductID = S.ProductID
            AND PT.CountryID = S.CountryID

This query is used in procedure and #PermissionsTable is created in there based on passed in criteria.

I've tried creating various indexes on my table, but I'm always getting only Index Scans, I'd like to get Seeks of course. For instance:

    ON dbo.Sizes (ProductID, CountryID, TypeID);

    ON dbo.Sizes (CountryID, ProductID, TypeID);

-- I've added TypeID into INCLUDE part, because I'm not using it in any clause except SELECT statement.
    ON dbo.Sizes (ProductID, CountryID) INCLUDE(TypeID);

    ON dbo.Sizes (CountryID, ProductID) INCLUDE(TypeID);

And on #PermissionTable I've tried creating both Clustered and Non Clustered indexes on ProductID, CountryID or CountryID, ProductID.

But I'm always ending up with Scans.

Sizes table has hundreds of millions of rows. Permissions table has around 400.000.

At the moment I've added WITH (FORCESEEK) hint next to FROM dbo.Sizes AS S, which forces seek, but I'd like this to be done by SQL Server engine.

Any literatur, tips, anything would be helpful.


Update: added execution plan Execution plan

  • 1
    Can you post the execution plan. You can use the free plan explorer from sqlsentry.
    – Kin Shah
    May 13, 2015 at 16:13
  • 2
    You are using a corrolated subquery which is executed for every row in the result set and will always end with scans. Rewrite the query as a join: select distinct D.TypeID From dbo.Sizes AS s inner join #PermissionsTable AS PT on PT.ProductID = S.ProductID AND PT.CountryID = S.CountryID
    – Spörri
    May 13, 2015 at 16:21
  • 2
    @Spörri : Without execution plan you cannot tell for sure whether subquery is executed multiple times. Optimizer will very likely generate exactly the same plan as your version with join.
    – a1ex07
    May 13, 2015 at 17:43
  • 1
    @a1ext07 actually in this case we know it will - As the subquery is dependant on the outer query and is therefore a repeating or correlated subquery it will most likely be executed for each row of the outer query: technet.microsoft.com/en-us/library/… and sqlservice.se/… except if you force it not to.
    – Spörri
    May 14, 2015 at 1:31
  • 1
    Avoiding the scans is not necessarily the best thing you can do for performance. Try with an index on ProductID, CountryID, TypeID for the Sizes table and also add an index to your temp table on ProductID, CountryID. That may give you a plan with scans and a merge join instead of the hash join. Note, the order of the columns in the indexes has to be the same. May 14, 2015 at 9:12

1 Answer 1


First of all, I have to ask: Why are you doing this? If you have a performance issue, pursue it. However, you only mention that you are seeing scans instead of seeks. Scans are not always bad - they can be the most efficient method of pulling large amounts of data since sequential file access is less costly in I/O terms than random access.

The commenters are correct - the query as written will always produce a scan. The outer query will always produce a scan since the query has neither a join nor a sargable condition in the WHERE clause to limit it. The inner query is likely using a scan because of the number of times it is executed and the probability of returning more than 30% of the rows over the course of the query. (30% is roughly the threshold for choosing a scan over a seek.) A join may produce a better plan, but it really depends on the distribution of values in the temporary table.

I can think of a couple of things you might try:

  • If you're running this query repeatedly to test it, make sure you've added OPTION(RECOMPILE) to the end while you're testing. This will force it to re-evaluate the query instead of using a cached plan. If you don't, the optimizer may not see that an option other than a scan is available.
  • Try using a CROSS APPLY instead of a join. You can use a subquery as the target of the APPLY; I've used that technique before with stubborn queries with good results. Your query would then resemble SELECT DISTINCT D.TypeID FROM dbo.Sizes S CROSS APPLY (SELECT TOP(1) 1 FROM #PermissionsTable PT WHERE PT.ProductID = S.ProductID AND PT.CountryID = S.CountryID) P. Keep in mind that the APPLY will still be run as many times as you have rows in Sizes, so even though you're using a seek, it might still produce a poor plan.

If neither of those has an effect, you may be able to improve performance by changing the way you think about the query. How many distinct ProductID/CountryID combinations are there in #PermissionsTable? Instead of searching on all 400,000 rows, can you select only those distinct combinations into another temp table, then join that to Sizes? Can you select only the distinct combinations of ProductID/CountryID/TypeID from Sizes? Can you reverse the query so that Sizes is in the inner query? The goal of all of these would be to reduce the number of rows that the query must retrieve.

  • Hi Ed! Thanks for your response. I was thinking about cross apply too. However, isn't it correlated subquery as well? And about these 400.000 records in permissions table - they are distinct already there. Recompiling isn't a solution, snippet is just the generated dynamic sql code, which, AFAIK, will always produce a new execution plan. And you are correct, I wanted to pursue performance issues on this, I should've be more specific on that. May 20, 2015 at 4:16
  • Not really. SQL Server runs the APPLY once for each row in the outer query, while a correlated subquery in a join clause is run once. Since it knows it will need to run the APPLY's query so many times, it will often pick a plan using a seek if it can. May 21, 2015 at 12:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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