Something forces recompilation and if we are “lucky enough” to have first call with untypical set of parameters – the inefficient plan would be cached and used for all queries. And this is real problem if we have data that distributed unevenly. And reuses the same plan for Canadian customers. Now we can see the opposite effect – when we run this SP with = ‘USA’, it generates clustered index scan. The same thing happens if we use stored procedures. You can see that SQL Server chooses clustered index scan which introduces ~50 times less logical reads in compare with original non-clustered index seek. Just to prove inefficiency – let’s run the query again but use constant instead of parameter. 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.
#Tsql 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)
