SQL Server Performance

Tracking queries that don't finish using SQL Profiler

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by WingSzeto, Mar 6, 2008.

  1. WingSzeto Member

    I have a question reqarding tracking unfinished sql queries. But before I go further, I need to make sure that I understand how profiler works in SQL 2005. If I run the profiler with the build-in Long running query template, as I understand, it will record all the queries that are finished. If a query never finishes or I stop the profier before a query finishes, those 'unfiinished' queries will not show up in the profiler result. Please correct me if I am wrong on that. To further my understanding, say a 'finished' query takes 5 minutes to complete and it starts at 1:05 pm and end at 1:10 pm. If I start the profiler at 1:04 pm, this query won't show up in the profiler until at 1:10 pm mark. Am I also correct in that? This behavior will be the same no matter if I run the profiler live on the screen, or run profiler live but export the result to a text file or a SQL server table, right?
    If the above statements are true, is there a way in SQL 2005 that would allow me to track this 'unfinish' query in profiler or make it show up on the profiler result live as it starts? So if the 'unfinished' query start at 1:05 pm, I would like to see it show up at 1:05 pm with the end time being blank (or null) in Profiler. In SQL 2005, it has improved its internal system info tracking so much compared to SQL 2000 that I can track current running unfinished query easily by querying a system 'view' sys.dm_exec_requests. With this improvement in mind and this system view available, I am looking for a way to track those 'unfinished queries' before I end the profiler. Is it possbile? If it can't be done in profiler, is there another way that I can track the query activity in a result format similar to profiler?
    I have a custom SQL agent job that I start and stop the profiler trace between 3:00 am and 9:00 pm so if a query doesn't finish before the profiler ends, right now I won't see any unfinished queries.
    Any help or clarification on this is very much appreicated.
  2. satya Moderator

Share This Page