Need to understand slowness of a query

Question

Hi All,

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.

1. Need help to see if there is any difference in the plans based on different parameters.
2. 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.
3. 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)

Thank you.
Sam

187823-14175203-goodrun.xml187730-14214922-badrun-4mins.xml

Answer 1

You would need to find the slow query. One tool that is good for this sort of task is Lee Tudor's sp_sqltrace, which you find on my web site: https://www.sommarskog.se/sqlutil/sqltrace.html. You give the procedure call as the first parameter, and it will then set up a trace filtered for your spid. You can then easily see where time was spent.

I did a quick compare of the plans in Beyond Compare, and there was not a whole of differences, so I guess that you have the same plan in both cases, but there is query that is sensitive to the parameter value.

Once you have tracked down the slow statement, my gut feeling says that OPTION(RECOMPILE) is the easy way out.

Answer 2

-------------------------------------------------------------------------------------

Test runs

-- good run -- 5 secs
declare @scode varchar(250), @sdesc varchar(4000);

EXEC [dbo].[sp_match]
@BID = NULL,
@Jae = 14175203,
@scode = @Sta _code OUTPUT,
@sdesc = @Sta _desc OUTPUT

-------------------------------------------------------------------------------------

-- bad run --- taking ~ 4 mins
declare @scode varchar(250), @sdesc varchar(4000);

EXEC [dbo].[sp_match]
@BID = NULL,
@Jae = 14214922,
@scode = @Sta _code OUTPUT,
@sdesc = @Sta _desc OUTPUT

Go

Answer 3

What you describe is always due to "parameter sniffing".

I suggest you read:
https://www.brentozar.com/archive/2013/06/the-elephant-and-the-mouse-or-parameter-sniffing-in-sql-server/