alexeyvg, тут как -то не совсем так написано?


авторEven though these two queries are essentially identical, they might each have a separate plan in the cache, just because the literal value is different in each case. Actually these two queries are so simple (with no joins) that SQL Server would probably parameterize them, even using just the default simple parameterization (as opposed to forced parameterization). However, in more complex cases, plan reuse would probably not be possible. While we can do our best to make sure queries are written in a way that enables plan reuse, users and utilities often execute batches that will simply have little reuse.
For SQL Server 2008, if you determine that you have a mostly ad hoc workload with minimal reuse, check out the "optimize for ad hoc workloads" system option. This setting changes SQL Server behavior and does not store a plan on first usage, only the query text. If it matches a second time, the plan is stored. Note that "ad hoc," in this sense, does vary from some people's understanding of the term. Here, it simply refers to the use of random queries that don't promote reuse.

После первого раза, вообще ничего кроме текста не сохраняется. Втрой раз текст парсится по новой, оптимизируется, и уже сохраняется план в кеш. Или я не допонял?

А во у меня есть большушие склейки с кучей инструкций скл. Я знаю, что они второй раз точно такие же не придут. Можно как-то сказать серверу, что бы он их не клал в кеш планов,
Без взведения глобальных опций?

Shakill,



в склейке несколько отдельных запросов. Даже если у них указывать у каждого эту опцию, то целиком весь пакет все равно кладется в кеш

alexeyvg, нашел, кажись

авторthe compiled plan stub
The stub that SQL Server caches when Optimize for Ad Hoc Workloads is enabled is only about 300 bytes in size and doesn’t contain any part of a query execution plan. It’s mainly just a placeholder to keep track of whether a particular query was compiled previously. The stub contains the full cache key and a pointer to the actual query text, which is stored in the SQL Manager cache. Later, the section “Plan cache internals” discusses cache keys and the SQL Manager. The usecounts value in the cache metadata is always 1 for compiled plan stubs because they are never reused.
When a query or batch that generated a compiled plan stub is recompiled, the stub is replaced with the full compiled plan. Initially, the usecounts value is set to 1 because the previous query isn’t guaranteed to have exactly the same execution plan. All that’s known is that the query itself is the same. I will execute some of the same queries used in the previous section after enabling the Op- timize for Ad Hoc Workloads option, so you can see what the usecounts query displays. I need to modify my usecounts query slightly, and rather than look for rows that have a cacheobjtype value of Compiled Plan