Post reply

showing stored procedure input arguments in sys.dm_exec_query_stats?

  • December 6, 2010 at 3:50 pm

    #388375

    I'm somewhat familiar with the DMV sys.dm_exec_query_stats that show SQL Server's query performance. This is great if you're passing in SQL from your application. But lets say you've got a stored procedure with a bunch of different input arguments.

    Is there a DMV where you can see the input arguments and the sql_handle and/or plan_handle?

    Our application's only interface into SQL Server is through stored procedures and I want to be able to find specific stored procedures with wildly nasty input arguments that has the slowest query performance. Is this possible?

    Thanks!

    -Kevin

  • December 7, 2010 at 1:38 am

    #1259414

    As sql server re-uses the same execution plan, its highly unlikely you will be able to capture what you intend.

    I would advice you to read this article from Kimberly (http://www.sqlskills.com/BLOGS/KIMBERLY/category/The-Tipping-Point.aspx)

    You can always free the database cache with Dbcc Flushprocindb() but this will have to be executed at the end of each procedures execution and will not allow you to take advantage of parameter sniffing

  • December 7, 2010 at 1:57 am

    #1259416

    As sql server re-uses the same execution plan, its highly unlikely you will be able to capture what you intend.

    I would advice you to read this article from Kimberly (http://www.sqlskills.com/BLOGS/KIMBERLY/category/The-Tipping-Point.aspx)

    You can always free the database cache with Dbcc Flushprocindb() but this will have to be executed at the end of each procedures execution and will not allow you to take advantage of parameter sniffing

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

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