![]() ![]() You can read on in the post to learn about this optimization, or read the whole thing to learn more about parameter sniffing and the different ways you can change SQL Server's behavior. ![]() Using the query hint instead means that a compiled plan can be cached, and performance information is available in the DMVs (though it is limited to the most recent execution, for the affected statement only).įor instances running at least SQL Server 2008 build 2746 (Service Pack 1 with Cumulative Update 5), using OPTION (RECOMPILE) has another significant advantage over WITH RECOMPILE: only OPTION (RECOMPILE) enables the Parameter Embedding Optimization. As a result, no performance information is maintained in DMVs such as sys.dm_exec_query_stats. Using WITH RECOMPILE also means the compiled plan for the stored procedure is not cached. This query hint results in a recompilation of the problematic statement only execution plans for other statements within the stored procedure are cached and reused as normal. A better alternative, on SQL Server 2005 and later, is to use the OPTION (RECOMPILE) query hint on just the statement that suffers from the parameter-sniffing problem. Using WITH RECOMPILE effectively returns us to SQL Server 2000 behaviour, where the entire stored procedure is recompiled on every execution. Unfortunately, that advice is often misinterpreted to mean adding WITH RECOMPILE option to the stored procedure. When a parameter-sensitivity problem is encountered, a common piece of advice on forums and Q&A sites is to "use recompile" (assuming the other tuning options presented earlier are unsuitable). I'll quote the relevant bit from Paul White's blog post, Parameter Sniffing, Embedding, and the RECOMPILE Options: I would opt for statement-level OPTION (RECOMPILE) every time. She primarily focuses on the database domain, helping clients build short and long term multi-channel campaigns to drive leads for their sales pipeline.I don't think it ever is (unless you are stuck in SQL Server 2000 and are looking for pity - don't worry, you'll have it). Nupur Dave is a social media enthusiast and an independent consultant. If you need help with any SQL Server Performance Tuning Issues, please feel free to reach out at is also a CrossFit Level 1 Trainer (CF-L1) and CrossFit Level 2 Trainer (CF-L2). Pinal is an experienced and dedicated professional with a deep commitment to flawless customer service. To freely share his knowledge and help others build their expertise, Pinal has also written more than 5,500 database tech articles on his blog at. Pinal has authored 13 SQL Server database books and 40 Pluralsight courses. Adjust the index on the tables (change from table scan to table seek). Use query hints such as 'Keep Plan' or 'KeepFixed Plan' while calling this statement. He holds a Masters of Science degree and numerous database certifications. Create Procedure with Recompile option and EXEC with Recompile option. Pinal Dave is an SQL Server Performance Tuning Expert and independent consultant with over 17 years of hands-on experience. Sys.objects o ON ps.object_id = o.object_id SELECT SCHEMA_NAME(SCHEMA_ID) SchemaName, name ProcedureName, Here is the simple script which you can run to identify which stored procedure is cached for your database and various associated properties of the stored procedure. One method can be used when you execute the stored procedure and another method that can be used to make stored procedure is ready for recompilation and recompiles during its first run. ![]() So now you know different methods to recompile stored procedures. Essentially this method removes the cache from the procedure cache. This method will recompile the stored procedure in the first run of the stored procedure. Here is a neat trick where you can make your stored procedure for recompilation but not actually execute it. Method 2: sp_recompile for Recompile Stored Procedures This method will recompile the stored procedure as soon as the stored procedure executes it. You can recompile your stored procedure while you execute it. ![]() Here are two easy methods: Method 1: WITH RECOMPILE To recompile a stored procedure you do not have to recreate the stored procedure. At that point, I realized that I should have a blog post that explains to easy way to recompile stored procedures. To recompile the stored procedure, DBA opened the stored procedure in SSMS and recreated it by using the alter statement. I told the DBA who was working with me to recompile the stored procedure so we have to brand new plans for the stored procedure. Recently while working with a large financial organization on Comprehensive Database Performance Health Check, I found an instance where stored procedure had a very incorrect execution plan cache. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |