Don’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.
#With recompile code#
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. Today I’d like us to talk about statement level recompilation and how it could help us in a few particular scenarios.
0 kommentar(er)
