3 Replies Latest reply on Aug 4, 2015 2:10 PM by mandevil

    DDL observed in SQL Text of Executed queries

    bird8088

      I'm reviewing an application running on MS-SQL 2008 R2.

      When I review Current / Most Executed queries I discovered many with DDL in them being run thousands of times a hour.

      The developer swears this is impossible, but when I see DDL from what I'm assuming is the equivalent to SQL Trace SQL:StmtCompleted I'm puzzled.

       

      Is there any reason to believe that this DDL is not running as it is shown below?

       

      Thanks

      Steve

       

      Example:

      /* (inserted by DPA)

      Procedure: 1267SAMPLE.dbo.GetOrderScheduleCount

      Character Range: 227 to 422

      Waiting on statement:

       

      Select @intTotal =

         (

         SELECT Count(DISTINCT ScheduleNo)

         FROM Schedule

         WHERE vPayerNo=@PayerNo

        AND OrderNo = @OrderNo

        AND vCareType = @CareType

        AND VDat BETWEEN @FromDate AND @ThroughDate

         )

       

      */

      CREATE 

         FUNCTION [dbo].[GetOrderScheduleCount]

            ( 

            @PayerNo     varchar(3), 

            @OrderNo     int, 

            @CareType    varchar(25), 

            @FromDate    varchar(25), 

            @ThroughDate varchar(25) 

            ) 

            RETURNS int 

      .....

        • Re: DDL observed in SQL Text of Executed queries
          mandevil

          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.

          • Re: DDL observed in SQL Text of Executed queries
            bird8088

            I'm also seeing the following in DPA SQL text:

             

            CREATE TRIGGER TActiveSessionsE ON dbo.myAppTable FOR Update 

            AS 

               SET NOCOUNT ON

            /* BEGIN ACTIVE SECTION (inserted by DPA) */ 

               INSERT ActivityLog

                  (

                     AppID, 

                     Username, 

                     page, 

                     Login,

                     emid

                  ) 

               SELECT inserted.appid,

               ...

             

            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.

            Steve

              • Re: DDL observed in SQL Text of Executed queries
                mandevil

                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)

                   FROM Schedule

                   WHERE vPayerNo=@PayerNo

                  AND OrderNo = @OrderNo

                  AND vCareType = @CareType

                  AND VDat BETWEEN @FromDate AND @ThroughDate

                   )

                 

                Hope that makes sense.