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?
Answer by Grant Fritchey ·
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.
Answer by Kev Riley ·
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'.