По материалам статьи Neil Boyle на swynk.com "SQL Server two minute memory tune-up"
http://www.swynk.com/friends/boyle/default.asp
В своей статье, Нейл предлагает нам удобный способ оценки оптимальности
настроек SQL сервера для обеспечения максимального КПД использования
памяти. Ниже представлен используемый автором для этих целей скрипт,
который запускается, как на SQL 6.5, так и на 7 (с учётом некоторых
оговорок, следующих по тексту статьи).
dbcc traceon(3604)
go
dbcc sqlperf(lrustats)
go
dbcc bufcount(1)
go
-- dbcc memusage - Перед использованием прочтите комментарий для SQL Server 7
go
dbcc proccache
go
dbcc traceoff(3604)
Dbcc traceon - разрешает использование некоторых команд из представленного
скрипта (иначе они не выполняются). Traceoff - в конец выключает эту возможность.
Dbcc sqlperf(lrustats) - выдаёт подробности использования кэша. Наиболее
интересные для Вашего анализа значения: "cache hit ratio" - которое у Вас должно стремиться к 100 %;
"cache flushes" - которое выдаёт число сброса данных из кэша на диск,
для высвобождения места другим данным. В идеальном случае, значение
"cache flushes" должно равняться нулю (если только размер Вашей базы
данных не значительно превышает размер ОЗУ).
Если выданные Вам значения не выглядят удовлетворительными, используйте
команду "Sp_configure memory", чтобы понять, имеет ли SQL сервер достаточно
памяти. Это особенно важно для 6.5, у которого по умолчанию установлено
очень маленькое значения отводимой серверу баз данных памяти. В SQL 7
память выделяется автоматически. См. "Причины не эффективного использования
кэша" в конце этой статьи, если с памятью у Вас всё в порядке.
Размер памяти, которую Вы отдаёте SQL 6.5, зависит от того, какие еще
задачи обслуживает ваш сервер. Для получения более подробной информации,
посмотрите статью Microsoft:
http://support.microsoft.com/support/kb/articles/Q110/9/83.asp
которая содержит примеры, рекомендации и параметры настройки памяти.
Dbcc bufcount(1) пользователи SQL 7 могут пропустить эту команду, поскольку
конфигурирование у них автоматическое. Для пользователей SQL 6.5 особый
интерес представляет выводимая в отчёте строка, подобная "The Average Chain
Size is: 2.922601".
Это означает, что эффективность структуры индексации кэша - оценивается
между 2 и 4. Величина близкая к 3, является оптимумом в SQL 6.5. Помочь в регулировке
этого значения может - "sp_configure hash buckets", которая плохо
документирована в документации по SQL 6.5. Как правило, у многих DBA сохраняется
настройка по умолчанию. Дополнительную информацию относительно
этой установки смотрите на узле поддержки Microsoft:
http://support.microsoft.com/support/kb/articles/Q151/2/56.asp
Dbcc memusage детализирует информацию о Ваших больших таблицах, которые
находятся в настоящее время в кэше. Идеально они должны находиться в кэше
как можно дольше, так что желательно, при каждом запуске скрипта, помечать
себе какие объекты находятся в кэше, и какое пространство они там занимают.
SQL 6.5 детализирует также информацию о самых больших хранимых процедурах,
находящихся в Вашем кэше. Более подробно о процедурах - ниже по тексту.
Предупреждение для пользователей SQL 7!
Статья Microsoft:
http://support.microsoft.com/support/kb/articles/Q196/6/29.ASP
не рекомендует Вам выполнять команду dbcc memusage на SQL 7. Нейл пишет,
что никогда не имел проблем с этой командой, но советует Вам не
использовать её на промышленных серверах.
Dbcc proccache выводит краткую информацию об использования кэша процедур.
В SQL 7 достаточно дескрипторов, характеризующих это, но в SQL 6.5
дополнительная информация, выдаваемая этой командой, будет Вам полезна. Это
происходит потому, что SQL 6.5 по умолчанию распределяет 30 % памяти SQL
сервера для кэша хранимых процедур, в то время как остальная память
отводится данным. В конфигурациях с большой емкостью памяти это может
привести к тому, что для процедур выделяется не оправдано большая часть
памяти, которая фактически не используется, а могла бы быть выделена для
кэширования данных. Для регулировки этого значения можно использовать
- "sp_configure procedure cache".
Помните, что существует опасность для нормальной работы сервера баз данных,
если установлено слишком маленькое значение кэша процедур. Изменяйте это
значение поэтапно и на небольшую величину и никогда его не обнуляйте. Этой
теме посвящена статья Microsoft:
http://support.microsoft.com/support/kb/articles/Q192/9/62.ASP
Причины не эффективного использования кэша
Наиболее очевидной причиной неэффективного использования кэша является
недостаток доступной оперативной памяти или выделение SQL серверу
недостаточного её количества. Нейл рекомендует, прежде чем Вы отправитесь
к поставщикам за дополнительными DIMM модулями, проанализируйте другие
возможные причины не эффективного её использования:
1. Отсутствие индексов может вызывать частое сканирования таблиц, которые
вытесняют другие данные из кэша. В SQL 7 можно наблюдать эти процессы с
помощью SQL Profiler и Index Wizard. Ознакомьтесь с материалами на
http://www.swynk.com/friends/boyle/indextuningwizard.asp ,
чтобы обнаружить вредные сканирования таблицы и улучшать эффективность использования
индексов. К сожалению, пользователи SQL 6.5 должны делает это более сложным
путём.
2. Плохо организованный проект базы данных и не достаточно продуманные
проекты запросов могут приводить к сканированию таблицы без использования
существующих индексов, что становится причиной чрезмерной утилизации
дисковой подсистемы и неэффективному использованию памяти.
3. Смешивание баз данных с OLTP (короткие транзакции) и OLAP (большие
отчёты) на одном сервере баз данных, также можно стать причиной
неэффективного использования кэша и появления других проблем.
Дополнительную информацию на эту тему можно почерпнуть из статьи Microsoft:
http://support.microsoft.com/support/kb/articles/Q110/3/52.asp
Другие возможности распределения памяти
Кэш это один из наиболее важных показателей ОПЕРАТИВНОЙ ПАМЯТИ, но ни в
коем случае ни единственно важный. SQL сервер выделяет память для кэширования только
после того, как выполнены многие другие требования. Из
этого следует, что Вы можете увеличивать или уменьшать количество памяти,
доступной для кэша, регулируя распределение памяти между другими аспектами
сервера, особенно это существенно для версии 6.5, которая не умеет
динамически распределять память, как более поздние версии.
Для примера, посмотрите некоторые параметры настройки, которые Вы можете
изменять:
Sort Pages (только 6.5);
Index Create Memory (только 7);
Locks;
Open Objects;
Tempdb In RAM (только 6.5, и обычно не рекомендуемые Microsoft
http://support.microsoft.com/support/kb/articles/Q115/0/50.asp );
Существуют и другие важные параметры, о которых Вы можете узнать на
http://support.microoft.com/
Предупреждение! Неправильная установка для любого из перечисленных
параметров может привести к "краху" Вашего сервера! Поэтому, тщательно
изучите рекомендации Microsoft по установке и изменению конфигурационных
значений и меняйте их постепенно на небольшую величину, анализируя
поведение сервера с помощью специализированных утилит или по результатам
работы соответствующих процедур.
КОМЕНТАРИЙ АВТОРА ПЕРЕВОДА:
От себя к этому добавлю, что нельзя забывать также о том, что сервер баз
данных находится в плотном взаимодействии с операционной системой и другим
программным и аппаратным обеспечением. Поэтому проблемы кэширования и распределения
памяти нужно анализировать комплексно, особенно тщательно
следя за разделяемыми между сервером, ОС и другими программами ресурсами.
