SQL в вопросах и ответах: Управляйте своими данными

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

Пол С. Рэндал

Числа не лгут

Вопрос Я пытаюсь измерить задержку ввода-вывода для некоторых наших баз данных и получаю разные результаты из Dynamic Management View (динамическое представление управления, DMV) и по счетчикам Performance Monitor. Не могли бы объяснить, почему эти числа не совпадают?

Ответ В этих двух методах используется DMV sys.dm_io_virtual_file_stats (см. сообщение в моем блоге, где об этом рассказывается подробнее) и счетчики Avg. Disk sec/Read и Avg. Disk sec/Write в объекте Physical Disk в Performance Monitor. Вполне возможно, что эти два механизма дают разные результаты.

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

Счетчики Performance Monitor вычисляют среднее значение за более короткий период времени. В блогах группы Windows Server Core имеется два отличных сообщения - Windows Performance Monitor Disk Counters Explained и Measuring Disk Latency with Windows Performance Monitor (Perfmon), в которых это объясняется подробнее. Вы получаете данные о задержках при чтении и записи, которые относятся к текущему моменту. Как видите, эти два метода измерения задержки сильно отличаются, поэтому и результаты могут быть разными.

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

По тем же самым причинам в какой-то момент времени производительность тома может быть низкой, но SQL Server может не осуществлять ввод-вывод в этот момент. Счетчики Performance Monitor покажут низкую производительность. Но, поскольку DMV измеряет ввод-вывод только SQL Server, низкая производительность в этот период не повлияет на результаты, получаемые из DMV.

Кроме того, надо помнить, что DMV собирает агрегированные данные. Если был период, когда производительность была низкой, и в течение этого периода осуществлялся только ввод-вывод SQL Server, то низкая производительность ввода-вывода повлияет на результаты, получаемые из DMV, даже после того как производительность снова вырастет. Счетчики Performance Monitor отразят тот факт, что в период низкой производительности задержки были высокими, а затем, когда производительность вырастет, покажут низкие задержки.

Как видите, чтобы понять, почему значения различны, нужно посмотреть, что на самом деле измеряется. К сожалению, счетчики DMV нельзя сбросить без перевода требуемой базы данных в офлайновый режим на какой-то момент времени.

HA для всех

Вопрос Меня попросили разработать новую стратегию обеспечения высокой доступности (high availability, HA) для наших SQL Server. Мне хотелось бы найти рекомендации относительно того, что принять во внимание и с чего начать. Не могли бы вы что-нибудь посоветовать?

Ответ Одна из проблем, возникающих при проектировании стратегии, - выяснить, какую HA-технологию использовать. Часто бывает, что компания выбирает существующую технологию просто потому, что эта технология уже имеется. Хуже того, компания может выбрать произвольную технологию из-за отсутствия четких требований.

Чтобы спроектировать правильную стратегию HA, вы должны собрать и проанализировать требования. Без этого важнейшего этапа нельзя надеяться на то, что ваша стратегия будет отвечать бизнес-требованиям. Для каждой порции данных, к которой будет применяться стратегия HA, нужно ответить на следующие вопросы:

1. Насколько важны эти данные в сравнении со всеми остальными? Утверждение, что все данные имеют первостепенное значение и их надо защитить в равной степени, работает в случае небольших количеств данных, но такой подход неосуществим, если у вас терабайты данных, распределенные по нескольким экземплярам SQL Server.
2. Какое количество данных предприятие может позволить себе потерять? По понятным причинам владельцы бизнеса могут желать, чтобы потери данных были нулевыми.
3. Как долго данные могут оставаться недоступными? Владельцам бизнеса хотелось бы, чтобы простои также были нулевыми. Хотя к этому можно приблизиться, это, к сожалению, не осуществимо на практике.
4. Изменятся ли ответы на предыдущие два вопроса, если брать различные времена дня или дни недели? Это может существенно повлиять на выполнимость требований. Гораздо больше шансов добиться нулевых простоев и потерь данных в течение ограниченного периода времени, например, с 9 до 17 часов по будням, чем в режиме 24х365.
5. Допустимо ли пожертвовать производительностью, чтобы обеспечить доступность данных и надежность? Единственные технологии, которые могут обеспечить нулевые потери данных, требуют синхронное зеркалирование записей журнала транзакций (зеркалирование баз данных или группы доступности SQL Server 2012) или операций записи, выполняемых подсистемой ввода-вывода (репликация в SAN). Оба подхода приводят к задержкам в обработке, но это плата за надежность.
6. Должна ли вторая копия данных быть доступной для записи или только для чтения?

Как только вы получили эти требования, можно проработать ограничения, устанавливаемые предприятием, а затем найти компромисс. Важно осознавать, что ограничения, с которыми вы имеете дело, могут означать, что вы не сможете выполнить все требования. В этом случае вы и бизнес-менеджеры должны придти к компромиссу. В противном случае любая разработанная вами стратегия HA не оправдает ожиданий. Более того, скорее всего, ваше решение будет состоять из нескольких технологий и для успеха крайне важно знать ограничения каждой из них, а также то, как они взаимодействуют.

Вы можете столкнуться со следующими ограничениями:

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

Подробные описания технологий и примеров стратегий приведены в следующих двух статьях:

• High Availability with SQL Server 2008
• Proven SQL Server Architectures for High Availability and Disaster Recovery

Хот эти статьи написаны для SQL Server 2008/2008 R2, они по-прежнему актуальны. И еще ознакомьтесь со статьей "AlwaysOn Architecture Guide: Building a High Availability and Disaster Recovery Solution by Using AlwaysOn Availability Groups", в которой рассказывается о новейших технологиях SQL Server 2012.

Работаем с группами

Вопрос Коллеги по работе сказали мне, что в новых базах данных следует использовать файловые группы вместо одного файла данных. Не могли бы вы объяснить преимущества и недостатки такого подхода?

Ответ Я не могу придумать, какие "темные стороны" могут быть у файловых групп, если вы не зайдете слишком далеко и не будете создавать их сотнями. Что касается преимуществ, файловые группы становятся необходимыми, когда ваша база данных растет (становится больше 50 ГБ - 100 ГБ). И все-таки имеется три основных причины создания файловых групп.

Файловые группы позволяют осуществить быстрое целенаправленное восстановление в случае аварии. Представьте, что у вас база данных размером 1 ТБ, большую часть пространства которой занимает таблица с данными о продажах, начиная с 2009 года и по сей день. Если база данных погибнет при аварии, то какую минимальную порцию данных вы сможете восстановить? Если у вас все хранится в одной файловой группе, у вас нет выбора - придется полностью восстанавливать 1 ТБ, в том числе все старые данные.

Лучший подход к аварийному восстановлению - использовать несколько файловых групп: основную и за  2009, 2010, 2011, 2012 и 2013. В случае аварии, вам будет нужно как можно быстрее перевести в онлайновый режим данные за 2013. Это данные, необходимые для работы OLTP-системы (Online Transaction Processing, онлайновая обработка транзакций), управляющей продажами. Если у вас SQL Server в редакции Enterprise, вы можете обеспечить частичную доступность базы данных. Начните процесс с восстановления файловой группы PRIMARY с использованием синтаксиса WITH PARTIAL. Затем восстановите другие файловые группы, которые вы хотите сразу же перевести в онлайновый режим. Затем можно прекратить процесс восстановления.

Можно восстановить другие файловые группы в онлайновом режиме в удобное для вас время. В данном случае используется еще одна возможность редакции Enterprise - Online Piecemeal Restore (фрагментарное онлайновое восстановление). Это комбинация функций для очень больших баз данных (very large databases, VLDB), которые сокращают время простоя и обеспечивают большую гибкость, когда требуется назначить приоритеты в последовательности восстановления данных при аварии. Кроме того, это означает, что вы можете выполнить фрагментарное восстановление, если по какой-то причине повреждена только часть базы данных. Это еще больше снижает время простоя, необходимое для восстановления в случае аварии.

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

• Partitioned Tables and Indexes in SQL Server 2005
• Partitioned Table and Index Strategies Using SQL Server 2008

Еще одно усовершенствование управления касается фрагментации. Воспользуемся предыдущим примером с таблицей с данными о продажах: если индексы этой таблицы стали фрагментированными и таблица и индексы не секционированы, то команда ALTER INDEX … REBUILD или REORGANIZE будет выполнять дефрагментацию всего индекса. Это будет происходить, даже если старые данные не фрагментированы. Если вы разобьете таблицу на несколько секций и будет хранить каждую секцию в отдельной файловой группе, можно будет дефрагментировать только секции индекса, которые фрагментированы. Это сэкономит много времени и ресурсов.

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

В этом случае можно выделить малоиспользуемые таблицы в одну файловую группу, для которой используется своя собственная часть подсистемы ввода-вывода. Затем поместить каждую интенсивно используемую и обновляемую таблицу в отдельную файловую группу. Каждой из этих файловых групп также сопоставить свою часть подсистемы ввода-вывода. Тогда нагрузки ввода-вывода будут отделены друг от друга и не будут мешать друг другу.

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

Ссылки по теме

• Купить Microsoft SQL Server Business Intelligence Edition 2012 - Лицензия на сервер
• Купить Microsoft SQL Server Standard Edition 2012 - Лицензия на сервер
• Курс "6234 Реализация и администрирование Аналитических Сервисов Microsoft SQL Server 2008"
• Экзамен 070-448: SQL Server 2008, Business Intelligence Development and Maintenance