0

This question already has an answer here:

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?

marked as duplicate by Erik Darling sql-server Jul 15 at 23:56

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • @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? – BeginnerDBA Jul 16 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. – Erik Darling Jul 16 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. – BeginnerDBA Jul 16 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 at 7:47
-2

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.

https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-set-options?view=sql-server-2017#auto_update_statistics_async

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