May 24, 2010 at 12:02 am
Hi All,
I made one observation, i just need to confirm with you folks if my assumptions are correct.
I executed the same code, once directly and once wrapping it up inside Dynamic-SQL.. I set the Statistcs TIME ON and found that i get 2 more results in the Results Pane. I observed (or rather assumed) that the extra statistic results are from the Parsing and Validating the Dynamic-SQL. Is my assumption correct ??
The statement is executed :
Normal Query:
SET NOCOUNT ON
SET STATISTICS TIME ON
DECLARE @TIME DATETIME
SELECT @TIME = GETDATE()
SET STATISTICS TIME OFF
Result:
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Dynamic-SQL:
SET NOCOUNT ON
SET STATISTICS TIME ON
DECLARE @SQL VARCHAR(100)
SELECT @SQL = 'DECLARE @TIME DATETIME SELECT @TIME = GETDATE()'
EXEC (@SQL)
SET STATISTICS TIME OFF
Result:
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
-- Extra effort for parsing and compiling
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
-- What are these for?
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
-- What are these for?
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
I remember one similar thread in SSC about this, i am so forgetfull that i forgot the thread name/thread location. Can you guys please shed some light on the extra times i get during Dynamic Sql ?
TIA,
C'est Pras!
May 24, 2010 at 12:32 am
ColdCoffee....I too observed the result. Yes...You are correct! Second one will display 2 more result.
karthik
May 24, 2010 at 8:05 am
i dont know about the sequence but few other operations are also got performed here like
@sql variable assignment.
excecution of dynamic sql , displaying results , different type of locks
i watched it on profiler( see attachment) and found some extra events in case of dynamic sql though i am not aware of them but it concluded that there are some hidden events occured in case of dymanic sql
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply