Post reply

sp for excessive reads

  • 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.

  • February 9, 2010 at 11:31 am

    #1116294

    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

  • February 9, 2010 at 1:28 pm

    #1116362

    Thanks Roy.

    Is SQL Profiler of use here in SQL 2005?

  • February 9, 2010 at 2:03 pm

    #1116388

    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

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

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