Duplicated SQL Server Execution Times For Dynamic Query

All we need is an easy explanation of the problem, so here it is.

Duplicated SQL Server Execution Times For Dynamic Query

I’m getting duplicated SQL Server Execution Times from SET STATISTICS IO,TIME ON with dynamic queries. Is this a normal behavior?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

You get an output for each statement. The query in @sql is one statement that gives you the first output and calling exec is a statement of its own and outputs duration for all the statements in @sql.

So if you have two statements in @sql you get three outputs.

exec (N'select * from dbo.YourTable; select * from dbo.YourTable;');

Output with set statistics time on:

(74 rows affected)

 SQL Server Execution Times:
   CPU time = 16 ms,  elapsed time = 164 ms.

(74 rows affected)

 SQL Server Execution Times:
   CPU time = 15 ms,  elapsed time = 144 ms.

 SQL Server Execution Times:
   CPU time = 31 ms,  elapsed time = 309 ms.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply