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 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).

share|improve this question
add comment

3 Answers

up vote 1 down vote accepted

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

share|improve this answer
add comment

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

share|improve this answer
    
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 –  Senthil Prabhu Sep 17 '13 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 ). –  Sagar Makhesana Sep 17 '13 at 13:20
add comment

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)
SELECT TOP 10
            source_code ,
            stats.total_elapsed_time / 1000000 AS seconds ,
            last_execution_time
    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/

SELECT TOP ( 10 )
        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] ,
        qs.cached_time
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
OPTION  ( RECOMPILE );
share|improve this answer
add comment

Your Answer

 
discard

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

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