Create windows service to monitor/trace execution of sql sproc query
I would like to set up a windows service to run continuously and monitor execution of a particular sql query (stored procedure) and output info on the user details into a sql table. Can this be done using sqldiag or profiler utility on a sql server 2005 instance? It seems possible to utilise sqldiag tool to run as a service, collect info and write to a file but not to a sql table. On the otherhand profiler utility appears to allow a trace table to load when launched but can this be set up to run as a continuous service on the server?
Best Answer
You can create a server side trace instead of running profiler. Also check out this article (its also published in SQL Server Central.
While I think the best approach is @DaniSql's, an alternative is to query sys.dm_exec_query_stats to gather aggregate performance metrics of the query. You can do this on the fly, so it's got less overhead than setting up a server side trace. However, it is only aggregate information and it's dependent on the query still being in cache.
What exactly are you trying to monitor about the stored procedure?
You can capture input parameters, timings, io stats, anything almost, within the proc, write the data to a table. Without the need for an external 'watcher'.
