Performance problem after data load

[katbear]

We have a table that is freshly loaded with data each day.

After the data load and additional updates on the table, a particular sp (with several sub-sp's in it) performs very slowly. However, if we recompile the sp by running it or doing an ALTER on it, it seems to "fix" the problem most of the time (temporariliy at least, until the next data load).

What could be causing this?

We've thought of several possible causes:

* updated statistics on the table causing new execution plan
* recompilation of the sp slowing things down
* data needs to be loaded into memory again

I'm going to run a profiler on it, but just wondering if there any OBVIOUS reasons for this behavior. I'm not a tuning expert.

Thanks

 

[ptheriault]

I would recommend that you update the stats after you import the data. That would be the most obvious cause to me.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[mrdenny]

To me it sounds like the procedure is using a cached execution plan. Is the stored procedure run only once per day (after the data load is complete)? If so try rebuilding it with the RECOMPILE option. This should force it to recompile each time it's run.

After doing a major data load it's always recommended to update stats.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[katbear]

Ok, I have a question here.

Do updated statistics on a table FORCE a new execution plan the next time a stored proc is run? Or does it just use whatever's cached, even though the stats are updated?

 

[mrdenny]

Updateing stats should invalidate the existing execution plan. You can verify this by doing an sp_recompile on the stored procedure in question.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com