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.