I have huge issues with a stored procedure that has not finished the last week. It seem slike it stops at around 140 000ms of CPU time and freezing there, while the elapsed time increases. This makes sense as the query is active but not doing anything.
The select from the query takes ~2 minutes to finish, while the procedure uses 3+ hours (I usually cancel job at 3 hours due to batch issues)
In general I would look for regressed/resource expensive in Query Store and analyze what has changed causing this query to take so much time, I cannot find it however.
My questions are:
What reasons makes the query stop from increasing CPU time? I assume this can me memory spill such as disk reads or sorting, but I do not know for sure.
Is it possible to find a query id (for tracking) when I cannot find it in Query Store? I have execution plan, SPID etc. I also know procedure name. How can I find it?
The procedure has paramteres as input, one which is used for a later join. Should I SET parameters in the procedure instead of just using them as input in procedure?
- Any other suggestions regarding tracking and monitoring of a given procedure? I have heard about trace flags and extended events, but I do not have any experience with this.
As a note, we tried force 2 join hints since these joins were used in fast select. Plan changed but performance did not change. I know this is a last resort solution.
I tried to reduce parameter sniffing issues by declaring and setting values from SSIS-input in the procedure, and this seemed to solve issues in test environment, however test environment chose bad plan when recompiling.
Production environment has bad plan as estimate, how can I ensure that it chooses the solution and not the problem plan?