Sql-server – What query specific counters / metric if any are exposed for monitoring outside of SQL Server itself

sql server

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.

enter image description here

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 :

  1. Creating a wrapper stored procedure for Adam Machanic's sp_WhoIsActive to filter out sessions you are not concerned about and apply practices at How to grant permissions for non-dbas section of sp_AskBrent from Brent Ozar to that stored procedure.
  2. You can log activities to a table using an agent job like every 15 second execute sp_WhoIsActive filter out unwanted records from resultset log them to a table then let GUI generate reports from that table. With a quick search while i'm writing this answer i have come across a blog post from Brent Ozar. I didn't read the whole post but the idea is same.