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

Откуда: Moscow
Сообщений: 888
Имеем запросы, на выходе которых несколько десятков миллионов строк. Запросы не сложные, но требуют то сортировки для 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

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

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

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

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

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

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

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

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

полный бред.


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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Откуда: Москва
Сообщений: 9779
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
Сообщений: 888
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
Сообщений: 888
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
Сообщений: 888
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
Сообщений: 888
Maxx
Архитектуру надо смотреть по факту

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

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

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

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

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

Для одного - не упираюсь. Для двух параллельно - уже упираюсь. Вот и идут они последовательно.
Ядер у SQL 8
27 фев 18, 18:40    [21223165]     Ответить | Цитировать Сообщить модератору
 Re: Как ограничить аппетиты запроса к памяти?  [new]
ptr128
Member

Откуда: Moscow
Сообщений: 888
Владислав Колосов
Используется ли NUMA?

Нет. Сервер SMP/UMA
27 фев 18, 18:42    [21223171]     Ответить | Цитировать Сообщить модератору
 Re: Как ограничить аппетиты запроса к памяти?  [new]
Владислав Колосов
Member

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

Ну если такой прессинг на память и изменить запросы нет возможности, то другого выхода нет, кроме как ее увеличить. На что мне было сказано "бред полный".
Попробуйте ограничить на сервере максимальную степень параллелизма до 2-4 ядер, ваш SSAS пытается захватить все 8, судя по всему.
28 фев 18, 12:51    [21224531]     Ответить | Цитировать Сообщить модератору
 Re: Как ограничить аппетиты запроса к памяти?  [new]
ptr128
Member

Откуда: Moscow
Сообщений: 888
Владислав Колосов
ptr128,
Ну если такой прессинг на память и изменить запросы нет возможности, то другого выхода нет, кроме как ее увеличить.

Оригинальный подход. Несмотря на то, что из 64 ГБ запросами используется меньше половины, надо увеличить память. Я уж скорее пойду неправедным путем через sp_create_plan_guide
28 фев 18, 13:59    [21224819]     Ответить | Цитировать Сообщить модератору
 Re: Как ограничить аппетиты запроса к памяти?  [new]
Andy_OLAP
Member

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

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

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

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

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

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

P.S. Почти четыре года они работали вообще ко мне не обращаясь. Все было хорошо, пока не обнаружили, что обновление куба не успевает закончиться с окончания рабочего дня до начала следующего. После отказа от плоской таблицы на продуктивном SQL сервере, обновление стало вписываться в 6 часов (2 репликация и трансформация, 2-4 - процессинг). А вот при переносе БД хранилища на отдельный SQL сервер, процессинг стал медленней в два раза. И именно из-за памяти, с чего я и начал топик. Вот такая Санта-Барбара...

А как так выходит, что выгрузка в цикле кусками по 40-50 миллионов строк длится 16 часов, а те же самые строки, засасываемые в OLAP базу, которая лежит на этом же RAID-5 (ведь так?), загружаются за 2-4 часа. Может быть, не все столбцы нужны для обработки в OLAP?
+

Вам Камаев внедрял InfoSoft EnergySupply для Аксапты? Сходите на форум axforum, там модератором кстати Георгий Нордический, он тут активно в ветке "OLAP и DWH" общается, поспрашивайте, как данные для энергетики выгружаются инкрементально в плоскую таблицу кусками, по сколько миллионов строк, возможно, он подскажет коллег, которые готовы поделиться опытом.
28 фев 18, 14:12    [21224908]     Ответить | Цитировать Сообщить модератору
 Re: Как ограничить аппетиты запроса к памяти?  [new]
Andy_OLAP
Member

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

"А вот при переносе БД хранилища на отдельный SQL сервер" - я понял, у Вас чтение из RAID-5 и загрузка по сети на сервер с OLAP, а витрину готовить нужно на том же сервере, где все хранилище целиком.
Вы можете на сервере с OLAP поднять SQL инстанс, сделать там плоскую таблицу для OLAP и туда по сети инкрементально перегонять строки из RAID-5, а затем локально уже с готовой таблицы в OLAP процессировать?
28 фев 18, 14:14    [21224925]     Ответить | Цитировать Сообщить модератору
 Re: Как ограничить аппетиты запроса к памяти?  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8711
ptr128
Владислав Колосов
ptr128,
Ну если такой прессинг на память и изменить запросы нет возможности, то другого выхода нет, кроме как ее увеличить.

Оригинальный подход. Несмотря на то, что из 64 ГБ запросами используется меньше половины, надо увеличить память. Я уж скорее пойду неправедным путем через sp_create_plan_guide


Как меньше половины, Вы написали, что два запроса превышают по суммарным затратам 64Гб из -за чего начинают выполняться последовательно.
28 фев 18, 16:02    [21225400]     Ответить | Цитировать Сообщить модератору
 Re: Как ограничить аппетиты запроса к памяти?  [new]
ptr128
Member

Откуда: Moscow
Сообщений: 888
Владислав Колосов
ptr128
пропущено...

Оригинальный подход. Несмотря на то, что из 64 ГБ запросами используется меньше половины, надо увеличить память. Я уж скорее пойду неправедным путем через sp_create_plan_guide


Как меньше половины, Вы написали, что два запроса превышают по суммарным затратам 64Гб из -за чего начинают выполняться последовательно.

Я написал, что несмотря на то, план запроса требует 45 гигабайт, реально при выполнении его пик потребления памяти не превышает 10 гигабайт. Это и есть больше половины.
28 фев 18, 19:10    [21226178]     Ответить | Цитировать Сообщить модератору
 Re: Как ограничить аппетиты запроса к памяти?  [new]
ptr128
Member

Откуда: Moscow
Сообщений: 888
Andy_OLAP
Вы можете на сервере с OLAP поднять SQL инстанс, сделать там плоскую таблицу для OLAP и туда по сети инкрементально перегонять строки из RAID-5, а затем локально уже с готовой таблицы в OLAP процессировать?

В чем смысл, если оба сервера на одном и том же дисковом массиве с RAID-5? Только память SSAS урежу, когда ему и так ее в обрез хватает, особенно во время процессинга.
28 фев 18, 19:13    [21226185]     Ответить | Цитировать Сообщить модератору
 Re: Как ограничить аппетиты запроса к памяти?  [new]
ptr128
Member

Откуда: Moscow
Сообщений: 888
Andy_OLAP
А как так выходит, что выгрузка в цикле кусками по 40-50 миллионов строк длится 16 часов, а те же самые строки, засасываемые в OLAP базу, которая лежит на этом же RAID-5 (ведь так?), загружаются за 2-4 часа. Может быть, не все столбцы нужны для обработки в OLAP?

Я писал 12-16, в зависимости от загрузки сервера, причем на котором параллелизм вообще запрещен. И при том же выключенном параллелизме все партиции считывались уже 6 часов. 4 - если не все. А вот с включенным параллелизмов в DOP 4 на другом SQL сервере я получил считывание всех партиций уже 4 часа.
А вот каким образом SSAS так пакует данные, что записывает их на диск в разы быстрее, чем SQL - я не скажу. Подозреваю, что у него организация хранения данных в разделах очень сильно отличается от страничной организации хранения данных у SQL сервера. Кроме того, сам вижу в мониторе ресурсов, как система на SSAS еще долго на диск эти партиции фигачит после процессинга.
По объемам прикинул:
- у SSAS эти разделы занимают ~16 гигабайт + соответствующие им измерения по таблицам фактов ~4 гигабайта
- на SQL размер пробной таблицы вообще без индексов ~200 гигабайт
Итого, в многомерном представлении это выглядит в 10 раз компактней.
С другой стороны, раз 20 млн. операций у меня сопоставляюся больше 200 млн. раз, то многомерное представление этой конструкции и должно было дать экономию места на порядок.

P.S. А ведь один хвост нашел. В SQL БД у меня DECIMAL(32,12), а в SSAS - double. В два раза сразу.
28 фев 18, 19:50    [21226282]     Ответить | Цитировать Сообщить модератору
 Re: Как ограничить аппетиты запроса к памяти?  [new]
ptr128
Member

Откуда: Moscow
Сообщений: 888
Andy_OLAP
Вам Камаев внедрял InfoSoft EnergySupply для Аксапты?

Нет. Мы сами внедряем Mecoms. Нам ничего не внедряют.
А то, что куб надо редизайнить я и сам знаю. Это был мой первый серьезный большой куб. И через 5 лет я вижу в нем уже массу недостатков и даже грубых ошибок, с точки зрения производительности. Все же во время проектирования у меня с трудом миллион записей в кубе набирались и многи огрехи просто не были заметны.
Но если модифицировать куб, то на это надо и мое время и клиенту прийдется модифицировать множество запросов к нему. Так что это отдельная история, хотя я настоятельно рекомендовал запланировать данный редизайн на текущий год.
28 фев 18, 20:05    [21226303]     Ответить | Цитировать Сообщить модератору
 Re: Как ограничить аппетиты запроса к памяти?  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
ptr128
Владислав Колосов
пропущено...


Как меньше половины, Вы написали, что два запроса превышают по суммарным затратам 64Гб из -за чего начинают выполняться последовательно.

Я написал, что несмотря на то, план запроса требует 45 гигабайт, реально при выполнении его пик потребления памяти не превышает 10 гигабайт. Это и есть больше половины.
Что то вы где то нас обманываете. На 1 запрос может максимум выделятся 25% от Maximum Workspace Memory которая обычно около 70-75% от Target Server Memory. Т.е. если у вас 64Гб отдано SQL, то 64*(70~75)%*25% = 11-12GB. И обычно одновременно может выполнятся до 3 очень прожорливых до памяти запросов. Так что это не совсем последовательно. Но я так понимаю вам это все равно не помогает и хотелось бы быстрее.

К сожалению магической кнопки в SQL 2008 R2. Единственный способ уменьшить резервирование памяти это переписать запросы. Смотрите почему предполагаемое количество строк превышает реальное. Хотя обычно единственный способ вправить мозги в таких случаях это разбить один сложный запрос на несколько простых с использованием временных таблиц, хотя похоже для вас это тоже может быть не вариант.

Хотя есть еще один хак:
DECLARE @top int = 2147483647
...
OPTION(OPTIMIZE FOR (@top = 10000 /* test different numbers */))

Потребление памяти должно уменьшится, но есть ненулевая вероятность что по скорости станет еще хуже. Во первых, если для запроса резервируется меньше памяти, то следовательно для каждого компонента запроса пропорционально уменьшается количество реально используемой памяти, там где она реально может быть нужна. Также при сильно низких значениях @top, план может кардинально поменятся - nested loop вместо hash, серийный план вместо паралельного и т.д. Короче, я бы не советовал идти по этому пути, но можете поэксперементировать.
28 фев 18, 21:04    [21226379]     Ответить | Цитировать Сообщить модератору
 Re: Как ограничить аппетиты запроса к памяти?  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
Что-то я затупил, можно игнорировать мой предыдущий комментарий. Конечно же в 2008 такая возможность есть, как тут уже советовали через Resource Governor:

REQUEST_MAX_MEMORY_GRANT_PERCENT =value
Specifies the maximum amount of memory that a single request can take from the pool. This percentage is relative to the resource pool size specified by MAX_MEMORY_PERCENT.

Я только совсем не понял ваши коментарии по поводу того что это не то что вам надо, потому что судя по вашему описанию все как раз наоборот:
ptr128
Можно ли как то вручную ограничить размер памяти запрашиваемую запросом

ptr128
invm
пропущено...
Для вас "ограничить" и "откорректировать" - синонимы?
Можно ограничить планировщик, заставляя его выдавать результат не выше ограничивающего, а можно откорретировать то, что он уже насчитал, до того, как это попадет в виде запроса на память к серверу.
Разница то в чем? Запрос будет требовать меньше памяти, следовательно больше запросов сможет выполнятся параллельно, что вам еще надо то? Чтобы сервер в зависимости от доступной памяти другой план построил что-ли? да даже новая опция в 2012 MAX_GRANT_PERCENT делает по сути все тоже самое что и RG, только на уровне одиночного запроса.
28 фев 18, 22:07    [21226481]     Ответить | Цитировать Сообщить модератору
 Re: Как ограничить аппетиты запроса к памяти?  [new]
ptr128
Member

Откуда: Moscow
Сообщений: 888
Mind
Что то вы где то нас обманываете.

Я не обманываю, а упрощаю. Физически у сервера 262 гигабайта. Но на нужды ETL дано 64.

Mind
Единственный способ уменьшить резервирование памяти это переписать запросы.

Знаю. Просто возни много. Необходимо будет вместо одной таблицы наплодить, как минимум, пять. Причем в них сделать уникальные индексы, чтобы планировщик понял, что количество записей при JOIN не умножается. Поэтому заполняться они будут медленно и грустно, старательно индексируясь.
28 фев 18, 23:20    [21226622]     Ответить | Цитировать Сообщить модератору
 Re: Как ограничить аппетиты запроса к памяти?  [new]
ptr128
Member

Откуда: Moscow
Сообщений: 888
Mind
Разница то в чем? Запрос будет требовать меньше памяти

Нет. Запрос меньше памяти требовать будет. Просто сервер его не выполнит, так как он требует больше памяти, чем серверу разрешено выдавать.
28 фев 18, 23:24    [21226630]     Ответить | Цитировать Сообщить модератору
 Re: Как ограничить аппетиты запроса к памяти?  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
ptr128
Mind
Что то вы где то нас обманываете.

Я не обманываю, а упрощаю. Физически у сервера 262 гигабайта. Но на нужды ETL дано 64.
Теперь вы еще большую ерунду говорите. При чем тут сколько у сервера физически? При чем тут ETL? Мы же про SQL Server говорили. И как вы отдали 64GB ETL?
Еще раз, при дефолтных настройках один запрос не может забрать себе всю память, таких запросов нужно как минимум 3.

ptr128
Mind
Единственный способ уменьшить резервирование памяти это переписать запросы.

Знаю. Просто возни много. Необходимо будет вместо одной таблицы наплодить, как минимум, пять. Причем в них сделать уникальные индексы, чтобы планировщик понял, что количество записей при JOIN не умножается. Поэтому заполняться они будут медленно и грустно, старательно индексируясь.
Очень редко на временных таблицах реально нужны индексы. Уникальные индексы не имеют ничего общего со статистикой.
1 мар 18, 02:18    [21226857]     Ответить | Цитировать Сообщить модератору
 Re: Как ограничить аппетиты запроса к памяти?  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
ptr128
Mind
Разница то в чем? Запрос будет требовать меньше памяти

Нет. Запрос меньше памяти требовать будет. Просто сервер его не выполнит, так как он требует больше памяти, чем серверу разрешено выдавать.
Вы это уже проверили, что так безапелляционно заявляете? Интересно, почему у меня все выполняется? И запросы которые до этого запрашивали по 5Гб, теперь хотят всего 1? Мы точно говорим о requested и granted memory, а не о ideal memory?

Может так понятнее?
автор
REQUEST_MAX_MEMORY_GRANT_PERCENT =value
Указывает максимальное количество памяти, которое может понадобиться одному запросу из пула. Это процентное соотношение относительно к размеру пула ресурсов, указанного в MAX_MEMORY_PERCENT.
Это настройка не для всего пула и ничего он не запрещает серверу выдавать.
1 мар 18, 02:30    [21226864]     Ответить | Цитировать Сообщить модератору
 Re: Как ограничить аппетиты запроса к памяти?  [new]
ptr128
Member

Откуда: Moscow
Сообщений: 888
Mind
Очень редко на временных таблицах реально нужны индексы. Уникальные индексы не имеют ничего общего со статистикой.

Теперь вы еще большую ерунду говорите. Мало того, что предлагаете процессить куб из временных таблиц (интересно как?) так еще и пытаетесь меня унизить. Я отлично вижу, почему в плане запроса у меня количество записей в ряде узлов в разы больше, чем на самом деле. Именно потому, что планировщик ничего не занет об уникальности некоторых сочетаний.
1 мар 18, 09:46    [21227222]     Ответить | Цитировать Сообщить модератору
 Re: Как ограничить аппетиты запроса к памяти?  [new]
StarikNavy
Member

Откуда: Москва
Сообщений: 2414
ptr128
Я отлично вижу, почему в плане запроса .

имхо, если планировщик ошибается, никакие хаки, кроме переписки запросов/правильных индексов не помогут
1 мар 18, 12:33    [21228022]     Ответить | Цитировать Сообщить модератору
 Re: Как ограничить аппетиты запроса к памяти?  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
ptr128
Mind
Очень редко на временных таблицах реально нужны индексы. Уникальные индексы не имеют ничего общего со статистикой.

Теперь вы еще большую ерунду говорите. Мало того, что предлагаете процессить куб из временных таблиц (интересно как?) так еще и пытаетесь меня унизить. Я отлично вижу, почему в плане запроса у меня количество записей в ряде узлов в разы больше, чем на самом деле. Именно потому, что планировщик ничего не занет об уникальности некоторых сочетаний.
Я не собирался никого унизить. Я не знаю что вы там обрабатываете и можете ли переписать запросы или нет. Если нет, решение уже было предложено - resource governor, и даже не надо никаких новых пулов создавать, просто ограничить memory grant для default workload группы.

Может я некорректно выразился, я хотел сказать то что планировщик (он же оптимизатор запросов) в первую очередь смотрит на статистики, и если они хорошие, то дополнительный уникальный индекс не поможет создать план еще лучше, если же статистики плохие, то далеко не всегда создание уникального индекса хоть чем то поможет. Особенно когда начинаются всякие уникальности сочетаний или оценка кардинальности соединения таблиц и т.д.
1 мар 18, 20:25    [21230127]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: 1 2      [все]
Все форумы / Microsoft SQL Server Ответить