Организация хранения данных в SQL сервере

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

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

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

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

Если для создания базы дан­ных используется среда SQL Server Management Studio (SSMS), то файлы данных и журналов хра­нятся на том же диске по умол­чанию. Если не указано иное, то файлы данных и журналов создаются в том же каталоге, что и системные базы данных SQL Server, то есть <диск>:\Рrogram Files\Microsoft SQL Server\MSSQL. MSSQLSERVER\MSSQL\DATA. Например, для экземпляра SQL Server 2014, установленного на диске С, файлы данных и журналов по умолчанию будут находить­ся в каталоге C:\Program Files\ Microsoft SQL Server\MSSQL12. MSSQLSERVER\MSSQL\DATA. Рекомендуется поместить файлы данных и журналов на различные диски. SQL Server записывает все транзакции базы данных в журнал транзакций, поэтому файлы жур­налов удобно располагать на дис­ках с высокой скоростью записи. Файлы данных используются для обслуживания запросов и часто должны выполнять множество операций чтения. При создании базы данных можно указать место­положение файлов данных и жур­налов с помощью команды T-SQL CREATE DATABASE. Чтобы изме­нить местонахождение существую­щих файлов данных и журналов, можно запустить команду ALTER DATABASE с параметром MODIFY FILE. В листинге показан пример переноса файла данных базы дан­ных в другое место.n

USE masternnSELECT name, physicaljiame FROM sys.masterjilesnnWHERE database_id = DBJD('AdventureWorks2012'); ALTER DATABASE AdventureWorks2012nnSET offlinennGOnnALTER DATABASE AdventureWorks2012 MODIFY FILE (NAME = AdventureWorks2012_Data, FILENAME "E:\Data\AdventureWorks2012_Data.mdf")nnALTER DATABASE AdventureWorks2012 MODIFY FILE (NAME = AdventureWorks2012_Log, FILENAME = "F:\Logs\AdventureWorks2012_log.ldf")nnALTER DATABASE AdventureWorks2012nnSET online GO

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

ALTER DATABASE AdventureWorks2012 MODIFY FILE (NAME = 'AdventureWorks2012_data'.nnFILEGROWTH = 1024MB) GOnnALTER DATABASE AdventureWorks2012 MODIFY FILE (NAME = 'AdventureWorks 2012Jog',nnFILEGROWTH = 256MB) GO

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

ALTER DATABASE AdventureWorks2012 SET AUT0_SHRINK OFF

Еще один полезный прием при работе с хранилищами дан­ных — немедленная инициализа­ция файлов Instant File Initiation.n

SELECT name AS FileName, size*1.0/128 AS FileSizeinMB,nnCASE max_sizennWHEN 0 THEN 'Autogrowth is off.'nnWHEN -1 THEN 'Autogrowth is on.'nnELSE 'Log file will grow to a maximum size of 2 ТВ.'nnEND,nngrowth AS 'GrowthValue', 'Growthlncrement' =nnCASEnnWHEN growth = 0 THEN 'Size is fixed and will not grow.'nnWHEN growth > 0 AND is_percent_growth = 0nnTHEN 'Growth value is in 8-KB pages.'nnELSE 'Growth value is a percentage.'nnENDnnFROM tempdb.sys.database_files;nnGO

В отличие от большинства рас­смотренных в статье параметров, 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, как показано на экране. В результате открывается диало­говое окно свойств Properties для Performance volume maintenance tasks («Выполнение задач по обслу­живанию томов»), в котором можно ввести имя учетной записи SQL Server Service.n

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

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

• 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.nnКак правило, 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 возвра­щается к последнему заданному значению.nnРазмер и перемещения файлов данных и журналов tempdb можно определять с помощью программ­ного кода, приведенного в разде­ле «Данные и файлы журналов». Запрос в листинге 2 (с сайта MSDN) показывает, как определить размер и процент роста файлов данных и журналов tempdb.n

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

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

• 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 выходит за рамки данной статьи, но ниже приводится обзор важней­ших измеряемых показателей хра­нилищ данных.nnПервая группа счетчиков произ­водительности, которые необхо­димо отслеживать, представляет собой счетчики, относящиеся к памяти в системном мониторе Windows. Технически это не счет­чики хранилища данных, но если памяти недостаточно, то осталь­ные счетчики не имеют значе­ния. Обязательно отслеживайте счетчик Available MBytes объекта Memory. Этот счетчик показы­вает объем физической памяти, доступной для выделения процес­су или системе.nnЕсли показатель меньше 100 Мбайт, то полезно увеличить размер памяти. Другой важный счетчик — % Usage объ­екта Paging File, который показы­вает используемый объем файла подкачки Windows. Это значение должно быть менее 70%. Если зна­чение выше, то, вероятно, системе требуется больше памяти.nnПомимо счетчиков, связан­ных с памятью Windows, имеет­ся несколько счетчиков произво­дительности хранилища Windows Server. Однако показания этих счетчиков полезны лишь в том случае, если экземпляр SQL Server работает с системой хранения дан­ных с прямым подключением DAS. Если используется SAN, то нужно обращать внимание на метрики производительности SAN. Если экземпляр SQL Server исполь­зует DAS, то в первую очередь убедитесь, что на каждом диске NTFS свободно по крайней мере 20% пространства. Впоследствии можно проверить счетчики хра­нилища Windows Server с помощью системного монитора. В табли­це 1 приведен список нескольких наиболее важных счетчиков; все они связаны с объектом Logical Disk.nnSQL Server располагает много­численными счетчиками произ­водительности, которые помога­ют управлять экземпляром SQL Server. Некоторые наиболее важные счетчики хранилища SQL Server, показания которых полезно отсле­живать, перечислены в таблице 2. Следить за ними можно с помощью системного монитора.n

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

Хранилище — высококритич­ный компонент в производитель­ности базы данных SQL Server. Знание некоторых простых при­емов поможет оптимизировать доступность и производительность SQL Server.nnИсточник: Windows IT Pro, №5 май 2014