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.
@t
table is small, an index on(isprocessed, qcode, subid)
seems more appropriate than both of the ones you have. – ypercubeᵀᴹ Jan 26 '13 at 10:18subid,isprocessed,qcode
and the matching rows are ordered byQueueItemID
(as either the index is unique and there is only one or it is not unique andQueueItemID
becomes an implicit key column rather than an included one) so it just needs to get the first one on each seek. – Martin Smith Jan 26 '13 at 19:41