Need some direction on query tuning a stored procedure. The stored proc has multiple sql statements inside it and ~500 lines of code which includes MERGE statement.
What's happening is , In Production, this stored procedure is taking more than an hour.
Note : the parameters passed to this stored proc changes.
I asked the dev team to provide some test cases for a GOOD run and BAD run. I have attached those as part of zip file which also contains Actual execution plans for both runs.
The good run case, It took ~ 5 secs.
The bad run case which they claim it taking more than 60 mins , it took around 4 mins when I ran it SSMS query window.
Not sure why there is big difference from 60 mins to 4mins.
I want to understand why it is taking 4mins for bad run.
The app team expectation for this query is 60 secs. Anything more than that, application calls are TIMING OUT.
Now they came back to DB team, to know why query was taking more than an hour. I said, it could be blocking during that time frame.
Could be some parameter sniffing issues. I am not sure. I am not able to tell confirmly. App team wanted to run a trace on prod.
I am thinking about to enabling a trace during that time frame especially when are seeing the issue.
So, Looking for some help on below.
- Need help to see if there is any difference in the plans based on different parameters.
- If I had to run a trace for live troubleshooting , what all events I need to select to run the trace?
what columns to select and what filters to keep to minimize the overhead of the trace. I should be able to capture blocking info, stored proc statements , if any parameter sniffing issues,
any missing indexes, all that stuff I should capture.
- Also, what all questions or data I should be collecting from the user/dev team so that we can narrow down the issue and take it to resolution or workaround?
SQL Server version is Microsoft SQL Server 2017 (RTM-CU23)