question

Ashwan1234 avatar image
0 Votes"
Ashwan1234 asked ErlandSommarskog answered

SQL Server timing take to run the SQL

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
+++++++++++++++++++++++++


114398-capture1.png


sql-server-general
capture1.png (45.4 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Ashwan1234 avatar image
0 Votes"
Ashwan1234 answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Criszhan-msft avatar image
0 Votes"
Criszhan-msft answered

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.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.