Home Forums SQL Server 7,2000 Performance Tuning Query Cost 27% RE: Query Cost 27%

  • May 17, 2012 at 4:45 am

    #1488901

    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)