Post reply

Query Behaviour - statistics io

  • July 24, 2008 at 11:04 am

    #75268

    Hi

    I have a query thats driving me slightly batty...

    On one system (2k Standard sp4) it runs like a dog - stats io

    Table t1. Scan count 1, logical reads 4055, physical reads 0, read-ahead reads 0.

    Table t2. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0.

    On another server (2k Ent SP4 - more cores, bigger disks) it runs like a dream with stats io

    Table t1. Scan count 1, logical reads 8, physical reads 0, read-ahead reads 0.

    Table t2. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0.

    t1 has 3m rows in slow system and 6m in fast.

    t2 has 24 (10 meeting join crit) rows in slow system and 23(9) in fast.

    Results: 320 in slow, 680 in fast.

    Both queries return the same plan, indexing/schema identical.

    Is there anything obvious that would cause such a difference in readIO? My initial thought was data distribution, stats or fragmentation, but it seems similar in both systems, and updating stats/defrag made no difference.

    Does Enterprise do something a little more clever than standard?

    Someone please save my head from the wall... :s

  • July 24, 2008 at 11:18 am

    #847967

    Does table1 have a clustered index in either environment? If not, please run the following and check the value of Forwarded records.

    DBCC ShowContig ('t1',0) WITH TableRESULTS

    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

  • July 25, 2008 at 3:28 am

    #848266

    Hi GM

    Forwarded Records are 0.

  • July 31, 2008 at 5:04 am

    #851161

    Hi

    Resolved:

    Anyone that was interested...

    The smaller table had 1 extra record. The distribution of this was effectively the first 9 records where the status codes we were looking for in the larger table - and returned around 400 out of many million records.

    By adding another record to the end of the sequence at #24 then we ended up scanning through the whole lot. By ditching this last record we return to a lovely efficient query.

    Seems obvious in hindsight... be careful how you add rows around here!

Viewing 4 posts - 1 through 3 (of 3 total)

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