I have SQL Server 2008 Enterprise. I know how to set up auditing on the SQL Server, but it looks like the audit logs don't include query start/stop times.

We currently use a product called Splunk to do a lot of data analysis at our organization. I would like to be able to feed Splunk our machine performance data along with a SQL query audit log to be able to correlate high CPU usage to long running queries and things like that.

However, I would need query start/stop times to be able to do that. It doesn't look like the built in auditing has that ability. Is there any other way to achieve this?

Edit: I am aware of the event time column in the audit log. That gets me half way there as it is the start time. So, I either need the query length or the end time to get the data I need.

  • The event_time column in sys.fn_get_audit_file will help. SELECT event_time, database_name, schema_name, object_name, statement FROM sys.fn_get_audit_file('C:\SqlAudits\*.sqlaudit', default, default) Ref: msdn.microsoft.com/en-us/library/cc280765.aspx – Kin Shah Jul 9 '13 at 21:40
  • That gets me half way there. That is the start time, but I need the end time as well. Maybe I should have said in my description that I mostly need the end time. – Davin Studer Jul 11 '13 at 6:51
  • Auditing really isn't meant to do that. You'll probably have to run a trace or extended events session to capture that information. You should be able to use those to correlate against the PerfMon data. – Jon Seigel Jul 11 '13 at 13:15
  • @DavinStuder agreed with Jon that you have to use profiler to get the start and end time of the queries. Audit wont give you that flexiblity - its by design. As a side note, you can have a server side trace running (remember that capturing all the queries will have a performance impact on the server) and then a procedure that will import the trace data in a sql server table and then you can do your analysis . – Kin Shah Jul 11 '13 at 15:30
  • I've never used server side tracing ... I understand the concept just never done it. Is it ok to have a server side trace running all the time ... will this kill my db performance? – Davin Studer Jul 12 '13 at 22:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.