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?

Answers


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.


Need Your Help

All possible ways to reach an ending position

java algorithm path catalan

http://www.cstutoringcenter.com/problems/problems.php?id=103

JavaScript not working when moved to an external .js file

javascript jquery html css

I had working a java script in my html page.Now i moved the script to an external js file.