I'm looking for a solution to the following case:

I have a trace(only captures queries above 5000ms duration, which is about 20 per hour) running in MSSQL which I can query by using:

SELECT TextData, Duration, CPU, Reads, Writes
FROM fn_trace_gettable(N'C:\temp\TRACE.trc', DEFAULT)
WHERE EndTime > DATEADD(MI,-15,GETDATE()) AND Reads < 10 AND Writes < 10

I want this query to run this query every 15 minutes and the results should be written(each result in it's own event log) to the eventvwr using xp_logevent. Our monitor software will then trigger on the results of the Event Viewer.

I tried to achieve this by putting the results in a temp table, and then using a while to loop through each result. Because there are no ID's in the data, I can't keep count of the rows. Should I look at a SELECT TOP 1 query which deletes the row after processing to the Event viewer? Or is there an easier way?

Could someone point me in the right direction? PS: This should work both on SQL 2008 R2 and SQL 2012

  • Can you please explain what you are trying to accomplish with this trace? This seems like an awfully expensive operation, and you might get some suggestions on simplifying things if we had more details. You could use a cursor to loop through the records and write each one to the event log. Again though, this would be a super-expensive operation depending on the amount of activity on the server, and the definition of the trace. – Mark Wilkinson Mar 14 '14 at 12:24
  • I'm trying to trace the database for long duration queries which have low reads or low writes. The nature of the application using the database executes many queries which shouldn't take longer than 500ms normally. During performance problems we often see that the duration of some queries is over 1000ms. Therefor we made a trace which captures all queries over 5000ms. During normal production this should result in a few queries per hour(about 20). 99% of these queries is high reads/writes and won't show up due to the WHERE clause in the query. The trace isn't causing any performance issues. – Thom Mar 14 '14 at 12:38

A CURSOR with the above query as it's definition would allow you to insert the records into the event log one-by-one.

Another option you may want to look at is querying sys.dm_exec_query_stats:

    sys.dm_exec_query_stats AS qs
    CROSS APPLY sys.dm_exec_sql_text( qs.sql_handle ) AS st
    CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS deqp
    qs.max_elapsed_time DESC

This query will return the top 10 longest running queries along with their plans. This is just a sample, it could easily be tweaked, and may point you in another direction.

  • Thank you for your answer. I'll take a look into the cursor because it seems todo what I need. I've tried sys.dm_exec_query_stats before, but it wasn't really usable in my situation. – Thom Mar 14 '14 at 13:06
  • No problem, if it works out for you, please remember to mark this as answered. Also note, if you do run the query above it will only show the specific statement that took a while, if that statement is part of a larger procedure it will not show you that, but it will have a clickable link to a graphical execution plan. – Mark Wilkinson Mar 14 '14 at 13:12

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.