Post reply

Almost same data, same number of records same index ...scan Vs Seek?

  • February 7, 2011 at 2:37 pm

    #233975

    i have a query which i run against two different servers ,one is production and other is copy of production(has 10 days old data). The query is using same index is doing index scan on production and index seek on copy of production.?

  • February 7, 2011 at 3:15 pm

    #1283460

    Same parameters?

    - 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

  • February 7, 2011 at 3:42 pm

    #1283471

    Craig Farrell (2/7/2011)

    Same parameters?

    Yes same value is passed. I am updating the statistics now, lets see..

  • February 8, 2011 at 6:41 am

    #1283638

    So what's happening after updating the statistics..?

  • February 8, 2011 at 6:49 am

    #1283642

    Same parameters but different data (copy of prod 10 days older than prod). Do the queries return the same number of rows?

    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

  • February 8, 2011 at 9:28 am

    #1283804

    updating the stats also didnt help. Queries do not really give the same result, but same number of records..I dont know where else to look for

  • February 8, 2011 at 11:34 am

    #1283919

    Can you post the two sqlplans? My guess is something went awry in the predicate selection or something similar.

    - 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

  • February 8, 2011 at 12:17 pm

    #1283974

    I just copied a recent full backup and still the same index is used but one does index scan and other does index seek? How can that be possible...

  • February 8, 2011 at 1:26 pm

    #1284020

    Since the queries don't give the same results, it's not a fair comparison. There could be different data leading to different optimal plans.

    As Craig said, we need to see the execution plans.

    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

  • February 8, 2011 at 1:44 pm

    #1284028

    Thanks Gail. The exec plan has all the table names and database names. I am little hesistant to send it that way. What do you suggest?

  • February 8, 2011 at 1:57 pm

    #1284038

    sqldba_icon (2/8/2011)

    Thanks Gail. The exec plan has all the table names and database names. I am little hesistant to send it that way. What do you suggest?

    Well, you could open the XML in a text editor and do a find/replace on the items (If you do this, please name each table differently. Nothing hurts more then seeing tblA join tblA join tblA...).

    Otherwise.. ummm... grab yourself a good book and start digging into the plan to see what you can find, and come back to us with questions.

    - 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

  • February 8, 2011 at 2:01 pm

    #1284041

    actually i think it is more of data related. Because when i run my tests against two days old data it does seek and if i do it against current data gives scan.

  • February 8, 2011 at 2:02 pm

    #1284044

    sqldba_icon (2/8/2011)

    Thanks Gail. The exec plan has all the table names and database names. I am little hesistant to send it that way.

    Why? What exactly is it going to give away?

    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

  • February 8, 2011 at 2:15 pm

    #1284060

    sqldba_icon (2/8/2011)

    actually i think it is more of data related. Because when i run my tests against two days old data it does seek and if i do it against current data gives scan.

    Yup, that's what I was expecting. Still going to need to see exec plans to see what's up.

    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

  • February 9, 2011 at 5:28 am

    #1284358

    a) parameter sniffing as cause:

    1) run your query on both servers with HARD-CODED input values instead of parameters.

    2) run query on both servers using OPTION (RECOMPILE)

    b) SET options as cause:

    1) use exact same SSMS window to execute exact same query against each server

    c) estimated rows different as cause:

    review actual query plan and compare estimated and actual rowcounts on each part of the query plan between the servers

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

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

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