showing stored procedure input arguments in sys.dm_exec_query_stats?

• kevin.wu

  Mr or Mrs. 500

  Points: 581

  More actions

  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

• jude.pieries

  Default port

  Points: 1438

  More actions

  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

• jude.pieries

  Default port

  Points: 1438

  More actions

  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