В чем разница между специальным и подготовленным запросом в кэше плана сервера SQL?

Question

Я пытаюсь понять план содержимого кэша сервера SQL.

так что мои вопросы:
1. В чем разница между специальными и подготовленными планами?
2. Что я должен знать об этом при попытке оптимизировать кэш плана сервера sql?

Answer 1

В чем разница между специальными и подготовленными планами?

Adhoc запрос:

select * from t1

Подготовленный запрос:
Запросы, которые заменяют заполнители вместо фактических значений, называются подготовленными утверждениями.

Некоторые примеры:

select * from t1 where id=@id

Еще один пример, взятый из википедии:

command.CommandText = "SELECT * FROM users WHERE USERNAME = @username AND ROOM = @room";

    command.Parameters.AddWithValue("@username", username);
    command.Parameters.AddWithValue("@room", room);

Что я должен знать об этом при попытке оптимизировать кэш плана сервера sql?

Есть статьи, написанные о том, как оптимизировать кеш плана. Поэтому я постараюсь сохранить его немного

Обычно, когда запрос выполняется к SQL,SQL компилирует план и сохраняет его в кэше плана. Этот кэш плана - это память, взятая из пула буферов, и разные версии имеют разные ограничения на объем используемой памяти.

Вы знаете, что память - это ценный ресурс, и если у вас есть утечки, вам не хватит аппаратного обеспечения.

Предположим, что вы отправляете запросы только один или два раза, и вы, как правило, отправляете подобные запросы много.SQL будет хранить план этих запросов в кэше планов, который обычно раздувает PlanCache, что плохо

Существуют разные DMVS, которые помогут вам копаться в кэше планов.

Запрос на поиск объектов другого типа находится в кеше плана:

select 
objtype,count(*) as countt,sum(size_in_bytes)*1024.0 as memoryinkb
 from   sys.dm_exec_cached_plans a
 group by objtype

Adhoc, подготовленные запросы, которые раздувают plancache и используются только один раз:

select q.query_hash, 
    q.number_of_entries, 
    t.text as sample_query, 
    p.query_plan as sample_plan
from (select top 20 query_hash, 
            count(*) as number_of_entries, 
            min(sql_handle) as sample_sql_handle, 
            min(plan_handle) as sample_plan_handle
        from sys.dm_exec_query_stats
        group by query_hash
        having count(*) > 1
        order by count(*) desc) as q
    cross apply sys.dm_exec_sql_text(q.sample_sql_handle) as t
    cross apply sys.dm_exec_query_plan(q.sample_plan_handle) as p

Чтобы удалить операторы, которые раздувают кэш плана:

DECLARE @MB decimal(19,3)
        , @Count bigint
        , @StrMB nvarchar(20)


SELECT @MB = sum(cast((CASE WHEN usecounts = 1 AND objtype IN ('Adhoc', 'Prepared') THEN size_in_bytes ELSE 0 END) as decimal(12,2)))/1024/1024 
        , @Count = sum(CASE WHEN usecounts = 1 AND objtype IN ('Adhoc', 'Prepared') THEN 1 ELSE 0 END)
        , @StrMB = convert(nvarchar(20), @MB)
FROM sys.dm_exec_cached_plans


IF @MB > 10
        BEGIN
                DBCC FREESYSTEMCACHE('SQL Plans') 
                RAISERROR ('%s MB was allocated to single-use plan cache. Single-use plans have been cleared.', 10, 1, @StrMB)
        END
ELSE
        BEGIN
                RAISERROR ('Only %s MB is allocated to single-use plan cache – no need to clear cache now.', 10, 1, @StrMB)
                — Note: this is only a warning message and not an actual error.
        END
go

Вышесказанное должно дать вам представление о том, с чего начать, ниже приведены обязательные к прочтению темы и ссылки:

1. http://www.sqlskills.com/blogs/kimberly/category/plan-cache/

2. http://sqlblog.com/blogs/kalen_delaney/archive/2007/11/04/did-you-know-sp2-does-not-limit-the-amount-of-plan-cache-you-can-have.aspx

3. https://technet.microsoft.com/en-us/library/dd672789(v=sql.100).aspx

4. Обязательно прочитайте статью SQLCAT о проблемах, с которыми сталкивался клиент при использовании подготовительных отчетов.

В упомянутых выше статьях kimberely предлагает включить опцию Enable Optimize for Adhoc для рабочих нагрузок, но я предлагаю сначала протестировать ее. На DBA.SE есть интересная тема