eftpotrm
eftpotrm

Reputation: 2281

Why would a SQL Server procedure run intermittently slow with identical input?

I've got a multi-query stored procedure which has started running intermittently very much more slowly - from < 1s to 15-20s minimum, sometimes up to a minute.

Ordinarily I'd suspect this was a parameter sniffing issue causing an inappropriate execution plan. However, it's doing this with totally identical input and no other load on the server, and it's done the same on multiple machines, through the client application and SSMS, and on SQL Server 2017 and 2019. Nothing in its processing contains any random or time-sensitive code, so it should perform identical operations with identical input.

I've tried running the call in SSMS with the execution plan display on. There isn't an obvious subquery which is intermittently slowing down, it's more that the whole thing slows down as one. Activity monitor isn't showing any obvious long-running queries either.

What else should I be looking for as to why this procedure is sometimes running very much more slowly?

Upvotes: 0

Views: 351

Answers (0)

Related Questions