I believe what you are seeing is the compilation of the function. That is not the same as DDL.
This is just how it's displayed when we query SQL DMVs.
When it ages out of plan cache, a recompilation is forced. There are other reasons this occurs, which can be discussed as needed.
Let me know if we need to go deeper into this.
I'm also seeing the following in DPA SQL text:
CREATE TRIGGER TActiveSessionsE ON dbo.myAppTable FOR Update
SET NOCOUNT ON
/* BEGIN ACTIVE SECTION (inserted by DPA) */
I've traced the execution of DDL like this (Create Trigger) and the use of the same Trigger and when you are simply using the trigger, SQL:StmtCompleted reports as you would expect.
There are only so many documented ways to get the SQL statements out of MS-SQL so I'm assuming DPA is using the SQL:StmtCompleted.
Can you please confirm this, so I can do an apples to apples comparison?
If as you suggest we are observing the compilation of a Function and or Trigger, how could you identify if a script has run amuk and starting issuing DDL against your database versus 'normal' use of a Function or Trigger.
Thanks for looking into this further.
Sorry about that - after reviewing again, I believe I misspoke.
It looks like what you are seeing is the complete definition of the SP, whereas Ignite/DPA is calling out which SQL statement within the SP that's accruing the waits.
In the first case, we're seeing this statement running within the SP referenced. This is likely not compilations as I originally mentioned - sorry about the confusion on this one!
Select @intTotal =
SELECT Count(DISTINCT ScheduleNo)
AND OrderNo = @OrderNo
AND vCareType = @CareType
AND VDat BETWEEN @FromDate AND @ThroughDate
Hope that makes sense.