I would like to monitor the script which takes more than 25 Sec. When i use filter i am not able to see the duration taken by the script. How can we get the time taken to execute the query.

My Requirement is simple:
Get all the scripts which takes more than 25 Sec(Time taken for execution of each statement is required).

3 Answers 3


25 seconds in the Profiler GUI is 25,000 ms. Use WAITFOR DELAY to test it is working, eg

25 seconds to go go go


Hello Senthil Prabhu,

When we are working with SQL Profiler we must keep in mind that we should not

OVERLOAD THE TRACES There is no easy way to decide which events or data columns you need to capture as part of a trace. It depends on the problem at hand, and it requires a good understanding of what events are available and what each one of them does.

As the SQL Profiler is Most powerful yet complicate to analyst tool

When you connect to SQL Profiler you can create a template of T-SQL Duration. and more in Event Selection Tab you can customize the object name and other filter so you will find only desire details.

TSQL_DURATION The TSQL_Duration template is used to capture all T-SQL statements submitted to SQL Server by clients and their execution time. It groups the results by execution time duration.

Hope this will help you.

Thank you, Sam

  • When you use filter greater than 2500ms there won't be any data in the duration column. That was the issue i know to bring the duration column but it was empty when i used filter Sep 17, 2013 at 7:59
  • It may be possible If there are no scripts that takes more than 2500ms time. can you please share screen shots of Profiler ( Genrel tab and Event Selection ). Sep 17, 2013 at 13:20

Instead of tracing your system live using profiler you can ask SQL Server which statements were expensive using DMVs(for queries that has been run once you can find out the info you need based on number of execution count, CPU usage, logical or physical disk IO or time elapsed(duration) etc)

So if your query is already executed once and cached you can use total_elapsed_time in the DMVs sys.dm_exec_query_stats and sys.dm_exec_procedure_stats to find out how long it took.

Source: http://www.sqlservercurry.com/2010/07/find-most-time-consuming-code-in-your.html

--Top 10 slowest queries that takes more than 25 seconds(25000000 micro seconds)
            source_code ,
            stats.total_elapsed_time / 1000000 AS seconds ,
    FROM    sys.dm_exec_query_stats AS stats
            CROSS APPLY ( SELECT    text AS source_code
                          FROM      sys.dm_exec_sql_text(sql_handle)
                        ) AS query_text
    WHERE stats.total_elapsed_time > 25000000
    ORDER BY total_elapsed_time DESC

Or you can use the below query to find the top 10 slowest stored procedures that takes more than 25 seconds(25000000 micro seconds)

Source: http://sqlserverperformance.wordpress.com/2013/08/08/sql-server-diagnostic-information-queries-for-august-2013/

        p.name AS [SP Name] ,
        qs.total_elapsed_time / qs.execution_count AS [avg_elapsed_time] ,
        qs.total_elapsed_time ,
        qs.execution_count ,
        ISNULL(qs.execution_count / DATEDIFF(Second, qs.cached_time, GETDATE()),
               0) AS [Calls/Second] ,
        qs.total_worker_time / qs.execution_count AS [AvgWorkerTime] ,
        qs.total_worker_time AS [TotalWorkerTime] ,
FROM    sys.procedures AS p WITH ( NOLOCK )
        INNER JOIN sys.dm_exec_procedure_stats AS qs WITH ( NOLOCK ) ON p.[object_id] = qs.[object_id]
WHERE   qs.database_id = DB_ID()
        AND qs.total_elapsed_time > 25000000
ORDER BY avg_elapsed_time DESC

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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