0

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:

  1. 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.

  2. 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?

  3. 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?

  4. 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.

EDIT:

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?

closed as too broad by kevinsky, mustaccio, Marcello Miorelli, MDCCL, Paul White Jun 20 at 4:20

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

1

Not a full answer to all your questions but:

What reasons makes the query stop from increasing CPU time?

As well as heavy IO (though I would expect CPU time should slowly tick up in that case too), there is the possibility that it is sat idle waiting for locks to be released by one or more other transactions. Check the relevant output of sp_who2 (built in but undocumented) or sp_whoisactive.

I have heard about trace flags and extended events, but I do not have any experience with this.

Trace flags are not something to play with without a specific and well understood need. Extended events will be useful for tracking plan execution amongst many other things, but a full run-down is almost certainly out of the scope of a DBA.SE answer. There are plenty of examples out there, and presumably official documentation (though that tends to be optimised for reference, not initial learning).

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