Post reply

Possible to find the parameters passed to an SP for performance tuning?

  • June 30, 2015 at 12:49 pm

    #299534

    Ran into an issue with a stored procedure earlier today. The SP was taking a *long* time to return results which was causing it to block some other queries.

    Now, I've got a query that pulls the SQL text from:

    sys.dm_exec_requests

    sys.dm_exec_sessions

    sys.dm_exec_connections

    sys.dm_exec_sql_text

    so I knew *which* SP was jamming things up. What I didn't know, and still don't know how to find out, is what the parameters being passed to the SP were. I've taken a look at both what's available in Extended Events and server-side Trace, and it doesn't look like there's a way to get this info...

    So, the question is, *is* there a way to find out (other than asking the user) what parameters are passed to a stored procedure?

    Lastly, due to where I work, I can't post the stored procedure, which I know, makes this a very hypothetical question for everyone. I'm not asking for help in tuning the query, just suggestions on how to find what it was running with (in the future) for next time...

    Jason

  • June 30, 2015 at 3:55 pm

    #1809331

    jasona.work (6/30/2015)

    Ran into an issue with a stored procedure earlier today. The SP was taking a *long* time to return results which was causing it to block some other queries.

    Now, I've got a query that pulls the SQL text from:

    sys.dm_exec_requests

    sys.dm_exec_sessions

    sys.dm_exec_connections

    sys.dm_exec_sql_text

    so I knew *which* SP was jamming things up. What I didn't know, and still don't know how to find out, is what the parameters being passed to the SP were. I've taken a look at both what's available in Extended Events and server-side Trace, and it doesn't look like there's a way to get this info...

    So, the question is, *is* there a way to find out (other than asking the user) what parameters are passed to a stored procedure?

    Lastly, due to where I work, I can't post the stored procedure, which I know, makes this a very hypothetical question for everyone. I'm not asking for help in tuning the query, just suggestions on how to find what it was running with (in the future) for next time...

    Jason

    A batch level server-side trace will return the full text, which usually includes the parameters passed to the stored procedure as a part of the call.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • June 30, 2015 at 4:07 pm

    #1809333

    Cool, I'll give that a try tomorrow.

    Thanks Jeff!

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

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