Post reply

proc call inside proc call not showing in trace

  • February 21, 2012 at 9:49 am

    #268590

    So i have some front end functionality that calls a stored proc that has copius amounts of logic within it. Several selects based on scnerios. Well actually there are only three scenerios. Nonetheless, i'm breaking each scenerio out into it's own proc then the original proc will call one of these threee procs. 1 for each scenerio. I'm going under the assumption that this method will increase usage of cached optimized execution plan hits. Well i'm done and now running traces to monitor the cach hits and i can't seem to even see the nested proc calls in the trace. Am i missing something?

  • February 21, 2012 at 9:54 am

    #1449816

    what events are you using?

    my traces usually have both SQL:BatchCompleted and RPC:Completed

    RPC:Completed will return code when a proc is executed, whether in TSQL or when a command object has a command type = stored procedure

    SQL:BatchCompleted will return when code is executed in TSQL, or when a command object has a commandtype=text

    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!

  • February 21, 2012 at 10:03 am

    #1449822

    If a front end makes an RPC call to execute procedure 1 and procedure 1 calls procedure 2, then only procedure 1 appears as an RPC event, both will appear if tracing SP:Completed (stored procedure completed)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • February 21, 2012 at 1:27 pm

    #1449944

    thanks guys. That worked.

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

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