Index scan vs. seek

Question

Index scan vs. seek

Viewing 10 posts - 16 through 24 (of 24 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

David Moutray (10/28/2011)

Right click anywhere on your execution plan, and select show execution plan xml. The XML will open in a new query window. Then just copy and paste the xml into a post here. (You can put xml tags around it. See the IFCode Shortcuts to the left. <--)

No, please don't do that.

Save the execution plan as a file and attach it to your post.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lexa Hall of Fame Points: 3314 More actions · Answer 2

GilaMonster (10/28/2011)
Lexa (10/28/2011)
Execution plans is 100% index seek on my_index
In your initial post you said you had a scan.

Yep, here is what I posted: "...which time to time gets scanned which I believe causes performance issues"

I suspect multiple things here: 1. major performance issues when I do get a scan; 2. even with the seeks the proc still takes 2.5 sec which could be due to hardware bottleneck; 3. after updating stats on the table it does seem to perform better, but can't say it solved the issue 100%

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

I'd like to see the exec plan with the scan please.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lexa Hall of Fame Points: 3314 More actions · Answer 4

GilaMonster (10/28/2011)
I'd like to see the exec plan with the scan please.

Now that I updated stats, don't seem to have any scans at all... But will post if I see one.

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 5

Agreed.

Lexa, I realize you purposely obfuscated that index, but we're going to need more to really get into this if you want to. Part of it is because we're probably going to hand you code to review selectivity heuristics, part of it is because we're going to want to see what's happening under the hood of this process.

As to it being a 'simple' query, those are the ones that can get in the most trouble. It will all come down to what value was set in the parameter and if SQL has decided it was time to re-compile for cache reusage, and all of the mechanics of dealing with it are hidden in the schema instead of being able to do SQL changes to workaround a minor issue.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 6

Lexa (10/28/2011)
GilaMonster (10/28/2011)
I'd like to see the exec plan with the scan please.
Now that I updated stats, don't seem to have any scans at all... But will post if I see one.

Heh, cool. Well, we'll still be here if you do. 🙂

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 7

Lexa (10/28/2011)
GilaMonster (10/28/2011)
Can you post the index definition please?
Can you post the execution plan please?
CREATE UNIQUE NONCLUSTERED INDEX my_index ON table1
(
[f1] ASC,
[f2] ASC
)
INCLUDE ( [f3],
[f4]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80)
GO
Execution plans is 100% index seek on my_index

I'm actually a bit surprised you'd ever get a seek on that index for the original query you posted...

select f1 from t1 where f2 = @val

--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)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

Jeff Moden (10/30/2011)
I'm actually a bit surprised you'd ever get a seek on that index for the original query you posted...
select f1 from t1 where f2 = @val

I suspect that the obfuscation messed things up to the point that what we see is not indicative of the real scenario. The OP also said

The index in question is on f2 with an include on a couple other fields.

which does not match with the index definition posted.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 9

That's kind of what I was getting at... you just took more words to say it. 😀

--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)

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 10

For the query that is taking 3 seconds, show the Actual Execution Plan when you run it and then let us know what the Estimated and Actual row counts for the various parts of the query were. My guess is that the estimated is too high a fraction of the total number of rows in the table and you are not getting a seek because of that?

Oh, another even more likely situation is implicit conversion due to using the wrong variable type??

What happens plan wise when you use a hard-coded value (of the correct data type)?

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service