![]() This of course removes the benefits of query plan reuse, but it does ensure that each time the query is run, that the correct query plan is used. With this option added, a stored procedure will always recompile itself and create a new query plan each time it is run. In this case, what you can do is to ALTER the stored procedure and add the WITH RECOMPILE option to it. ![]() If you identify a stored procedure that usually runs fine, but sometimes runs slowly, it is very possible that you are seeing the problem described above. But, in some circumstances, it can cause a problem, assuming the parameters vary substantially from execution to execution of the query. Most of the time, you probably don’t need to worry about the above problem. But if the parameters are not typical, it is possible that the cached query plan that is being reused might not be optimal, resulting in the query running more slowly because it is using a query plan that is not really designed for the parameters used. If the values of the parameters of the stored procedure are similar from execution to execution, then the cached query plan will work fine and the query will perform optimally. But what if the same stored procedure is run, but the values of the parameters change? What happens depends on how typical the parameters are. If the query inside the stored procedure that runs each time has identical parameters in the WHERE clause, then reusing the same query plan for the stored procedure makes sense. So if you need to run the same stored procedure 1,000 times a day, a lot of time and hardware resources can be saved and SQL Server doesn’t have to work as hard. Each time the same stored procedure is run after it is cached, it will use the same query plan, eliminating the need for the same stored procedure from being optimized and compiled every time it is run. Whenever a stored procedure is run in SQL Server for the first time, it is optimized and a query plan is compiled and cached in SQL Server’s memory.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |