Нам нравится думать, что SQL Server использует всю нашу память для кэширования данных, но это лишь часть правды. SQL Server использует память для многих вещей:

• Кэширование объектов базы данных
• Сортировка данных для результатов запроса
• Планирование выполнения кэширования
• Выполнение системных задач

Часто мы удивляемся тому, как мало данных кэшируется для каждой базы данных.

На прошлой неделе мы просмотрели 8KB страницы в нашей базе данных. Эти страницы одинаковы независимо от того, находятся ли они на диске или в памяти - они включают в себя идентификатор базы данных и идентификатор объекта, поэтому, если мы посмотрим на все страницы в памяти, мы можем выяснить, какие таблицы кэшируются в памяти прямо сейчас. Следующий запрос дает нам волшебные ответы, но имейте в виду, что чем больше у вас памяти, тем больше времени это займет. Он не будет блокировать других пользователей, но это может занять минуту или две, если у вас >64 ГБ памяти, несколько минут, если у вас есть терабайт или больше:

SELECT CAST(COUNT(*) * 8 / 1024.0 AS NUMERIC(10, 2)) AS CachedDataMB ,

CASE database_id WHEN 32767 THEN 'ResourceDb' ELSE DB_NAME(database_id) END AS DatabaseName

FROM sys.dm_os_buffer_descriptors

GROUP BY DB_NAME(database_id) , database_id

ORDER BY 1 DESC

Сравните размер каждой базы данных и количество кэшируемых данных. Часто в поле, я могу увидеть 100 ГБ базы данных, у которых только  8-12 ГБ кэшированных данных. Это может быть вполне нормально - если вы регулярно запрашиваете только этот объем данных, но что, если вам постоянно нужны все 100 Гбайт?

Это ведет нас к интересным вопросам

Этот DMV-запрос ведет к множеству вопросов о настройке производительности!

Как быстро меняются кэшированные страницы? С того момента, как мы прочитали страницу размером 8КБ, сколько времени она остается в памяти, прежде чем мы должны будем  удалить ее из кеша для того, чтобы освободить место для чего-то еще, что мы читаем с диска? Эта концепция - ожидание страницы Page Life, счетчик Perfmon, который измеряет в секундах, сколько времени что-либо остается в оперативной памяти. Чем дольше, тем лучше.

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

Мы кэшируем малоценные данные? Если вы смешиваете приложения поставщиков и собственные приложения на сервере, вы часто можете обнаружить, что самое плохо написанное приложение будет использовать большую часть памяти. Дело в том, что это может быть не самое важное приложение. К сожалению, у нас нет способа ограничить объем памяти, используемый каждой базой данных. Именно поэтому большинство магазинов предпочитают запускать приложения-вендоры на отдельных виртуальных машинах или серверах - таким образом, они не выводят всю память на SQL Server, которая должна обслуживать другие приложения.

У нас достаточно памяти? Если вы используете SQL Server 2008 / R2 / 12 Standard Edition, вы ограничены только 64 ГБ физической оперативной памяти. Если вы используете SQL Server на голом железе (а не на виртуальной машине) и у вас меньше 64 ГБ, покупайте еще, чтобы добраться до отметки в 64 ГБ. Это самое безопасное и простое изменение настроек производительности, которое вы можете сделать. Если вы работаете на виртуальной машине или работаете с Enterprise Edition, вопрос с памятью усложняется. Чтобы узнать больше, прочитайте  A Sysadmin's Guide to SQL Server Memory.

Используем ли мы память для чего-либо другого, кроме SQL Server? Если у нас есть Integration Services, Analysis Services, Reporting Services или любые другие приложения, установленные на нашем сервере, это лишает нас драгоценной памяти, которая нам может понадобиться для кэширования данных. Не запускайте SSMS - это пожиратель памяти. Поместите свои средства управления на виртуальную машину в центре обработки данных и удаленный рабочий стол вместо этого.

Можем ли мы уменьшить потребность в памяти с помощью индексов? Если у нас есть действительно широкая таблица (много полей) или действительно широкий индекс, и мы не запрашиваем активно большинство этих полей, то мы кэшируем целую кучу данных, которые нам не нужны. Помните, SQL Server кэширует на уровне страницы, а не на уровне поля. Некластерный индекс - это более узкая копия таблицы с нужными нам полями / столбцами. Чем меньше полей, тем больше данных мы можем упаковать на страницу. Чем больше мы можем упаковать, тем больше данных мы кэшируем.

Когда я настраиваю индексы на сервере, который я никогда раньше не видел, sys.dm_os_buffer_descriptors - одно из первых мест, куда я смотрю. База данных с самым большим количеством элементов, кэшированных здесь, скорее всего, будет той, которая нуждается в помощи с индексами, и мы поговорим об индексах дальше.

No Comments