June 10, 2008 at 4:43 am
Is it advisable to run sp_recompile on a table after:
a) updating statistics on a table?
b) reindexing a table?
If so, is there a way to do this easily in a maintenance plan?
June 11, 2008 at 8:04 am
sp_recompile is not required after either of these events. They both will lead to recompile of all necessary objects.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 11, 2008 at 9:22 am
I'd only run this on particular objects if things were out of whack and I had very bad query plans, or if my distribution of data changed substantially.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
June 11, 2008 at 9:23 am
sp_recompile looks for an object in the current database only.
The queries used by stored procedures and triggers are optimized only when they are compiled. As indexes or other changes that affect statistics are made to the database, compiled stored procedures and triggers may lose efficiency. By recompiling stored procedures and triggers that act on a table, you can reoptimize the queries.
Note:
SQL Server automatically recompiles stored procedures and triggers when it is advantage to do this.
MCP, MCTS (GDBA/EDA)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply