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. Единственный способ уменьшить резервирование памяти это переписать запросы. Смотрите почему предполагаемое количество строк превышает реальное. Хотя обычно единственный способ вправить мозги в таких случаях это разбить один сложный запрос на несколько простых с использованием временных таблиц, хотя похоже для вас это тоже может быть не вариант.

Хотя есть еще один хак:


Потребление памяти должно уменьшится, но есть ненулевая вероятность что по скорости станет еще хуже. Во первых, если для запроса резервируется меньше памяти, то следовательно для каждого компонента запроса пропорционально уменьшается количество реально используемой памяти, там где она реально может быть нужна. Также при сильно низких значениях @top, план может кардинально поменятся - nested loop вместо hash, серийный план вместо паралельного и т.д. Короче, я бы не советовал идти по этому пути, но можете поэксперементировать.

Что-то я затупил, можно игнорировать мой предыдущий комментарий. Конечно же в 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Можно ли как то вручную ограничить размер памяти запрашиваемую запросом
ptr128invmпропущено...
Для вас "ограничить" и "откорректировать" - синонимы?Можно ограничить планировщик, заставляя его выдавать результат не выше ограничивающего, а можно откорретировать то, что он уже насчитал, до того, как это попадет в виде запроса на память к серверу.Разница то в чем? Запрос будет требовать меньше памяти, следовательно больше запросов сможет выполнятся параллельно, что вам еще надо то? Чтобы сервер в зависимости от доступной памяти другой план построил что-ли? да даже новая опция в 2012 MAX_GRANT_PERCENT делает по сути все тоже самое что и RG, только на уровне одиночного запроса.

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

ptr128MindЕдинственный способ уменьшить резервирование памяти это переписать запросы.
Знаю. Просто возни много. Необходимо будет вместо одной таблицы наплодить, как минимум, пять. Причем в них сделать уникальные индексы, чтобы планировщик понял, что количество записей при JOIN не умножается. Поэтому заполняться они будут медленно и грустно, старательно индексируясь.Очень редко на временных таблицах реально нужны индексы. Уникальные индексы не имеют ничего общего со статистикой.

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

Может так понятнее?
авторREQUEST_MAX_MEMORY_GRANT_PERCENT =value
Указывает максимальное количество памяти, которое может понадобиться одному запросу из пула. Это процентное соотношение относительно к размеру пула ресурсов, указанного в MAX_MEMORY_PERCENT. Это настройка не для всего пула и ничего он не запрещает серверу выдавать.

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

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