SQL-Server: Is there an equivalent of a trigger for general stored procedure execution

Question

Hi All,

Hope you can help.

Is there a way to reliably detect when a stored proc is being run on SQL Server without altering the SP itself?

Here's the requirement. We need to track users running reports from our enterprise data warehouse as the core product we use doesn't allow for this. Both core product reports and a slew of in-house ones we've added all return their data from individual stored procs.

We don't have a practical way of altering the parts of the product webpages where reports are called from. We also can't change the stored procs for the core product reports. (It would be trivial to add a logging line to the start/end of each of our inhouse ones).

What I'm trying to find therefore, is whether there's a way in SQL Server (2005 / 2008) to execute a logging stored proc whenever any other stored procedure runs, without altering those stored procedures themselves.

We have general control over the SQL Server instance itself as it's local, we just don't want to change the product stored procs themselves.

Any one have any ideas? Is there a kind of "stored proc executing trigger"? Is there an event model for SQL Server that we can hook custom .Net code into? (Just to discount it from the start, we want to try and make a change to SQL Server rather than get into capturing the report being run from the products webpages etc)

Thoughts appreciated
Thanks

Answer 1

You can only use SQL Profiler reliably: there is no execute hook or "execution trigger" to use.

Saying that, you can indirectly but not 100% reliably monitor the plan cache. See this link. However, if you have OPTION RECOMPILE then it is removed from the cache after use. Also, stats are reset after server start and you need to run the dmv to see what's in the cache too.

It depends how thorough you need to be...

Answer 2

You could setup a background trace that is run automatically when SQL Server initializes. Then, in your trace, you could output the trace statement to a table.

For example, open up SQL Server Profiler. Create the trace you would want, i.e. include SP:Starting and the columns you want. Also choose to save the trace to a table.

Now, after setting up the script, choose File/Export/Script Trace Definition. This will create a SQL statement that generates the trace.

Next, create a stored procedure that creates this trace using SQL. Install the proc in the master database and tell SQL Server to run it automatically on startup:

exec sp_procoption N'sp_MyProc', N'startup', N'true'

Now every time SQL Server restarts, it will configure your trace automatically, and all SP invocations will be logged to a table.

-- EDIT --

Also note that there are some Dynamic Management Views (DMVs) useful for monitoring stored procedures. Two you might be interested in:

• sys.dm_exec_procedure_stats
• sys.dm_exec_query_stats

For example, sys.dm_exec_procedure_stats will tell you when the proc was last run, how many times it was run, the longest execution time, etc. Note that these views only affect stored procedures currently in the database cache. If the proc is unloaded, so will be the information.

Answer 3

You can use a tool such as SQL Log Rescue(free) to query your SQL Log files http://www.red-gate.com/products/SQL_Log_Rescue/index.htm It can tell you which stored procedures have been run & when. This is the easiest method, since it doesn't involve modifying any existing code and doesn't increase the overhead of the applications & server.