Как ограничить дисковую активность ms sql server
Мониторинг эффективности MS SQL Server. Практические рекомендации.
В этой статье собраны несколько материалов, опубликованных в рассылке «MS SQL Server — дело тонкое…» и посвящённых проблемам мониторинга эффективности MS SQL Server, методам выяснения причин возникновения аппаратных проблем и их разрешения. Кроме того, Вашему вниманию будут предложены несколько полезных, практических рекомендаций и предостережений.
К вопросу исследования SQL сервера с помощью Performance Monitor
Процессор: «% Processor Time» — показывает загрузку CPU. В многопроцессорной системе возможна диагностика загрузки, как каждого процессора в отдельности, так и всех вместе. Также, одноимённый параметр можно использовать для определения утилизации процессора каждым потоком. Если «% Processor Time» показывает среднее значение в диапазоне 80 % — 100 %, это говорит о проблеме с производительностью Вашей системы. Необходимо принимать меры по масштабированию системы или изменения конфигурации. В то же время, кратковременное достижение «% Processor Time» — 80 % уровня или даже пики до 100 % ничто опасного или нежелательного не представляют. Поскольку ресурсы процессора использует не только сервер баз данных, Вы должны определить является ли SQL Server причиной высокой утилизации процессорного времени. Используйте SQLServer: CPUtime для определения доли SQL сервера в «% Processor Time». Выяснив, что причина повышенной загрузки процессоров является SQL сервер, а также какой процесс её провоцирует, Вы должны проанализировать проект исполняемого в это время запроса. Убедитесь, что в запросе индексы используются оптимальным образом. Возможны случаи, когда неумело построенный запрос не использует или не оптимально использует существующий индекс.
Запрос может хорошо кэшироваться но, в то же время, перегружать систему ввода — вывода (I/O), что отвлекает большое количество циклов CPU.
Это может говорить о том, что при проектировании таблицы индекс был задуман не оптимально. Если проект запроса оптимален, можно решить проблему за счёт масштабирования, например, добавить процессоры или установить более производительный процессор. Естественно, добавлять процессоры следует только в том случае, если Ваш объe:м ОЗУ достаточно велик, чтобы удовлетворить запросы SQL сервера.
Напротив, если «% Processor Time» постоянно показывает очень низкую утилизацию CPU, это, как правило, говорит о наличии проблем. Вариант, когда сервер чрезвычайно избыточен для решения задач СУБД, в данной статье не рассматривается. Низкая утилизация возможна из-за ограничений в конфигурации SQL сервера (например, используются значения по умолчанию, когда требуется их увеличение) или проблема кроется в Вашем приложении прикладной задачи.
Процессор: «% Privileged Time» — удобен для определения чрезмерной загрузки I/O. Если средне значение превышает 20%, а «% Processor Time»- существенно ниже 80 %, это говорит о том, что SQL Server чересчур сильно утилизирует систему I/O. Вам необходимо проанализировать проект базы данных, загрузку RAID контроллера и сетевой платы. Существенное влияние на «% Privileged Time» могут оказывать и работающие параллельно с SQL сервером процессы или сервисы, в том случае, когда сервер используется не только для обслуживания СУБД. Одним из распространe:нных вариантов решения роблемы высокой утилизации I/O является размещение tempdb в ОЗУ.
Система: «Processor Queue» — предназначен для диагностики очередей процессоров. Если его значение больше чем 2 значит, что CPU работает с перегрузкой. Очевидно, что для решения этой проблемы необходимы дополнительные процессорные мощности.
Система: «Context Switches/sec» — переключение контекста, когда NT или SQL Server переключают обслуживание процессором с одного потока на другой, что вызывает всплеск утилизации CPU. Если при этом «Processor Queue» > 2-х, постарайтесь изменить число потоков, используемых SQL сервером.
Процесс: «Thread Count» — число активных потоков. Значение этого счётчика совместно с «Context Switches/sec» можно использовать для оптимального конфигурирования SQL сервера, чтобы снизить чрезмерную утилизацию CPU.
Процесс: «Virtual Bytes» — позволяет определить, сколько памяти использует SQL сервер и какие приложения используют её недостаточно эффективно; процесс: «Working Set» — объe:м памяти используемый процессом. Изменение конфигурационных настроек SQL сервера после анализа этих счe:тчиков позволит оптимизировать распределение памяти между сервером баз данных, операционной системой и другими приложениями сервера.
SQLServer: «Cache Hit Ratio» — для хорошо сбалансированных приложений число попаданий в кэш должно стремиться к 100%. Часто, достижение высокого уровня попадания в кэш достигают просто увеличением ОЗУ. Боле тонко регулировать кэширование можно контролируя 1081 trace flag, добиваясь, что бы страницы индексов оставались в кэше данных дольше, чем страницы данных.
Настройка конфигурационного параметра SQL Server 7.0: «Max Async I/O»
Как правило, значение по умолчанию параметра Max Async I/O достаточно только для дисковых подсистем нижнего класса. Для более продвинутых RAID — контроллеров с очень высокой пропускной способностью и обслуживающих большое количество дисков этого может оказаться недостаточно или просто возможности системы будут сдерживаться.
Исключение составляет случай использования в качестве операционной системы Windows 95/98, которая просто не поддерживает асинхронный ввод — вывод. Оптимальный выбор значения Max Async I/O позволит серверу полностью отработать Checkpoint, до его следующего цикла и, при этом, не помешает выполнению параллельно исполняемых процессов/потоков.
Microsoft предлагает следующее эмпирическое правило для установки
максимального значения Max Async I/O, если Вы используете RAIDE с большим количеством дисков: «Умножьте число физических дисков, доступных для одновременного ввода/вывода на 2 или на 3, и полученное значение присвойте параметру Max Async I/O».
После этого, наблюдайте средствами Performance monitor или Microsoft Management Console поведением дисковой подсистемы и очередей.
Следите, что бы Checkpoint не монополизировал всю ширину, которая обслуживает дисковую подсистему.
Мониторинг производительности сервера баз данных с помощью SQL Server Profiler
(По материалам статьи Maxim Smirnov на swynk.com «Monitoring Performance With SQL Server Profiler»)
Как пишет Михаил, контроль исполнения хранимых процедур и производительности баз данных может помочь Вам прогнозировать на ранних стадиях появление возможных проблем с утилизацией ресурсов системы.
Обычно, анализируя простые счётчики (например, продолжительность исполнения процедур и количество чтений, выполненных ядром базы данных), можно в достаточной степени идентифицировать процедуры или запросы, которые создают чрезмерную перегрузку. Как правило, это случается из-за возможных алгоритмических ошибок в коде, не продуманности проекта базы данных, неправильной стратегии индексации, и т.д.
Одним из эффективных инструментов анализа работы сервера баз данных является SQL PROFILER. Основным его преимуществом является возможность сохранения собранной в процессе трассировки информации в специально созданных администраторам таблицах базы данных. Кроме того, этот инструмент DBA имеет простой, дружественный интерфейс.
Установка трассировки в SQL PROFILER
Запустите SQL PROFILER, и в пункте меню FILE выберите NEW, а затем TRACE. Введите информацию о SQL сервере, который Вы хотите исследовать. Введите имя сервера, логин и пароль. В появившейся форме введите название новому заданию для трассировки, например TRACE1. Для опции «Save to table» — Вы должны указать базу данных и таблицу в которую Вы хотите сохранять собранную информацию. Очень хорошая идея, использовать отдельный SQL сервер (например, настольную версию), чтобы избегать конкуренции за ресурсы между последовательной записью данных трассировки в таблицу и другими процессами сервера баз данных.
В настройках для таблицы результатов трассировки (EVENTS) лучше удалить все заданные по умолчанию счётчики и затем выбирать всё, что относится к хранимым процедурам и запросам TSQL и любым другим дополнительным событиям, которые Вас интересуют.
В настройках фильтров задают параметр OBJECTID для дерева и флаг проверки «Exclude system objects». После этого можно вернуться в основную форму и нажать кнопку RUN. С этого времени SQL PROFILER начнёт собирать информацию обо всех процедурах, исполняемых сервером баз данных. Процесс трассировки можно оставить запущенным на несколько часов, в течение обычной активности пользователей. После этого, Вы готовы к анализу данных трассировки.
Михаил предлагает, в первую очередь, обратить внимание на два счётчика: READS и DURATIOIN. В листинге трассировщика будет содержаться информация по каждой завершённой процедуре или запросу наряду с id пользователей и другими параметрами.
Перед составлением запросов к таблице данных трассировки (TRACE1), Михаил предлагает создать два индекса для полей DURATION и READS. Это существенно ускорит анализ.
CREATE NONCLUSTERED INDEX IND_TRACE_1 ON dbo.TRACE1(Duration)
GO
CREATE NONCLUSTERED INDEX IND_TRACE_2 ON dbo.TRACE1(Reads)
GO
Первое, что Вас может заинтересовать в работе сервера, это наиболее длительные в исполнении процедуры и запросы. Следующий запрос выводит 20 процедур, которые выполняются дольше всех:
SELECT * FROM TRACE WHERE Duration IN
(SELECT DISTINCT TOP 20 Duration
FROM TRACE ORDER BY Duration DESC)
Чтобы понять, почему процедура исполняется так долго, нужно анализировать параметры для READS. Когда текущая величина READS не высока, вероятно предположить, что требуемые ресурсы (например таблицы или представления) были заблокированы другим процессом. Это указывает на возможные проблемы блокировок, и Вы можете искать процедуры, которые в это время используют те же самые объекты.
Высокое значение READS может указывать на сканирование таблиц или на не оптимальные индексы. Обычно, нужно добиваться, что бы параметр READS был настолько низким, насколько это возможно получить (при отсутствии существенного влияния блокировок), потому что задачи ввода/вывода являются самыми медленными в исполнении системой.
Перетащите долго выполняемый запрос в Query Analyzer и с помощью Плана Выполнения запроса определите, какие таблицы сканируются или какие индексы используются. Простого сканирования можно избежать, если применить надлежащий индекс к полям, участвующим в предложении WHERE или объединении. Просмотр индекса исполняется намного быстрее чем сканирование таблицы (для этого их и придумали). Часто, для таблиц создаётся составной индекс, и, если поля, используемые в WHERE или объединении, не входят ни в один из существующих индексов, можно создать дополнительные, не составные индексы для этих столбцов, что может существенно снизить READS и повысить эффективность исполнения запроса.
Задачи, которые используют большие массивы данных (высокий READS) но не исполняются в разумные сроки, можно найти с помощью следующего запроса:
SELECT * FROM TRACE WHERE Reads IN
(SELECT DISTINCT TOP 20 Reads
FROM TRACE ORDER BY Duration DESC)
В случае прямого сканирования таблиц, также можно использовать аналогичные запросы к таблице результатов трассировки.
Снижение интенсивности операций ввода-вывода
По материалам сообщения John Savill на:
http://www.ntfaq.com/Articles/Index.cfm?ArticleID=14921
Если Ваш сервер баз данных чересчур интенсивно использует I/O, можно изменить значение параметра операционной системы I/O Page Lock Limit, который может увеличить эффективную норму чтения/записи данных операционной системой на жесткий диски.
Сначала, выполните эталонный тест I/O для вашей обычной загрузки сервера. Затем, в regedit.exe откройте ключ:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SessionManager\MemoryManagement\IoPageLockLimit
Смысл Ваших действий состоит в пошаговом подборе значений этого ключа до наиболее оптимального, с точки зрения изменений результатов эталонного тестирования, значения.
В этом ключе операционная система считывает максимальное число байт, которые она можете использовать для операций I/O. По умолчанию установлено значение 0, которому соответствует 512КБ. Увеличивайте это значение по шагам, каждый раз прибавляя по 512КБ (например: «512», «1024», и т.д.), и выполняйте после каждого изменения эталонное тестирование вашей системы. Увеличивать этот параметр есть смысл только до тех пор, пока вы наблюдаете увеличение пропускной способности операций ввода – вывода, которое может проявляться в снижении временных затрат на стандартные дисковые операции. Когда Вы перестанете наблюдать существенное улучшение, возвратитесь в редактор реестра и уничтожьте последнее приращение.
Предостережение : Есть ограничение на максимальный размер значения этого ключа. Если Вы имеете 16 МБ ОЗУ, не устанавливайте IoPageLockLimit более 2048 байт; для 32МБ ОЗУ, не превышайте 4096 байт, и так далее.
“Тюнинг” – для Windows NT, обеспечивающий более эффективную работу MS SQL Server
По материалам статьи Sergey A. Vartanyan на swink.com “Tips on tuning the Windows NT server”.
Как DBA или разработчик, Вы должны знать некоторые общие аспекты конфигурирования системы Windows NT для повышения эффективности работы SQL сервера. В этой статье, Сергей предлагает некоторые настройки Windows NT, на котором предполагается запускать MS SQL Server:
1.) Если у Вас ОЗУ большого размера, Вы можете запретить Windows NT сбрасывать страницы в pagefile. Для этого, с помощью regedit.exe, установите значение ключа
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SessionManager\MemoryManagement
в единицу. (Замечание Автора: лично я этот ключ трогать боюсь…)
2.) Создать на каждом отдельном физическом дисковом массиве свой page file. Исключение может составить только тот диск, где расположен системный каталог Windows NT.
3.) Установите для сервера опцию «Maximize Throughput for Network Applications»:
Start => Settings => Control Panel => Network => Services
Выберите Server и потом Properties.
Из списка выберите последний пункт «Maximize Throughput for Network Applications».
4.) Вы можете повысить производительность отключив учёт даты последнего доступа к файлам. Для этого нужно установить в единицу ключ:
HKLM\SYSTEM\CurrentControlSet\Control\FileSystem\NtfsDisableLastAccessUpdate
5.) Используйте минимальный набор сетевых протоколов. Например, только TCP/IP. Если Вы используете несколько протоколов, определите наиболее часто используемый, и поместите его на первое место в Bindings листе.
6.) Используйте как можно меньшее количество счётчиков в Performance Monitor.
7.) Не используйте Open GL хранители экрана, потому что они используют очень много системных ресурсов.
8.) При настройке Audit Policy, добавляйте новые проверки очень осторожно. Не проверяйте «File and Objects Access» и «Process Tracking», т.к. это может привести к почти полной потере доступности сервера.
9.) Укажите серверу запускать приложения с консоли с тем же приоритетом, что и background applications
Start => Settings => Control Panel => System => «Performance»
Далее с помощью клавиши табуляции перейдите к нужному пункту и установите «Application Performance» в «None».
Важное замечание: Перечисленные Сергеем операции при не правильном или ошибочном применении могут привести к краху Вашей системы. Поэтому, позаботьтесь предварительно о наличии резервных копий баз и системы, сохраните рабочую конфигурацию NT, и промоделируйте возможные изменения на полигоне. Никогда не делайте более одного изменения конфигурации за один раз.
Параметры редактора Transact-SQL
Этот раздел содержит сведения о некоторых параметрах редактора Transact-SQL. Чтобы настроить эти параметры, откройте меню Средства/Параметры и перейдите в диалоговое окно Параметр.
Выполнение запроса
| Свойство | Описание |
|---|---|
| SET ROWCOUNT | Значение по умолчанию, равное 0, указывает на то, что SQL Server будет продолжать ожидание результатов, пока все из них не будут получены. При установке значения больше 0 SQL Server прервет запрос после получения указанного числа строк. Для выключения этого параметра (чтобы возвращались все строки) задайте SET ROWCOUNT 0. |
| SET TEXTSIZE | Значение по умолчанию, равное 2 147 483 647 байт, указывает на то, что SQL Server предоставит содержимое поля полностью вплоть до пределов для полей данных text, ntext, nvarchar(max) и varchar(max). Этот параметр не влияет на тип данных xml. Задав меньшее число, можно ограничить вывод результатов в случае больших значений. Содержимое столбцов большего размера, чем заданное число, будет усекаться. |
| Время ожидания выполнения | Указывает число секунд ожидания перед отменой запроса. Значение, равное 0, указывает на неограниченное время ожидания или отсутствие времени ожидания. |
| По умолчанию открывать новые запросы в режиме SQLCMD | При установке этого флажка новые запросы будут открываться в режиме SQLCMD. Этот флажок становится видимым только в случае, если диалоговое окно открыто из меню Средства. |
При выборе этого параметра следует учитывать следующие ограничения.
Результаты запроса
| Свойство | Описание |
|---|---|
| Включение запроса в результирующий набор | Возвращает текст запроса как часть результирующего набора. |
| Включение заголовков столбцов при копировании или сохранении результатов | Включает верхние колонтитулы столбцов (заголовки) при копировании результатов в буфер обмена или при сохранении файла. Снимите этот флажок, если нужно, чтобы при сохранении или копировании данных результатов они содержали только данные, без заголовков столбцов. |
| Сброс результатов после выполнения | Освобождает память посредством сбрасывания результатов запроса после того, как они были выведены на экран. |
| Отображение результатов на отдельной вкладке | Отобразить результирующий набор в новом окне документа, а не в нижней части окна документа запроса. |
| Переключение на вкладку результатов после выполнения запроса | Автоматически переводит изображение на экране к результирующему набору. |
| Максимальное число полученных символов | Данные не в формате XML: |
Введите число от 1 до 65 535, чтобы указать максимальное число символов, отображаемых в каждой ячейке. Примечание. Указание слишком большого числа символов может привести к тому, что результирующий набор будет отображаться усеченным. Максимальное число символов, отображаемых в каждой ячейке, зависит от размера шрифта. Высокое значение в этом окне может привести к нехватке памяти для среды SQL Server Management Studio и снижению производительности системы при возвращении больших результирующих наборов.
Обратная связь
Были ли сведения на этой странице полезными?
Настройка системы хранения для SQL Server
При установке и настройке нового сервера SQL Server или приложений многие пренебрегают точным планированием объема дисковой памяти, необходимой для проекта. Благодаря постоянному снижению стоимости хранения данных можно запросто попросить администратора системы хранения добавить еще 500 Гбайт дискового пространства. Но о чем идет речь? На какой срок хватит этой добавки? На полгода, три года, или на целое десятилетие? Без ответа на этот вопрос нельзя быть уверенным, что требование обосновано. При этом необходимо учитывать скорость работы накопителя, которая напрямую связана со стоимостью решения. Может быть, в некоторых организациях эти затраты не учитываются слишком строго, но нельзя забывать, что одной из задач подразделения ИТ является эффективное использование выделенного бюджета. Предлагаю вниманию читателей методику определения необходимого дискового пространства, настройки дисков и выбора типа системы хранения из представленных на рынке.
Подготовка требований
Проще всего определить требования к объему, который займет ваша база данных. Просто рассчитаем, сколько байтов потребуется для хранения за период N месяцев. Хотя на первый взгляд это может показаться сложной задачей, если рассматривать базу данных как набор отдельных таблиц, все оказывается не так уж страшно. Сначала суммируем объем всех статических поисковых таблиц. Этот объем практически не меняется с течением времени, так что требуется просто оценить его и не забыть добавить к общему результату. Что касается рабочих таблиц, размер которых будет расти каждый день, месяц, год, то вам потребуются всего несколько величин — размер строки данных и среднее количество строк данных, добавляемых за выбранный период (день, месяц, год). Если речь идет о системе реального времени, например, ввода заказов или бронирования билетов, системы сетевой безопасности, за период логичнее всего взять один день. Размер строки данных вам подскажет разработчик базы. Также следует учитывать срок хранения данных в системе — следует ли хранить данные в системе в течение месяца, года или требуется накапливать полный объем данных за всю историю существования компании. Еще одной расчетной величиной является размер страницы данных на диске — для SQL Server он составляет 8060 байт.
Простейшие арифметические действия позволят нам определить общий объем данных для хранения. Допустим, в систему ежедневно добавляется 20 тыс. строк, каждая строка 187 байт, а сохранять эти данные требуется в течение трех лет. Сначала определим, сколько строк может храниться в странице данных (SQL Server не разбивает данные одной строки между двумя таблицами). Разделив размер страницы на размер строки, получаем 8060/187 = 43,101, как показано в таблице 1.
Полученное значение необходимо округлить до меньшего целого числа. Чтобы получить число добавляемых страниц, делим число ежедневно добавляемых строк на только что посчитанное количество строк на одной странице. 20000/43 = 465,11 — примерно такое число страниц будет добавляться в таблицу (таблица 2).
Это число мы округляем в большую сторону (до 466). Хотя страница может хранить 8060 байт данных, ее физический размер на диске составит 8 Кбайт. Умножив 466 на 8 Кбайт, получим, что в таблицу ежедневно добавляется 3728 Кбайт или 3,64 Мбайт, как показано в таблице 3.
Если считать, что в месяце 30,5 дня, то ежемесячное увеличение базы составит 111,02 Мбайт. Умножив на нормативный срок хранения данных (36 месяцев), получаем 3996,72 Мбайт или 3,9 байт за три года (см. таблицу 4).
Определив таким образом размеры, необходимые для хранения каждой таблицы, сложим все значения и получим размер базы данных на заданный период времени. Если в этот период будут происходить плановые перемещения данных в архивные базы, которые будут использоваться для аналитики и отчетов, действительный размер рабочей базы данных окажется меньше, так как некоторый объем данных будет удаляться из таблиц. После того как требования по объему данных определены, следует уточнить требования к скорости и избыточности дисков, на которых будет размещаться база данных.
Настройка накопителей RAID
Общей рекомендацией является использование RAID 10 для баз данных. RAID 10 обеспечивает быструю запись данных, при этом исключаются дополнительные затраты времени на вычисление контрольных сумм. Но, как правило, не учитывается стоимость предлагаемого решения. Конечно, RAID 10 обеспечивает максимальную производительность, но, кроме того, это и самое дорогое решение. В RAID 10 вдвое больше дисков, которые требуется настроить, и эти диски небесплатные. В некоторых случаях использование RAID 10 является оправданным, но применение RAID 10 для всех имеющихся баз данных может оказаться очень неэффективным использованием ресурсов. Большинство баз данных, которые настроены на применение систем хранения RAID 10, могли бы прекрасно работать и на системах RAID 5.
Чтобы определить, какой уровень RAID требуется для базы данных, давайте вспомним, что они собой представляют. RAID 5 называют также чередующимся набором с контролем четности. При записи данных на диск выполняется расчет контрольной суммы для каждой страницы данных. Эта контрольная сумма также записывается на диск. В случае отказа диска данные могут быть восстановлены по контрольной сумме контроллером дискового массива, а неисправный диск должен быть заменен. Одни поставщики систем хранения размещают всю контрольную информацию на один диск, другие распределяют эти данные по всем дискам в массиве. Хотя оба подхода обеспечивают одинаковый уровень защиты, распределение контрольных данных по всем дискам позволяет увеличить скорость работы массива, поскольку для чтения/записи оказывается доступен дополнительный диск.
RAID 10 называется также зеркальным набором. Количество дисков в массиве делится пополам, каждая половина выделяется под свой набор. Эти наборы зеркально отражают содержимое друг друга. При отказе одного из дисков набор с этого диска становится недоступен, после замены отказавшего диска данные восстанавливаются с зеркальной копии. Поскольку вычисление контрольных сумм при выполнении каждой операции записи при таком подходе не требуется, операция записи выполняется быстрее. С другой стороны, поскольку данные зеркалируются, для их хранения требуется вдвое большее количество дисков. На рисунке изображены варианты размещения наборов данных на физических дисках для систем хранения уровня RAID 5 и RAID 10.
Оптимизация кэширования
При настройке сети хранения SAN следует учитывать, что современные системы SAN оснащаются просто огромными объемами кэш-памяти. Такое решение позволяет кэшировать большое количество операций записи, так что, когда SQL Server выполняет запись данных на диск, в действительности физическая запись данных на диск не производится. На самом деле SAN кэширует эти данные и записывает их на диск позже. Благодаря кэшированию уровень RAID не влияет на скорость работы SQL Server, поскольку операции записи SQL Server и записи на диск оказываются не связанными между собой.
По умолчанию большинство систем SAN настроены на использование 50% кэша под чтение и столько же — под запись. Такая настройка может быть оптимальной для хранилища данных, но не всегда подходит для баз данных OLTP. Система SAN применяет упреждающее чтение данных в буфер, предполагая, какие данные могут быть востребованы хостом при следующей операции еще до того, как хост их действительно затребует. При следующем запросе данных хостом данные могут быть выданы немедленно прямо из кэша. Но для OLTP-приложений (баз данных, файловых и почтовых серверов) чтение большого количества последовательных блоков данных из файла происходит достаточно редко, в большинстве случаев выполняется чтение блоков вразнобой. Может оказаться, что стратегия упреждающего чтения даже снижает производительность системы за счет чтения больших объемов данных, которые оказываются невостребованными. Изменение соотношения объемов кэша чтения к записи с 50/50 на 30/70 или даже 20/80 может заметно повысить производительность системы за счет увеличения скорости записи данных SQL Server.
Такое изменение кэша позволяет добиться большей производительности и для дисков RAID 5. Так как SQL Server будет в действительности обращаться к дискам только для операций чтения, оптимальная производительность будет достигаться при чтении с максимального количества дисков. В случае RAID 10 только половина дисков будет использоваться для чтения данных, а для RAID 5 чтение производится со всех имеющихся дисков (или всех, кроме одного, в зависимости от выбранной производителем технической реализации).
Настройка системы хранения
Кроме настройки кэширования, рост производительности системы может быть достигнут за счет настройки логических томов LUN и уровней дисков. Например, имеет смысл проверить, какое влияние на производительность окажет отключение кэша непосредственно для LUN. Это отключит передачу кэша LUN в кэш SAN, что может оказаться полезным. Дело в том, что SQL Server тоже стремится кэшировать максимальный объем данных в оперативной памяти сервера. Благодаря этому SQL Server значительно сокращает число чтений с диска, а когда необходимость в чтении с диска возникает, вряд ли эти данные могут оказаться в кэше LUN. Данные, которые кэшируются SQL Server, также кэшируются на уровне SAN, так что кэширование на уровне LUN может дать лишь незаметное преимущество. Когда SQL Server запрашивает данные с диска, SAN будет пытаться выполнить упреждающее чтение, чтобы для следующего запроса на чтение побыстрее предоставить данные, не обращаясь к физическому диску. Но из-за того, что в базах данных OLTP запрос блоков носит случайный, а не последовательный характер, шансы на то, что SAN угадает, какие данные потребуются при следующем запросе, очень невелики, и выигрыш от упреждающего чтения стремится к нулю. В системах OLTP, где диск уже обрабатывает запросы, включение кэша чтения может незначительно влиять на производительность; если диск не используется, кэш чтения также не влияет на производительность.
На уровне диска важным элементом настройки системы хранения является корректное выравнивание (aligning) диска. В те времена, когда разрабатывались спецификации главной загрузочной записи (Master Boot Record, MBR), стоимость дисков была значительно выше, чем сейчас. Поэтому MBR была настроена так, чтобы занимать 63 блока на диске, а данные начинаются только с 64-го блока. Такая настройка создает определенные проблемы, так как дисковые операции оптимальны для 64-блочных записей. При смещении данных на один блок операция чтения/записи 64-блочной структуры в действительности требует выполнения 2 операций. Для приложений типа Microsoft Exchange или Oracle ASM дисковые операции используют 8-Кбайт (8-блочные) структуры, и дополнительные операции не оказывают значительного воздействия. Но SQL Server использует страницы 64 Кбайт (64 блока) для чтения и записи. И неправильное выравнивание диска приводит к тому, что при каждой операции чтения/записи вместо одной операции выполняется две.
Эту проблему относительно просто решить с помощью утилиты DISKPART.EXE, которая входит в состав Windows Server 2008 и Windows Server 2003. После того как логический том LUN подключен к серверу, выполните в командной строке команду DISKPART.EXE и в ответ на приглашение введите команду:
где 3 обозначает номер только что подключенного диска. Затем выполните команду
CREATE PARTITION PRIMARY ALIGN=64
которая создаст на диске основной раздел с выравниванием на 64-й блок физического диска, а не на 63-й (по умолчанию). Далее эти же операции следует выполнить для всех номеров LUN, подключенных к серверу.
Если у вас уже имеются LUN, которые не были корректно выровнены, ситуация осложняется, поскольку выравнивание раздела нельзя выполнить, если раздел уже создан. В этом случае проще всего выполнить резервное копирование базы, удалить раздел и создать его вновь с правильным выравниванием, а после этого восстановить базу на диск из резервной копии. Некоторые производители SAN предлагают собственные средства миграции, доступны также инструменты сторонних разработчиков. Мы не советуем использовать их, так как часто эти средства выполняют полное копирование информации, включая неправильное выравнивание.
Совместное использование — все или ничего
Существует два основных подхода к настройке системы хранения — общий доступ ко всем ресурсам и никакого доступа. Общий доступ ко всем ресурсам выглядит так: все диски SAN могут использоваться каждым LUN. И хотя это кажется удобным, такой подход имеет свои недостатки. При замедлении одного из дисков в массиве происходит замедление всех LUN массива. Поэтому предоставление полного общего доступа оправданно только в том случае, если у вас нет нормального администратора SAN.
При втором подходе — никакого совместного доступа (хотя правильнее говорить об избирательном доступе), LUN создаются для небольших групп RAID на SAN. Небольшие группы физических дисков выделяются в группы RAID, и LUN присваиваются этим группам RAID. Благодаря такому подходу если один из LUN или дисков начинает замедляться, то снижение производительности затрагивает только этот LUN в пределах данной группы RAID. Такой подход требует больше внимания, трудоемких детальных настроек, больше знаний об операциях ввода-вывода и параметров настройки и сопровождения. Зато благодаря этому при необходимости обеспечить большую пропускную способность вы можете перейти на другую группу RAID или подключить дополнительные группы RAID для предоставления большей пропускной способности.
SAN или DAS — что выбрать
На сегодня основной технологией систем хранения является SAN. SAN обеспечивает очень высокую скорость работы и может содержать сотни накопителей, которые подключаются к серверам высокоскоростными соединениями Fibre Channel. Но решения SAN очень дорогостоящие, цены часто начинаются с сотен тысяч долларов. Более приемлемой альтернативой являются системы DAS, которые могут оказаться оптимальным решением, если большое хранилище SAN не требуется. DAS предоставляет почти те же функции, что и SAN, но в несколько меньшем масштабе и с меньшим объемом кэша. Устройства DAS могут содержать от 2 до 45 накопителей SCSI, SAS или SATA с размером кэша от нескольких мегабайтов до гигабайта.
Для DAS годятся все настройки, которые были рекомендованы для SAN, в том числе балансировка кэша чтения/записи. Как и для SAN, накопители DAS нуждаются в выравнивании (как, впрочем, и любые другие массивы RAID). В отличие от SAN, DAS, как правило, не позволяет управлять кэшированием на уровне отдельных дисков. Кроме того, системы DAS подключаются к определенному серверу, так что вы не сможете подключить к DAS несколько серверов, и это самое существенное отличие от SAN.
Самым главным ограничением использования DAS является невозможность наращивания, так как в DAS можно подключить только определенное количество дисков, к тому же при использовании DAS невозможно организовать общий пул ресурсов для группы серверов. Еще одно ограничение — невозможно расширить массив RAID на большее число дисков, чем может быть размещено в одной полке накопителей. В большинстве DAS полка может вмещать 15 дисков, при этом каждая полка обслуживается собственным контроллером RAID. Поскольку контроллеры RAID не взаимодействуют друг с другом, диски одного RAID могут размещаться только на одной полке. Но всеми этими ограничениями можно пренебречь, если принять во внимание стоимость. Системы хранения DAS стоят существенно меньше, чем самые доступные решения SAN.
Использование iSCSI для баз данных
Нельзя не упомянуть об iSCSI как о новой технологии хранения для баз данных. iSCSI позволяет использовать обычные сети Ethernet для подключения баз данных к системам хранения. Многие производители SAN предлагают интерфейс iSCSI в своих решениях SAN. Преимущество iSCSI заключается в том, что вы можете организовать систему хранения класса SAN без дополнительных затрат на дорогостоящее оборудование Fiber Channel. Недостаток этого подхода заключается в том, что обмен сервера баз данных с системой хранения идет по общей сети Ethernet и конкурирует с остальным трафиком за полосу пропускания сети. Если ваша сеть Ethernet недостаточно быстрая, скорее всего, не стоит рассчитывать на решения iSCSI. Еще одна потенциальная проблема при использовании iSCSI состоит в том, что, поскольку трафик обмена с системой хранения идет по сети TCP, необходимо учитывать настройки тайм-аутов для TCP. Если по каким-то причинам массив хранения вдруг даст сбой, Fibre Channel отреагирует очень быстро и сможет переключиться на другой маршрут. А тайм-ауты для TCP значительно больше, так что база данных может оказаться заблокированной или произойдет сбой по тайм-ауту процесса.
Для устранения ограничений пропускной способности сети можно использовать виртуальные локальные сети VLAN для разграничения и изоляции трафика сети iSCSI от общей сети. Такая изоляция обеспечивает логическое разделение сети на сегменты VLAN на уровне 2 базовой модели OSI. VLAN представляет собой логический домен в рамках сетевого коммутатора, который обеспечивает разграничение трафика. Весь трафик VLAN, включая широковещательные запросы, не выходит за пределы данной VLAN. Если часть трафика из данной VLAN должна быть направлена в другую VLAN, она маршрутизируется. Такой подход позволяет полностью разграничить трафик iSCSI и общий, не связанный с iSCSI трафик. VLAN организовывается на уровне 2 и разделяет трафик только на этом уровне. Для дальнейшего разграничения трафика на уровне 3 коммутации и маршрутизации сети VLAN могут быть ассоциированы с подсетями маршрутизации, которые позволяют выпускать трафик из домена VLAN для сообщения с другими сетями VLAN. Более того, уровень 3 маршрутизации позволяет настроить списки контроля доступа ACL для разрешения или запрета определенного трафика в отдельных подсетях. Дополнительные возможности управления доступны также на уровне 4 и уровне 5, но их обсуждение уже выходит за рамки данной статьи.
Настройте SQL Server на максимальную производительность
Правильное планирование и настройка физических дисков имеет очень большое значение для повышения производительности сервера. Если же речь идет об SQL Server (или любом другом сервере баз данных), то это является критически важным фактором, поскольку серверы баз данных выполняют операции чтения/записи значительно большего объема и более интенсивно, чем большинство других корпоративных приложений и серверов. Поэтому к планированию и настройке системы хранения для базы данных следует подходить особенно внимательно, особенно если учесть, что переделка потребует значительно больше времени и усилий.
Денни Черри (dcherry@awarenesstech.com) — старший администратор баз данных и архитектор в компании Awareness Technologies. Имеет несколько сертификатов Microsoft и звание Microsoft MVP
10 самых полезных функций SQL Server 2008 для администратора баз данных
Автор: Майк Вайнер (Mike Weiner)
Соавтор: Бурцин Пэйтел (Burzin Patel)
Редакторы: Любор Коллар (Lubor Kollar), Кевин Кокс (Kevin Cox), Билл Эммерт (Bill Emmert), Грег Хузмайер (Greg Husemeier), Пол Бурпо (Paul Burpo), Джозеф Сак (Joseph Sack), Дэнни Ли (Denny Lee), Санджай Мишра (Sanjay Mishra), Линдси Аллен (Lindsey Allen), Марк Суза (Mark Souza)
Microsoft SQL Server 2008 содержит ряд улучшений и новых функциональных возможностей, расширяющих функциональность предыдущих версий. Администрирование и обслуживание баз данных, поддержание управляемости, доступности, безопасности и производительности — все это входит в обязанности администратора базы данных. В этой статье описаны десять самых полезных новых функций SQL Server 2008 (в алфавитном порядке), облегчающих работу администратора БД. Помимо краткого описания, для каждой из функций приведены возможные ситуации ее применения и важные рекомендации по использованию.
Монитор активности
При устранении проблем, связанных с производительностью, или отслеживании работы сервера в реальном времени администратор обычно запускает ряд скриптов или проверяет соответствующие источники сведений, чтобы собрать общие данные о выполняющихся процессах и выявить причину проблемы. Монитор активности SQL Server 2008 объединяет такие сведения, предоставляя наглядную информацию по выполняющимся и недавно выполнявшимся процессам. Администратор БД может как просматривать высокоуровневые сведения, так и проанализировать любой из процессов более детально и ознакомиться со статистикой ожидания, что облегчает выявление и разрешение проблем.
Чтобы открыть монитор активности, щелкните правой кнопкой мыши на имени зарегистрированного сервера в обозревателе объектов, затем выберите Монитор активности или же воспользуйтесь стандартным значком на панели инструментов в среде SQL Server Management Studio. Монитор активности предлагает администратору раздел обзора, внешне похожий на Диспетчер задач Windows, а также компоненты детального просмотра отдельных процессов, ожидания ресурсов, ввода-вывода в файлы данных и последних ресурсоемких запросов, как показано на рис. 1.
Рис. 1: Вид представления Монитора активности SQL Server 2008 в среде Management Studio
Примечание. Монитор активности использует параметр периодичности обновления данных, который можно изменить щелчком правой кнопки мыши. При выборе частого обновления данных (раз в менее чем 10 секунд) производительность высоконагруженной рабочей системы может понизиться.
С помощью монитора активности администратор также может выполнять следующие задачи:
· Приостанавливать и возобновлять работу монитора активности одним щелчком правой кнопки мыши. Это позволяет администратору «сохранить» сведения о состоянии на определенный момент времени, они не будут обновлены или перезаписаны. Но не забывайте, что при обновлении данных вручную, развертывании или сворачивании раздела старые данные будут обновлены и утеряны.
· Щелкните правой кнопкой мыши элемент строки, чтобы отобразить полный текст запроса или графический план выполнения с помощью пункта меню «Последние ресурсоемкие запросы».
· Выполнять трассировку приложением Profiler или завершать процессы в представлении «Процессы». События приложения Profiler включают события RPC:Completed, SQL:BatchStarting и SQL:BatchCompleted, а также Audit Login и AuditLogout.
Монитор активности также позволяет отслеживать активность любого локального или удаленного экземпляра SQL Server 2005, зарегистрированного в среде SQL Server Management Studio.
Аудит [SQL Server]
Возможность отслеживать происходящие события и регистрировать сведения о них, включая сведения о пользователях, обращающихся к объектам, а также времени и содержании вносимых изменений, помогает администратору обеспечить соблюдение стандартов соответствия нормативным или организационным требованиям по безопасности. Кроме того, понимание происходящих в среде событий также может помочь при разработке плана по снижению рисков и поддержания безопасности в среде.
В SQL Server 2008 (только выпуски Enterprise и Developer) SQL Server Audit реализует автоматизацию, позволяющую администратору и другим пользователям подготавливать, сохранять и просматривать аудиты различных компонентов серверов и баз данных. Функция предусматривает возможность аудита с детализацией уровня сервера или базы данных.
Существуют группы действий аудита на уровне сервера, например, следующие:
· FAILED_LOGIN_GROUP отслеживает неудачные попытки входа в систему.
· BACKUP_RESTORE_GROUP сообщает, когда создавалась резервная копия базы данных или выполнялось ее восстановление.
· DATABASE_CHANGE_GROUP проводит аудит времени создания, изменения или удаления базы данных.
Группы действий аудита на уровне базы данных включают следующие:
· DATABASE_OBJECT_ACCESS_GROUP вызывается при каждом выполнении инструкции CREATE, ALTER или DROP для объекта базы данных.
· DATABASE_OBJECT_PERMISSION_CHANGE_GROUP вызывается при использовании инструкций GRANT, REVOKE или DENY для объектов базы данных.
Существуют и другие действия аудита, например, SELECT, DELETE и EXECUTE. Дополнительные сведения, в том числе полный список всех групп и действий аудита, см. в разделе Группы действий и действия аудита SQL Server.
Результаты аудита можно направить для последующего просмотра в файл или журнал событий (системный журнал или журнал событий безопасности Windows). Данные аудита создаются с применением Расширенных событий — еще одной новой функции SQL Server 2008.
Аудиты SQL Server 2008 позволяют администратору ответить на вопросы, на которые раньше было очень сложно ответить постфактум, например, «Кто удалил этот индекс?», «Когда была изменена хранимая процедура?», «Какое внесенное изменение может мешать пользователю получить доступ к этой таблице?» и даже «Кто выполнил инструкцию SELECT или UPDATE для таблицы [dbo.Payroll]?».
Дополнительные сведения об использовании аудита SQL Server и примеры его реализации см. в разделе Руководство по обеспечению соответствия требованиям в SQL Server 2008.
Сжатие резервных копий
Администраторы БД долгое время предлагали включить эту функцию в SQL Server. Теперь это сделано, и как раз вовремя! В последнее время по ряду причин, например, в связи с возросшей длительностью хранения данных и необходимостью физического хранения большего объема данных, размеры баз данных стали расти экспоненциально. При резервном копировании большой базы данных необходимо выделение значительного дискового пространства для файлов резервной копии, а также выделение для операции существенного временного промежутка.
При использовании сжатия резервных копий SQL Server 2008 файл резервной копии сжимается по мере его записи, благодаря чему требуется не только меньше дискового пространства, но и меньше операций ввода-вывода, а резервное копирование занимает меньше времени. В ходе лабораторных испытаний с реальными пользовательскими данными во многих случаях наблюдалось уменьшение размера файла резервной копии на 70-85%. Кроме того, испытания показали, что длительность операций копирования и восстановления сократилась примерно на 45%. Следует отметить, что дополнительная обработка при сжатии увеличивает загрузку процессоров. Чтобы отделить во времени ресурсоемкий процесс копирования от других процессов и минимизировать его влияние на их работу, можно воспользоваться другой описанной в этом документе функцией — Resource Governor.
Сжатие включается путем добавления предложения WITH COMPRESSION в команду BACKUP (дополнительные сведения см. в разделе Электронная документация по SQL Server) или установкой этого параметра на странице Параметры диалогового окна Резервное копирование базы данных. Чтобы не требовалось вносить изменения во все существующие сценарии резервного копирования, реализован глобальный параметр, включающий сжатие всех создаваемых на экземпляре сервера резервных копий по умолчанию. (Этот параметр доступен на странице Настройки базы данных диалогового окна Свойства сервера; его также можно установить, выполнив хранимую процедуру sp_configure со значением параметра backup compression default, равным 1). Команда создания резервной копии требует явного задания параметра сжатия, а команда восстановления автоматически распознает сжатую резервную копию и распаковывает ее при восстановлении.
Сжатие резервных копий — исключительно полезная функция, сберегающая дисковое пространство и время. Дополнительные сведения о настройке сжатия резервных копий см. в техническом примечанииНастройка производительности сжатия резервных копий в SQL Server 2008. Примечание. Создание сжатых резервных копий поддерживается только в выпусках SQL Server 2008 Enterprise и Developer , однако все выпуски SQL Server 2008 позволяют восстанавливать сжатые резервные копии.
Серверы централизованного управления
Часто администратор БД управляет сразу многими экземплярами SQL Server. Возможность централизации управления и администрирования многими экземплярами SQL в единой точке позволяет экономить существенные усилия и время. Реализация серверов централизованного управления, доступная в среде SQL Server Management Studio посредством компонента «Зарегистрированные серверы», позволяет администратору выполнять различные административные операции над многими серверами SQL Servers из единой консоли управления.
Серверы централизованного управления позволяют администратору зарегистрировать группу серверов и выполнять над ними, как над единой группой, например, следующие операции:
· Многосерверное выполнение запросов: теперь из одного источника можно выполнить скрипт на многих серверах SQL Server, данные будут возвращены этому источнику, причем раздельно выполнять вход в каждый из серверов не требуется. Это может быть особенно полезным в случае, когда необходимо просмотреть или сравнить данные с нескольких серверов SQL Server, не выполняя распределенный запрос. Кроме того, при условии поддержки синтаксиса запроса предыдущими версиями SQL Server, запускаемый из редактора запросов SQL Server 2008 запрос может выполняться и на экземплярах SQL Server 2005 и SQL Server 2000. Дополнительные сведения см. в блоге рабочей группы по управляемости SQL Server в разделе Выполнение многосерверных запросов в среде SQL Server 2008.
· Импорт и определение политик на многих серверах: в рамках функциональности Управления на основе политик (еще одной новой функции SQL Server 2008, также описанной в этой статье), SQL Server 2008 обеспечивает возможность импорта файлов политик в отдельные группы серверов централизованного управления и позволяет определять политики на всех серверах, зарегистрированных в определенной группе.
· Управление службами и вызов диспетчера конфигурации SQL Server: инструмент «серверы централизованного управления» помогаает создать центр управления, в котором администратор БД может просмотреть и даже изменить (при наличии соответствующих разрешений) состояние служб.
· Импорт и экспорт зарегистрированных серверов: серверы, зарегистрированные в серверах централизованного управления (Central Management Servers), могут экспортироваться и импортироваться при их передаче между администраторами или различными установленными экземплярами SQL Server Management Studio. Эта возможность служит альтернативой импорту или экспорту администратором его собственных локальных групп в SQL Server Management Studio.
Не забывайте, что разрешения применяются с помощью проверки подлинности Windows, поэтому права и разрешения пользователей могут различаться на различных серверах, зарегистрированных в группе сервера централизованного управления. Дополнительные сведения см. в разделе Администрирование нескольких серверов с помощью серверов централизованного управления и в блоге Кимберли Трипп (Kimberly Tripp): Центральные серверы управления SQL Server 2008 — знакомы ли вы с ними?
Сборщик данных и хранилище данных управления
Настройка производительности и диагностика занимают много времени и могут требовать профессиональных навыков работы с SQL Server, а также понимания внутренней структуры баз данных. Системный монитор Windows (Perfmon), профилировщик SQL Server Profiler и динамические административные представления решали часть этих задач, но они нередко оказывали влияние на работу сервера, были трудоемки в применении или задействовали методы сбора разрозненных данных, затрудняющие их последующее объединение и интерпретацию.
Чтобы предоставить понятные сведения о производительности системы, позволяющие предпринять конкретные меры, в SQL Server 2008 реализовано полностью расширяемое средство сбора и хранения данных о производительности — сборщик данных. Оно содержит несколько непосредственно готовых к работе агентов сбора данных, централизованное хранилище данных о производительности, так называемое хранилище данных управления, и несколько подготовленных заранее отчетов для представления собранных данных. Сборщик данных — это масштабируемое средство, обеспечивающее сбор и объединение данных из различных источников, таких как динамические административные представления, монитор производительности Perfmon и запросы Transact-SQL, в соответствии с полностью настраиваемой частотой сбора данных. Сборщик данных можно расширить, реализовав сбор данных по любому измеряемому атрибуту приложения.
Еще одна полезная функция хранилища данных управления — это возможность его установки на любом сервере SQL Server с последующим сбором данных с одного или нескольких экземпляров SQL Server. При этом минимизируется влияние на производительность рабочих систем, а также улучшается масштабируемость в контексте отслеживания и сбора данных со многих серверов. При лабораторных испытаниях наблюдаемая потеря пропускной способности при выполнении агентов и работе хранилища данных управления на нагруженном сервере (с применением рабочей нагрузки OLTP) составила приблизительно 4%. Потеря производительности может изменяться в зависимости от периодичности сбора данных (упомянутое испытание велось при расширенной рабочей нагрузке, с передачей данных в хранилище каждые 15 минут), она также может резко увеличиваться во время периодов сбора данных. В любом случае следует ожидать некоторого уменьшения доступных ресурсов, так как процессDCExec.exe использует определенный объем памяти и ресурсы ЦП, а запись в хранилище данных управления повысит нагрузку на подсистему ввода-вывода и потребует выделения пространства в месте расположения файлов данных и журнала.На диаграмме (рис. 2) показан типичный отчет сборщика данных.
Рис. 2: Вид отчета сборщика данных SQL Server 2008
В отчете показана деятельность SQL Server за время периода сбора данных. В нем собраны и отражены такие события, как ожидания, использование ЦП, ввода-вывода и памяти, а также статистика по ресурсоемким запросам. Администратор может также перейти к детальному рассмотрению элементов отчетов, сконцентрировавшись на отдельном запросе или операции, чтобы исследовать, определить и устранить проблемы, связанные с производительностью. Эти возможности сбора данных, их хранения и создания отчетов позволяют реализовать упреждающее отслеживание состояния серверов SQLServer в среде. При необходимости они позволяют возвращаться к историческим данным, чтобы понять и оценить изменения, повлиявшие на производительность за отслеживаемый период. Сборщик данных и хранилище данных управления поддерживаются во всех выпусках SQLServer 2008, кроме SQLServerExpress.
Сжатие данных
Простота управления базой данных значительно облегчает выполнение рутинных задач администрирования. По мере роста размеров таблиц, индексов и файлов и распространения очень больших баз данных (VLDB) управление данными и работа с громоздкими файлами становятся все более сложными. Кроме того, растущие с объемом запрашиваемых данных потребности в памяти и обеспечении физической пропускной способности ввода-вывода также усложняют деятельность администраторов и дорого обходятся организации. Вследствие этого во многих случаях администраторам и организациям приходится либо расширять память или gпропускную способность ввода-вывода серверов, либо смиряться с понижением производительности.
Сжатие данных, представленное в SQL Server 2008, помогает разрешить эти проблемы. Эта функция позволяет администратору избирательно сжимать любые таблицы, секции таблиц или индексы, благодаря чему уменьшается занимаемое пространство на диске и в памяти, а также размер операций ввода-вывода. Сжатие и распаковка данных нагружают процессор; тем не менее, во многих случаях дополнительная нагрузка на процессор более чем компенсируется выигрышем в объемах ввода-вывода. В конфигурациях, в которых ввод-вывод является «узким местом», сжатие данных также может обеспечить рост производительности.
В некоторых лабораторных испытаниях включение сжатия данных обеспечивало экономию 50-80% дискового пространства. Экономия пространства значительно различалась: если в данных содержалось мало повторяющихся значений, или значения использовали все выделяемые для указанного типа данных байты, экономия была минимальной. При этом производительность многих рабочих нагрузок не увеличивалась. Однако при работе с данными, содержащими много числовых данных и много повторяющихся значений, отмечались значительная экономия дискового пространства и рост производительности, составляющий от нескольких процентов до 40-60% для некоторых образцов рабочих нагрузок запросов.
SQLServer 2008 поддерживает два типа сжатия: сжатие строк, при котором сжимаются отдельные столбцы таблицы, и сжатие страниц, при котором страницы данных сжимаются с помощью сжатия строк, префиксов и словарного сжатия. Достигаемая степень сжатия сильно зависит от типов данных и содержимого базы данных. В общем, при использовании сжатия строк уменьшается дополнительная нагрузка на операции приложений, однако уменьшается и степень сжатия, то есть выигрывается меньше места. В то же время сжатие страниц приводит к большей дополнительной нагрузке на приложение и загрузке процессора, но и экономит значительно больше пространства. Сжатие страниц является надмножеством сжатия строк, то есть если объект или секция объекта сжимаются с помощью сжатия страниц, к ним также применяется и сжатие строк. Кроме того, SQLServer 2008 поддерживает формат хранения vardecimal из SQL Server 2005 с пакетом обновления 2 (SP2). Следует учитывать, что, поскольку этот формат является подмножеством сжатия строк, он считается устаревшим и будет исключен из будущих версий продукта.
И сжатие строк, и сжатие страниц может включаться для таблицы или индекса в оперативном режиме, нисколько не нарушая доступность данных для приложений. В то же время сжать или распаковать отдельную секцию секционированной таблицы в оперативном режиме без ее отключения невозможно. Испытания показали, что наилучшим оказывается совмещенный подход, при котором сжимаются лишь несколько наибольших таблиц: при этом достигается отличное отношение экономии дискового пространства (значительной) к потере производительности (минимальной). Поскольку у операции сжатия, как и у операций создания или перестроения индекса, также имеются требования к доступному дисковому пространству, проводить сжатие следует с учетом этих требований. Минимум свободного пространства во время процесса сжатия потребуется, если сжатие начать с объектов наименьшего размера.
Сжатие данных можно провести с помощью инструкций Transact-SQL или мастера сжатия данных. Чтобы определить возможное изменение размера объекта при его сжатии, можно воспользоваться системной хранимой процедуройsp_estimate_data_compression_savings или мастером сжатия данных. Сжатие базы данных поддерживается только в выпусках SQLServer 2008 Enterprise и Developer. Оно реализуется исключительно в самих базах данных и не требует внесения каких-либо изменений в приложения.
Дополнительные сведения об использовании сжатия см. в документе Создание сжатых таблиц и индексов.
Управление на основе политик
Во многих бизнес-сценариях необходимо поддерживать определенные конфигурации или соблюдать политики либо на определенном сервере SQLServer, либо многократно, в группе серверов SQLServer. Администратор или организация могут потребовать применения особой схемы именования всех создаваемых пользовательских таблиц или хранимых процедур или требовать определенные изменения применялись к конфигурации на многих серверах.
Управление на основе политик (PBM) предоставляет администратору широкий набор возможностей по управлению средой. Политики можно создавать и выполнять проверку на соответствие им. Если цель проверки (например, ядро базы данных, база данных, таблица или индекс SQLServer) не соответствует требованиям, администратор может автоматически перенастроить ее в соответствии с этими требованиями. Также существует ряд режимов определения политик (многие из которых автоматизированы), упрощающих проверку соответствия требованиям политик, регистрацию в журнале нарушений политики и отправку уведомлений, и даже выполняющих откат изменений для обеспечения соответствия требованиям политики. Дополнительные сведения о режимах определения и об их сопоставлении с аспектами (понятием управления на основе политик (PBM), также обсуждаемым в этом блоге) см. в разделе Блог об управлении SQL Server на основе политик.
Политики можно экспортировать и импортировать в виде XML-файлов для их определения и применения на многих экземплярах серверов. Кроме того, в среде SQLServerManagement Studio и в представлении зарегистрированных серверов политики можно определять на многих серверах, зарегистрированных в локальной группе серверов или в группе сервера централизованного управления.
В предыдущих версиях SQL Server может быть реализована не вся функциональность управления на основе политик. Тем не менее, функцию составление отчетов политики можно использовать на серверах SQL Server 2005 и SQL Server 2000. Дополнительные сведения об использовании управления на основе политик см. в разделе Администрирование серверов с помощью управления на основе политик в электронной документации по SQLServer. Дополнительные сведения о самой технологии политик с примерами см. в разделе Руководство по обеспечению соответствия в SQL Server 2008.
Прогнозируемая производительность и параллелизм
Многие администраторы сталкиваются со значительными трудностями при поддержке серверов SQLServers с постоянно изменяющимися рабочими нагрузками и обеспечении предсказуемого уровня производительности (или минимизации расхождений в планах запросов и производительности). Неожиданные изменения производительности при выполнении запросов, изменения планов запросов и/или общие связанные с производительностью проблемы могут быть вызваны рядом причин, в том числе повышением нагрузки от выполняющихся на сервере SQLServer приложений или обновлением версии самой базы данных. Предсказуемость выполняемых на сервере SQLServer запросов и операций значительно облегчает достижение и поддержание целей по уровню доступности, производительности и/или непрерывности бизнес-деятельности (выполнение соглашений об уровне обслуживания и уровне операционной поддержки).
В SQLServer 2008 некоторые функции изменены для увеличения прогнозируемости производительности. Так, внесены определенные изменения в структуры планов SQLServer 2005 (закрепление планов) и добавлена возможность управления эскалацией блокировок на уровне таблиц. Оба улучшения способствуют более прогнозируемому и упорядоченному взаимодействию между приложением и базой данных.
Во-первых, структуры планов (Plan Guide):
В SQL Server 2005 было реализовано улучшение стабильности и предсказуемости запросов с помощью новой на тот момент функции — «структур планов», содержавших указания для выполнения запросов, которые нельзя было изменить непосредственно в приложении. Дополнительные сведения см. в техническом документе Принудительное использование планов запросов . Хотя подсказка в запросе USE PLAN является очень мощной функцией, она поддерживала только операции SELECT DML и часто была неудобна в применении из-за чувствительности структур планов к форматированию.
В SQL Server 2008 механизм структур планов расширен в двух направлениях: во-первых, расширена поддержка подсказки в запросе USE PLAN, которая теперь совместима со всеми инструкциями DML (INSERT, UPDATE, DELETE, MERGE); во-вторых, введена новая функция закрепления планов, позволяющая непосредственно создавать структуру плана (закрепление) любого плана запроса, существующего в кэше планов SQL Server, как показано в следующем примере.
sp_create_plan_guide_from_handle
@name = N’MyQueryPlan’,
@plan_handle = @plan_handle,
@statement_start_offset = @offset;
У структуры планов, созданной любым способом, имеется область базы данных; она хранится в таблице sys.plan_guides. Структуры планов лишь влияют на процесс выбора плана запроса оптимизатором, но не избавляют от необходимости компиляции запроса. Также добавлена функция sys.fn_validate_plan_guide, для проверки существующих структур планов SQL Server 2005 и обеспечения их совместимости с SQL Server 2008. Закрепление планов доступно в выпусках SQL Server 2008 Standard, Enterprise и Developer.
Во-вторых, эскалация блокировок:
Эскалация блокировки часто вызывало проблемы блокировки, а иногда даже взаимоблокировку. Устранять эти проблемы приходилось администратору. В предыдущих версиях SQLServer можно было управлять эскалацией блокировок (флаги трассировки 1211 и 1224), но это было возможно только для детализации на уровне экземпляра. Для одних приложений это устраняло проблему, а для других вызывало еще большие проблемы. Другим недостатком алгоритма эскалации блокировок в SQL Server 2005 было то, что блокировки секционированных таблиц укрупнялись напрямую до уровня таблиц, а не до уровня секций.
SQLServer 2008 предлагает решение обеих проблем. В нем реализован новый параметр, позволяющий управлять эскалацией блокировки на уровне таблиц. С помощью команды ALTERTABLE можно выбрать отключение эскалации или эскалацию до уровня секций для секционированных таблиц. Обе эти возможности улучшают масштабируемость и производительность без нежелательных побочных эффектов, затрагивающих другие объекты в экземпляре. Эскалация блокировки задается на уровне объекта базы данных и не требует внесения каких-либо изменений в приложения. Оно поддерживается во всех выпусках SQLServer 2008.
Resource Governor
Поддерживать устойчивый уровень обслуживания, предотвращая бесконтрольные запросы и обеспечивая выделение ресурсов для важнейших рабочих загрузок, раньше было сложно. Отсутствовала возможность гарантировать выделение определенного объема ресурсов набору запросов, отсутствовало управление приоритетами доступа. У всех запросов были равные права на доступ ко всем доступным ресурсам.
Новая функция SQL Server 2008 — «Регулятор ресурсов»(Resource Governor) — помогает справиться с этой проблемой, давая возможность дифференцировать рабочие нагрузки и распределять ресурсы в соответствии с потребностями пользователей. Ограничения регулятора ресурсов легко перенастраиваются в реальном времени при минимальном воздействии на выполняющиеся рабочие нагрузки. Распределение рабочих нагрузок по пулу ресурсов настраивается на уровне соединения, причем этот процесс полностью прозрачен для приложений.
На диаграмме ниже показан процесс выделения ресурсов. В данном сценарии настраиваются три пула рабочих нагрузок (рабочие нагрузки Admin, OLTP и Report), после чего пулу рабочих нагрузок OLTP присваивается высший приоритет. В то же время настраиваются два пула ресурсов (пул Pool и пул Application) с заданными ограничениями по объему памяти и времени процессора (ЦП). На последнем этапе рабочая нагрузка Admin назначается пулу Admin, а рабочие нагрузки OLTP и Report назначаются пулу Application.
Ниже приведены особенности, которые следует учитывать при использовании регулятора ресурсов.
— Регулятор ресурсов использует учетные данные входа, имя узла или имя приложения в качестве «идентификатора пула ресурсов», поэтому использование для приложения одного имени входа при определенных количествах клиентов, приходящихся на один сервер, может усложнить создание пулов.
— Не поддерживается группировка объектов на уровне базы данных, при которой доступ к ресурсам регулируется на основе объектов базы данных, к которым осуществляется доступ.
— Регулятор ресурсов позволяет управлять ресурсами только в пределах одного экземпляра SQL Server. Для управления несколькими экземплярами SQL Server или процессами на сервере из единого источника следует рассмотреть возможность применения Диспетчера системных ресурсов Windows.
— Настраивать можно только использование ресурсов процессора и памяти. Управление ресурсами ввода-вывода не реализовано.
— Динамическое переключение рабочих нагрузок между пулами ресурсов после соединения невозможно.
— Регулятор ресурсов поддерживается только в выпусках SQL Server 2008 Enterprise и Developer и может использоваться только для ядра базы данных SQL Server; управление службами SQL Server Analysis Services (SSAS), SQL ServerIntegration Services (SSIS) и SQL Server Reporting Services (SSRS) не поддерживается.
Прозрачное шифрование данных (TDE)
Многие организации уделяют очень большое внимание вопросам безопасности. Существует множество различных слоев, защищающих один из самых ценных активов организации — ее данные.Чаще всего организации успешно защищают используемые данные с помощью мер физической защиты, брандмауэров и строгих политик ограничения доступа. Однако, в случае утери физического носителя с данными, например, диска или ленты с резервной копией, все перечисленные меры обеспечения безопасности оказываются бесполезными, так как злоумышленник может просто восстановить базу данных и получить полный доступ к данным. SQL
Server 2008 предлагает решение этой проблемы путем прозрачного шифрования данных ( TDE ). При шифровании TDE данные в операциях ввода-вывода шифруются и дешифруются в реальном времени; файлы данных и журналов шифруются с помощью ключа шифрования базы данных ( DEK ). DEK — это симметричный ключ, защищаемый сертификатом, который хранится в базе данных >master сервера, или асимметричный ключ, защищаемый модулем расширенного управления ключами ( EKM ).
Функция TDE защищает «неактивные» данные, поэтому данные в файлах MDF, NDF, и LDF невозможно просматривать с помощью редактора шестнадцатеричных данных или каким-либо другим способом. Однако активные данные, например, результаты выполнения инструкции SELECT в среде SQL Server Management Studio, останутся видимыми для пользователей, у которых имеются права на просмотр таблицы. Кроме того, поскольку функция TDE реализована на уровне базы данных, база данных может задействовать индексы и ключи для оптимизации запросов. TDE не следует путать с шифрованием на уровне столбцов — это отдельная функция, позволяющая шифровать даже активные данные.
Шифрование базы данных — одноразовый процесс, который можно запустить командой Transact — SQL или из среды SQL Server Management Studio , после чего он выполняется в фоновом потоке. Состояние шифрования или дешифровки можно отслеживать с помощью динамического административного представления sys.dm_database_encryption_keys. Во время проведенных лабораторных испытаний шифрование базы данных размером 100 Гб с применением алгоритма шифрования AES _128 заняло около часа. Хотя накладные расходы при использовании TDE определяются в основном рабочей нагрузкой приложения, в некоторых из проведенных испытаний эти дополнительные расходы составили менее 5%. Следует учитывать одну особенность, которая может повлиять на производительность: если TDE используется в любой из баз данных на экземпляре, то также шифруется и системная база данных tempDB. Наконец, при одновременном использовании различных функций необходимо учитывать следующее:
- При использовании сжатия резервных копий для сжатия зашифрованной базы данных размер сжатой резервной копии будет большим, чем без использования шифрования, так как зашифрованные данные сжимаются плохо.
- Шифрование базы данных не влияет на сжатие данных (строчное или страничное).
TDE позволяет организации обеспечить соответствие требованиям нормативных стандартов и общему уровню защиты данных. TDE поддерживается только в выпусках SQL Server 2008 Enterprise и Developer ; его активация не требует внесения изменений в существующие приложения. Дополнительные сведения см. в разделе Шифрование данных в выпуске SQL Server 2008 Enterprise или в обсуждении в Руководстве по обеспечению соответствия требованиям в SQL Server 2008 на тему Прозрачное шифрование данных.
Подводя итог, можно сказать, что в SQL Server 2008 предлагаются функции, усовершенствования и возможности, облегчающие работу администратора базы данных. 10 самых популярных из них описаны здесь, но в SQL Server 2008 есть и много других возможностей, упрощающих жизнь администраторов и других пользователей. Списки «10 лучших функций» по другим направлениям работы с SQL Server можно найти в других статьях «Лучшие 10 … в SQL Server 2008» на этом сайте. Полный список функций и их подробное описание см. в электронной документации по SQL Server и на обзорном веб-сайте SQL Server 2008.