I/O Reads

Question

I/O Reads

SA-1

SSCrazy Eights

Points: 8113
More actions
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.

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

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

sa24 SSCrazy Eights Points: 8027 More actions · Answer 1

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.

SA-1 SSCrazy Eights Points: 8113 More actions · Answer 2

The execution plan is the same for both the servers.

Would physical disk fragmentation be responsible for this?

Thanks

sa24 SSCrazy Eights Points: 8027 More actions · Answer 3

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.

SA-1 SSCrazy Eights Points: 8113 More actions · Answer 4

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+

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 5

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

SA-1 SSCrazy Eights Points: 8113 More actions · Answer 6

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.

Jorge Campos Old Hand Points: 324 More actions · Answer 7

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

SA-1 SSCrazy Eights Points: 8113 More actions · Answer 8

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.