Query Cost 27%

Question

Query Cost 27%

Alone

Right there with Babe

Points: 783
More actions
May 15, 2012 at 1:08 pm

#260832

How to change non clustered index scan to non clustered index seek?The concerned tables already have non clustered index configured.

Viewing 15 posts - 1 through 15 (of 18 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Alone (5/15/2012)
How to change non clustered index scan to non clustered index seek?The concerned tables already have non clustered index configured.

I have no idea based solely on what you have posted. With no information regarding the query, table or tables involved, the indexes defined, or the actual execution plan all you are going to get is ignored or wild shots in the dark that may not even be useful.

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

Please post query, execution plan, table definition, index definition.

Also, ask yourself if the scan is a problem before you spend any time thinking about it.

http://sqlinthewild.co.za/index.php/2010/03/11/the-root-of-all-evil/

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

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

Alone (5/15/2012)
How to change non clustered index scan to non clustered index seek?The concerned tables already have non clustered index configured.

Typically? You don't. A seek happens when the data and the filter are selective enough. To get close to guaranteeing a seek you'd need a filter roughly on par with a single day in a year, with even distribution. Even then it would depend on how the index is setup and what your where clause looked like.

After that you're dealing with data volume and tipping points and row width and a number of other factors.

Schema, Actual Execution Plan, and the T-SQL would help us help you here.

- 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: 1004402 More actions · Answer 4

Evil Kraig F (5/15/2012)
Alone (5/15/2012)
How to change non clustered index scan to non clustered index seek?The concerned tables already have non clustered index configured.
Typically? You don't. A seek happens when the data and the filter are selective enough. To get close to guaranteeing a seek you'd need a filter roughly on par with a single day in a year, with even distribution. Even then it would depend on how the index is setup and what your where clause looked like.
After that you're dealing with data volume and tipping points and row width and a number of other factors.
Schema, Actual Execution Plan, and the T-SQL would help us help you here.

Gosh... if all that were true, it wouldn't be worth having things like calendar tables.

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

GilaMonster (5/15/2012)
Also, ask yourself if the scan is a problem before you spend any time thinking about it.

+1 😎

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

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

Jeff Moden (5/15/2012)
Gosh... if all that were true, it wouldn't be worth having things like calendar tables.

Alright, I'll bite into that one... there's nothing in there I see as being inaccurate, how did a calendar table get involved?

The only piece of that which is a generality is the 1 day in a year bit for seek vs. scan guarantees, and perhaps I should have been more clear. 0.03 is the 'near guarantee' mark, which is roughly 1/300... for round numbers I use one day in a year. It CAN scan before than, but you're not guaranteed.

- 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: 1004402 More actions · Answer 7

Evil Kraig F (5/16/2012)
Jeff Moden (5/15/2012)
Gosh... if all that were true, it wouldn't be worth having things like calendar tables.
Alright, I'll bite into that one... there's nothing in there I see as being inaccurate, how did a calendar table get involved?
The only piece of that which is a generality is the 1 day in a year bit for seek vs. scan guarantees, and perhaps I should have been more clear. 0.03 is the 'near guarantee' mark, which is roughly 1/300... for round numbers I use one day in a year. It CAN scan before than, but you're not guaranteed.

Are you suggesting that if I use something like a tenth of a table that a scan is virtually guaranteed?

--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 (5/16/2012)
Are you suggesting that if I use something like a tenth of a table that a scan is virtually guaranteed?

If you are using a non-covering index, then absolutely yes. The tipping point (where using a noncovering index and doing key lookups is less efficient than a table scan) is somewhere around 0.5% of the total rows in the table (it's ~ number of rows = 30% of the number of pages in the table)

There's a post on my blog "Seek or Scan" which shows this. Sorry, don't have time to find it, got to get to class.

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: 1004402 More actions · Answer 9

Thanks, folks... I'll take a deeper look.

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

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 10

GilaMonster (5/17/2012)
Jeff Moden (5/16/2012)
Are you suggesting that if I use something like a tenth of a table that a scan is virtually guaranteed?
If you are using a non-covering index, then absolutely yes. The tipping point (where using a noncovering index and doing key lookups is less efficient than a table scan) is somewhere around 0.5% of the total rows in the table (it's ~ number of rows = 30% of the number of pages in the table)
There's a post on my blog "Seek or Scan" which shows this. Sorry, don't have time to find it, got to get to class.

I guess I need a lesson on how to do a search once in your site, Gail.

{edit} Found it using Google but there doesn't appear to be a way to search in your fine site.

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

The built in search was useless, so I turned it off. It's on my to-fix list. Should have mentioned the category - indexes - would have made things easier to find.

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: 1004402 More actions · Answer 12

No problem. Thanks.

I ran the test you included in the Blog entry and actually did get seeks at the .5% and .3% levels instead of scans, but that still absolutely supports what you said. I was having a hard time accepting it all until I realized that you and Craig where talking about things that didn't cover in the index.

Shifting back to the likes of things like calendar tables (keeps coming up because I'm considering writing an article about such things)... your examples seem to exemplify why to not fill it with a bunch of garbage that SQL has functions for. If you don't need to search for it, you might not want to include it in the actual table just because its convenient. Then again, if you use it a lot, maybe. Heh... "It Depends".

Thanks for the feedback and "SQL in the Wild".

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

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

Jeff Moden (5/17/2012)
No problem. Thanks.
I ran the test you included in the Blog entry and actually did get seeks at the .5% and .3% levels instead of scans, but that still absolutely supports what you said. I was having a hard time accepting it all until I realized that you and Craig where talking about things that didn't cover in the index.

Apologies for the confusion. Yeah, my numbers were off too, sorry, it's 0.003 or .3%... and I dropped it in the middle of the two, heh. Whoops. I find most non-clusters are non-covering and used for search mechanics, though you do have the occassional exception for tight data pulls.

Shifting back to the likes of things like calendar tables (keeps coming up because I'm considering writing an article about such things)... your examples seem to exemplify why to not fill it with a bunch of garbage that SQL has functions for. If you don't need to search for it, you might not want to include it in the actual table just because its convenient. Then again, if you use it a lot, maybe. Heh... "It Depends".
Thanks for the feedback and "SQL in the Wild".

An alternative to proc or table is to meet in the middle with a non-persisted calculated column, Jeff. Keeps it out of the data-pages but available on row-usage. I'd recommend being gentle with them though. Somewhere around here I have some tests where I shattered table speed goofing around with them but I'll have to find it and my notes. Another article I started research on and then let fall off the earth.

- 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

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

Evil Kraig F (5/18/2012)
Apologies for the confusion. Yeah, my numbers were off too, sorry, it's 0.003 or .3%... and I dropped it in the middle of the two, heh.

No, it's not. It's usually around 0.5% of the table, but it is not a hard and fast % of the table. The most accurate calculation would be that the query tips around row count = 20% of total pages in the table.

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