Stored Proc performance VS Index Rebuild | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Stored Proc performance VS Index Rebuild

Hi I have a stored procedure that normally takes a couple of minutes to run. Over the weekend I also run an optimisations job, which essentially uses xp_sqlmaint to rebuild indexes. After this optimisations job the stored procedure generally seems to take a longer time to execute. I’m on SQL Server 2000 SP4. Has anyone noticed a similar problem? Thanks
Does that mean that you had exactly the same proc and optimization jobs before you applied sp4 and only after you applied it stored procedure takes more time?
Sorry for not being clear. I had the same problem before applying SP4 and I installed SP4 hoping that would help solve it, but it didn’t.
It’s hard to guess the reason unless you give us more info. Have you tried to catch sp execution plan before and after index rebuild? Do you have fill-factor other then 0 on your indexes? Have you checked what else is running at the same time as the proc? Have you checked if there is locking issue during the run?
]]>