Оптимальная организация хранения данных в SQL Server

Обеспечиваем доступность и производительность хранилищ данных

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

Данные и файлы журналов

Базовый принцип, который лежит в основе работы SQL Server с хранилищами данных, заключается в том, что базы данных состоят из файлов двух типов:

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

Если для создания базы данных используется среда SQL Server Management Studio (SSMS), то файлы данных и журналов хранятся на том же диске по умолчанию. Если не указано иное, то файлы данных и журналов создаются в том же каталоге, что и системные базы данных SQL Server, то есть <диск>:\Program Files\Microsoft SQL Server\MSSQL.MSSQLSERVER\MSSQL\DATA. Например, для экземпляра SQL Server 2014, установленного на диске C, файлы данных и журналов по умолчанию будут находиться в каталоге C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA.

Рекомендуется поместить файлы данных и журналов на различные диски. SQL Server записывает все транзакции базы данных в журнал транзакций, поэтому файлы журналов удобно располагать на дисках с высокой скоростью записи. Файлы данных используются для обслуживания запросов и часто должны выполнять множество операций чтения. При создании базы данных можно указать местоположение файлов данных и журналов с помощью команды T-SQL CREATE DATABASE. Чтобы изменить местонахождение существующих файлов данных и журналов, можно запустить команду ALTER DATABASE с параметром MODIFY FILE. В листинге 1 показан пример переноса файла данных базы данных в другое место.

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

И все же AutoGrow следует рассматривать как механизм последнего рубежа защиты. Его не следует использовать в качестве основного метода управления ростом базы данных. Ростом всех файлов данных и журналов следует управлять вручную. Активность базы данных прекращается, когда происходят операции AutoGrow. Частые события AutoGrow — хороший индикатор непредвиденного роста данных. Следующая команда показывает, как установить настройку AutoGrow для файлов данных и журналов:

ALTER DATABASE AdventureWorks2012
MODIFY FILE (NAME = 'AdventureWorks2012_data',
FILEGROWTH = 1024MB)
GO
ALTER DATABASE AdventureWorks2012
MODIFY FILE (NAME = 'AdventureWorks2012_log',
FILEGROWTH = 256MB)
GO

Почти никогда не рекомендуется активировать функцию AutoShrink для базы данных. Как и операции AutoGrow, операции AutoShrink приводят к остановке всех действий базы данных. Кроме того, администратор не может контролировать время запуска AutoShrink. Использование AutoShrink может привести к спирали операций AutoGrow, а затем AutoShrink, а результатом будет снижение производительности базы данных и чрезмерная фрагментация файлов. Запустить AutoShrink можно с помощью команды:

ALTER DATABASE AdventureWorks2012 SET AUTO_SHRINK OFF

Еще один полезный прием при работе с хранилищами данных — немедленная инициализация файлов Instant File Initiation. В отличие от большинства рассмотренных в статье параметров, Instant File Initialization управляется политикой Windows Server. Instant File Initialization не обнуляет выделенное пространство для файла, а просто выделяет нужное количество места. SQL Server использует Instant File Initialization во время создания базы данных, AutoGrow и операции восстановления базы данных. Можно включить режим Instant File Initialization на сервере через меню Administrative, чтобы открыть Local Security Policy («Локальная политика безопасности»). Затем разверните Local Policies («Локальные политики») и дважды щелкните на пункте Performance volume maintenance tasks, как показано на экране.

Экран. Включение Instant File Initialization

В результате открывается диалоговое окно свойств Properties для Performance volume maintenance tasks («Выполнение задач по обслуживанию томов»), в котором можно ввести имя учетной записи SQL Server Service.

Хранение данных и уровни RAID

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

• RAID 0 (иногда именуется чередованием дисков). На этом уровне RAID данные распределяются между всеми доступными дисками. Он часто используется в различных тестах производительности баз данных. RAID 0 обеспечивает хорошую производительность, но его никогда не следует применять на производственном сервере, так как отказ одного диска приводит к потере данных.
• RAID 1 (иногда именуется зеркальным отображением дисков). В конфигурации RAID 1 данные отображаются на дисках зеркально. Скорость операций чтения и записи хорошая, но общая емкость дисков уменьшается вдвое. RAID 1 часто используется для файлов журналов SQL Server. В случае отказа одного диска данные не теряются.
• RAID 5 (иногда именуется чередованием дисков с контролем четности). В конфигурации RAID 5 данные распределяются по нескольким дискам с целью обеспечить избыточность данных. Часто используется для файлов данных. Этот уровень RAID обеспечивает хорошую производительность чтения и устойчив к отказу одного диска. Однако скорость записи невелика.
• RAID 10 (иногда именуется зеркальным отображением дисков с чередованием). RAID 10 сочетает в себе быстродействие вариантов с чередованием и защиту через зеркальное отображение. RAID 10 обеспечивает самые высокие уровни производительности и доступности среди всех уровней RAID. Для RAID 10 требуется вдвое больше дисков, чем для RAID 5, но конфигурация устойчива к отказу нескольких дисков. Массив RAID 10 продолжает успешно функционировать при отказе половины дисков в наборе. RAID 10 подходит как для файлов данных, так и для журналов.

Tempdb

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

Как правило, tempdb — одна из самых активных баз данных в рабочем экземпляре SQL Server, поэтому следующие рекомендации помогут обеспечить хорошую производительность базы данных SQL Server. Прежде всего, файлы данных и журналов tempdb следует разместить на других физических дисках, нежели файлы журналов и данных рабочей базы данных. По причине очень активного использования tempdb полезно защитить диски, организовав массив RAID 1 или массив RAID 10 с чередованием. Специалисты группы Microsoft SQL Server Customer Advisory Team (SQLCAT) рекомендуют, чтобы в tempdb был один файл данных для каждого ядра процессора. Но эта рекомендация эффективна для очень высоких рабочих нагрузок. Чаще рекомендуется, чтобы отношение файлов данных к ядрам процессора составляло 1:2 или 1:4. Как и в большинстве случаев, это общие рекомендации; оптимальные подходы для конкретной системы могут различаться. Если вы не знаете точно, сколько файлов использовать для tempdb, можно начать с четырех файлов данных. Обычно для tempdb достаточно одного файла журнала. Более подробные рекомендации tempdb вы найдете в материалах, перечисленных во врезке «Учебная литература».

Кроме того, размер tempdb должен быть достаточным, чтобы избежать операций AutoGrow. Как и пользовательские базы данных, tempdb будет испытывать задержки из-за операций AutoGrow. По умолчанию tempdb содержит файл данных в 8 Мбайт, файл журналов в 1 Мбайт и 10% пространства для AutoGrow, а это слишком мало для большинства производственных рабочих нагрузок. Также важно помнить, что при перезапуске SQL Server размер tempdb возвращается к последнему заданному значению.

Размер и перемещения файлов данных и журналов tempdb можно определять с помощью программного кода, приведенного в разделе «Данные и файлы журналов». Запрос в листинге 2 (с сайта MSDN) показывает, как определить размер и процент роста файлов данных и журналов tempdb.

Твердотельные диски

Благодаря нескольким ядрам увеличилась вычислительная мощь, и многие современные системы поддерживают очень большой объем оперативной памяти, из-за чего подсистема ввода-вывода стала узким местом для многих рабочих нагрузок. Традиционные жесткие диски стали более емкими, но быстродействие практически не увеличилось. Проблему можно решить с помощью твердотельных дисков (SSD). Твердотельные диски — сравнительно новая технология хранения данных, которая начала набирать вес на рынке SQL Server в течение последнего года. В прошлом цена устройств SSD была слишком велика, а информационная емкость слишком мала для многих рабочих баз данных. Одна из причин растущей популярности твердотельных дисков — преимущество в производительности перед традиционными жесткими дисками с вращающимся шпинделем. Например, диск Serial Attached SCSI (SAS) с частотой вращения шпинделя 15 000 об/мин может обеспечить пропускную способность 200 Мбайт/с. Для сравнения, SSD-диск Serial ATA (SATA) с 6-Гбайт соединением может обеспечить последовательную пропускную способность около 550 Мбайт/с. Основная причина превосходства SSD-дисков в быстродействии заключается в резком сокращении времени поиска. Когда нужно получить данные с вращающегося жесткого диска, головка должна переместиться в новое место. У SSD-диска нет движущихся частей, поэтому перемещение к новому месту хранения данных определяется быстродействием ячеек памяти.

Твердотельные и быстродействующие флэш-хранилища можно реализовать несколькими способами. Типичное применение — 2,5-дюймовые диски SSD. Эти устройства подключаются напрямую, как хранилища типа DAS, а электронный интерфейс — такой же, как у стандартного жесткого диска. Другая распространенная реализация SSD — в виде плат PCI Express (PCIe), подключаемых непосредственно к системной шине. В этом подходе используются преимущества быстродействующей шины PCIe, чтобы повысить число операций ввода-вывода в секунду (IOPS) и пропускную способность по сравнению со стандартным интерфейсом диска. Кроме того, многие хранилища SAN располагают разделами SSD и функцией автоматического распределения данных по разделам, что позволяет переместить важные рабочие нагрузки на высокопроизводительный раздел SSD, оставляя менее важные рабочие нагрузки на медленных и менее дорогостоящих жестких дисках.

Существуют хранилища SSD различных типов. Среди них — хранилище SSD на основе DRAM и хранилище SSD на основе технологии флэш-памяти, такой как одноуровневые ячейки (SLC) и многоуровневые ячейки (MLC). У каждого типа есть свои достоинства и недостатки.

• DRAM. Как обычная оперативная память для компьютера, DRAM отличается очень высоким быстродействием, но ненадежна. Для DRAM требуется постоянный элемент питания, чтобы сохранить данные на время отключения данных. Такие хранилища часто выпускаются в виде плат PCIe, устанавливаемых на системной плате сервера.
• SLC. Быстродействие и жизненный цикл хранилищ на SLC выше, чем у MLC, поэтому SLC используется в хранилищах SSD корпоративного уровня. Однако цена устройств SLC существенно выше, чем у MLC.
• MLC. Обычно флэш-память типа MLC используется в потребительских устройствах и обходится дешевле, чем SLC. Однако у MLC более низкая скорость операций записи и существенно более высокий износ, чем у SLC.

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

Если нужно использовать диски SSD, не применяйте единственный накопитель SSD и приготовьтесь заменять диски SSD в течение срока эксплуатации сервера. Перечислим возможности применения SSD в SQL Server.

• Перемещение индексов на диски SSD. Как правило, индексы не очень велики и связаны с интенсивными беспорядочными операциями чтения, поэтому идеально подходят для размещения на дисках SSD.
• Перемещение файлов данных на диски SSD. С файлами данных обычно связано больше операций чтения, чем записи, поэтому в большинстве случаев они подходят для дисков SSD.
• Перемещение файлов журналов на диски SSD. Файлы журналов связаны с большим числом операций записи. Поэтому если для файлов журналов применяются диски SSD, используйте диски SSD корпоративного уровня и конфигурации RAID 1 или RAID 10 с зеркальным отображением.
• Перемещение tempdb на SSD-диск. Как правило, tempdb отличается высоким уровнем неупорядоченных операций записи, что может привести к порче SSD. Поэтому если диски SSD используются для tempdb, то это должны быть SSD корпоративного уровня в конфигурации RAID 1 или RAID 10 с зеркальным отображением, и нужен план замены дисков SSD. Кроме того, обратите внимание на вариант с PCIe DRAM для tempdb. Хранилище DRAM обеспечивает более высокое быстродействие при записи и имеет неограниченный срок эксплуатации. Однако цены хранилищ DRAM могут быть высокими.

Базовые уровни производительности

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

Первая группа счетчиков производительности, которые необходимо отслеживать, представляет собой счетчики, относящиеся к памяти в системном мониторе Windows. Технически это не счетчики хранилища данных, но если памяти недостаточно, то остальные счетчики не имеют значения. Обязательно отслеживайте счетчик Available MBytes объекта Memory. Этот счетчик показывает объем физической памяти, доступной для выделения процессу или системе. Если показатель меньше 100 Мбайт, то полезно увеличить размер памяти. Другой важный счетчик — % Usage объекта Paging File, который показывает используемый объем файла подкачки Windows. Это значение должно быть менее 70%. Если значение выше, то, вероятно, системе требуется больше памяти.

Помимо счетчиков, связанных с памятью Windows, имеется несколько счетчиков производительности хранилища Windows Server. Однако показания этих счетчиков полезны лишь в том случае, если экземпляр SQL Server работает с системой хранения данных с прямым подключением DAS. Если используется SAN, то нужно обращать внимание на метрики производительности SAN.

Если экземпляр SQL Server использует DAS, то в первую очередь убедитесь, что на каждом диске NTFS свободно по крайней мере 20% пространства. Впоследствии можно проверить счетчики хранилища Windows Server с помощью системного монитора. В таблице 1 приведен список нескольких наиболее важных счетчиков; все они связаны с объектом Logical Disk.

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

Сохраняем и движемся вперед

Хранилище — высококритичный компонент в производительности базы данных SQL Server. Знание некоторых простых приемов поможет оптимизировать доступность и производительность SQL Server. Более подробные сведения об особенностях хранения данных можно найти в материалах, перечисленных во врезке «Учебная литература».

USE master
SELECT name, physical_name
FROM sys.master_files
WHERE database_id = DB_ID('AdventureWorks2012');
ALTER DATABASE AdventureWorks2012
SET offline
GO
ALTER DATABASE AdventureWorks2012
MODIFY FILE (NAME = AdventureWorks2012_Data, FILENAME =
«E:\Data\AdventureWorks2012_Data.mdf»)
ALTER DATABASE AdventureWorks2012
MODIFY FILE (NAME = AdventureWorks2012_Log, FILENAME =
«F:\Logs\AdventureWorks2012_log.ldf»)
ALTER DATABASE AdventureWorks2012
SET online
GO

SELECT
name AS FileName,
size*1.0/128 AS FileSizeinMB,
CASE max_size
WHEN 0 THEN 'Autogrowth is off.'
WHEN -1 THEN 'Autogrowth is on.'
ELSE 'Log file will grow to a maximum size of 2 TB.'
END,
growth AS 'GrowthValue',
'GrowthIncrement' =
CASE
WHEN growth = 0 THEN 'Size is fixed and will not grow.'
WHEN growth > 0 AND is_percent_growth = 0
THEN 'Growth value is in 8-KB pages.'
ELSE 'Growth value is a percentage.'
END
FROM tempdb.sys.database_files;
GO