question

tw77 avatar image
tw77 asked

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?

sql-server-2005administrationprofilermonitoringtrace
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

3 Answers

·
DaniSQL avatar image
DaniSQL answered

You can create a server side trace instead of running profiler. Also check out this article (its also published in SQL Server Central.

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered

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'.

1 comment
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

tw77 avatar image tw77 commented ·
I need information on the user running this proc like login name, date time etc
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered

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.

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.