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?