With the proc compiled for a low row result, the next search is for S%. The last name index realizes this is very specific and will return, lets say, 3 rows from a million - so one execution plan is built. Use of Recompile makes SQL Server to recompile to SP, hence instead of using existing plan for same SP, Database engine will create new execution plan. However, the real question one should ask is when do we actually need to recompile the stored procedure.
STORED PROCEDURE RECOMPILE HOW TO
First pass someone searches on Zebr% for zerbrowski. Question: How to Recompile Stored Procedure Answer: The answer to this question is here: EXEC sprecompile NameofSP Well, that’s it technically we answered the question which was asked to us. Stored procedures do ' parameter sniffing' which is a blessing (if it works for you) and a curse (if it works against you).
So when would you want it to recompile? Let's look at one example of a proc that searches by last name. With that said, the presumption in your question is that recompiles are always bad for performance.
Optimizing SQL Server Stored Procedures to Avoid Recompiles.Troubleshooting stored procedure recompilation (an oldie, but a goodie) When a ddl occurs on underlying tables such as (add column), the stored procedures become invalid.But here are some resources that may be of use: The query optimizer evolves and suprises no matter how long you've been using SQL Server. query plan ages out (think not used recently and sql want's to clean up memory use).Creating/dropping temp tables (again, underlying DML changes).Rebuilding indexes (a change to underlying objects).If there is already a plan in cache for this specific stored procedure, it’s intact. Inserting or deleting lots of data (data density in indexes & tables often controls query plans) When executing a stored procedure with a WITH RECOMPILE option in the EXECUTE statement, a new query execution plan is created and used for this specific execution, but it’s not stored in cache.Some of these are interwoven with the list above, but I want to re-present them b/c it might not be obvious. At the query level an individual statement within a proc can be recompiled with the RECOMPILE query hint (SQL 2008).īesides the hard-factors listed above, what causes stored procedure recompilation? Well, lots of things.changing the schema that a cached query plan relies upon.marking the proc for recompile with sp_recompile. If you are writing a SQL queries or stored procedures, probably sometimes you noticed that stored procedure execution sometimes is quick and sometimes is.making the stored procedure dynamic (think exec()).Now, the CPU usage stayed beyond 90 constant. In order to mitigate this, we have changed the procedure to do a recompile every time it executes. As the number of execution grows, one of the bad plans causes a 100 CPU spike. There are a few ways to ensure recompilation of a stored procedure: 2 There is a heavily queried stored procedure that uses a bad plan once in few days.
0 kommentar(er)
