Query Cost 27%

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

  • 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.

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

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

  • 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

  • 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

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

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

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

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

  • 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

  • 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

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

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