Советы по настройке компонента базы данных MS SQL Server Engine

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

Привилегия обслуживания томов

Возможность SQL Server выполнять “мгновенную инициализацию файлов” приведет к существенной экономии времени при создании новых баз данных с большими начальными размерами файлов или при восстановлении баз данных из резервных копий. Короче говоря, это разрешение позволяет SQL Server пропустить обычный процесс “обнуления” дискового пространства, прежде чем будет подготовлен новый файл данных, что делает его более быстрым. Это особенно полезно, когда VLDB требуется восстановить либо в обычном режиме, либо во время процесса аварийного восстановления (DR).

Этот параметр теперь доступен для настройки во время установки, и необходимо просто установить флажок напротив него, как показано ниже:

Конфигурация базы данных TempDB

База данных TempDB является критически важной системной базой данных в SQL Server. Его использование выходит за рамки размещения пользовательских временных таблиц. База данных TempDB используется внутри SQL Server для таких целей, как размещение хэш-таблиц (промежуточных таблиц в соединениях), переменных таблиц, возвращающих табличные значения функций, для создания индексов, сортировок и т. д., Хотя все это в основном прозрачно для пользовательских процессов, плохо настроенная или размерная база данных TempDB может привести к неоптимальному функционированию ядра СУБД и потенциально может вызвать проблемы.

Во время установки есть возможность настроить количество файлов в базе данных TempDB, их начальный размер и коэффициент их роста. Для начала корпорация Майкрософт рекомендует, чтобы число файлов базы данных TempDB было равно числу логических процессоров или восьми (8), в зависимости от того, что меньше.

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

Здесь количество файлов настроено равным 8, равным количеству логических процессоров и с начальным размером файла 1 ГБ с коэффициентом роста 512 МБ. Если конфигурация диска позволяет это, начальный размер также может быть намного больше, что уменьшает потребность в росте файла.

Каталог данных

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

На приведенном выше рисунке диски E, F были назначены для данных и журналов соответственно. Папки “данные SQL” и “журналы SQL” были созданы заранее.

Настройки памяти

Рекомендуется выделять SQL Server всю память сервера, кроме 80%. Однако это может существенно измениться в зависимости от общего объема доступной памяти. Но, в данном примере, мы предполагаем 80% в качестве начального распределения.

Простой трюк для вычисления этого – 0.8*(общая память сервера в ГБ)*1024 = число, которое помещается в максимальную память.

Стандартная рекомендация 80% состоит в том, чтобы обеспечить меньшую конкуренцию памяти и разбиение на страницы, выделив по меньшей мере 20% доступной памяти для ОС, что в основном достаточно для нормальных операций.

Конфигурация модели базы данных

В экземпляре SQL Server создаются серверы баз данных модели в качестве ссылки для новых баз данных. Конфигурация базы данных модели и ее параметры копируются в новые базы данных. Хотя существует несколько параметров базы данных, которые могут быть настроены на модели, и таким образом распространять их на новые базы данных, они в основном зависят от индивидуальных требований среды.

Два параметра, однако, могут применяться к большинству сред, и они являются начальным размером файла и фактором роста. Начальный размер файла гарантирует, что база данных и файлы журналов, созданные для новых баз данных, достаточно велики. Фактор роста позволяет событиям роста быть менее частыми, что-то, что может значительно помочь производительности OLTP.

После установки, получив доступ к свойствам базы данных Model, параметры файла могут быть изменены. В приведенном выше примере для базы данных Model был настроен начальный размер файла 10 ГБ с коэффициентом роста 1 ГБ для файла данных и начальный размер файла 1 ГБ с коэффициентом роста 256 МБ.

Параметры сервера

Существует множество параметров сервера, которые управляют функционированием SQL Server. Вещи от количества используемых параллельных процессоров до управления доступом через xp_cmdshell-это функции, которые можно установить с помощью команды sp_configure с помощью T-SQL.

MAXDOP, Max Server Memory, Min Server Memory – это некоторые функции, которые DBAs настраивают таким образом. Двумя другими наиболее распространенными настройками, которые обычно применяются к большинству серверов, являются “сжатие резервных копий” и “оптимизация для нерегламентированных рабочих нагрузок”.’

Сжатие резервных копий – это очень полезная функция, которая экономит дисковое пространство за счет сжатия файлов резервных копий. Это можно контролировать с помощью планов обслуживания и отдельных команд резервного копирования T-SQL, но оптимально включить их на уровне сервера, чтобы на диске не создавались большие файлы, в таких ситуациях, как выполнение нерегламентированных резервных копий.

Параметр оптимизировать для нерегламентированных рабочих нагрузок позволяет SQL Server уменьшить использование памяти планами запросов, которые не используются повторно. Только планы запросов, которые перезапускаются, получают полное пространство в памяти.

Используя T-SQL ниже, они могут быть включены:

EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO

EXEC sp_configure 'optimize for ad hoc workloads', 1
RECONFIGURE
GO

EXEC sp_configure 'backup compression default', 1
RECONFIGURE
GO

Флаги трассировки взаимоблокировки

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

Флаги трассировки 1204 и 1222 захватывают узел взаимоблокировки и сведения о запросе. Эти флаги трассировки можно включить с помощью команды DBCC TRACEON T-SQL, но лучший способ включить их и, что еще более важно, оставаться включенными после перезагрузки сервера-это добавить их в параметры запуска SQL Server.

Это можно сделать, обратившись к разделу параметры запуска и указав флаги трассировки, как показано ниже, из диспетчера конфигурации SQL Server:

Дополнительные настройки задачи

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

• Задание полного резервного копирования, выполняемое по расписанию с папками, назначенными для каждой базы данных
• Задание разностного резервного копирования, при необходимости с аналогичными характеристиками, указанными выше
• Задание резервного копирования T-Log для покрытия баз данных в полной модели восстановления. Исключения должны быть сделаны для баз данных, которые будут участвовать в доставке журналов
• Задание обслуживания индекса и статистики, которое выполняется не реже одного раза в неделю для удаления фрагментации из индексов и вычисления статистики с подходящим значением процента сканирования, чтобы обеспечить создание оптимальных планов выполнения
• Задание регулярно перерабатывать журнал ошибок, чтобы помочь быстро загрузить его, в моменты непосредственной необходимости устранения неполадок
• Задача очистки обслуживания для периодического удаления истории обслуживания
• Настройка агента SQL Server для хранения достаточного количества данных для анализа исторических заданий

Как уже упоминалось в начале этого блога, это некоторые элементы конфигурации и настройки, соответствующие общим рекомендациям по установке SQL Server. Часто существуют другие элементы, которые должны быть настроены в соответствии со стандартами компании или конкретными требованиями приложений или пользователей. Примерами таких программ могут быть компонент Database Mail, TLS-шифрование соединений с базой данных, изоляция моментальных снимков, MAXDOP и т. д.

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

(2 оценок, среднее: 3,00 из 5)

Загрузка...