InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 20:42:19 GMT20http://www.sqlservercentral.com/Forums/Topic1395149-1292-1.aspxThrough the trace, I'd be able to get all the pieces of data I mentioned in the OP?Tue, 11 Dec 2012 13:36:47 GMTfernandoxdurandhttp://www.sqlservercentral.com/Forums/Topic1395149-1292-1.aspxWhat 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?Tue, 11 Dec 2012 13:35:17 GMTfernandoxdurandhttp://www.sqlservercentral.com/Forums/Topic1395149-1292-1.aspx[quote][b]fernandoxdurand (12/11/2012)[/b][hr]My goal is to monitor data for each SP every time one is called. Ideally, this data will be saved to a table.[/quote] Why do you need it?Tue, 11 Dec 2012 13:23:10 GMTAlexander Suprunhttp://www.sqlservercentral.com/Forums/Topic1395149-1292-1.aspxWhile 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.Tue, 11 Dec 2012 10:15:56 GMTMyDoggieJessiehttp://www.sqlservercentral.com/Forums/Topic1395149-1292-1.aspxMy 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 completeIO logical readIO writesIO physcial readSP 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 executedThe parameters passed to the SPWhat 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?Tue, 11 Dec 2012 08:18:14 GMTfernandoxdurand