Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
darinh
Yak Posting Veteran
58 Posts |
 Posted - 2004-05-17 : 19:42:24
|
| Hi,I have a select stored proc that was written 6 months ago when the primary table had about 20,000 rows. It took about .3 seconds then. Now the table has 1.2 million rows and it takes about 12 seconds. If I create a new stored procedure thats exactly identical except for the object name it takes about 0.8 seconds and has a completely different execution plan than the original. i.e. 2 identical stored procs run differently on the same database. I figured than the query optimizer figured out the best way to run this sp when the table was small and hasn't changed its mind as the table grew. I have tried dropping the sp and recreating it and rebuilding the stats on the offending table but it made no difference. I also tried changing the SQL slightly to try and force it to re-evaluate the way it runs. The database is set to Auto Create and Auto Update Statistics. Is there anything else I should try?Thanks. |
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2004-05-17 : 19:45:41
|
| Try EXEC sp_recompile 'Table or StoredProc name'...DavidM"Always pre-heat the oven" |
 |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-05-17 : 19:48:05
|
| Also, run DBCC FREEPROCCACHEMeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
darinh
Yak Posting Veteran
58 Posts |
Posted - 2004-05-17 : 20:06:21
|
| Thanks,sp_recompile cause it to recompile and it took 90, 24 then 13,12,12, 12.. seconds when I subsequently ran it. The execution plan didn't change. DBCC FREEPROCCACHE didn't seem to have any effect. |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2004-05-18 : 10:29:00
|
| I wouldn't do "Auto Update Statistics"....best practice would be to 'manually control the update'.....using some weekly/monthly housekeeping jobs. |
|
|
|
kselvia
Aged Yak Warrior
526 Posts |
Posted - 2004-05-18 : 14:44:39
|
| Forgive me for suggesting the obvious, but if the new one is fast and the old one is slow, drop the old one and run;sp_rename 'new_name', 'old_name'No explanation for why the execution times would be different in the first place but running sp_recompile should have fixed it.You might also try ALTER PROCEDURE proc () WITH RECOMPILE AS ...that will cause it to recompile every time it is run. Not usually a good idea but sometimes useful.The first time a stored procedure is run a query plan is generated based on the paramaters passed and the table statistics.That query plan is used for all subsequent executions. It might not be optimal for different passed parameters.Also run SET STATISTICS PROFILE ON set STATISTICS IO ONset STATISTICS TIME ONand run each version. You said the query plans were identical so I don't expect the statistics output to be different, but considering the execution time difference, something, someplace must be different.Cheers |
|
|
|
|
|
|
|
|
SP Performance Issue