SQL - How to determine why a stored procedures sometimes takes many times longer than usual
We have a recently implemented data warehouse and reporting solution. Generally, the process of pulling the information together takes around 8 minutes.
About once per day, one particular stored procedure takes much longer (~2 hours) than expected. There is no rhyme or reason to the time of day, and usually the next run will proceed normally, making this harder to track down.
So far, I've used the profiler to find which stored procedure is taking longer than expected, and have the expected execution plan.
How can I figure out why this happens?
If I understand correctly from the explain plan, you have 2 tables where you are doing an union all and then inserting the records.
I would split this into the following;
Have a temporary table which will represent the structure of the union all table.
1> Truncate the temporary table 2> Insert records from the first part of the union all query 3> Insert records from the second part of the union all query 4> restructure the actual insert to work from the temporary table
This should run multiple times faster; you can use appropriate selection criteria (where clause) to limit the two inserts which will be beneficial.