Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm trying to understand why a non-clustered index is not available for a given query when filtered. The relevant part of my (large) query is this:

) results
JOIN BE_Insurance ins
ON results.PayorId = ins.Id

Up in the select, I'm only grabbing ins.name. The index I initially created was this:

CREATE INDEX IX_BE_Insurance_PayorId_PayorName
ON      BE_Insurance (Id) INCLUDE (Name)
WHERE ParentId IS NULL

My query is such that only payorIds with a NULL parentId will be chosen, but I understood the optimizer's disinclination to choose it. But when I added a hint to try to force the index, the whole thing error'd out.

Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.

I assumed it would follow my hint, and maybe error out at execution time if I wound up with some bad data and the index was missing some needed values. Removing the filter from the index caused it to be chosen successfully by the query (even without the hint).

Are indexes with a WHERE clause ineligible for queries like this? Are they eligible only if the optimizer can be guaranteed that the filter is valid and won't produce any missing values?


As requested, here's the whole query, which is still a work in progress.

SELECT results.*
FROM (
        SELECT auths.*,
               worked.WorkedHours,
               worked.WorkedUnits,
               worked.WorkedAmount,
               worked.ActiveClients
        FROM (
                SELECT PayorId,
                       SUM(AuthHours) AuthHours, 
                       SUM(AuthUnits) AuthUnits, 
                       SUM(AuthAmount) AuthAmount
                FROM (
                        --DYNAMIC TEMPLATE ----------------------------------------------------------------------------------
                        SELECT PayorId,
                               PayorName,
                               AuthHours AuthHours, 
                               AuthUnits AuthUnits, 
                               AuthAmount AuthAmount
                        FROM PayorAuthorizations_Level1Data_Authorizations auths WITH(NOEXPAND)
                        WHERE OrganizationId = @organizationId AND StartDate <= @endDate AND @startDate <= EndDate
                        --/DYNAMIC TEMPLATE----------------------------------------------------------------------------------

                        --INTERSECT / EXCEPT dynamically generated queries 
                ) q
                GROUP BY PayorId
        ) auths
        JOIN (
                SELECT PayorId,
                       SUM(ISNULL(TotalWorkedHours, 0)) WorkedHours,
                       SUM(ISNULL(TotalWorkedUnits, 0)) WorkedUnits,
                       SUM(ISNULL(TotalWorkedAmount, 0)) WorkedAmount,
                       COUNT(DISTINCT clientId) ActiveClients
                FROM (
                        --DYNAMIC TEMPLATE ----------------------------------------------------------------------------------
                        SELECT PayorId,                   
                               TotalWorkedHours,
                               TotalWorkedUnits,
                               TotalWorkedAmount,
                               clientId clientId
                        FROM PayorAuthorizations_Level1Data_CurrentlyWorked worked WITH(NOEXPAND)
                        WHERE OrganizationId = @organizationId AND StartDate <= @endDate AND @startDate <= EndDate
                        --/DYNAMIC TEMPLATE----------------------------------------------------------------------------------

                        --INTERSECT / EXCEPT other dynamically generated queries 
                ) q
                GROUP BY PayorId
        ) worked
        ON auths.payorId = worked.payorId
) results
JOIN BE_Insurance ins WITH (INDEX(IX_BE_Insurance_PayorId_PayorName))
ON results.PayorId = ins.Id
OPTION(FORCE ORDER, MERGE JOIN)
share|improve this question
    
What happens if you change the join to JOIN BE_Insurance ins ON results.PayorId = ins.Id AND ins.ParentId IS NULL ? –  ypercube Aug 7 at 17:30
    
@ypercube - I'd have to add parentId to my index .... but that all around seems like a decent idea –  Adam Rackis Aug 7 at 17:31
    
You already have ParentId in your index, in the filter part. You don't need to add anything. All values (in the rows indexed) will be NULL anyway in that column. –  ypercube Aug 7 at 17:31
    
@ypercube - quite right. The optimizer seems to have caught that - including that column doesn't seem to affect the size of the index at all, so either the engine omitted it, or storing all those nulls increased the size a miniscule amount –  Adam Rackis Aug 7 at 17:36
1  
But you seem to be satisfied with the improvement, just because it works now. I can't answer without understanding why it happens. –  ypercube Aug 7 at 18:49

2 Answers 2

up vote 6 down vote accepted

I assumed it would follow my hint, and maybe error out at execution time if I wound up with some bad data and the index was missing some needed values.

The query optimizer will only use a filtered index in a query plan if it can guarantee (within its reasoning framework) that all possible matches can be served from the index. This is by design, to avoid the sort of runtime error you describe.

Failure to results in a NESTED LOOPS JOIN from my non-clustered index against a clustered index Key Lookup, presumably to grab the parentId. INCLUDING parent ID eliminates this, and leaves me with a nice non-clustered index scan.

This is a known current limitation. Adding the filtered column(s) to the key or include list is the standard workaround, and a current best practice for all sorts of semi-related reasons.

The FORCE ORDER, MERGE JOIN is definitely needed though.

Be extremely careful using hints (directives) like this unless you fully understand all the consequences. FORCE ORDER in particular is an extremely powerful and wide-ranging hint, with a number of non-obvious side-effects including the placement of aggregate operators, and the order of evaluation of subqueries and common table expressions.

For the most part, you should try to write queries that provide the query optimizer with enough good-quality information to make the right decisions without hints. The hinted plan may be 'optimal' today, but it may not remain so as the data volume and/or distribution changes over time.

share|improve this answer
    
Very good points, thank you. The MERGE JOIN hint I'm definitely comfortable with. I've taken great pains to make sure those indexed views are stored in the right order, and the engine doesn't seem to choose MERGE JOIN on its own (and performs much better when I force it to). I will definitely take a closer look at the FORCE ORDER hint, but I'm fairly certain the IX scan as the last step is optimal, again given how the views are set up. –  Adam Rackis Aug 8 at 1:48
2  
@AdamRackis Speaking of the merge join, it's hard to be certain without seeing all the details, but you may be running up against this issue –  Paul White Aug 8 at 2:16
    
thanks for the link - very cool. Haven't read the whole thing quite yet, but the MERGE JOIN hint was in fact needed even before I added that last INNER JOIN against BE_Insurance (with the non clustered filtered index). But thanks! –  Adam Rackis Aug 8 at 3:11

To use a filtered index it wants to see the predicate there, or one that matches very closely. So explicitly saying AND ins.ParentID IS NULL is going to be useful.

Now, you should generally include the columns you're filtering on in the index itself because of a QO quirk. If the predicate in the query matches exactly the one in the index, and you're not referring to the column anywhere else, then you ought to be able to avoid having the included column. But there are times when the QO won't quite make that connection, and you're better off throwing in the included column just to be sure.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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