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

Откуда: Moscow
Сообщений: 398
Имеем запросы, на выходе которых несколько десятков миллионов строк. Запросы не сложные, но требуют то сортировки для MERGE, то памяти для построения HASH таблиц. Параметр max degree of parallelism скромно 4. При 1 эти запросы работают в два раза дольше. При больше 4 - выгода малозаметна.
Проблема в том, что эти запросы упорно требуют по 25-45 гигабайт оперативки и покорно ждут, пока сервер не сможет им столько выделить. Причем, при выполнении, рекордный результат реального потребления памяти был меньше 10 гигабайт. Статистики обновлял, даже с FULLSCAN. Не помогает. В плане запроса количество записей в разы превышает реальное. Пока для хранилища не был выделен отдельный сервер, max degree of parallelism был 1 и памяти вполне хватало. Когда стало 4 - запросы стали выполнятся быстрее, но по одному. То есть, свыше половины времени, пока SSAS считывает результаты запроса, SQL сервер просто отдыхает.
Можно ли как то вручную ограничить размер памяти запрашиваемую запросом у сервера при старте?
27 фев 18, 08:40    [21220636]     Ответить | Цитировать Сообщить модератору
 Re: Как ограничить аппетиты запроса к памяти?  [new]
Владислав Колосов
Member

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

автор
25-45 гигабайт

не много для
автор
несколько десятков миллионов строк

Ищите спонсора, объем кузова должен соответствовать грузу...

автор
Когда стало 4 - запросы стали выполнятся быстрее, но по одному

Мало ядер под задачи, см выше...

Семь шапок можно сшить из одной шкуры, но маленьких.
27 фев 18, 11:27    [21221176]     Ответить | Цитировать Сообщить модератору
 Re: Как ограничить аппетиты запроса к памяти?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Владислав Колосов,

полный бред.


ptr128
смотрите в планы и правьте запрос, у вас неправильная оценка.


ну и ограничитель есть с 2012, но это не вариант

https://support.microsoft.com/en-us/help/3107401/new-query-memory-grant-options-are-available-min-grant-percent-and-max
27 фев 18, 11:30    [21221188]     Ответить | Цитировать Сообщить модератору
 Re: Как ограничить аппетиты запроса к памяти?  [new]
invm
Member

Откуда: Москва
Сообщений: 9347
ptr128
Можно ли как то вручную ограничить размер памяти запрашиваемую запросом у сервера при старте?
Повелитель вомов и транзакций видимо не читает документацию? Или просто не стал утруждать себя прочтением разделов, относящихся к resource governor?
27 фев 18, 11:56    [21221281]     Ответить | Цитировать Сообщить модератору
 Re: Как ограничить аппетиты запроса к памяти?  [new]
ptr128
Member

Откуда: Moscow
Сообщений: 398
TaPaK
ну и ограничитель есть с 2012, но это не вариант

У клиента вообще 2008R2, так что точно не вариант.
27 фев 18, 12:27    [21221424]     Ответить | Цитировать Сообщить модератору
 Re: Как ограничить аппетиты запроса к памяти?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
ptr128
TaPaK
ну и ограничитель есть с 2012, но это не вариант

У клиента вообще 2008R2, так что точно не вариант.

тогда governor в руки
27 фев 18, 12:30    [21221435]     Ответить | Цитировать Сообщить модератору
 Re: Как ограничить аппетиты запроса к памяти?  [new]
ptr128
Member

Откуда: Moscow
Сообщений: 398
invm
Повелитель вомов и транзакций видимо не читает посты? Только писать умеет?

Каким образом регулятор ресурсов может откорректировать неверно рассчитанный планом запроса требуемый объем памяти?
27 фев 18, 12:34    [21221458]     Ответить | Цитировать Сообщить модератору
 Re: Как ограничить аппетиты запроса к памяти?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
ptr128
invm
Повелитель вомов и транзакций видимо не читает посты? Только писать умеет?

Каким образом регулятор ресурсов может откорректировать неверно рассчитанный планом запроса требуемый объем памяти?

у вас вопрос "Можно ли как то вручную ограничить размер памяти запрашиваемую запросом" а не откорректировать
27 фев 18, 12:36    [21221469]     Ответить | Цитировать Сообщить модератору
 Re: Как ограничить аппетиты запроса к памяти?  [new]
ptr128
Member

Откуда: Moscow
Сообщений: 398
TaPaK
у вас вопрос "Можно ли как то вручную ограничить размер памяти запрашиваемую запросом" а не откорректировать

Извиняюсь. Видимо неверно выразился. Имелось в виду именно ограничить в процессе составления плана запроса (откорректировать) и я старательно описывал ситуацию. После того, как планировщик уже рассчитал эти 45ГБ, сервер может только или выделить ему столько, или поставить в очередь.
На сервере 64 ГБ RAM. Поэтому запросы исполняются последовательно, хотя ни одни из них не потребляет во время выполнения более 10 ГБ. Так как после того, как первому сервер выделяет запрошенные 45 ГБ, на следующий запрос уже мало остается.
27 фев 18, 12:45    [21221505]     Ответить | Цитировать Сообщить модератору
 Re: Как ограничить аппетиты запроса к памяти?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
ptr128,

судя по вашему описанию вы хотите нажать кнопку - "сделать хорошо". Кривые запросы правятся конкретно каждый
27 фев 18, 12:48    [21221526]     Ответить | Цитировать Сообщить модератору
 Re: Как ограничить аппетиты запроса к памяти?  [new]
invm
Member

Откуда: Москва
Сообщений: 9347
ptr128
Каким образом регулятор ресурсов может откорректировать неверно рассчитанный планом запроса требуемый объем памяти?
Для вас "ограничить" и "откорректировать" - синонимы?
27 фев 18, 12:52    [21221540]     Ответить | Цитировать Сообщить модератору
 Re: Как ограничить аппетиты запроса к памяти?  [new]
Andy_OLAP
Member

Откуда: я знаю, что Хапоэль Беэр-Шева - чемпион
Сообщений: 3151
ptr128
Имеем запросы, на выходе которых несколько десятков миллионов строк. Запросы не сложные, но требуют то сортировки для MERGE, то памяти для построения HASH таблиц. Параметр max degree of parallelism скромно 4. При 1 эти запросы работают в два раза дольше. При больше 4 - выгода малозаметна.
Проблема в том, что эти запросы упорно требуют по 25-45 гигабайт оперативки и покорно ждут, пока сервер не сможет им столько выделить. Причем, при выполнении, рекордный результат реального потребления памяти был меньше 10 гигабайт. Статистики обновлял, даже с FULLSCAN. Не помогает. В плане запроса количество записей в разы превышает реальное. Пока для хранилища не был выделен отдельный сервер, max degree of parallelism был 1 и памяти вполне хватало. Когда стало 4 - запросы стали выполнятся быстрее, но по одному. То есть, свыше половины времени, пока SSAS считывает результаты запроса, SQL сервер просто отдыхает.
Можно ли как то вручную ограничить размер памяти запрашиваемую запросом у сервера при старте?

Резюмирую. У Вас есть измерение чеков (или скидочных карт или еще чего). Вы вместо view поверх плоской таблицы сделали view как сцепка из основной таблицы и присоединенных через left join с разными служебными (перечень id и названий типов чека или перечень id и названий видов скидочных карт и так далее).

А дальше SSAS при попытке обновления измерения ProcessUpdate кидает в сторону SQL запросы select distinct [столбец_с_key_ключевого_атрибута_измерения],[столбец_с_name_ключевого_атрибута_измерения],[столбец_с_key_неключевого_атрибута1_измерения],[столбец_с_key_неключевого_атрибута2_измерения] from view, затем для каждого атрибута измерения select distinct [столбец_с_key_неключевого_атрибута1_измерения],[столбец_с_name_неключевого_атрибута1_измерения] from view - и в какой-то момент SQL строит совсем неверный план. Когда он считает, что key_неключевого совсем мало в большой таблице - он делает предварительный merge и использует несколько ядер, когда Вы ему зажимаете яйца в тисках степень MAXDOP в единицу - он отжирает память для HASH таблицы.

А совсем все таки печально и некошерно, когда у Вас внутри view используется openquery на другой сервер и джойнится что-либо из локальной SQL базы. И еще прав нет на просмотр статистики на удаленном сервере.

Поэтому - сначала выгружаете в плоскую таблицу измерения. А затем используете для измерения view поверх этой плоской таблицы. И тогда ProcessUpdate будет быстрым и безболезненным, память использоваться ровно столько, сколько нужно и сколько запрошено, а Ваши волосы будут мягкими и шелковистыми (и не будут преждевременно выпадать из-за стресса в борьбе с гувернером ресурсов).
27 фев 18, 12:56    [21221563]     Ответить | Цитировать Сообщить модератору
 Re: Как ограничить аппетиты запроса к памяти?  [new]
Andy_OLAP
Member

Откуда: я знаю, что Хапоэль Беэр-Шева - чемпион
Сообщений: 3151
TaPaK
ptr128,

судя по вашему описанию вы хотите нажать кнопку - "сделать хорошо". Кривые запросы правятся конкретно каждый

Кнопка "сделать хорошо" в ее различных вариациях подробно описана в статьях блога Александра Южакова, который широко известен в узких кругах как уважаемый пользователь этого форума под ником Alex_496.
27 фев 18, 12:58    [21221573]     Ответить | Цитировать Сообщить модератору
 Re: Как ограничить аппетиты запроса к памяти?  [new]
ptr128
Member

Откуда: Moscow
Сообщений: 398
invm
ptr128
Каким образом регулятор ресурсов может откорректировать неверно рассчитанный планом запроса требуемый объем памяти?
Для вас "ограничить" и "откорректировать" - синонимы?

Нет, разные способы добиться того, чтобы во время расчета необходимой памяти в планировщике получилось более близкое к реальному занчению. Можно ограничить планировщик, заставляя его выдавать результат не выше ограничивающего, а можно откорретировать то, что он уже насчитал, до того, как это попадет в виде запроса на память к серверу.
Раз речь зашла о регуляторе ресурсов, то до него доходит дело только по окончании работы планировщика. Следовательно остается только корректировка.
27 фев 18, 12:58    [21221574]     Ответить | Цитировать Сообщить модератору
 Re: Как ограничить аппетиты запроса к памяти?  [new]
Andy_OLAP
Member

Откуда: я знаю, что Хапоэль Беэр-Шева - чемпион
Сообщений: 3151
ptr128
То есть, свыше половины времени, пока SSAS считывает результаты запроса, SQL сервер просто отдыхает.

Вы посмотрите, сколько служебных файлов типа map он обрабатывает внутри кубов, которые связаны с тем измерением, для которого Вы запустили ProcessUpdate, по дисковому монитору - и Вы таки удивитесь.
Строки со свежими данными из SQL - это далеко не все. Внутри SSAS своя черная магия.
27 фев 18, 13:00    [21221582]     Ответить | Цитировать Сообщить модератору
 Re: Как ограничить аппетиты запроса к памяти?  [new]
Andy_OLAP
Member

Откуда: я знаю, что Хапоэль Беэр-Шева - чемпион
Сообщений: 3151
ptr128,

+

Вообще Южаков пояснял Вам подробно по SSAS еще в 2012-м, когда Вы начинали пилить кубы в Энергосбыте.
Неужели Вы его блог до сих пор не изучили вдоль и поперек?
В SSAS он понимает больше всех нас, вместе взятых :)
27 фев 18, 13:15    [21221679]     Ответить | Цитировать Сообщить модератору
 Re: Как ограничить аппетиты запроса к памяти?  [new]
ptr128
Member

Откуда: Moscow
Сообщений: 398
Andy_OLAP
Поэтому - сначала выгружаете в плоскую таблицу измерения.

Классная рекомендация, но не выполнимая. Не лезет.
Есть операции и есть их строки. До этого момента все классно. Меньше 20 млн. строк. Однако каждая операция сопоставляется с несколькими другими построчно, причем частично и разными датами. В итоге на выходе, после JOIN с таблицей сопоставления, возникают 300 млн. строк. И пишется этот результат на диск в случае плоской таблицы 12-16(!) часов. При том, что SSAS процессит это все из VIEW даже последовательно за 4-6 часов. Если запараллелить хотя бы по 2 крупных запроса, будет уже 3-4 часа. Вопрос однако...

Что касается DISTINCT-ов, то их почти и нет. Так как почти все аналитики вынесены из таблицы фактов выше. По сути, в таблице фактов сопоставления, а сами платежи/инвойсы и строки - отдельные измерения в плоских таблицах. Конфликтуют по памяти именно запросы загрузки групп мер по разделам. А заставить клиента не лезть руками в закрытые периоды и не править там я не могу (
Доказать, что на одну дисковую систему в 5-ом RAID вешать все БД с двух SQL серверов (включая tempdb!) мне тоже пока не удалось. Спасибо, хоть убедил tempdb вынести отдельно, что и позволило отказаться от плоской таблицы.
27 фев 18, 13:26    [21221738]     Ответить | Цитировать Сообщить модератору
 Re: Как ограничить аппетиты запроса к памяти?  [new]
Andy_OLAP
Member

Откуда: я знаю, что Хапоэль Беэр-Шева - чемпион
Сообщений: 3151
ptr128
Andy_OLAP
Поэтому - сначала выгружаете в плоскую таблицу измерения.

Классная рекомендация, но не выполнимая. Не лезет.
Есть операции и есть их строки. До этого момента все классно. Меньше 20 млн. строк. Однако каждая операция сопоставляется с несколькими другими построчно, причем частично и разными датами. В итоге на выходе, после JOIN с таблицей сопоставления, возникают 300 млн. строк. И пишется этот результат на диск в случае плоской таблицы 12-16(!) часов. При том, что SSAS процессит это все из VIEW даже последовательно за 4-6 часов. Если запараллелить хотя бы по 2 крупных запроса, будет уже 3-4 часа. Вопрос однако...


Так у Вас есть виртуальная таблица фактов в 300 миллионов строк, поверх которой Вы соорудили несколько групп мер (в том числе с агрегациями distinct count).
Но инкрементальную подзагрузку плоской таблицы Вы почему-то не хотите. Потому как у Вас реализовано может быть только полная перевыгрузка за 12-16 часов (?!!!).
В таком случае, нужно разобраться, почему выгрузка из view со всеми ключами в плоскую занимает 12 часов, а выгрузка части столбцов, которые нужны для группы мер - 4-6 часов или 2-3 часа.
И перевыгружать порциями по 10-20 миллионов строк в цикле в плоскую таблицу. А далее поверх нее группы мер.

А иначе как Вы вообще сверяете факты в кубе и в исходной учетной системе, без построения витрин?

"эти запросы упорно требуют по 25-45 гигабайт оперативки и покорно ждут, пока сервер не сможет им столько выделить" - нет, Вы ведь кидаете запросы к операциям и их строкам напрямую на учетной системе, в этот момент пользователи активно вбивают новые документы, все в блокировках, все тормозит и ждет и душит друг друга.
Не надо так. Это грабли.
27 фев 18, 13:37    [21221794]     Ответить | Цитировать Сообщить модератору
 Re: Как ограничить аппетиты запроса к памяти?  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
мдя..
Делайте правильные партишенны в кубе с ограничением по диапазону выборки и не будет у вас таких монстровВ лоб вы все равно не решите .если на выходе 300 лямов , даже если сделаете правильные планы запросов
Архитектуру надо смотреть по факту
27 фев 18, 13:48    [21221835]     Ответить | Цитировать Сообщить модератору
 Re: Как ограничить аппетиты запроса к памяти?  [new]
ptr128
Member

Откуда: Moscow
Сообщений: 398
Andy_OLAP
Но инкрементальную подзагрузку плоской таблицы Вы почему-то не хотите.

Она есть, но не всегда срабатывает. Регулярно происходит правка старых периодов, вплоть до лохматых годов. Причем именно в сопоставлениях. И из-за буквально нескольких сопоставлений приходится процессить целиком раздел за год. Кстати DISTINCT COUNT группа мер всего одна, мелкая с одной мерой (специально выносил), и с ней проблем у меня как раз нет.

Andy_OLAP
В таком случае, нужно разобраться, почему выгрузка из view со всеми ключами в плоскую занимает 12 часов, а выгрузка части столбцов, которые нужны для группы мер - 4-6 часов или 2-3 часа.

Я знаю почему. Потому что дисковая система SQL серверов перегружена и стоит не на RAID 1+0, а на 5, который заметно тормозит при записи.

Andy_OLAP
И перевыгружать порциями по 10-20 миллионов строк в цикле в плоскую таблицу.

Так и делалось, но по 40-50 млн., что не суть как важно.

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

С чего Вы это взяли? Сначала за 30-40 минут все модификации из продуктивной БД реплицируются в БД хранилища, а далее все трансформации и процессинг куба происходит уже из БД хранилища, к которому, кроме SSAS во время процессинга, вообще никто не обращается.

P.S. Почти четыре года они работали вообще ко мне не обращаясь. Все было хорошо, пока не обнаружили, что обновление куба не успевает закончиться с окончания рабочего дня до начала следующего. После отказа от плоской таблицы на продуктивном SQL сервере, обновление стало вписываться в 6 часов (2 репликация и трансформация, 2-4 - процессинг). А вот при переносе БД хранилища на отдельный SQL сервер, процессинг стал медленней в два раза. И именно из-за памяти, с чего я и начал топик. Вот такая Санта-Барбара...
27 фев 18, 14:01    [21221895]     Ответить | Цитировать Сообщить модератору
 Re: Как ограничить аппетиты запроса к памяти?  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
Analysis Services Operations Guide
These two settings determine how much memory is allocated for the creation of aggregations and indexes in each partition. When Analysis Services starts partition processing, parallelism is throttled based on the AggregationMemoryMin/Max setting. The setting is per partition. For example, if you start five concurrent partition processing jobs with AggregationMemoryMin = 10, an estimated 50 percent (5 x 10%) of reserved memory is allocated for processing. If memory runs out, new partition processing jobs are blocked while they wait for memory to become available. On a large memory system, allocating 10 percent of available memory per partition may be too much. In addition, Analysis Services may sometimes misestimate the maximum memory required for the creation of aggregates and indexes. If you process many partitions in parallel on a large memory system, lowering the value of AggregationMemoryLimitMin and AggregationMemoryMax may increase processing speed. This works because you can drive a higher degree of parallelism during the process index phase.
27 фев 18, 15:19    [21222221]     Ответить | Цитировать Сообщить модератору
 Re: Как ограничить аппетиты запроса к памяти?  [new]
ptr128
Member

Откуда: Moscow
Сообщений: 398
Maxx
Архитектуру надо смотреть по факту

Это долго и явно за рамками данного форума. Развесистая структура все же из почти сотни измерений и десятков разделов.
Понятно, что архитектуру надо оптимизировать. Но на это уже воля клиента - заплатит, займусь. Не заплатит - так и останется.
27 фев 18, 17:49    [21222993]     Ответить | Цитировать Сообщить модератору
 Re: Как ограничить аппетиты запроса к памяти?  [new]
Владислав Колосов
Member

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

А сколько ядер всего у вас для SQL сервера? Используется ли NUMA?
27 фев 18, 18:20    [21223117]     Ответить | Цитировать Сообщить модератору
 Re: Как ограничить аппетиты запроса к памяти?  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7757
При 64Гб вы никак не упираетесь в память при выделении 25-45 для запроса.
27 фев 18, 18:22    [21223121]     Ответить | Цитировать Сообщить модератору
 Re: Как ограничить аппетиты запроса к памяти?  [new]
ptr128
Member

Откуда: Moscow
Сообщений: 398
Владислав Колосов
При 64Гб вы никак не упираетесь в память при выделении 25-45 для запроса.

Для одного - не упираюсь. Для двух параллельно - уже упираюсь. Вот и идут они последовательно.
Ядер у SQL 8
27 фев 18, 18:40    [21223165]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить