Сколько памяти необходимо для сохранения таблицы в кеше? (SQL Server 2008)


5

Я запускаю длинные и сложные запросы, которые в основном используют одну большую таблицу - 8 ГБ, 40 М строк. AFAIK, все/большинство строк используются в каждом запросе. Я вижу много IO в мониторе активности - для первого запроса и для каждого последующего. В настоящее время сервер использует 6,5 ГБ памяти, и я хочу обновить его. Вопрос в том, сколько памяти требуется, чтобы избежать всех этих чтений на диске? Является ли это в приблизительном размере стола или более?

Это выходной сигнал SET STATISTICS IO. BigTable - это тот, о котором я спрашиваю, у SmallTable есть отношение «1 ко многим» с BigTable. #entrance содержит вывод запроса (несколько сотен строк вывода).

Table 'SmallTable'. Scan count 249005, logical reads 2829948, physical reads 2605, read-ahead reads 10395, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. 
Table 'BigTable'. Scan count 194004, logical reads 13482115, physical reads 33841, read-ahead reads 1181136, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. 
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. 
Table '#entrance__000000000023'. Scan count 0, logical reads 1568, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. 
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. 
+2

не сделать ли этот сервер/база данных ничего другого, кроме выполнения запросов в течение 1 таблицы 8 Гб данных? Я также немного почесываю голову над 1 столом, но сложный аспект вопроса этого вопроса. Любая вероятность DDL или небольшое понимание характера этой сложности? 14 сен. 112011-09-14 22:20:40

  0

Просто базовая проверка, какую версию SQL-сервера вы используете? если это не предприятие, то вы не сможете использовать всю свою текущую память для сервера sql. 16 сен. 112011-09-16 15:30:00

  0

В конечном итоге я обновил оперативную память сервера и выполнил тест. Я опубликовал результаты в качестве ответа ниже. 09 фев. 132013-02-09 08:50:11

3

Какова природа ваших запросов? ВЫБОР только? Или смесь DUI (DELETE, UPDATE, INSERT)? и какова ваша модель восстановления? используете ли вы уровень изоляции транзакций по умолчанию?

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

Рассмотрите все вышеперечисленное ... да, отметьте размер таблицы на диске как объем памяти. Просто не удивляйтесь, если вы обнаружите, что у вас все еще есть дисковый ввод-вывод.

  0

Я обновил оперативную память сервера и «приблизил размер стола на диске, так как количество памяти» абсолютно верно. См. Мой ответ ниже. 08 фев. 132013-02-08 11:59:48


8

Это ответ на две части.

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

Вторая часть - то, что вы всегда будете видеть, читает данные. Трюк заключается в том, чтобы увидеть, являются ли эти чтения физическими или логическими. Физические чтения спускаются на диск. Логические чтения считываются из пула буферов в памяти. Вы можете это увидеть, поставив «SET STATISTICS IO ON» в верхней части SQL-скрипта, затем запустив его в SSMS. На вкладке сообщений вы получите конкретную информацию IO для каждого выполняемого запроса, в частности, вы получите количество логических и физических операций (чтение в вашем случае), которые происходят в таблице по таблицам. Если физические числа чтения довольно высокие (эти числа в основном представляют собой количество блоков, которые были прочитаны так на 8, а затем делят на 1024, чтобы получить число в Megs), а затем вам нужно больше оперативной памяти. Если логические числа чтения высоки, то вы фактически не собираетесь на диск, но ваш запрос может нуждаться в настройке, создании индексов и т. Д.

(Примечание: для целей этого ответа я принимаю только инструкции SELECT Операции INSERT/UPDATE/DELETE немного сложнее, но применяются те же основы.)

  0

Я постараюсь: УСТАНОВИТЬ СТАТИСТИКУ IO ON и отчитаться. Дополнительная информация: запросы на 100% выбираются. Каждый запрос занимает несколько минут. Сервер также запускает небольшое приложение, которое делает результаты запросов и дампов в Excel, но я сомневаюсь, что это влияет на производительность сервера. 15 сен. 112011-09-15 05:00:37

  0

Если вам нужна помощь в чтении вывода, не стесняйтесь публиковать план выполнения и вывод SET STATISTICS IO. 15 сен. 112011-09-15 05:05:59


1

Не уверен, что здесь будет больше ОЗУ. Быстрый расчет предполагает, что ваш запрос обрабатывается где-то в области 125 ГБ данных, что весьма впечатляет для таблицы, содержащей 8 ГБ.

(2829948 + 13482115 + 1568) х 8/1048576 = 125GB

270MB физических считываний будет составлять часть медленного бега, но ничего подобного столько, сколько накладных расходов в памяти.

Вы получите лучшие результаты при повторном выполнении запроса, чем путем добавления дополнительной памяти. Отправьте DDL/запрос/план выполнения в новый вопрос, и кто-то, безусловно, сможет помочь.


3

Я всегда опаздываю на вечеринку. :) Этот запрос, скорее всего, может быть настроен на большой эффект. Но, по вопросу о том, сколько оперативной памяти кэшировать таблицу на 8 ГБ ...предварительный вопрос заключается в том, сколько узлов памяти делает отчет SQL Server для сервера? Каждый узел памяти (логический эквивалент физическим узлам NUMA) имеет автономно управляемый кеш пула буферов. Задачи, выполняемые на процессорах в этом узле, вставляют блоки в пул буферов в этом узле.

Итак, если есть вероятность, что задачи, которые будут читать таблицу 8 ГБ в полном объеме, будут выполняться на одном узле NUMA, буферный пул этого узла должен содержать таблицу 8 ГБ. Поэтому начните с 8 ГБ максимальной памяти сервера на узел NUMA. Тогда давайте начнем его увеличивать. Память запросов может потреблять до 70% пула буферов, оставляя только 30% для содержимого блока базы данных. Таким образом, таблица 8 ГБ должна быть не более 30% максимальной памяти сервера на одном узле NUMA для сервера. 26 2/3 ГБ для максимальной памяти сервера. На узел NUMA. Если его SQL Server 2012, то материал, который SQL Server 2008 R2 и предыдущий использовал многостраничный распределитель (который, таким образом, был за пределами области максимальной памяти сервера), теперь находится в пределах максимальной памяти сервера и должен учитываться. Он должен быть включен в общую память сервера в любом случае, независимо от того, является ли его часть максимального объема памяти сервера SQL Server максимальным или нет.

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

Достаточно памяти для ОС Windows (минимум 4 ГБ). Добавьте достаточно для рабочих потоков SQL Server, основного процесса SQL Server, заданий агента SQL и любых других потребностей в памяти приложения.

На одном сервере узлов NUMA я бы приблизил около 36 ГБ ОЗУ с максимальной памятью сервера не менее 27 ГБ, чтобы надежно кэшировать таблицу 8 ГБ. И если есть несколько узлов NUMA, это увеличится как минимум на 26 2/3 ГБ максимальной памяти сервера на узел NUMA, а также на дополнительные рабочие потоки, которые будут сопровождать дополнительные ядра в дополнительных узлах NUMA.

Хотя это может показаться чрезмерным, отчасти это связано с тем, как SQL Server обрабатывает NUMA. Но вам может быть повезло! Если достаточно одного ленивого автора, вы можете использовать флаг 8015 трассировки запуска, чтобы игнорировать NUMA и управлять единственным пулом буферов. Таким образом, достаточно 36 - 40 ГБ, чтобы сохранить резидентную память на 8 ГБ независимо от того, сколько узлов NUMA существует на сервере. обсудите это чуть больше в следующем сообщении в блоге. Если вы решите использовать флаг трассировки 8015, я рекомендую также оценить флаг 8048 трассировки вместе с ним, чтобы предотвратить эскалацию конкуренции спин-блокинга и ожидания CMEMTHREAD, связанные с распределением памяти. http://sqlsasquatch.blogspot.com/2013/02/sql-server-cache-database-working-set.html


2

Я обновил оперативную память сервера с 8 до 64 ГБ и выполнил тест. Размер таблицы теперь составляет 16,05 ГБ, а индексное пространство - 187 МБ (1 кластеризованный и 1 уникальный некластеризованный индекс). Я проверил общее использование ОЗУ сервера до и после запуска запроса, который усредняет каждое из 137 полей в таблице. Общая память выросла с 4.22GB до 20.4GB = 16.18GB, что составляет 100.8% от размера таблицы. SQLRockstar был абсолютно прав, предсказывая, что объем использования ОЗУ является приблизительным размером стола.

Подробнее:

  • Страница Полнота кластерного индекса 98,1%
  • Дальнейшие запросы на столе не вызывает диск IO.
  • SQL Server 2008R2 Разработчик
  • План выполнения показывает, что кластерный индекс использовался