Why a query would pick unrelated index
-
Hi
I came across a scenario where a query is picking totally unrelated index in the execution plan. Here is the case:
SELECT A.tranid,A.transittime
FROM table1 A WITH(nolock)
JOIN table2 B WITH(NOLOCK) On (A.tranid=B.TranId and A.ProdType=B.Prodtype )
WHERE A.transittime Between '2017-11-14 23:30:00.000' AND '2018-01-13 23:30:00.000'
There are couple of indexes which are best suited for this query like :
CREATE INDEX IDX1 ON table1(tranid,ProdType,transittime)
CREATE INDEX IDX2 ON table2(tranid,ProdType )
But surprisingly, the query gets below index scan in plan:
CREATE INDEX IDX3 ON table1(Visitduration )
this index column Visitduration is no where part of my query, but still optimizer scans it. Why index IDX1 seek in not happening here? What scenario might cause it? the query return around 200K records.
If I use index hint to force the IDX1 use, then my performance gets slower compared to IDX3 use.
Any thoughts?
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
-
Can you post the DDL of your tables please? Please also make sure to use the "Insert/edit code sample" button to make it readable too. I can't replicate the problem on my sandbox:
USE Sandbox;
GO
CREATE TABLE table1 (tranid int,
transittime datetime,
ProdType int,
Visitduration time);
CREATE TABLE table2 (SomeID int,
Tranid int,
ProdType int);
CREATE INDEX IDX1 ON table1(tranid,ProdType,transittime);
CREATE INDEX IDX2 ON table2(tranid,ProdType );
CREATE INDEX IDX3 ON table1(Visitduration );
GO
INSERT INTO Table1
VALUES(1,'2017-12-17T17:15:00.000',7,'01:17');
INSERT INTO Table2
VALUES(1,1,7);
GO
SELECT A.tranid,A.transittime
FROM table1 A
JOIN table2 B ON A.tranid=B.TranId
AND A.ProdType=B.Prodtype
WHERE A.transittime Between '2017-11-14T23:30:00.000' AND '2018-01-13T23:30:00.000';
GO
DROP TABLE table1;
DROP TABLE table2;Produces the query plan
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
Without the full structure, the execution plan and the statistics, it's hard to say. Is visitduration a calculated column or something like that? It might explain it (maybe). If you at least posted the plan, preferably the actual plan to include runtime metrics, it'd be easier to provide an answer. Are you using query store and plan forcing? Check the plan header properties for a Use Plan property.
If SQL Server is scanning an index, it will usually attempt to scan a smaller index, if that index can satisfy the query. Just what you've given us, it sounds really odd, but I don't think we have nearly enough information.
And NOLOCK... <sigh>.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
I understand. Let me arrange the plan and query. I can't add all the data as it is big enough but will see If I can reproduce it with minimum required.
Grant:
Are you using query store and plan forcing: NO
Is visitduration a calculated column or something like that? :NO
SQL Server is scanning an index, it will usually attempt to scan a smaller index: yes , this index has the smallest key width but it no way satisfies my query.
Will add more details by Monday
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
- S_Kumar_S wrote:
I understand. Let me arrange the plan and query. I can't add all the data as it is big enough but will see If I can reproduce it with minimum required. Grant: Are you using query store and plan forcing: NO Is visitduration a calculated column or something like that? :NO SQL Server is scanning an index, it will usually attempt to scan a smaller index: yes , this index has the smallest key width but it no way satisfies my query. Will add more details by Monday
I had a query that was scanning a small index as the plan had an implicit data type conversion on the primary key and it was faster to scan the smallest NC index to complete the conversion rather than the clustered index where the primary key was the clustering key. Eliminated the implicit conversion and the scan of the NC index went away.
- S_Kumar_S wrote:
Hi I came across a scenario where a query is picking totally unrelated index in the execution plan. Here is the case: SELECT A.tranid,A.transittime FROM table1 A WITH(nolock) JOIN table2 B WITH(NOLOCK) On (A.tranid=B.TranId and A.ProdType=B.Prodtype ) WHERE A.transittime Between '2017-11-14 23:30:00.000' AND '2018-01-13 23:30:00.000' There are couple of indexes which are best suited for this query like : CREATE INDEX IDX1 ON table1(tranid,ProdType,transittime) CREATE INDEX IDX2 ON table2(tranid,ProdType ) But surprisingly, the query gets below index scan in plan: CREATE INDEX IDX3 ON table1(Visitduration ) this index column Visitduration is no where part of my query, but still optimizer scans it. Why index IDX1 seek in not happening here? What scenario might cause it? the query return around 200K records. If I use index hint to force the IDX1 use, then my performance gets slower compared to IDX3 use. Any thoughts?
Yes... obviously, the two indexes you created aren't up to the task of quickly isolating the data you wanted. The optimizer also decided (apparently) that your indexes weren't even worth being used in a scan. The optimizer is a "cost based" system and, to be quick, it doesn't necessarily try everything and will sometimes come up with a "good enough" plan. It's also heavily dependent on "statistics" and indexes (especially but not limited to Clustered Indexes) based on columns that have "ever-increasing" values, such as an IDENTITY column, can quickly go "out of date" because they quickly surpass what has been stored in "statistics". You do have to be diligent in maintaining "statistics" in such cases.
All that being said, I believe that your indexes are actually to blame and that the optimizer picked a different index to use as a more narrow version of what your table contains. Of course, that will result in an index scan unless the leading column is something that it can do a seek on. It cannot seek on other columns alone.
Not to be repetitive but your indexes aren't up to the task. I believe you will need the lead column of the index to start with the "transittime" column because the criteria in the WHERE clause is normally evaluated first to cut down on the number of joined rows that must be accomplished to satisfy the query.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply