A customer was asking about how to track all the stored procedures that execute on their system. We have a section in Redgate Monitor that’s set to look at stored procedures, and you can easily find information about them.
This is part of a series of posts on Redgate Monitor. Click to see the other posts.
Finding Stored Procedures
If I go to the SQLServerCentral_Test system on monitor.red-gate.com, I can scroll down and see the top queries for the time period selected. Here’s an example of a query. The main query window shows SQL, but these are parts of procs.
If I expand to the full query, we see this is part of a proc. That’s good, in that if the query causing problems is part of a procedure, I can track that down.
However, what if I know people are reporting a problem that is related to this proc. Can I find it knowing the name? I can’t in the Top Queries.
However, we’ve added a Top procedures item to the right. That’s the place to focus on if you use stored procedures. And you should, since they create a looser coupling between your schema and the various business things your app needs to do.
On this test system, there are relatively few procedures running, but on many systems in production, this is a large list of things. To the right (not shown) there are various metrics that help you determine which of these items might be stressing your system because of execution counts, duration, CPU, etc.
If I know I have a procedure, I can search using the box. Here, I’m searching for UserA, but you might be searching for FinanceReport, hoping to find which of the FinanceReport_A, FinanceReport_B, FinanceRepot_Prep, etc. procs is using resources.
Expanding any of these (clicking the arrow) gives you more detail, as you would get in the Top Queries section.
Summary
Tracking down which queries are stressing your system and why can be hard. We added the focus on procedures as this is an easier way for some customers to determine what part of their workload is causing issues. In the top queries, sometimes procedures get lost, so this section lets you drill down into how your system is performing.
Redgate Monitor is a world class monitoring solution for your database estate. Download a trial today and see how it can help you manage your estate more efficiently.