Здравствуйте, Softwarer, Вы писали:

S>Ораклоиды там компетентные. Странно, если mssql-евцы — поголовно левые.
Не поголовно, но, IMHO, толковых там катастрофически мало... Ну не люблю я его (sql.ru)...

Здравствуйте, Softwarer, Вы писали:

S>А насколько дорого это обходится в случае, когда разработчик не заблуждается?
Да практически ни на сколько. Задачи когда нужно держать отдельный план для какой-то константы встречаются крайне редко, и, как правило, решаются хинтами.

S> Смущает другое: в ситуации, когда в принципе предусмотрено вытеснение из кэша (то есть я не могу выделить под планы столько места, чтобы они никогда не вытеснялись) сервер может решить параметризировать те запросы, которые я настоятельно хотел бы оставить с константами.
Если запрос пользуется с константой, одной/двумя, то он так с этой константой в кеше и останется. А вот если запрос с константой, но при каждом новом запросе константа разная, то в какой-то момент сервер совершенно справедливо забъет на эти константы и начнет использовать параметры.
Ситуация же, когда нао держать в кеше два плана, параметризованый и с константой, выглядит довольно странно.

S> Или в MSSQL такое желание бессмысленно?
По моему глубокому убеждению и не только в MSSQL, а вообще где бы то ни было. Необходимость держать фиксированный план для какой-то константы — это первый звоночек о том, что возможно где-то косяк в базе.

Здравствуйте, Merle, Вы писали:

S>> Или в MSSQL такое желание бессмысленно?
M>По моему глубокому убеждению и не только в MSSQL, а вообще где бы то ни было. Необходимость держать фиксированный план для какой-то константы — это первый звоночек о том, что возможно где-то косяк в базе.
Я бы сказал что это колокол...

M>По моему глубокому убеждению и не только в MSSQL, а вообще где бы то ни было. Необходимость держать фиксированный план для какой-то константы — это первый звоночек о том, что возможно где-то косяк в базе.

А если у сервера есть гистограммы распределения?
И там написано, что константа1 встречается в таблице 10 раз, а константа2 — 1000000 раз?

Здравствуйте, Igor Trofimov, Вы писали:

iT>А если у сервера есть гистограммы распределения?
iT>И там написано, что константа1 встречается в таблице 10 раз, а константа2 — 1000000 раз?
Значит у него в кеше для данного запроса с параметрами(!) будет прописано, что возможно два физических плана выполнения в зависимости от того что пришло в параметре.

Здравствуйте, Merle, Вы писали:

M>Здравствуйте, Igor Trofimov, Вы писали:

iT>>А если у сервера есть гистограммы распределения?
iT>>И там написано, что константа1 встречается в таблице 10 раз, а константа2 — 1000000 раз?
M>Значит у него в кеше для данного запроса с параметрами(!) будет прописано, что возможно два физических плана выполнения в зависимости от того что пришло в параметре.

Со ссылками на документацию и статьи в msdn.
Я не занимаюсь микрософтовским сиквелом, а работаю с ораклом и реально предствляю, что такое тьюнинг базы
Забавно читать про "умные" сервера. Выглядит совершенной отсебятиной.

Здравствуйте, Merle, Вы писали:

M>До какого-то момента, как я уже писал, сервер одни и те же запросы, но с разными костантами воспринимает как разные запросы, а потом заменяет это дело на одни параметризованый.

Пожалйуста, подкрепите свои слова ссылками на BOL, MSDN, KB, наконец, собственный эксперимент.

Заранее благодарен, по жизни заблуждающийся pkarklin.

Здравствуйте, Альт, Вы писали:

А>Со ссылками на документацию и статьи в msdn.
А>Я не занимаюсь микрософтовским сиквелом, а работаю с ораклом и реально предствляю, что такое тьюнинг базы
Да, мы все сочувствуем оракл-DBA
А>Забавно читать про "умные" сервера. Выглядит совершенной отсебятиной.
Merle щас на конференции. Вернется — надо попытаться склонить его к написанию статьи. А то вообще в среде СУБД-разработчиков ходит большое количество мифов. Самое противное в мифах об MS SQL то, что многие из них основываются на довольно-таки двусмысленных статьях в MSDN/BOL.

Здравствуйте, Merle, Вы писали:

M>Да практически ни на сколько. Задачи когда нужно держать отдельный план для какой-то константы встречаются крайне редко, и, как правило, решаются хинтами.

"Нужно" — не знаю, но "эффективнее" — бывает, и не так уж редко.

Что касается хинтов — у меня к ним отношение "использовать только если никак иначе не обойтись". Слишком уж неприятна вероятность того, что через какое-то время соотношения изменятся и хинт начнет резко тормозить там, где раньше ускорял.

M>Если запрос пользуется с константой, одной/двумя, то он так с этой константой в кеше и останется. А вот если запрос с константой, но при каждом новом запросе константа разная, то в какой-то момент сервер совершенно справедливо забъет на эти константы и начнет использовать параметры.

Это понятно. Но есть и промежуточный случай — когда этих констант, допустим, несколько десятков. Насколько я понимаю, должен быть какой-то критерий, начиная с которого сервер в принципе начинает "сворачивать" запросы — каков он?

M>Ситуация же, когда нао держать в кеше два плана, параметризованый и с константой, выглядит довольно странно.

Хм. С одной стороны — странно. С другой стороны, держать план "для значения 1", "для значения 2" и "для остальных значений" — может оказаться разумным.

M>Значит у него в кеше для данного запроса с параметрами(!) будет прописано, что возможно два физических плана выполнения в зависимости от того что пришло в параметре.

Хм. Ну это, простите, совершенно терминологический момент. Как именно сервер организовывает данные — его вопрос. Факт в том, что он держит в памяти несколько планов. А дальше идут мелочи и моменты, не оцениваемые на пальцах — например, сколько стоит отпарсить запрос (с целью замены константы параметрами) по сравнению с тратой памяти на хранение непараметризованных версий.

Здравствуйте, pkarklin, Вы писали:

P>Пожалйуста, подкрепите свои слова ссылками на BOL, MSDN, KB, наконец, собственный эксперимент.
В доке по семерке упомянута эта возможность:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql7/html/queryproc.asp
(искать Automatic Parameters)
P>Заранее благодарен, по жизни заблуждающийся pkarklin.

Здравствуйте, Sinclair, Вы писали:

S>В доке по семерке упомянута эта возможность:
S>http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql7/html/queryproc.asp
S>(искать Automatic Parameters)

Ага, упомянута. Вот тока не уточнено, для запросов какой сложности оптимизатор может автопараметризацию сделать. Зато в доке по 2000 нам раскрыли "страшную тайну". Читайте последний абзац:
здесь

Здравствуйте, pkarklin, Вы писали:

P>Пожалйуста, подкрепите свои слова ссылками на BOL, MSDN, KB, наконец, собственный эксперимент.
Да запросто, просто ход предыдущей дискуссии никаких ссылок на авторитетные источники не предполагал...
На самом деле в BOL или MSDN я ссылок на подробное объяснение механизма кеширования не припомню, мои знания берутся в основном из личного опыта и общения непосредственно с разработчиками SQL сервера.
Если интересно могу попросить у них ссылки на открытые источники в которых это описано, но не обещаю, что это будет быстро.. Ну вернемся к экспериментам.
Во-первых, скешировался параметризованный план или нет, всегда можно посмотреть с помощю замечательной команды SET SHOWPLAN_TEXT ON, в плане всегда видно подставилась ли там константа (e. g. WHERE[tbl1].[a]=Convert(1)), или параметр (e. g. WHERE[tbl1].[a]=Convert([@1]))
Для более подробного исследования полезно взглянуть на системную табличку master..syscacheobjects, которая собственно и отражает, что же за планы хранятся в сервере. В общих словах, как примерно все устроено:
Допустим поступил запрос в сервер, запрос разбирается, парсится и подготавливается план запроса.
План может быть нескольких типов, но нас сейчас интересуют всего два: Ad-hoc queries — это, очевидно, план с константами, и parameterized queries, соответственно с параметрами.
Итак, у нас есть план запроса, это некоторая Read Only структура, к которой могут одновременно обращаться сколько угодно желающих. При этом планов реально два, один для последовательного исполнения, а другой для распараллеливания.
Но это далеко не все, каждому плану принадлежат так называемые контексты исполнения (execution context), которые и отвечают, что на самом деле реально исполняется, эти контексты исполнения так же кешируются и переиспользуются. Каждый реально исполняющийся запрос имеет свой собственный контекст исполнения, который содержит параметры, данные, ect... И еще один нюанс, не смотря на то, что контексты кешируются, одновременно использовать один контекст может только один запрос. Соответственно для каждого плана есть несколько контекстов (хотя может и не быть ни одного).
Таким образом, сначала ищется в кеше подходящий план, если нашелся, ищется подходящий контекст из имеющихся, далее либо находится и используется, либо готовится новый. Ну да это все лирика, теперь самое время для полевых испытаний:
Готовим тестовые данные и очищаем кеш:

drop table tbl1
create table tbl1(a int)

insert into tbl1(a)
select 1 union
select 2 union
select 3 union
select 4 union
select 5 union
select 6 union
select 7 union
select 8 union
select 9 union
select 10 

DBCC FREEPROCCACHE

Теперь выполняем запрос и смотрим что происходит в таблице кеша, где нас будут интересовать следующие поля:
cacheobjtype — что за объект кеширования(план или контекст), objtype — с константой или нет, usecounts — сколько раз использовался, sql — что собственно скешировалось.

select a from tbl1 where a=7
GO
select cacheobjtype, objtype, usecounts, sql from master..syscacheobjects

В результате видим:

Compiled Plan     | Adhoc        |1|    select a from tbl1 where a=7  
Executable Plan    | Prepared    |1|    (@1 tinyint)SELECT [a]=[a] FROM [tbl1] WHERE [a]=@1
Compiled Plan      | Prepared    |2|    (@1 tinyint)SELECT [a]=[a] FROM [tbl1] WHERE [a]=@1

У нас есть план с константой (Adhoc), который использовался один раз.
В первой колонке Compiled Plan означает собственно готовый план запроса, а Executeble Plan — означает контекст исполнения.
Во второй, Ad-hoc означает, что план с константой, Prepared, что сиквел этот план может параметризовать.
Циферка — количество использования данного плана/контекста.
Повторяем эксперимент, с другой константой:

select a from tbl1 where a=8
GO
select cacheobjtype, objtype, usecounts, sql from master..syscacheobjects

Картина та же самая, только еще один Ad-hoc запрос добавился

Compiled Plan     | Adhoc        |1|    select a from tbl1 where a=7  
Compiled Plan     | Adhoc        |1|    select a from tbl1 where a=8  
Executable Plan    | Prepared    |1|    (@1 tinyint)SELECT [a]=[a] FROM [tbl1] WHERE [a]=@1
Compiled Plan      | Prepared    |2|    (@1 tinyint)SELECT [a]=[a] FROM [tbl1] WHERE [a]=@1

Выполняем еще раз, с еще одной константой:

select a from tbl1 where a=9
GO
select cacheobjtype, objtype, usecounts, sql from master..syscacheobjects

Смотрим:

Compiled Plan     | Adhoc        |1|    select a from tbl1 where a=7  
Compiled Plan     | Adhoc        |1|    select a from tbl1 where a=8  
Executable Plan    | Prepared    |2|    (@1 tinyint)SELECT [a]=[a] FROM [tbl1] WHERE [a]=@1
Compiled Plan      | Prepared    |2|    (@1 tinyint)SELECT [a]=[a] FROM [tbl1] WHERE [a]=@1

Уупс, Adhoc с девяткой не появился, однако счетчик использования параметризованного контекста увеличился на еденичку, то есть реально был использован параметризованый контекст.
В дальнейшем, какие бы цифры не были подставлены, новых Adhoc'ов не появляется, а лишь увеличивается счетчик контекста, из чего можно сделать вывод, что используется параметризованный план.

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

P>Заранее благодарен, по жизни заблуждающийся pkarklin.
Всегда пожалуйста..

Здравствуйте, Альт, Вы писали:

А>Я не занимаюсь микрософтовским сиквелом, а работаю с ораклом и реально предствляю, что такое тьюнинг базы
Здесь многие реально представляют что такое тюнинг базы..

А>Забавно читать про "умные" сервера.
Всегда полезно свой кругозор расширить, не Ораклом же единым, особенно учытывая что его оптимизатор весьма далек от совершенства...

Здравствуйте, pkarklin, Вы писали:

P>Ага, упомянута. Вот тока не уточнено, для запросов какой сложности оптимизатор может автопараметризацию сделать.
Ну, во-первых некто pkarklin уверял всех на sql.ru, что

SELECT * FROM  sysobjects WHERE id = 2

и

SELECT * FROM   sysobjects WHERE id = 123456

то это РАЗНЫЕ ЗАПРОСЫ.

P>Зато в доке по 2000 нам раскрыли "страшную тайну". Читайте последний абзац:
P>здесь
Во-вторых, не вижу там раскрытия тайны. Сказано лишь, что чем больше параметров, тем труднее сиквелу их выделить.

Здравствуйте, Softwarer, Вы писали:

S>Что касается хинтов — у меня к ним отношение "использовать только если никак иначе не обойтись". Слишком уж неприятна вероятность того, что через какое-то время соотношения изменятся и хинт начнет резко тормозить там, где раньше ускорял.
Совершенно согласен, но ведь и с фиксированными планами точно такая же ситуация, соотношение изменилось и данная константа уже отличается от других в противоположную сторону.

S>Это понятно. Но есть и промежуточный случай — когда этих констант, допустим, несколько десятков. Насколько я понимаю, должен быть какой-то критерий, начиная с которого сервер в принципе начинает "сворачивать" запросы — каков он?

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

S>Хм. Ну это, простите, совершенно терминологический момент. Как именно сервер организовывает данные — его вопрос. Факт в том, что он держит в памяти несколько планов. А дальше идут мелочи и моменты, не оцениваемые на пальцах — например, сколько стоит отпарсить запрос (с целью замены константы параметрами) по сравнению с тратой памяти на хранение непараметризованных версий.
ТАм на самом деле не совсем тривиальный механизм и кеш многоуровневый, я там примерно описал в соседнем посте, если будет время распишу еще подробнее...

Здравствуйте, Merle, Вы писали:

M>Здравствуйте, pkarklin, Вы писали:

P>>Пожалйуста, подкрепите свои слова ссылками на BOL, MSDN, KB, наконец, собственный эксперимент.
M>Да запросто, просто ход предыдущей дискуссии никаких ссылок на авторитетные источники не предполагал...
Ай малацца! А я уж тут не вытерпел — поковырялся... Правда, я про syscachedobjects ни сном ни духом до сих пор; потому помучил SQL Profiler.
В общем, заметил почти то же, что и ты, за исключением:
1. Сервер опознает тип параметра по размеру целой переменной; попробуй сделать where ...= 10000 и ты увидишь новую пару планов (в tinyint не влезет)
2. Не все запросы приезжают в кэш. Оба следующих не оставят о себе ни следа:

SELECT productID, total = SUM(UnitPrice)
FROM [order details]
GROUP BY productID
HAVING SUM(UnitPrice) > 40000

declare @p int
set @p=10
SELECT productID, total = SUM(UnitPrice)
FROM [order details]
GROUP BY productID
HAVING SUM(UnitPrice) > @p

Здравствуйте, Sinclair, Вы писали:

S>Здравствуйте, pkarklin, Вы писали:

P>>Ага, упомянута. Вот тока не уточнено, для запросов какой сложности оптимизатор может автопараметризацию сделать.
S>Ну, во-первых некто pkarklin уверял всех на sql.ru, что
S>

S>

S>SELECT * FROM  sysobjects WHERE id = 2
S>

S>и
S>

S>SELECT * FROM   sysobjects WHERE id = 123456
S>

S>то это РАЗНЫЕ ЗАПРОСЫ.

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


P>>Зато в доке по 2000 нам раскрыли "страшную тайну". Читайте последний абзац:
P>>здесь
P>Во-вторых, не вижу там раскрытия тайны. Сказано лишь, что чем больше параметров, тем труднее сиквелу их выделить.

Вы, все-таки не совсем корректно переводите.

When processing complex SQL statements, the relational engine may have difficulty determining which expressions can be auto-parameterized.

Испытывать трудность — значит не принять решение об автопараметризации.Правда, количественных оценок не приведено.

Здравствуйте, Merle, Вы писали:

M>На самом деле в BOL или MSDN я ссылок на подробное объяснение механизма кеширования не припомню, мои знания берутся в основном из личного опыта и общения непосредственно с разработчиками SQL сервера.

Мои знания берутся тоже из личного опыта. И на, в принципе, классическом запросе вида Звезда, когда идет объединение порядка 10-15 таблиц и в предложение WHERE несколько критериев отбора как по полям целого типа, так и по datetime, оптимизатор не может провести автопараметризацию. О чем явно сказано в BOL. И именно для повторного использования плана выполнения в таких случаях, я использую хп. Хотя тем, кто любит зашивать инструкции в клиента, и sp_executesql подойдет.

M>Если интересно могу попросить у них ссылки на открытые источники в которых это описано, но не обещаю, что это будет быстро.. Ну вернемся к экспериментам.

Ссылки будут тока приветсвоваться. А на счет эксперимента...

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

M>Ясен хвост, что автоматически распознать что можно параметризовать, а что нельзя сиквел может далеко не всегда, поэтому очень приветствуется явное указание что именно мы хотим параметризовать.
M>И сделать это можно двумя способами, либо указывая вопросики и передавая параметры в должном порядке, либо используя процедуру sp_executesql, что и делают большинство клиентских библиотек.

Вот и я о том же твердил в том топике на SQL.RU, тока Вы еще третий способ забыли — использование хп.

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

Здравствуйте, Аноним, Вы писали:

А>фишка в том что когда константу заменяют на на переменую меняется план, т.к. оптимайзер теперь не знает что может появится вместо переменной и например с джойном уже не знает какая таблица из 2х будет меньше и выбирает наихудший из возможных планов.


Тоже самое происходит и у сиквела в некотрых хп. Оптимизатор при отсутствии явного значения параметра выбирает не самый оптимальный план. В этом случаи такие хп приходиться писать WITH RECOMPILE.