Post reply

Problem Tracing Scalar Functions - Profiler does not match actual results

  • May 19, 2009 at 9:29 am

    #221390

    We noticed this week if we trace a SQL statement containing a CASE statment and a scalar function, the scalar function appears in the Profiler trace multiple times despite being invoked only once.

    STEP 1 - Run a Profiler trace checking only SP:Starting

    STEP 2 - Start a SQL Server job that will run for a few minutes (such as a backup or DBCC)

    STEP 3 - Run the script below

    -- Display the job name of any running SQL Server Agent job

    Select LEFT(Program_Name,30),

    CASE

    When es.Program_Name like 'SQLAgent - TSQL JobStep%' -- Only call function for SQLAgent jobs

    Then (select name from msdb..sysjobs where master.sys.fn_varbintohexstr(job_id) = substring(program_name, 30, 34))

    Else ''

    End as JobName

    From sys.dm_exec_sessions es

    GO

    The trace will show an ObjectName of fn_varbintohexstr multiple times with an event of SP:Starting. If you run SELECT COUNT(*) FROM sys.dm_exec_sessions you will see the record count matches the number of times fn_varbintohexstr was invoked. However, if you only have one SQL job running you will only see results in your query window for one SQL job. Our tests showed that despite Profiler listing the function as being started one time per record in dm_exec_sessions, it appears that it is executed only one time per running SQL job. This leads us to believe that either (A) we are tracing scalar functions incorrectly or (B) there is a bug with Profiler.

    Any thoughts?

    Thanks, Dave

  • May 19, 2009 at 4:38 pm

    #997307

    i think seeing a function called multiple times is normal.

    wouldn't this WHERE statement:

    WHERE master.sys.fn_varbintohexstr(job_id) = substring(program_name, 30, 34))

    be needed to be called one time for each row in sysjobs because you are translating each row value,fn and comparing it to a static value?

    if you did the reverse jobid = fn_stringtovarbinary??(substring(program_name, 30, 34)) it would be called just once, to convert the static value, right?

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • May 19, 2009 at 6:35 pm

    #997339

    You're absolutely correct. Not sure how I missed that. Thanks

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply