I have this below query which is behaving bit weird . well Weird in the sense that I couldn't find complete explanation for this.
Version : Sql Server 2008 R2 Enterprise
No fragmentation . Statistics updated with fullscan and for all indexes.
DECLARE @t TABLE ( subid INT ) INSERT INTO @t VALUES (7)--,(3) SELECT TOP 1 t.QueueItemID FROM QueueTable t WHERE t.IsProcessed = 0 AND t.QCode = 'USA' AND SubID IN (SELECT SubID FROM @t) ORDER BY t.QueueItemID
Little bit about schema :
Table variable (@t ) is just one column subid .
QueueTable schema is :
CREATE TABLE [dbo].[QueueTable]( [QueueItemID] [int] IDENTITY(1,1) NOT NULL, [SubID] [int] NOT NULL, [IsProcessed] [bit] NOT NULL, [Qload] [varchar](max) NOT NULL, [QCode] [varchar](5) NOT NULL, [QDesc] [varchar](max) NULL, CONSTRAINT [PK_QueueTable] PRIMARY KEY CLUSTERED ( [QueueItemID] ASC )
This table is big as obvious from varchar(max) type columns in above schema..
There are 2 NC index:
NC index NCx_1 on (isprocessed,qcode) include(queueitemid,subid)
NC index NCx_2 on (subid,isprocessed,qcode) include(queueitemid)
Total rowcount is around 9 million rows. Group by subid is below :
SubID RowCount ------ -------- 1 68 2 8255571 3 378584 7 5350 11 5318
Rows satisfying condition (t.IsProcessed = 0 and t.QCode = 'USA' ) are around 350k .
When I run above query it takes 1.5 sec to complete with seek on NC NCx_1 and then scan on table variable. Here is plan.
Above plan is for subid = 11 or 7 in @t table variable
Not sure why it isn't using index NCx_2 (subid,isprocessed,qcode) include(queueitemid) which matches the criteria . It is using index NCX_1 instead .
It appears as it is seeking around 350k rows to satisfy (t.IsProcessed = 0 and t.QCode = 'USA' ) and then filtering out data based on subid column.
I would expect it to first filter out data based on subid column (which would be very less) and then apply other filters which is exactly what is NCX_2 is for.
I tried couple of optimization here that improved performance but want to understand this strange behavior at least to me.
- When I add merge join hint in the query then query runs very fast ( 100 ms)
- When I add index hint (NCX_2) in the query then also query runs very fast (60 ms)
- When I modify query to do MIN(t.QueueItemID) and remove order by query again runs very fast( 60 ms)
Not sure why optimizer not choosing it by default.