Post reply

I/O Reads

  • September 15, 2004 at 7:11 am

    #86447

    I made some changes to Indexes on a test server and got the reads down from 80k+ to 8k+. The effect as anyone might guess is faster searches and updates.

    Now when I updated the changes to the Production Server I get reads of 12k+. I reindexed the table and updated statistics. If it helps there are currently over 200k+ records in this table.

    Any thoughts appreciated.

  • September 15, 2004 at 9:02 am

    #523182

    Try comparing the execution plans on both servers ? It is possible that production (because of the load) might be taking a slightly different execution plan resulting in the higher no. of reads.

  • September 15, 2004 at 10:02 am

    #523199

    The execution plan is the same for both the servers.

    Would physical disk fragmentation be responsible for this?

    Thanks

  • September 15, 2004 at 10:43 am

    #523207

    Yes, fragmentation can be a cause. The reads gives you the no. of pages read and this can vary due to the fragmentations. Also try running

    set statistics_io on

    And check what exactly is accounting to the higher amount of reads.

  • September 15, 2004 at 11:10 am

    #523215

    Closely looking at the execution plan

    The Clustered Index Scan for a Look Up table is costing 0% on the test server while the same table is taking 4% on the production server. This is true for a Row Count Spool / Lazy Spool on the same table.

    Also this is on the test server

    Table 'tblLookUp'. Scan count 586, logical reads 2930, physical reads 0, read-ahead reads 0.

    Table 'tblData'. Scan count 2, logical reads 1282, physical reads 0, read-ahead reads 0.

    Reads 8k+

    This is on the production server

    Table 'tblLookUp'. Scan count 1089, logical reads 5445, physical reads 0, read-ahead reads 0.

    Table 'tblData'. Scan count 1, logical reads 1274, physical reads 0, read-ahead reads 0.

    Reads 12k+

  • September 16, 2004 at 8:31 am

    #523349

    Clustered Index Scan means table scan. You need turn the indexes in this lookup table.

  • September 16, 2004 at 9:45 am

    #523367

    I changed the indexes on the table and this is new reading for the execution plan.

    Table 'tblLookUp'. Scan count 289, logical reads 294, physical reads 0, read-ahead reads 0.

    Table 'tblData'. Scan count 1, logical reads 20, physical reads 0, read-ahead reads 0.

    Thanks.

  • September 16, 2004 at 3:54 pm

    #523429

    I see table's & indexe's structures are both the same in test server and production server... but are you sure data is the same on the two environments?. A better query performance depends basically on #records and data fragmentation. In some cases (not always) a clustered index is better because it finds data retrieved in less 'page jumps'.

    Regards

    Jorge

  • September 17, 2004 at 7:32 am

    #523488

    The LookUp table essentially has the same records on both servers the Data table has 50 fewer records on the test server. Another thing that caught my eye is that the LookUp table has just abt 220 records and I got

    Table 'tblLookUp'. Scan count 289, logical reads 294, physical reads 0, read-ahead reads 0.

    in the Statistics IO.

    Any inputs on this.

Viewing 9 posts - 1 through 8 (of 8 total)

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