Massive jump in stored procedure execution time

Question

Massive jump in stored procedure execution time

ertemen

SSC-Addicted

Points: 479
More actions
February 27, 2012 at 9:59 am

#256437

Hi All,
I have a procedure which inserts 5 million rows to a table currently has 212 million of rows.
Procedure logic is roughly
INSERT INTOTable A
SELECT Sum(Column1), sum(column2), ....
FROM
(SELECTsum(Column3), sum(column4), .......
FROM table B Where Criteria
GROUP BY Column...
UNION ALL
SELECT sum(Column5), sum(column6)
FROM
(Select column7, column8
FROM Table C
Where Criteria
UNION ALL

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Can you explain the problem a little more?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ertemen SSC-Addicted Points: 479 More actions · Answer 2

Hi,

Thanks for the reply first of all. Also most of the text seems to have been removed from the context. I don't why it happened. This time I have included a cutdown version of the actual procedure as an attachment.

Anyway, we have a stored procedure which populates a large table - around 200 million rows -. For the same month overall time it takes about 1 hour 45 minutes. Tables from which the procedure reads data has additional 2000- 3000 rows during the same month.

When a particular month is closed however, procedure time jumps up t0 4 hours to 4 and half hours - you might say that it could be due to additional rows being brought in from the tables it reads data from but the next day or so it comes back down again 1 hour 45 minutes mark and this pattern repeats every time a particular month is closed and new one is introduced.

I have checked the data volume increase that might happen every time a month is closed but actual amount of data don't change much and other stored procedures which process data for source tables for this proc takes about same amount of time every time.

It is puzzling for me why such a thing might occur.

I have included the actual execution plan for before and after event. you will see that there are lots of eager spools and the main difference between the two is actually amount of data being pumped from one operation to another.

I am after some ideas that might help to reveal the root cause of this.

In the meantime, I am investigating this as well. I will post it online if I find anything.

ertemen SSC-Addicted Points: 479 More actions · Answer 3

I have attached the cutdown version of the procedure

ertemen SSC-Addicted Points: 479 More actions · Answer 4

Hi Guys,

I have found what the problem was. This table has 19 non clustered in all of the foreign keys most of which are not even used anywhere. I have removed those and problem went away.

Any tips for further improvement would be appreciated.

E