Analyzing Performance

  • My goal is to monitor data for each SP every time one is called. Ideally, this data will be saved to a table.

    The data I would like to gather for each instance of a SP is:

    Wait statistics for what the query is waiting on to complete

    IO logical read

    IO writes

    IO physcial read

    SP ExcutionExecution(calculated by SP endtime - SP starttime as datetime or = to the output from set statistics time on)

    SQL Text of the SP that is being executed

    The parameters passed to the SP

    What would be better to accomplish this an Extended Event or Profiler?

    Is it possible to get these pieces of data for each exec SP instance?

    I have seen an example online for using Extended Events to do the wait stats, but I am unsure if I will be able to get the IO data. The IO data I want is what shows in the message of a query when set statistics io on is enabled on the connection. I am also unsure of the possibility to get the parameters being passed for each SP call. Maybe collecting the execution plan for each sp call will work. I think I remember reading the paramaters used can be found there.

    What I am trying to accomplish:

    Get the above data into a table, so that I can query to see the highest IO usage for each SP, the parameters passed that caused this high IO, and the frequency that these parameters are used. With this data I will decide what indexes need to be added or queries optimized.

    Would anyone like to offer some insight in how to accomplish this?

  • While others may advise against it, I use a server side profile trace that runs continuously on the server (see to automatically start each time the MSSQL service is started). Naturally with all traces you need to be careful not to overload the server so we use carefully calculated filters to minimize the impact on the server.

    For instance, setting the trace to only include the columns you are interested in tracking, setting other column filters i.e (tracking only certain sql logins who may be executing the procs, or perhaps set a minimum number of reads to track - say when a query's reads exceed 50,000 or say when the duration exceeds 5 seconds, and so forth.

    ______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

  • fernandoxdurand (12/11/2012)


    My goal is to monitor data for each SP every time one is called. Ideally, this data will be saved to a table.

    Why do you need it?


    Alex Suprun

  • What I am trying to accomplish:

    Get the above data into a table, so that I can query to see the highest IO usage for each SP, the parameters passed that caused this high IO, and the frequency that these parameters are used. With this data I will decide what indexes need to be added or queries optimized.

    Would anyone like to offer some insight in how to accomplish this?

  • Through the trace, I'd be able to get all the pieces of data I mentioned in the OP?

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

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