SQL Server - How to identify and collect time consuming query information using Trace (SQL Server Profiler)

Views: 2.413
Reading Time: 4 minutes

Hello everybody!
How are you ?

In this post I will demonstrate to you how to identify and collect information from time consuming queries using Trace (SQL Server Profiler). This is very useful to assist you in performance tuning analysis, making it easy to identify routines that have high response time, both procedure execution and ad-hoc queries.

In everyday life, I use this feature a lot to help me identify timeouts in applications (Query Duration = maximum timeout time configured in the app) and possible queries candidates to perform a performance tuning job.

The flow of this routine works as follows:

  • Checks if the trace is already active.
  • If trace is active, deactivate trace and close the file
  • Creates the query history table (if none exists).
  • Read data from trace file and insert into history table
  • Activates the xp_cmdshell feature dynamically (if not enabled)
  • Delete trace file
  • Disables the xp_cmdshell feature dynamically (if not previously enabled)
  • Create the trace again
  • Enable newly created trace

The idea is to create a Job that runs every X minutes that performs this entire process, clearing the trace file and inserting the collected data in the history table so that the DBA's can consult the data read from the trace file.

If you want to know a solution that uses Extended Events (XE) instead of Trace, which is a more modern and intuitive technology, read my article SQL Server - How to identify and collect time consuming query information using Extended Events (XE).

Collection routine source code

Result:

In my routine, I set it to collect any query that runs for more than 7 seconds. Feel free to change and apply filters as needed.

That's it folks!
A hug and see you next.