Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
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 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 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
show 2 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.