Is there a way to automatically record the duration and row count of the results of a stored procedure execution and save the plan?

I have a procedure i'm troubleshooting for possible parameter sniffing or an issue with the app server. I'm running it in SSMS with different parameters, but not getting the results i want and it's taking too long.

There is one parameter that will change and I can build a list of the parameters I want to pass it.

My question is there a way to have SSMS automate the execution of a procedure with a parameter changing every time and then record the duration and row count. and possibly save the execution plan.

Basically if I have a procedure with three parameters, I want to change the second one every time it executes from a list I create, automate the execution of a few hundred executions with a different parameter each time and record the duration and row count of each execution. And possibly save the plan.

  • 2
    You're on 2016 -- why not use Query Store? Extended Events is a good option to target a specific proc, too. Let us know what you've tried and if you've run into issues. Commented Mar 18, 2019 at 20:54
  • It's not activated here and it's a huge process to make a change. I've had an extended events trace going for a week and sometimes the procedure does take 40 seconds. The most i've had it take through SSMS is 11 seconds so I'm leaning towards the application being the issue but would like to automate the testing of the procedure with dozens of parameters to be sure.
    – Alen
    Commented Mar 19, 2019 at 12:51


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.