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

Откуда:
Сообщений: 13148
другими словами, неоднократно наблюдаю, что некоторые запросы (даже с option( recompile )) получают радикально разные планы в практически одинаковых условиях. проблема всплывает, разумеется, когда запрос начинает работать нетипично долго. причем KILL процесса и перезапуск команды, зачастую, не приводит к воспроизведению ситуации. то есть повторный запуск "тут же" дает "обычное" (быстрое) а не "медленное" (аномальное) выполнение
16 фев 16, 18:41    [18825608]     Ответить | Цитировать Сообщить модератору
 Re: а может ли план запроса быть построен / перестроен в зависимости от доступных ресурсов?  [new]
ппаыаы
Guest
Crimean,

А может быть проблема во фразе "практически равных условиях". Может они уж и не такие уж практически равные.
16 фев 16, 18:47    [18825650]     Ответить | Цитировать Сообщить модератору
 Re: а может ли план запроса быть построен / перестроен в зависимости от доступных ресурсов?  [new]
Crimean
Member

Откуда:
Сообщений: 13148
с точки зрения данных / актуальности индексов - статистик - точно равные
а вот с точки зрения доступных ресурсов на сервере - категорически нет
тут и PLE может в 0 упасть и всякое прочее
16 фев 16, 18:57    [18825719]     Ответить | Цитировать Сообщить модератору
 Re: а может ли план запроса быть построен / перестроен в зависимости от доступных ресурсов?  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
Crimean,

Как насчет банального "не хватило времени чтобы перебрать все варианты". Может поделитесь хорошим/плохим планом?

У меня был немного похожий случай. В какой то момент начинал строится плохой план. Запросы надолго повисали в бесконечных nested loops, при этом запросы запущенные в тот же момент вручную тоже генерили плохой план. Но спустя какое то время, 5-10-30 минут, план опять приходил в норму. Никакой разницы во входных параметрах или специфичной нагрузки замечено не было. Ситуация могла повторится через несколько дней. Я так и не смог выяснить в чем причина. Запросы были с SharePoint, захинтованные по самое не хочу.
17 фев 16, 04:17    [18827157]     Ответить | Цитировать Сообщить модератору
 Re: а может ли план запроса быть построен / перестроен в зависимости от доступных ресурсов?  [new]
WarAnt
Member

Откуда: Питер
Сообщений: 2421
Crimean,

Если точно знаете какие индексы используются в "хороших планах", пропишите их хинтами, тогда время поиска плана уменьшится и и он будет всегда один и тот же не зависимо от нагрузки.
17 фев 16, 10:46    [18827892]     Ответить | Цитировать Сообщить модератору
 Re: а может ли план запроса быть построен / перестроен в зависимости от доступных ресурсов?  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7868
Без специальных знаний в этом не разобраться, а если не шашечки, а ехать, то option (use plan N'...' ) для хорошего плана.
17 фев 16, 11:06    [18828056]     Ответить | Цитировать Сообщить модератору
 Re: а может ли план запроса быть построен / перестроен в зависимости от доступных ресурсов?  [new]
komrad
Member

Откуда:
Сообщений: 5260
Crimean
с точки зрения данных / актуальности индексов - статистик - точно равные
а вот с точки зрения доступных ресурсов на сервере - категорически нет
тут и PLE может в 0 упасть и всякое прочее

ну так ведь подъем данных с диска в кэш стоит ресурсов (время, цпу, io)

ну и неплохо бы показать @@version сиквела :)
17 фев 16, 13:33    [18829350]     Ответить | Цитировать Сообщить модератору
 Re: а может ли план запроса быть построен / перестроен в зависимости от доступных ресурсов?  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Crimean,

Добрый день.

Про рекомпиляцию

Возможные причины указаны в документации Plan Caching in SQL Server 2008. Или проще их можно получить из такого запроса: select * from sys.dm_xe_map_values where name = 'statement_recompile_cause'

Среди них значения:
• Schema changed
• Statistics changed
• Deferred compile
• Set option change
• Temp table changed
• Remote rowset changed
• For browse permissions changed
• Query notification environment changed
• PartitionView changed
• Cursor options changed
• Option (recompile) requested
• Parameterized plan flushed
• Test plan linearization
• Plan affecting database version changed
• QDS plan forcing policy changed
• QDS plan forcing failed

Как видно, нет ни одной рекомпиляции из-за ресурсов.

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

Про компиляцию

Если говорить в разрезе ресурсов, то на компиляцию влияют теоретически доступные ресурсы, которые отражаются в свойстве плана OptimizerHardwareDependentProperties. Это память (EstimatedAvailableMemoryGrant, EstimatedPagesCached) и число процессоров (EstimatedAvailableDegreeOfParallelism).
Картинка с другого сайта.

Эти параметры используются при вычислении стоимости операторов которые потребляют память (например Hash, Sort) и при принятии решении о параллелизме. В каком-то из докладов у меня был пример на эту тему, по моему «Внутри оптимизатора – Стоимость».

Т.е. разных серверах полностью одинаковые БД могут давать разные планы из-за отличающихся настроек/оборудования.

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

Оптимизатор имеет три причины раннего завершения плана, которые отражаются в свойстве StatementOptmEarlyAbortReason:
  • Good Enough Plan Found - на самом деле не является причиной

  • Time Out - означает что исчерпано число возможных преобразований, измеряется в преобразованиях, а не в единицах времени, поэтому нет такого, что оптимизатор «не успел» до-оптимизировать план по времени, а в следующий раз, типа успеет, когда будет менее загружен. Более подробно писал тут изменение плана со временем и в блоге: Оптимизатор без границ (ч.1)

  • Memory Limit Exceeded – а вот это интересная причина, в контексте заданного вопроса. Во время оптимизации сервер под внутренние нужды выделает память динамически. Это отражается в свойстве плана Compile Memory. Обычно это не много, но для сложных запросов может быть внушительно. Учитывая что одновременно могут компилировать несколько запросов – памяти может не хватить. Если это произошло, оптимизация прекращается и указывается причина Memory Limit Exceeded. Соответственно, в момент сильной нагрузки на память, оптимизатор может остановить поиск плана раньше, чем в тот момент, когда памяти хватает.

    Пример

    Для примера взят сервер 12.0.2495.0. Понятно, что я тут не смогу выложить репро моделирующую реальную сложную систему, поэтому, чтобы искусственно создать дефицит памяти я ограничу память на тестовом сервере всего 420 МБ.
    exec sp_configure 'show advanced options', 1;
    reconfigure;
    go
    exec sp_configure 'max server memory (MB)', 420; -- 2147483647
    reconfigure;
    go
    


    Теперь требуется довольно сложный запрос, где много соединений. Я сделал запрос к AdwentureWorks2014 (смысла в нем нет, используется просто для демонстрации). Чтобы запрос во время компиляции отъедал как можно больше памяти и дольше компилировался, я отключил (а точнее повысил) проверку на Time Out при помощи флага трассировки 8780.
    Теперь после того как ограничили память, открываем два окна. В каждое копируем скрипт, который выполняет компиляцию сложного запроса:
    set showplan_xml on;
    go
    select *
    from
    	Sales.SalesOrderDetail sod
    	join Sales.SalesOrderHeader soh on sod.SalesOrderID = soh.SalesOrderID
    	join Sales.CreditCard cc on cc.CreditCardID = soh.CreditCardID
    	join Sales.CurrencyRate cr on cr.CurrencyRateID = soh.CurrencyRateID
    	left join Sales.Currency crn on crn.CurrencyCode = cr.FromCurrencyCode and crn.ModifiedDate = cr.ModifiedDate
    	join Sales.SalesPerson sp on soh.SalesPersonID = sp.BusinessEntityID
    	left join Sales.SalesTerritory st on soh.TerritoryID = st.TerritoryID
    	join Sales.SalesTerritoryHistory sth on st.TerritoryID = sth.TerritoryID and sth.StartDate = st.SalesLastYear
    	join Person.BusinessEntity be on sp.BusinessEntityID = be.BusinessEntityID
    	join Person.Person p on p.BusinessEntityID = be.BusinessEntityID
    	join Person.BusinessEntityAddress bea on bea.BusinessEntityID = be.BusinessEntityID
    	join Person.Address a on bea.AddressID = a.AddressID
    	left join Person.StateProvince spr on a.StateProvinceID = spr.StateProvinceID
    	join (
    		select m = max(tha.TransactionDate), tha.ProductID from Production.TransactionHistoryArchive tha group by tha.ProductID
    	) tha on tha.ProductID = sod.ProductID
    where
    	cc.CardType in ('SuperiorCard','Vista') and
    	p.PersonType = 'IN' and
    	(soh.SalesOrderNumber > 'SO' or spr.CountryRegionCode in ('FR', 'US'))
    option(querytraceon 8780);
    go
    set showplan_xml off;
    go
    

    Одновременно запускаем скрипты в двух окнах, ждем пока скомпилируется, смотрим, что в свойствах планов корневых элементов:
    Картинка с другого сайта.

    Мы видим, что стоимости отличаются. Тот план, который был сгенерирован в момент, когда памяти не хватало – получился более дорогим и в нем указана причина, что его оптимизация была прервана раньше.

    А вот и само различие в формах планов (планы целиком не привожу, кому надо могут сгенерировать, а места на картинке много займут):
    Картинка с другого сайта.

    Видно, что отличается порядок таблиц и типы соединений.

    Итог

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

    Менее экзотические варианты

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

    Самый простой и последний (ну я так понял вы этот вариант самым первым отмели, привожу просто для полноты описания) вариант это если планы одинаковые, ну тогда логично, что в моменты пиковой нагрузки одинаковые планы могут выполняться гораздо дольше, чем если нагрузки нет. Может не быть нужных страниц в буфферном пуле, может быть занятый шедулер, конкуренция за гранты памяти и т.д.
  • 17 фев 16, 14:18    [18829676]     Ответить | Цитировать Сообщить модератору
     Re: а может ли план запроса быть построен / перестроен в зависимости от доступных ресурсов?  [new]
    SomewhereSomehow
    Member

    Откуда: Moscow
    Сообщений: 2480
    Блог
    SomewhereSomehow,

    Забыл в описании эксперимента, прежде чем запустить скрипт на компиляцию в двух окнах сразу. Нужно запустить только в одном, чтобы получить план, который генерируется при "нормальных" условиях, т.е. без сильной нагрузки на память.
    17 фев 16, 14:21    [18829710]     Ответить | Цитировать Сообщить модератору
     Re: а может ли план запроса быть построен / перестроен в зависимости от доступных ресурсов?  [new]
    Crimean
    Member

    Откуда:
    Сообщений: 13148
    SomewhereSomehow,

    спасибо!!
    17 фев 16, 15:25    [18830191]     Ответить | Цитировать Сообщить модератору
     Re: а может ли план запроса быть построен / перестроен в зависимости от доступных ресурсов?  [new]
    Владислав Колосов
    Member

    Откуда:
    Сообщений: 7868
    Я припоминаю, что у меня был случай, когда на худшем железе формировался лучший план (по времени выполнения), чем на более мощном. И тот план я как раз принудительно использовал с помощью option (use plan N'...' ) на новом сервере.
    17 фев 16, 17:21    [18831240]     Ответить | Цитировать Сообщить модератору
     Re: а может ли план запроса быть построен / перестроен в зависимости от доступных ресурсов?  [new]
    SomewhereSomehow
    Member

    Откуда: Moscow
    Сообщений: 2480
    Блог
    Владислав Колосов
    Я припоминаю, что у меня был случай, когда на худшем железе формировался лучший план (по времени выполнения), чем на более мощном. И тот план я как раз принудительно использовал с помощью option (use plan N'...' ) на новом сервере.

    Вполне может быть.

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

    Или если на более мощном сервере больше памяти, то бывает, что большее число памяти делает стоимость операторов потребляющих память ниже и сервер выбирает план с операторами потребляющими память, например, hash join, поскольку они дешевле, хотя эффективнее было бы выбрать какой-то другой тип соединения. Есть даже отдельный TF 2335 на эту тему и KNOB - Using large amounts of memory can result in an inefficient plan in SQL Server

    План ведь это вопрос оценок.

    А еще бывает как в загадке =)
    Сидят трое играют в карты, у одного падает карта под стол, он нагибается ее поднять, смотрит, а под столом 5 ног.
    Вопрос: как такое может быть?
    + отгадка
    Показалось, обсчитался просто
    17 фев 16, 17:54    [18831434]     Ответить | Цитировать Сообщить модератору
     Re: а может ли план запроса быть построен / перестроен в зависимости от доступных ресурсов?  [new]
    o-o
    Guest
    ооо, optimizer снова планировщиком обзывают?!?
    спасибо хоть, что не виндовым
    18 фев 16, 12:25    [18834399]     Ответить | Цитировать Сообщить модератору
     Re: а может ли план запроса быть построен / перестроен в зависимости от доступных ресурсов?  [new]
    o-o
    Guest
    МуМу
    ситуация моделируется, есть текст который не проходит(может движок чего то не пропускает?). Специально снял скриншот экрана.

    давайте текст, я тоже хочу попробовать.
    что именно надо попытаться запостить?

    я так понимаю, нЕкто не пропускает определенные слова в вашем исполнении, да?
    типа если я говорю "планировщик", мне дозволено, ибо не обзывательство.
    а из ваших уст оно оптимайзеру обидно звучит, может такое и не пропускать.
    так вы картинкой этот обидный текст cюда выложьте,а я перенаберу в виде текста.
    обещаю отчитаться
    18 фев 16, 15:06    [18835815]     Ответить | Цитировать Сообщить модератору
     Re: а может ли план запроса быть построен / перестроен в зависимости от доступных ресурсов?  [new]
    МуМу
    Member

    Откуда:
    Сообщений: 1134
    По моим наблюдения странное поведение планировщика происходит в первую очередь при изменении статистики. Выражается это в том что после пересчета с full scan и очистке хэша планов выполнения запросов ситуация меняется. Но также замечал что видимо сервер анализирует объем доступной(по сегментам) памяти. Выражалось это например при падении счетчика "время жизни страницы"(это скорее всего косвенный счетчик) оптимизатор без видимых причин начинал использовать преимущественно(на определенных типах запросов) нестед лупс вместо хэш джоина. Были мысли эту ситуацию смоделировать но как то лень. Да и главное как мы на это можем повлиять? Гарантированный способ - явно приклеивать xml плана выполнения. Если есть вырожденные случаи в селективности то явно в клиенте анализировать и подкладывать другой план. По моему опыту это не нужно для всех запросов , обычно их набирается 5-10ть на всю систему.
    18 фев 16, 15:47    [18836241]     Ответить | Цитировать Сообщить модератору
     Re: а может ли план запроса быть построен  [new]
    Гавриленко Сергей Алексеевич
    Member

    Откуда: Moscow
    Сообщений: 37069
    Модератор: Почистил топик
    18 фев 16, 15:54    [18836304]     Ответить | Цитировать Сообщить модератору
     Re: а может ли план запроса быть построен / перестроен в зависимости от доступных ресурсов?  [new]
    МуМу
    Member

    Откуда:
    Сообщений: 1134
    Есть возможность прикреплять план запроса к конкретному запросу. При этом не модифицируя его из приложения. Работает по следующему принципу - Проверяется "быстрая" сигнатура запроса - если проходит то проводится полный парсинг запроса. Если он совпадает с заранее указанным - то подставляется конкретный фиксированный план(XML). Если не совпадает - то не подставляется.(работает в любом случае правильно). Мне кажется не хватает такой типовой функциональности в MSSQL. Если кому нужно - пишите.
    18 фев 16, 16:17    [18836483]     Ответить | Цитировать Сообщить модератору
     Re: а может ли план запроса быть построен / перестроен в зависимости от доступных ресурсов?  [new]
    Владислав Колосов
    Member

    Откуда:
    Сообщений: 7868
    МуМу,

    куда же делась эта функциональность? Называется она "руководство планов".
    18 фев 16, 16:25    [18836543]     Ответить | Цитировать Сообщить модератору
     Re: а может ли план запроса быть построен / перестроен в зависимости от доступных ресурсов?  [new]
    Crimean
    Member

    Откуда:
    Сообщений: 13148
    Владислав Колосов
    куда же делась эта функциональность? Называется она "руководство планов".


    вы, видать, не использовали эту штуку. речь про "опциональное" использование "заготовленного" плана, только по определенным условиям. а не всегда. что для сложных запросов более чем востребовано.
    18 фев 16, 16:30    [18836587]     Ответить | Цитировать Сообщить модератору
     Re: а может ли план запроса быть построен / перестроен в зависимости от доступных ресурсов?  [new]
    SomewhereSomehow
    Member

    Откуда: Moscow
    Сообщений: 2480
    Блог
    МуМу
    Есть возможность прикреплять план запроса к конкретному запросу. При этом не модифицируя его из приложения. Работает по следующему принципу - Проверяется "быстрая" сигнатура запроса - если проходит то проводится полный парсинг запроса. Если он совпадает с заранее указанным - то подставляется конкретный фиксированный план(XML). Если не совпадает - то не подставляется.(работает в любом случае правильно). Мне кажется не хватает такой типовой функциональности в MSSQL. Если кому нужно - пишите.

    Эта функциональность добавлена в SQL Server 2016, называется Query Store.
    Буду рассказывать об этом в марте на 24HOP,
    Внутри оптимизатора запросов: Query Store
    Многие из нас, пытаясь понять, почему запрос вдруг стал работать медленно, наверняка хотели бы повернуть время вспять и получить план этого запроса, когда он выполнялся быстро. Понять причину замедления гораздо проще, имея на руках два плана запроса: «быстрый» и «медленный». Было бы еще лучше, если бы можно было указать оптимизатору, использовать «быстрый» план, пока мы ищем причину в «медленном». Хорошие новости состоят в том, что SQLServer 2016 и механизм QueryStore позволяют решать и первую, и вторую задачу.

    В докладе будет сделан обзор механизма QueryStore в структуре SQLServer и приведены несколько сценариев его использования для решения задач производительности. Также доклад коснется некоторых внутренних особенностей архитектуры и реализации QueryStore, чтобы лучше понять, как работает этот механизм, и какое место он занимает в общем процессе оптимизации.

    всех буду рад видеть в качестве слушателей.
    (да-да, маленький промоушн =)
    18 фев 16, 16:34    [18836613]     Ответить | Цитировать Сообщить модератору
     Re: а может ли план запроса быть построен / перестроен в зависимости от доступных ресурсов?  [new]
    Владислав Колосов
    Member

    Откуда:
    Сообщений: 7868
    Crimean
    Владислав Колосов
    куда же делась эта функциональность? Называется она "руководство планов".


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


    Почему же, я активно использовал, пока не было признано, что для базы эффективнее было бы отключить простую модель прослушивания.
    Логика подсказывает, что лучше использовать фиксированный эффективный план, чем выключать его по какому-тот закону для одного и того же запроса с различными значениями параметров. Разве что для NULL значений может быть сделано исключение, но и здесь отключение не однозначно.
    18 фев 16, 16:48    [18836713]     Ответить | Цитировать Сообщить модератору
     Re: а может ли план запроса быть построен / перестроен в зависимости от доступных ресурсов?  [new]
    МуМу
    Member

    Откуда:
    Сообщений: 1134
    Ну, вот - всю бизнес модель поломали:) Ладно, хотя бы год есть в перспективе.
    18 фев 16, 17:01    [18836831]     Ответить | Цитировать Сообщить модератору
     Re: а может ли план запроса быть построен / перестроен в зависимости от доступных ресурсов?  [new]
    Crimean
    Member

    Откуда:
    Сообщений: 13148
    МуМу
    Ну, вот - всю бизнес модель поломали:) Ладно, хотя бы год есть в перспективе.


    ну, не все так просто. пока начнется переход на 2016.. хотя плюшек там, безусловно, достаточно
    хотя если эффективность будет как у того же Backup Compression, то - да
    но - интересно для каких редакций это будет доступно
    18 фев 16, 17:03    [18836848]     Ответить | Цитировать Сообщить модератору
     Re: а может ли план запроса быть построен / перестроен в зависимости от доступных ресурсов?  [new]
    SomewhereSomehow
    Member

    Откуда: Moscow
    Сообщений: 2480
    Блог
    МуМу
    Ну, вот - всю бизнес модель поломали:) Ладно, хотя бы год есть в перспективе.

    Вам нужно было интегрироваться в команду разработки QDS =)

    Crimean
    но - интересно для каких редакций это будет доступно

    Точно не известно до выхода релиза (хотя может быть уже где-то анонсировалось, а я просто пропустил).
    Но когда я смотрел какой-то из докладов Конора Канингема (Partner Software Architect, SQL Server Engine at Microsoft) около полугода назад, ему задавали этот вопрос и он ответил что-то вроде: "You would be happy". Так что есть основания полагать, что для всех?
    18 фев 16, 17:42    [18837127]     Ответить | Цитировать Сообщить модератору
     Re: а может ли план запроса быть построен / перестроен в зависимости от доступных ресурсов?  [new]
    SomewhereSomehow
    Member

    Откуда: Moscow
    Сообщений: 2480
    Блог
    Crimean,

    Хочу сказать спасибо за вопрос!

    Похожий вопрос у меня давно лежал в черновиках идей для заметки в блог, но вы его пнули под пятую точку, в итоге, по-моему, получилась годная заметка, там даже есть время выполнения, которое в среднем ниже в два раза (писал, правда, на английском). Кому интересно: http://www.queryprocessor.com/query-plan-on-a-busy-server/
    18 фев 16, 22:41    [18838229]     Ответить | Цитировать Сообщить модератору
    Топик располагается на нескольких страницах: [1] 2 3   вперед  Ctrl      все
    Все форумы / Microsoft SQL Server Ответить