Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Analyzing Performance Expand / Collapse
Author
Message
Posted Tuesday, December 11, 2012 8:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 19, 2014 1:58 PM
Points: 4, Visits: 104
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?
Post #1395149
Posted Tuesday, December 11, 2012 10:15 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, July 14, 2014 2:06 PM
Points: 3,860, Visits: 7,130
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; They'll drag you down to their level and beat you with experience"
Post #1395231
Posted Tuesday, December 11, 2012 1:23 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 17, 2014 3:21 PM
Points: 185, Visits: 917
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
Post #1395301
Posted Tuesday, December 11, 2012 1:35 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 19, 2014 1:58 PM
Points: 4, Visits: 104
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?
Post #1395305
Posted Tuesday, December 11, 2012 1:36 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 19, 2014 1:58 PM
Points: 4, Visits: 104
Through the trace, I'd be able to get all the pieces of data I mentioned in the OP?
Post #1395307
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse