SQL Server timing take to run the SQL

Question

I am looking to have sql time take to run the sql in one go. How ever sys.dm_exec_query_stat , total_elapsed_time looks that it is a aggregation figures of same sql timing which run in the history. AS per the image attached total_elapsed_time is 254235971 mean a lot. When I run the sql manually I can see that run around 20 min.
AS far as no session number can get it from sys.dm_exec_query_stat, the outcome of the timing is looks aggregated? any way of getting same SQL with time duration to run

++++++++++++++++++++++++
SELECT TOP 50
qs.execution_count,
AvgPhysicalReads = isnull( qs.total_physical_reads/ qs.execution_count, 0 ),
MinPhysicalReads = qs.min_physical_reads,
MaxPhysicalReads = qs.max_physical_reads,
total_logical_writes as [Writes],
convert(money, total_logical_writes/(execution_count + 0.0)) as [AvgIOLogicalWrites],
AvgPhysicalReads_kbsize = isnull( qs.total_physical_reads/ qs.execution_count, 0 ) *8,
MinPhysicalReads_kbsize = qs.min_physical_reads*8,
MaxPhysicalReads_kbsize = qs.max_physical_reads*8,qs.total_elapsed_time/1000 as TotDuration_seconds,qs.total_elapsed_time,
CreationDateTime = qs.creation_time,
[sql_handle],
plan_handle,
SUBSTRING(qt.[text], qs.statement_start_offset/2, (
CASE
WHEN qs.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.[text])) * 2
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2
) AS query_text,
tp.query_plan,
qt.[dbid],
qt.objectid,object_schema_name(qt.objectid, qt.dbid)+'.'+object_name(qt.objectid, qt.dbid) as [object_name],
tp.query_plan.exist('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan";
/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/MissingIndexes') missing_index_info
FROM
sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text (qs.[sql_handle]) AS qt
--CROSS APPLY sys.dm_exec_sessions (r.sql_handle) AS st
OUTER APPLY sys.dm_exec_query_plan(qs.plan_handle) tp
ORDER BY AvgPhysicalReads DESC
+++++++++++++++++++++++++

Answer 1

Hi,

Try this:

SET STATISTICS TIME ON;
GO
--query
SET STATISTICS TIME OFF;
GO

SET STATISTICS TIME (Transact-SQL)
Displays the number of milliseconds required to parse, compile, and execute each statement.

Answer 2

In addition to what Cris says, what I typically do is:

DECLARE @d datetime2(3)
-- Do work here
PRINT concat('It took ', datediff(ms, @d, sysdatetime()), ' ms.')

Answer 3

Hi Cris This when run a SQL from client terminal and set up. however I am looking from DBA point what are the sql is running and how long. I dont think that setting is not help

thank you

Answer 4

If you want to capture the time for individual executions from other users, you will need to have a Trace or Extended Events sessions running that you can query. Beware that such tracing can, depending on the profile of your workload, take up resources that has a detrimental effect on the system.