November 9, 2021 at 4:34 pm
Running the below code will produce two different execution plans (in the same ssms query). One using an index seek and one doing an index scan (choosing different index).
Why is this happening and how can I prevent this?
The table "imageproduct" has around 134 million rows. We use update auto statistics.
There is an index on imageproduct by mid which also has include columns. All of the columns being reference by this query are included in this index. However, the cursor version is picking an index that is by orderno, roll, imagename (which doesn't include mid and has to do a key lookup to retrieve this query.
The query optimizer suggests that I should create an index on mid and include orderno, roll, and imagename; however we already have one.
Why is the cursor version making a different execution plan?
CREATE NONCLUSTERED INDEX [IX_ImageProduct_MID] ON [dbo].[ImageProduct]
(
[MID] ASC
)
INCLUDE([PPID],[SeqNo],[RenderSeq],[ImageNo],[OrderNo],[Roll],[ImageName],[ProductionImageName],[RetID],[RefRetID],[ImageTypeID],[ReprintReason],[ReprintSpecialInstructions]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_ImageProduct_OrderNo_Roll_ImageName] ON [dbo].[ImageProduct]
(
[OrderNo] ASC,
[Roll] ASC,
[ImageName] ASC,
[ProductionImageName] ASC,
[RetID] ASC,
[RefRetID] ASC,
[ID] ASC
)
INCLUDE([AcctNo],[OrdID],[Cycle],[SeqNo],[ImageNo],[RenderSeq],[MID],[PPID]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
declare @mid int
select orderno, roll, imagename
from imageproduct
where mid=@mid
order by orderno, roll, imagename
declare c1 cursor local for
select orderno, roll, imagename
from imageproduct
where mid=@mid
order by orderno, roll, imagename
November 9, 2021 at 5:09 pm
You should change the index so that it has the proper sort order already, which will (should) avoid the sort in the query:
CREATE NONCLUSTERED INDEX [IX_ImageProduct_MID] ON [dbo].[ImageProduct]
( [MID], [OrderNo], [Roll], [ImageMame] ) --<<--
INCLUDE( [ImageNo], [ImageTypeID], [PPID], [ProductionImageName], [RefRetID], [RenderSeq], [ReprintReason], [ReprintSpecialInstructions], [RetID], [SeqNo] )
WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON, DROP_EXISTING = ON, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY]
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
November 9, 2021 at 5:13 pm
This doesn't explain why the two execution plans are different.
I understand that I could make the perfect index to make this query work but that isn't the question.
The question is why is SQL choosing a different index in the cursor version.
The non cursor version has the same order by clause and pulls the information correctly.
November 9, 2021 at 5:31 pm
Sorry to have bothered you then. Good luck with this. (I believe if you changed your cursor to LOCAL FAST_FORWARD (as in standard in SQL Server unless you explicitly need something else) then the index change would solve the cursor issue too. Without proper data ordering, the cursor would otherwise have to sort data with every data retrieval, and SQL Server is not going to do that because of the huge overhead it would entail.)
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
November 9, 2021 at 5:33 pm
You are correct. That fixed the problem...
I believe if you changed your cursor to LOCAL FAST_FORWARD (as in standard in SQL Server unless you explicitly need something else) then the index change would solve the cursor issue too. Without proper data ordering, the cursor would otherwise have to sort data with every data retrieval, and SQL Server is not going to do that because of the huge overhead it would entail.
Thanks
November 9, 2021 at 5:38 pm
It actually does explain it. If SQL has to do a sort to get the rows in the correct order, then the cursor would be required to do a sort for every row fetched. That's just way too much overhead for SQL to even consider.
Besides which, SQL would have the problem of figuring out how not to return the same row again if the sort order had dups and the SELECT clause had a column that wasn't part of the ORDER BY columns (did not happen in this specific case but possible in general).
If you change the cursor to LOCAL FAST_FORWARD (which should be the default unless you specifically need a different type of cursor), then I'm virtually certain SQL can use the index directly without having to do any sort to return the results.
(Sorry, I couldn't see my other comments on my initial post so I repeated them here. I put them in brackets to make it parenthetical and I guess the brackets "confused" the display into not showing them.)
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply