CODEWITHSUNDEEP
sqlsql-serverindexing
Samarth
Samarth

Reputation: 71

SQL query not using required index in SQL Server

I have two non clustered index on table A:

key column: DETECTED_UTC ASC

Columns included: APPROVAL_STATUS, IS_ROOT, AGENTGUID

Key column: agentguid

Now query is using agentguid index and taking 1min 17 sec.

But if I specify a query hint like 

option (table hint(A, index(DETECTED_UTC)))

It takes 4 sec.

Why SQL Server is not considering detected_utcin query plan. Can the query be modified so that it'll use detected_utc index. I don't want to specify query hint in my query. 

SELECT
    AUTO_ID
FROM
    (
      SELECT
        ROW_NUMBER() OVER ( ORDER BY A.DETECTED_UTC DESC ) AS ROWNUM
      , A.AUTO_ID
      , A.DETECTED_UTC
      FROM
        A
        INNER JOIN B
        ON A.AGENTGUID = B.AgentGUID
        LEFT JOIN C
        ON B.ParentID = C.AutoID
      WHERE
        ( DETECTED_UTC > DATEADD(day, -7, GETUTCDATE()) )
        AND ( APPROVAL_STATUS = '0' )
        AND IS_ROOT = '1'
        AND EXISTS ( SELECT
                        1
                     FROM
                        B epf
                     WHERE
                        epf.AgentGUID IS NOT NULL
                        AND epf.AgentGUID = A.AGENTGUID
                        AND epf.ParentID IN (
                        SELECT
                            AutoID
                        FROM
                            C
                        WHERE
                            AutoID IN ( SELECT
                                            NodeID
                                        FROM
                                            D
                                        WHERE
                                            D.GroupID IN ( 42 ) ) ) )
    ) AS TEMP
WHERE
    ROWNUM >= 1000
    AND ROWNUM < 1041
ORDER BY
    DETECTED_UTC DESC

Upvotes: 2

Views: 657

Answers (2)

gbn
gbn

Reputation: 432611

  • Your JOIN is on AGENTGUID
  • Your WHERE uses DETECTED_UTC, APPROVAL_STATUS, IS_ROOT

Only the DETECTED_UTC column is useful in the detected_utc index to the optimiser: the other columns used are included columns. Your index hint overrides this: I suspect you'll see lookups or spools or sorts in the plan with the hint to workaround the included columns not being key columns in the index

I'd expect that one of these would be more useful

(AGENTGUID, DETECTED_UTC DESC, APPROVAL_STATUS, IS_ROOT) INCLUDE (AUTO_ID)
(DETECTED_UTC DESC, AGENTGUID, APPROVAL_STATUS, IS_ROOT) INCLUDE (AUTO_ID)

Upvotes: 1

MatBailie
MatBailie

Reputation: 86775

The optimiser is estimating that the JOIN is the most selective part of the query. Based on that estimate, it is making choices to make that join as performant as possible.

Your use of an index hint shows that the estimated selectivity is wrong. Updating statistics and recompiling the code MAY help here, but just as likely it won't : The query is highly complex and the best query plan (and so which index to use) will depend on what data exists, and the characteristics of that data, in several different tables.

I would either keep using the hint (if the basic characteristics of your data won't change), or create a compound index as suggested by GBN.

Upvotes: 0

Related Questions