Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Несколько вопросов по построителю запросов при работе 1С  [new]
chel74
Member

Откуда: Челябинск
Сообщений: 110
Добрый день. Все вопросы - в контексте использования sql приложением 1С.

1) По рекомендации фирмы 1С https://its.1c.ru/db/metod8dev/content/5837/hdoc надо выполнять DBCC FREEPROCCACHE после обновления статистики, выполненного в плане обслуживания (через мастера):
"MS SQL Server строит план запроса на основании статистической информации о распределении значений в индексах и таблицах. Статистическая информация собирается на основании части (образца) данных и автоматически обновляется при изменении этих данных. Иногда этого оказывается недостаточно для того, что MS SQL Server стабильно строил наиболее оптимальный план выполнения всех запросов. "

Почему "Иногда этого оказывается недостаточно", в каких случаях?
Как я понимаю, DBCC FREEPROCCACHE нужен для удаления из кеша всех планов запроса, чтобы план, построенный на неактуальной статистике более не использовался. Но с другой стороны, оптимизатор перед выполнением запроса проверяет, изменилась ли статистика и если да - создает новый план запроса - или это не так?

2) Если оптимизатору поступает запрос, для которого уже есть план в кеше, но параметры запроса отличаются - будет ли скомпилирован новый план из-за "прослушивания параметров" - или это работает только для хранимых процедур (которые 1С вроде как не использует DBCC FREEPROCCACHE и Дед Мороз)

3) Кроме неактуальной статистики - какие еще могут быть причины построения неоптимального плана запроса? В голову приходит лишь ситуация когда система сильно нагружена, возможных планов для данного запроса множество, при выборе планов сработал таймаут построителя и он выбрал лучший из тех что успел скомпилировать (а успел он 2-3 штуки из-за сильной нагрузки), а не оптимальный.

4) В плане обслуживания из вышеуказанной статьи уже есть дефрагментация индексов - зачем тогда реиндексация?
И если использовать скрипт вида http://www.community.terrasoft.ru/blogs/8318:
-- 30% считаем пределом для определения типа обновления индекса.
IF @frag < 30.0
	SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
IF @frag >= 30.0
	SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';

надо ли все равно делать "полный" реиндекс всей БД (например создав задание через мастер плана обслуживания как в вышеупомянутой статье 1С)?
30 июл 17, 11:37    [20685584]     Ответить | Цитировать Сообщить модератору
 Re: Несколько вопросов по построителю запросов при работе 1С  [new]
chel74
Member

Откуда: Челябинск
Сообщений: 110
Сменить чтоль заголовок а то видимо все пугаются
31 июл 17, 08:23    [20686761]     Ответить | Цитировать Сообщить модератору
 Re: Несколько вопросов по построителю запросов при работе 1С  [new]
o-o
Guest
chel74
"MS SQL Server строит план запроса на основании статистической информации о распределении значений в индексах и таблицах. Статистическая информация собирается на основании части (образца) данных и автоматически обновляется при изменении этих данных. Иногда этого оказывается недостаточно для того, что MS SQL Server стабильно строил наиболее оптимальный план выполнения всех запросов. "
--> По рекомендации фирмы 1С https://its.1c.ru/db/metod8dev/content/5837/hdoc надо выполнять DBCC FREEPROCCACHE после обновления статистики
...
Почему "Иногда этого оказывается недостаточно", в каких случаях?

"недостаточно" может быть по разным причинам.
статистика это всего 200 шагов гистограммы на всю таблицу,
разумеется, чем больше таблица, тем приблизительнее статистика.
построить оптимальный план можно для 2-3 соединений,
если вы напихали 20-30 джойнов в 1 запрос, чего можно ожидать на выходе?
мало того, что оценка результата каждого соединения вносит свою погрешность,
так еще и различных вариантов возможных планов становится несчетное число.

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

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

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

автор
оптимизатор перед выполнением запроса проверяет, изменилась ли статистика и если да - создает новый план запроса - или это не так?

не совсем так.
простое изменение статистики не инвалидирует план,
надо, чтобы еще и данные в таблице изменились.
т.е. если вы только что посчитали статистику, запустили запрос, план построился,
данные не поменялись, а вы снова обловляете статистику, план не будет инвалидирован
Does Updating Statistics Cause a Recompile if No Data Has Changed?
31 июл 17, 10:05    [20686961]     Ответить | Цитировать Сообщить модератору
 Re: Несколько вопросов по построителю запросов при работе 1С  [new]
o-o
Guest
автор
2) Если оптимизатору поступает запрос, для которого уже есть план в кеше, но параметры запроса отличаются - будет ли скомпилирован новый план из-за "прослушивания параметров" - или это работает только для хранимых процедур

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

мой ответ именно для параметризованного запроса (sp_executesql)
для select * from T where field = C, хоть запрос и будет внутренне параметризован,
для другой константы C1 будет построен другой план
31 июл 17, 10:12    [20686978]     Ответить | Цитировать Сообщить модератору
 Re: Несколько вопросов по построителю запросов при работе 1С  [new]
chel74
Member

Откуда: Челябинск
Сообщений: 110
o-o
может, если бы они посоветовали обновлять статистику чаще и с фуллсканом,

Такой совет там есть:
В реально работающей системе разные таблицы требуют различной частоты обновления статистик. Путем анализа планов запроса можно установить, какие таблицы больше других нуждаются в частом обновлении статистик, и настроить две (или более) различных регламентных процедуры: для часто обновляемых таблиц и для всех остальных таблиц. Такой подход позволит существенно снизить время обновления статистик и влияние процесса обновления статистики на работу системы в целом.
Добавлю так же что в статье предлагается создавать план обслуживания, и обновлять статистику через него с full scan.

А про
o-o
chel74
оптимизатор перед выполнением запроса проверяет, изменилась ли статистика и если да - создает новый план запроса - или это не так?


не совсем так.
простое изменение статистики не инвалидирует план,
надо, чтобы еще и данные в таблице изменились.
т.е. если вы только что посчитали статистику, запустили запрос, план построился,
данные не поменялись, а вы снова обновляете статистику, план не будет инвалидирован
Вы имели в виду "простой факт пересчета статистики, без изменения её в результате пересчета - не инвалидирует план"?
Т.е. если изменилась статистика в результате изменения данных, то план будет инвалидирован, а если статистику пересчитывали, но она не изменилась (т.к. исходные данные не изменились) - то и план останется тот же?
1 авг 17, 08:28    [20690063]     Ответить | Цитировать Сообщить модератору
 Re: Несколько вопросов по построителю запросов при работе 1С  [new]
chel74
Member

Откуда: Челябинск
Сообщений: 110
o-o
для того и придумали forced-параметризацию

так она же вроде по-умолчанию выключена?
1 авг 17, 08:29    [20690067]     Ответить | Цитировать Сообщить модератору
 Re: Несколько вопросов по построителю запросов при работе 1С  [new]
chel74
Member

Откуда: Челябинск
Сообщений: 110
Буду рад если кто-нибудь ответит и на 4й вопрос из первого сообщения.
1 авг 17, 08:34    [20690074]     Ответить | Цитировать Сообщить модератору
 Re: Несколько вопросов по построителю запросов при работе 1С  [new]
chel74
Member

Откуда: Челябинск
Сообщений: 110
chel74
o-o
для того и придумали forced-параметризацию

так она же вроде по-умолчанию выключена?
Источник: http://skproj.ru/pochemu-dbcc-freeproccache-pomogaet/
1 авг 17, 08:41    [20690088]     Ответить | Цитировать Сообщить модератору
 Re: Несколько вопросов по построителю запросов при работе 1С  [new]
o-o
Guest
chel74
o-o
для того и придумали forced-параметризацию

так она же вроде по-умолчанию выключена?

Вот именно, по умолчанию выключена.
Поэтому для каждого запроса с константами свой план.
Сомневаюсь, что это 1С посылает именно параметризованные запросы.

А про четвертый вопрос, еще раз напишите (командами), что и за чем они предлагают делать.
Дефрагментация и реиндексация у них это одно и то же или нет?
1 авг 17, 08:42    [20690090]     Ответить | Цитировать Сообщить модератору
 Re: Несколько вопросов по построителю запросов при работе 1С  [new]
o-o
Guest
chel74
Вы имели в виду "простой факт пересчета статистики, без изменения её в результате пересчета - не инвалидирует план"?
Т.е. если изменилась статистика в результате изменения данных, то план будет инвалидирован, а если статистику пересчитывали, но она не изменилась (т.к. исходные данные не изменились) - то и план останется тот же?

Нет не так, у меня же приведена ссылка, где все расписано.
"изменилась статистика" или нет, сервер понимает не сравнивая прежднюю с новой("а она правда изменилась?"), а банально по дате создания этой статистики. Есть дата последнего обновления статистики и есть дата построения плана. Если первая дата свежее второй, значит статистика актуальнее плана.
Но этого недостаточно, еще надо проверить, были ли изменения данных по тем колонкам, по которым статистика. Это обычный счетчик изменений
1 авг 17, 09:09    [20690152]     Ответить | Цитировать Сообщить модератору
 Re: Несколько вопросов по построителю запросов при работе 1С  [new]
chel74
Member

Откуда: Челябинск
Сообщений: 110
o-o
А про четвертый вопрос, еще раз напишите (командами), что и за чем они предлагают делать.
Дефрагментация и реиндексация у них это одно и то же или нет?

Уважаемый, доступ к статье открытый. Можете и сами посмотреть: там в картинках создаются планы обслуживания, путем перетаскивания стандартных "кирпичиков". Дефрагментация и реиндексация - соответственно разные "кирпичики".

o-o
Нет не так, у меня же приведена ссылка, где все расписано.

Действительно, невнимательно статью прочитал. Спасибо за уточнения.
3 авг 17, 15:54    [20698404]     Ответить | Цитировать Сообщить модератору
 Re: Несколько вопросов по построителю запросов при работе 1С  [new]
o-o
Guest
chel74
o-o
А про четвертый вопрос, еще раз напишите (командами), что и за чем они предлагают делать.
Дефрагментация и реиндексация у них это одно и то же или нет?

Уважаемый, доступ к статье открытый. Можете и сами посмотреть

извините, это последнее, что меня интересует, читайте это сами.
ваши цитаты оттуда наводят на определенные мысли.
у меня туча непрочитанных книг достойных авторов,
если я читаю всякий трэш на форуме, то только развлечения ради,
но то ваше чтиво даже на "развлечение" не тянет
3 авг 17, 16:01    [20698440]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить