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

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

Adhoc query:

select * from t1 

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

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

 select * from t1 where [email protected] 

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

 command.CommandText = "SELECT * FROM users WHERE USERNAME = @username AND ROOM = @room"; command.Parameters.AddWithValue("@username", username); command.Parameters.AddWithValue("@room", room); 

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

Есть рекомендации о том, как оптимизировать план cache.so, я попытаюсь сохранить его немного ..

Обычно, когда запрос выполняется с 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-n- have.aspx

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

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

В упомянутых выше статьях, kimberely предлагает параметр «Включить оптимизацию для Adhoc workloads», но я предлагаю сначала протестировать его. В DBA.SE есть интересный поток