Search code examples
sql-serverperformanceresponse-time

SQL Server query start end times

How can I get the temporal moments delimiting starting and ending an SQL query in SQL Server? If I set statistics time on, is there any way of adding the resulting cpu / elapsed time to the current datetime?

Thanks

Solution

  • You could use

    1) SQL Trace (including SQL Profiler) to intercept { SP:StmtCompleted | SP:StmtCompleted } with with following columns CPU time, Duration/Elapsed time, Starting & Ending time of every query

    enter image description here

    or

    2) Extended events sessions (SQL2008+)

    In both cases, these events (%:StmtCompleted) could have a negative impact on server performance so don't use this methods on a heavy used server (production). If you have performance problems on a production server this could aggravate the problem.

    3) Instead, if you want to detected those { queries / sql modules } with performance problems you could use

    SQL2005+ sys.dm_exec_query_stats

    SQL2008+ sys.dm_exec_proc_stats

    SQL2008+ sys.dm_exec_trigger_stats

    Example: 

    SELECT TOP(50) ... 
    total_worker_time *1.00 / execution_count AS Avg_CPU_time,
    total_elapsed_time *1.00 / execution_count AS Avg_Duration,
    total_logical_reads *1.00 / execution_count AS Avg_LIO_time
FROM sys.dm_..._stats 
ORDER BY Avg_LIO_time DESC

    You could use Glenn Berry's scripts http://sqlserverperformance.wordpress.com/2013/11/15/sql-server-diagnostic-information-queries-for-november-2013/