sp for excessive reads

Question

sp for excessive reads

Tharg

SSC Eights!

Points: 980
More actions
February 9, 2010 at 11:13 am

#229814

What I am looking for is a way to track down what is causing the excessive read, basically I need something to return the PID of the job that is executing the most reads. Is there an sp for this?
I am aware of the command sp_who2 and have queries to return the longest running, but I need to have a something to return currently executing queries and the I/O values.
Solutions for both SQL 2005 and 2008 would be great if possible.

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

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

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 1

For SQL 2008, you can run one of the standard reports that is available. There is one called Top Queries by Average IO. Check if that helps

-Roy

Tharg SSC Eights! Points: 980 More actions · Answer 2

Thanks Roy.

Is SQL Profiler of use here in SQL 2005?

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 3

You can use SQL Profiler to get any kind of trace. It would be best to get the profiler information stored into a table where you can do better analysis of the data.

Edit : In SQL 2008 there are lots of DMVs you can use to get more information regarding the performances of stored procs etc. Keep in mind that you will loose all your information when the SQL Server is restarted. To keep track of all the historical data, it is a good idea to store in a table. Kind of like a Management warehouse.

-Roy