SQLTeam.com | Weblogs | Forums

How to find the reason of too many times 'reads' which find in the profiler?


#1

###A statement like this in a stored procedure cost a lot of time to exe it.
###I use profiler to track it and I find that the 'reads' time in the profiler seems too high.

Below is the statement

UPDATE GameScoreInfo SET Score=Score+@VariationScore, InsureScore=InsureScore+@VariationInsure, Revenue=Revenue+@InsureRevenue
    WHERE UserID=@dwUserID

###The info for this statement track in marked by gray color, obviously it's too high

###And for make a constraction to find the reason of the problem easier, I copy the DataBase to my local machine.And I find the 'reads' time is about 170 seems normal. So may the question lies in the different hardware or high concurrency

The question for me now it's that I don't know how to find the reason for this 'high reads time' problem. What should I do to find it? Some methods or tools? I search on the web haven't find sth useful.

Looking forward to you reply :smile:


#2

Hardware and/or contention for resources could be an issue, but before you suspect hardware, start by comparing the query plan and the number of rows read.

Press control-m in SSMS to turn on query plan and then run the query. Then look in the query plan window and compare.

If they are not the same, see if the database is being maintained properly (index rebuilds/reorgs,stats).

If the query plan is the same, and the database is maintained properly, then it very well could be hardware or resources.


#3

tnx a lot for your reply

The query plan is the same, both read line is one
I ask the question on the Microsoft forum

But I still think that the hardware may not that bad to make the 'reads' reach up to 80000+

Still tnx :smile: