I am trying to troubleshoot the performance issue on one of the SP which executes throughout the day daily every 20 mins and on avg completes under a min.

For past couple of weeks it happens once in a week that it will stuck and keep running for hours. Stats maintenance is done daily for modified stats and automatic update stats on is ON

There is no change to SP neither too much of load being increased in that run time.

I recompiled that SP to see if the plan was bad due to parameter sniffing issue, but does not help.

Due to security constraints i am not able to share the plan or any table structure here.

I know without seeing plan and query its tough to comment, but need possible ideas on what else should i be checking here?

  • @Erik-- Only thing i notice is the bad plan is created in tempdb for bad run while for good run its getting created on that userdb itself. Not sure whats this? Jul 16, 2019 at 0:31
  • 1
    I'd check if there are any different options set between the two. sp_Blitz will tell you about non-default options. Jul 16, 2019 at 1:39
  • I checked and they are same. I have the cached execution plans in user db which when used work good, but really horrible ones in tempdb doing all sort of hash match etc. I am wandering if this is related to tempdb spill where plan gets created in tempdb rather than for userdb. Jul 16, 2019 at 1:43
  • My guess is "BAD query".When less rows are fetch by Bad query or any join condition then query perform under a min,But same query sometimes involve millions of rows and because Join condition is faulty... it takes hour to complete and also...created in tempdb.It is not clear how you have done testing of parameter sniffing.
    – KumarHarsh
    Jul 16, 2019 at 7:47

1 Answer 1


Is auto_update_stats_async on or off?

If this is off, it is possible that the query prepares, the stats are being discovered to be stale, and updated before the query executes.


Not the answer you're looking for? Browse other questions tagged or ask your own question.