I am aware of generic Microsoft SQL Server Performance counters and other ways to monitor general SQL server performance through monitoring system, files, and executable performance, and its internal metrics etc. and I monitor that today through an enterprise wide monitoring solution.
I am wondering what, if any, query specific metrics (specific stored procedures) are exposed outside of Microsoft SQL server that I could query, collect, and monitor outside of SQL Server consoles, AND not requiring extensive permission. The monitoring account for example cannot run sp_who and sp_who2 and I am currently concerned with specific stored procedures.
So for example if I know an application that when it performs a particular task it kicks off sp_Task1, and then sp_task2.. etc. I need to monitor, alert on long duration, and track long term run times of each of those tasks through an already adopted enterprise monitoring solution that does not currently have the ability to do it itself.
I need this as the application is 'sensitive' to long run times and it can set off a chain of other negative events. Using the image below, I am more concerned about width of the wave front trough-to-read trough than total height or peak-to-peak as if the waves touch things go down hill for a lot of applications really fast.
Though I am trying specifically know how to monitor the run time of a stored procedure from outside of Microsoft SQL server consoles…
My question is: What query specific counters / metric if any are exposed for monitoring outside of SQL Server itself in MS SQL Server 2008 or later?
Best Answer
SQL Server does not expose performance counters for any user defined object.
In fact PerfMon architecture is not a suitable platform for highly dynamical changes. If SQL Server would provide such feature it has to create counters every time a SP created. Think about SP renames, restores etc.
A couple of suggestions for monitoring problem of yours would be :