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

1. Отключите параметр AUTO_SHRINK и установите параметр AUTO_GROW

Настройка AUTO_SHRINK предписывает базе данных автоматически уменьшаться в объеме. Казалось бы, идея правильная, но в большинстве случаев это не так. Чаще всего возникает следующая ситуация. Поскольку базы данных имеют обыкновение увеличиваться в объеме спорадически, использование команды AUTO_SHRINK приводит к сокращению объема базы данных, когда определенное количество данных стерто. В результате в процессе выполнения операции сокращения объема содержимое базы данных становится временно недоступным. В дальнейшем, если вновь будет возникать необходимость в увеличении объема базы данных с последующим сокращением, может образоваться порочный круг «рост-сокращение», что отрицательно скажется на производительности и приведет к фрагментированию файлов и индексов. В наше время лучше использовать другое решение: обзаведитесь дополнительным хранилищем и отключите функцию AUTO_SHRINK. Некоторые специалисты рекомендуют отключать параметр AUTO_GROW, но я полагаю, что лучше оставить его активированным из соображений безопасности. Если вы будете использовать это средство, лучше всего установить для него значение Megabytes option for File Growth; при этом значение должно быть достаточно большим для того, чтобы базе данных не приходилось увеличиваться в объеме слишком часто. С другой стороны, функция AUTO_GROW не должна заменять собою средства управления размером баз данных. Рекомендую использовать ALTER DATABASE MYDB SET AUTO_SHRINK OFF, а также ALTER DATABASE MyDB MODIFY FILE (NAME=MyDB_data, FILEGROWTH=1024 MB).

2. Отключите настройку AUTO_CLOSE

Настройка AUTO_CLOSE обеспечивает закрытие баз данных SQL Server и высвобождение ресурсов в тот момент, когда работу с базой данных завершает последний пользователь. В какой-то мере это правильное решение, однако дело в том, что в такой ситуации последующий запуск базы данных при обращении к ней пользователей влечет за собой значительные дополнительные расходы. Настройка AUTO_CLOSE не устанавливается по умолчанию, но она может быть артефактом для обновления с уровня SQL Server Express. Возможно также, что какой-либо другой администратор указал ее по ошибке. Используйте следующую настройку: ALTER DATABASE MyDB SET AUTO_CLOSE OFF.

3. Установите настройку AUTO_CREATE_ STATISTICS & AUTO_UPDATE_STATISTICS

Для выбора оптимального плана выполнения запроса оптимизатор запросов Query Optimizer использует статистические гистограммы. Для индексированных столбцов статистические показатели создаются автоматически, но, если вы хотите, чтобы SQL Server получал такие показатели и для других столбцов, следует установить настройку AUTO_CREATE_ STATISTICS. Планы использования оптимизатора запросов могут изменяться по мере того, как создаются и обновляются строки таблицы. При использовании настройки AUTO_UPDATE_STATISTICS оптимизатор запросов обновляет статистические данные перед выполнением компиляции запроса. Используйте настройки: ALTER DATABASE MyDB SET AUTO_CREATE_STATISTICS ON и ALTER DATABASE MyDB SET AUTO_UPDATE_STATISTICS ON.

4. Устанавливайте минимальные и максимальные значения для памяти SQL Server

Эти значения определяют верхнюю и нижнюю границы объемов памяти, используемой буферным пулом. Минимальное значение памяти позволяет сделать так, чтобы SQL Server не выделял объем памяти ниже установленного значения по достижении указанного порога. Ценность данного значения очевидна в случаях, когда SQL Server выполняется наряду с другими приложениями или в виртуальной машине с активированной динамической памятью. Максимальное значение серверной памяти отражает максимальный объем памяти, который SQL Server может выделять при запуске и выполнении. Задаваемые здесь значения определяются объемом памяти в главной системе. Рекомендуется ограничивать максимальный объем серверной памяти SQL Server значением, не достигающим уровня общего объема памяти главной системы. Задействуйте настройку sp_configure ‘max server memory’, 4096.

5. Используйте настройку Optimize for Ad Hoc Workloads

Эта настройка служит для оптимизации объемов памяти, применяемых однопользовательскими и созданными для конкретного случая планами запросов в кэше процедур. Она позволяет системе SQL Server при первом запуске хранимого в кэше процедур нерегламентированного плана запросов содержать в памяти только небольшую заглушку для этого плана. Таким образом объем памяти, необходимый для работы с кэшем процедур, сокращается. Используйте настройку sp_configure ‘optimize for ad hoc workloads’,1.

6. Разделяйте файлы данных и файлы журналов

Кому-то такая рекомендация, возможно, покажется излишней. Но мне так часто приходилось сталкиваться с системами, на которых файлы данных и журнальные файлы размещаются на одном и том же диске, что многим в это трудно поверить. Данная ситуация особенно часто встречается в виртуальных системах SQL Server. В сущности, такая организация подходит лишь для очень небольших установок — во всех прочих случаях быстро начинается конкурентная борьба за каналы ввода-вывода. Физические системы должны «привязываться» к различным накопителям. Виртуальные системы должны обеспечивать размещение файлов данных и журнальных файлов на виртуальных жестких дисках, причем каждый виртуальный диск обслуживается отдельными физическими накопителями.

7. Отделяйте файлы TEMPDB от других файлов данных

Файлы TEMPDB используются для размещения глобальных или локальных таблиц временного хранения, временных хранимых процедур, табличных переменных, а также внутренних объектов, созданных средствами процессора базы данных. Известно, что файлы данных рекомендуется хранить отдельно от журнальных файлов. Подобным же образом необходимо отделить испытывающую большую нагрузку из-за операций записи базу данных TEMPDB от производственных баз данных и журнальных файлов. В физических системах необходимо указывать особый накопитель для TEMPDB. В виртуальных системах базу данных TEMPDB следует размещать на отдельном виртуальном жестком диске, который обслуживается отдельным физическим накопителем, где не размещаются ни файлы данных, ни журнальные файлы.

Работа с унаследованным экземпляром SQL Server

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

  • Проверьте, сделаны ли резервные копии. Способность восстановить данные — самая важная обязанность администратора базы данных, и первый шаг к этому — резервное копирование баз данных.
  • Проверьте модель восстановления. Большинство производственных баз данных должны быть настроены на использование полной модели восстановления, чтобы обеспечить восстановление данных на определенный момент времени. Необходимо проверить модель для понимания возможностей восстановления.
  • Контролируйте дисковое пространство. При переходе на новую систему стоит убедиться, что на диске достаточно места для экземпляра SQL Server.
  • Редакция SQL Server и пакет обновления. Для понимания возможностей и текущего состояния системы необходимо знать редакцию SQL Server и пакет обновления.
  • Найдите файлы базы данных (.mdf) и файлы журналов (.ldf). В большинстве систем следует размещать эти файлы на различных накопителях или в пулах хранения.
  • Найдите tempdb. Для интенсивно используемых систем tempdb также следует разместить на отдельном накопителе или в пуле хранения.
  • Проверьте параметры AutoGrow и AutoShrink. Как правило, AutoShrink следует отключать, но рекомендуется назначить определенный размер AutoGrow, чтобы уменьшить возможность разрастания базы данных.
  • Автоматически создавайте статистические данные и автоматически же обновляйте статистику. Чтобы помочь SQL Server оптимизировать запросы, обычно предпочтительно включить оба параметра.

Достижение целевых уровней производительности SQL Server

Производительность всегда была одной из основных характеристик, на которую обращали внимание администраторы баз данных, в основном из-за ее важности для пользователей. Если сервер работает медленно, об этом всегда становится известно администратору. Поддержание производительности на нужном уровне — необходимое условие эффективности работы сотрудников компании. Однако обеспечение успешного функционирования экземпляров SQL Server — не та задача, которую можно решить раз и навсегда. Базы данных постоянно растут. Рабочие нагрузки пользователей не всегда прогнозируемы и меняются со временем. Постоянно изменяются требования бизнеса и информационные условия. Кроме того, особенности доступа приложения к данным могут меняться при каждом обновлении приложения. Все эти факторы приводят к подвижности характеристик производительности SQL Server.

Важность эталонных тестов

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

Ключевые показатели производительности

Основной инструмент эталонного тестирования — системный монитор perfmon.exe. С его помощью можно отслеживать характеристики производительности как Windows Server, так и SQL Server.

Существует множество различных счетчиков производительности как для Windows Server, так и для SQL Server. Перечислим ключевые показатели производительности, которые полезно отслеживать для Windows Server.

  • Процессор: %Processor Time — отслеживает время использования процессора сервера.
  • Available Mbytes — отслеживает доступную память.
  • Сетевой интерфейс: Bytes Total/sec — отслеживает общее использование сети.

И некоторые ключевые показатели производительности, которые полезно отслеживать для SQL Server:

  • SQLServer: SQL Statistics: Batch Requests/sec — отслеживает, насколько занята система;
  • SQLServer: Buffer Manager: Buffer Cache Hit Ratio — отслеживает процент запросов, обслуживаемых кэшем буферного пула;
  • SQLServer: Access Methods: Full Scans/sec — отслеживает число выполненных полных просмотров таблиц.