SQL Server Performance Forum – Threads Archive
sp Recompile EventSubClass 3 help
Hi SQL Folks, I am testing some sql code for performance and trying to eliminate or offer suggestion on why this sp is recompiling. Here’s the statement or pseudo statement causing it: Create Table #T1… Insert Into #T1 Select… Update AliasForT1set AliasForT1.x = y
From #T1 AliasForT1
Where AliasForT1.x = xxx… Is the updating of the temp table causing the #3 recompile (object did not exist)? Or is the aliasing of it in this manner causing it? Or other ideas? Thanks for your help!
~Fish
http://www.sql-server-performance.com/rd_optimizing_sp_recompiles.asp
http://www.sql-server-performance.com/rd_create_stored_procedures.asp Qualifying the table or view and the columns you use is also good practice.
SQL Server recompiles a stored procedure or a cached query plan even if only one table1 exists because through the recompilation process. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
Thanks for info. Several other stored procedures where caused by the info you provided and info in the links. However, none of it made any sense for this particular issue. I am going to try and play around with the query today and see if I can get it to stop recompiling. Thanks again!
~Fish
Can you just run PROFILER and see the activity during this recompilation.
Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx This paper is an excellent reference to understand why recompiles happen. Although the paper is intended for SQL Server 2005 changes, the current implementation in SQL Server 2000 has been explained beautifully. Look at following article as well … Troubleshooting stored procedure recompilation
Pasted from <http://support.microsoft.com/default.aspx?scid=kb;en-us;243586>
Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
]]>