SQL Server Performance Forum – Threads Archive

stored procedure recompiling

I have a stored procdedure that is suddenly wanting to recomplie each time it is called. There have been no changes to it for a month nor to the stored proc that calls it. I have no idea as to why this is happening. Has anyone had this happen or have any ideas as to where I can look for why this is happening. Our users are not able to do their work because of this issue as it is creating a complete table lock on the database. Any help or idea is VERY MUCH appriciated. Thanks
Beth Beth Michuda
Database Analyst

Did you recompile the SP with sp_recompile? Also, if this keeps happening, try to drop and re-create the SP with the "WITH RECOMPILE" option.
– Tahsin

I did not recompile the sp with sp_recompile. I’m confused as to why I would want to drop and recreate the sp with the "with recompile" option becasue that will call recompiling as well – thus giving me a double recompile. Beth Michuda
Database Analyst

Recompiling simply adjusts the query plan for a stored procedure as data and indexes get modified in a database. There may be a table that is being called by your stored procedure that has changed, which is why you may need to recompile it in the first place. I would suggest that you read "recompiling stored procedures" in SQL Server Books Online. Since, we don’t know the nature of your stored procedure, try to use sp_recompile first and see if you still generate that error message. The "WITH RECOMPILE" option ensures that there isn’t a predefined cache plan. SQL server recompiles the stored procedure at each execution, which depending on your needs, may be justifiable, but it does slow down performance a bit. I would recommend that you recompile the "parent" SP and any "children" SPs of the ones you are having problems with as well just to ensure that you have an updated query plan – Tahsin

I’m not sure if that is our problem or not. I just ran sql profilier for that program and it seems that during the run of the program that its removed from cache and then reinserted.. every time its removed from cache it is then recompiled. Do you know what could be causing it to be removed from cache? Thanks again Beth Michuda
Database Analyst

A long shot here .. but perhaps when the SP was created, the "WITH OPTION" was already specified. Were you the one who originally created this SP or are you just running it? If you weren’t the one who created it, I would suggest dropping and re-creating the SP without the "WITH OPTION" and running your profiler trace again. Also, in your profiler trace, ensure that you are not selecting the SP.CacheRemove and SP.Recompile event classes. These articles may provide you with some guidance:
http://www.mssqlcity.com/Articles/Adm/stored_procedures_administration.htm
http://www.sqlservercentral.com/columnists/bkelley/procedurecache.asp
– Tahsin

http://www.sql-server-performance.com/rd_optimizing_sp_recompiles.asp too an useful tips andhttp://www.sql-server-performance.com/rd_temp_tables.asp for information. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>