5/21/2023 0 Comments Sql option recompileDon’t be confused by query cost – let’s look at Statistics IO: And when we run select for = ‘USA’, it re-uses the same plan which is extremely inefficient. But the problem is that plan has been cached. This makes sense – we have only ~1% of canadian customers – value is selective enough. When we run the first statement with = ‘Canada’, SQL Server chooses to use non-clustered index on Country and perform key lookup. Most part of the client libraries would generate code like that. Now let’s execute the query that selects customers for particular country. In addition to that let’s create an index on Country column. Let’s create the table and populate it with some data with ~99% of the customers in US. Let’s think about company that is doing some business internationally although most part of the customers are in USA. Next time, when SQL Server reuses the cached plan, there is the chance that plan would not be good for another set of values. As result, the plan (which will be cached) would be optimal for specific set of parameters provided during compilation stage. SQL Server is looking at actual parameter values during compilation/recompilation stage and using them for cardinality estimations. The interesting thing happens in case if query has parameters. As result, SQL Server tries to cache execution plan and reuse it when the same query runs again. Compilation itself is not cheap, especially in case of complex queries. When we submit the query to SQL Server, one of the first things SQL Server is doing is compiling the query. Let’s look at extremely oversimplified picture. But first of all, let’s talk why do we need to worry about that at all. Such as parameter sniffing, search with optional parameters and filtered indexes. 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.Today I’d like us to talk about statement level recompilation and how it could help us in a few particular scenarios. 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. 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).
0 Comments
Leave a Reply. |