Alexander Gladchenko

HDD DEAD?!

gladchenko — Tue, 20 Oct 2009 13:24:00 GMT

…и наконец моё любопытство возобладало, и я решил таки заглянуть, что же такого понаделел HP в TPC-H…

Не удивляйтесь, но поначалу я не предавал особого значения появляющимся с завидной регулярностью (раз в месяц) новым, ничем с виду не примечательным результатам. Но, когда появился третий из них, я заметил тенденцию, что они становятся с каждым разом немного ХУЖЕ и ДОРОЖЕ. Именно тогда мне стало любопытно, в чём там "порылась собака"…
Конечно, виною всему было то, как представлены результаты в сводной таблице: Complete TPC-H Results List - Sorted by Date Submitted
Просто, в представлении на сайте не видно главного изменения, поскольку испокон веков эта составляющая была неизменной и только в последние год - два ситуация начала выправляться к лучшему. На самом деле, HP в последнем из трёх своих результатов показал, как SAS диски могут оказаться более дорогим решением, чем SSD диски, при близкой производительности.
Вот несколько видоизменённое представление результатов, с детализацией по конфигурации дисковой подсистемы:

Ссылки на подробные описания тестов:

Результаты потрясающие! Оказывается решение на базе SSD получается чуть ли не в двое дешевле, чем на привычных нам SAS.
Похоже, гегемонии производителей жёстких дисков приходит долгожданный конец, а точнее, последний рудимент зари IBM PC, этот кошмарный механический монстр, самое слабое звено любого компьютера - HDD может уйти в небытие!

Tips for DBA: Замена для стандартного "Back Up Database Task"

gladchenko — Wed, 07 Oct 2009 14:08:00 GMT

Очень часто получается так, что реальные бизнес -требования оказываются сложнее, чем возможности мастеров программного инструментария, поставляемого разными производителями ПО для задач администрирования SQL Server 2008. Например, недавно мне стало недостаточно гибкости мастера создания задачи резервного копирования базы данных для стандартного плана обслуживания БД. У меня возникла необходимость делать копию в несколько фалов на разных дисковых массивах и поддерживать хронологию копий по единым правилам. В несколько файлов выполнять резервное копирование бывает необходимо для повышения производительности этой операции, например, как это рекомендовано в этой статье: "A Technical Case Study: Fast and Reliable Backup and Restore of Multi-Terabytes Database over the Network".
Мастер SSMS может либо поддерживать хронологию для одного файла копий, либо копировать в фиксированные имена нескольких указанных файлов. К счастью, совершенно не составляет труда заменить задачу резервного копирования на задачу исполнения сценария T-SQL, в котором выполнить нужную работу. Ниже представлен соответствующий шаблон сценария, взяв который за основу можно составить необходимую задачу исполнения сценария T-SQL.

DECLARE @path varchar(128) DECLARE @DBName AS nvarchar(4000) = 'ИМЯБАЗЫДАННЫХ' DECLARE @File1 AS nvarchar(4000), @File2 AS nvarchar(4000), @File3 AS nvarchar(4000) -- Если диск для копий один, и он корректно задан, узнать путь к папке копий можно так: EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer', @value_name='BackupDirectory', @value=@path OUTPUT -- Формируем часть имени файла, в которой фиксируется имя БД и время создания копии DECLARE @FileName AS nvarchar(4000) = @path + '\' + @DBName +'\' + @DBName + '_' + 'backup_' + CONVERT(nvarchar(4),YEAR(CURRENT_TIMESTAMP), 112 ) + '_' + CASE MONTH(CURRENT_TIMESTAMP) WHEN 1 THEN '01' WHEN 2 THEN '02' WHEN 3 THEN '03' WHEN 4 THEN '04' WHEN 5 THEN '05' WHEN 6 THEN '06' WHEN 7 THEN '07' WHEN 8 THEN '08' WHEN 9 THEN '09' ELSE CONVERT(nvarchar(2),MONTH(CURRENT_TIMESTAMP), 112 ) END + '_' + CASE DAY(CURRENT_TIMESTAMP) WHEN 1 THEN '01' WHEN 2 THEN '02' WHEN 3 THEN '03' WHEN 4 THEN '04' WHEN 5 THEN '05' WHEN 6 THEN '06' WHEN 7 THEN '07' WHEN 8 THEN '08' WHEN 9 THEN '09' ELSE CONVERT(nvarchar(2),DAY(CURRENT_TIMESTAMP), 112 ) END + '_' + REPLACE(REPLACE(CAST(CONVERT(time(7),CURRENT_TIMESTAMP, 109 ) AS nvarchar(14)),':',''),'.','_'); -- Добавляем к пути и имени файла идентификаторы и расширения SELECT @File1 = @FileName + + '01.bak', @File2 = @FileName + + '02.bak', @File3 = @FileName + + '03.bak' -- Запускаем резервное копирование в три файла. BACKUP DATABASE [ИМЯБАЗЫДАННЫХ] TO DISK = @File1, DISK = @File2, DISK = @File3 WITH NOFORMAT, NOINIT, SKIP, REWIND, NOUNLOAD, COMPRESSION, STATS = 10

Как справиться с PAGELATCH при больших INSERT-нагрузках

gladchenko — Mon, 28 Sep 2009 12:30:00 GMT

По материалам статьи: "Resolving PAGELATCH Contention on Highly Concurrent INSERT Workloads".
Авторы: Thomas Kejser, Lindsey Allen, Arvind Rao и Michael Thomassy
При участии и с рецензиями: Mike Ruthruff, Lubor Kollar, Prem Mehra, Burzin Patel, Michael Thomassy, Mark Souza, Sanjay Mishra, Peter Scharlock, Stuart Ozer, Kun Cheng и Howard Yin
Перевод Александра Гладченко

Введение

Недавно, мы проводили лабораторные испытания в Microsoft Enterprise Engineering Center, при которых использовалась большая рабочая нагрузка, характерная для OLTP систем. Целью этой лабораторной работы было определить, что случится при увеличении числа процессоров с 64 до 128, при обслуживании Microsoft SQL Server интенсивной рабочей нагрузки (примечание: эта конфигурация была ориентирована на релиз Microsoft SQL Server 2008 R2). Рабочая нагрузка представляла собой хорошо распараллеленные операции вставки, направляемые в несколько больших таблиц.
Рабочая нагрузка масштабировалась до 128 процессорных ядер, но в статистике ожиданий было очень много кратких блокировок PAGELATCH_UP и PAGELATCH_EX. Средняя продолжительность ожидания была десятки миллисекунд, и таких ожиданий было очень много. Такое их количество оказалось для нас неожиданностью, ожидалось, что продолжительность не будет превышать несколько миллисекунд.
В этой технической заметке вначале будет описано, как диагностировать подобную проблему и как для разрешения подобной проблемы использовать секционирование таблиц.

Диагностика проблемы

Когда в sys.dm_os_wait_stats наблюдается большое число PAGELATCH, с помощью sys.dm_os_waiting_tasks можно определить сессию и ресурс, который задача ожидает, например, с помощью этого сценария:

SELECT session_id, wait_type, resource_description FROM sys.dm_os_waiting_tasks WHERE wait_type LIKE 'PAGELATCH%'

Пример результата:

В столбце resource_description указаны местоположения страниц, к которым ожидают доступ сессии, местоположение представлено в таком формате:

<database_id>:<file_id>:<page_id>

Опираясь на значения в столбце resource_description, можно составить довольно сложный сценарий, который предоставит выборку всех попавших в список ожидания страниц:

SELECT wt.session_id, wt.wait_type, wt.wait_duration_ms , s.name AS schema_name , o.name AS object_name , i.name AS index_name FROM sys.dm_os_buffer_descriptors bd JOIN ( SELECT * , CHARINDEX(':', resource_description) AS file_index , CHARINDEX(':', resource_description , CHARINDEX(':', resource_description)) AS page_index , resource_description AS rd FROM sys.dm_os_waiting_tasks wt WHERE wait_type LIKE 'PAGELATCH%' ) AS wt ON bd.database_id = SUBSTRING(wt.rd, 0, wt.file_index) AND bd.file_id = SUBSTRING(wt.rd, wt.file_index, wt.page_index) AND bd.page_id = SUBSTRING(wt.rd, wt.page_index, LEN(wt.rd)) JOIN sys.allocation_units au ON bd.allocation_unit_id = au.allocation_unit_id JOIN sys.partitions p ON au.container_id = p.partition_id JOIN sys.indexes i ON p.index_id = i.index_id AND p.object_id = i.object_id JOIN sys.objects o ON i.object_id = o.object_id JOIN sys.schemas s ON o.schema_id = s.schema_id

Сценарий показал, что ожидаемые страницы относятся к кластеризованному индексу, определённому первичным ключом таблицы с представленной ниже структурой:

CREATE TABLE HeavyInsert ( ID INT PRIMARY KEY CLUSTERED , col1 VARCHAR(50) ) ON [PRIMARY]

Что происходит, почему возникает очередь ожиданий к страницам данных индекса - всё это будет рассмотрено в этой технической заметке.

Основная информация

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

В журнале транзакций создаётся запись о том, что строка изменилась.
Осуществляется поиск в В-дереве местонахождения той страницы, куда должна будет попасть новая запись.
Осуществляется наложение на эту страницу краткой блокировки PAGELATCH_EX, которая призвана воспрепятствовать изменениям из других потоков.
Осуществляется добавление строки на страницу и, если это необходимо, осуществляется пометка этой страницы как "грязной".
Осуществляется снятие краткой блокировки со страницы.

В итоге, страница должна будет быть сброшена на диск процессом контрольной точкой или отложенной записи.
Если же все вставки строк направлены на ту же самую страницу, можно наблюдать рост очереди к этой странице. Даже притом, что краткая блокировка весьма непродолжительна, она может стать причиной конкуренции при высоком параллелизме рабочей нагрузки. У нашего клиента, первый и единственный столбец в индексе являлся монотонно возрастающим ключом. Из-за этого, каждая новая вставка шла на ту же самую страницу в конце В-дерева, пока эта страница не была заполнена. Рабочие нагрузки, которые используют в качестве первичного ключа IDENTITY или другие столбцы с последовательно увеличивающимися значениями, также могут столкнуться с подобной проблемой, если распараллеливание достаточно высоко.

Решение

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

CREATE PARTITION FUNCTION pf_hash (INT) AS RANGE LEFT FOR VALUES (0,1,2) CREATE PARTITION SCHEME ps_hash AS PARTITION pf_hash ALL TO ([PRIMARY])

Представленный выше пример использует четыре секции. Число необходимых секций зависит от числа активных процессов, выполняющих операции INSERT в описанную выше таблицу. Есть некоторая сложность в секционировании таблицы с помощью хэш-столбца, например, в том что всякий раз, когда происходит выборка строк из таблицы, будут затронуты все секции. Это означает, что придётся обращаться более чем к одному В-дереву, т.е. не будет отброшенных оптимизатором за ненадобностью ненужных секций. Будет дополнительная нагрузка на процессоры и некоторое увеличение времени ожиданий процессоров, что побуждает минимизировать число планируемых секций (их должно быть минимальное количество, при котором не наблюдается PAGELATCH). В рассматриваемом нами случае, в системе нашего клиента имелось достаточно много резерва в утилизации процессоров, так что было вполне возможно допустить небольшую потерю времени для инструкций SELECT, и при этом увеличить до необходимых объёмов норму инструкций INSERT.
Ещё одной сложностью является то, что нужен дополнительный столбец, по которому будет выполняться секционирование, т.е. на основании значения которого будут распределяться вставки по четырем секциям. Такого столбца изначально в сценарии Microsoft Enterprise Engineering Center не было. Однако, его достаточно просто было создать. Для этого использовался тот факта, что столбец ID монотонно увеличивается с приращением равным единице, и здесь легко применима довольно простая хеш-функция:

CREATE TABLE HeavyInsert_Hash( ID INT NOT NULL , col1 VARCHAR(50) , HashID AS CAST(ABS(ID % 4) AS TINYINT) PERSISTED NOT NULL)

С помощью столбца HashID, вставки в четыре секции будут выполняться циклически.
Создаём кластеризованный индекс следующим образом:

CREATE UNIQUE CLUSTERED INDEX CIX_Hash ON HeavyInsert_Hash (ID, HashID) ON ps_hash(HashID)

Используя новую схему таблицы с секционированием вместо первоначального варианта таблицы, мы сумели избавиться от очередей PAGELATCH и повысить скорость вставки. Этого удалось достичь за счёт балансировки вставки между несколькими секциями и высокого параллелизма. Вставка происходит в несколько страниц, и каждая страница размещается в своей собственной структуре В-дерева. Для нашего клиента удалось повысить производительность вставки на 15 процентов, и справится с большой очередью PAGELATCH к горячей странице индекса одной таблицы. Но при этом удалось также оставить достаточно большой резерв циклов процессоров, что оставило возможность сделать аналогичную оптимизацию для другой таблицы, тоже с высокой нормой вставки.
Строго говоря, суть этой уловки в оптимизации логической схемы первичного ключа таблицы. Однако, потому что ключ просто стал длиннее на величину хеш-функции относительно изначального ключа, дубликатов для столбца ID удалось избежать. Уникальные индексы по единственному столбцу таблицы зачастую становятся причиной проблем с очередями PAGELATCH. Но даже если эту проблему удастся устранить, у таблицы может оказаться другой, некластеризованный индекс, который будет подвержен той же самой проблеме. Как правило, проблема наблюдается для уникальных ключей на единственном столбце, где каждая вставка попадает на одну и ту же страницу. Если и у других таблиц индексы подвержены проблеме с PAGELATCH, можно применить эту же уловку с секционированием к индексам таких таблиц, используя такой же ключ с хэшем в качестве первичного ключа.
Не всегда возможно внести изменения в приложение, особенно, если оно является плодом усилий третьих фирм. Но если изменение запросов возможно, становится доступной их оптимизация за счёт добавления к ним условий фильтрации по предикатам первичного ключа.

Пример: Чтобы отбросить ненужные секции, можно внести следующие изменения в сценарий:

SELECT * FROM HeavyInsert_Hash WHERE ID = 42

Который после изменений будет выглядеть так:

SELECT * FROM HeavyInsert_Hash WHERE ID = 42 AND HashID = 42 % 4

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

Query Processor Modeling Extensions

gladchenko — Mon, 31 Aug 2009 13:13:00 GMT

По материалам стать Иан Джоз (Ian Jose): Query Processor Modelling Extensions in SQL Server 2005 SP1
Перевод Александра Гладченко
Редактура Алексея Халако

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

Появившееся в SQL Server 2005 SP1 расширение оптимизатора Query Processor Modelling Extensions можно включить с помощью флага трассировки 2301. Это расширение обеспечивает возможность системы моделирования оптимизатора запросов выбирать более производительные планы исполнения сложных запросов к базе данных. Улучшенное моделирование планов запроса в некоторых случаях может привести к существенному повышению производительности исполнения запросов. Однако, эти расширения моделирования процессора запросов, могут привести к заметному увеличенному времени компиляции, и поэтому рекомендуются для использования только в тех приложениях, в которых компиляций бывает немного, и они происходят нечасто. Были добавлены следующие расширения моделирования:

Целочисленное моделирование (Integer Modelling)

Обычно, моделирование предполагает, что между кодовыми точками чисел двойной ширины значения шага гистограммы распределены одинаково. Расширение этой модели запоминает, что значения могут встречаться только для целочисленных кодовых точек, и за счёт этого оценки множества элементов для фильтров неравенства выполняются точнее.
Углублённое использование гистограммы (Comprehensive Histogram Usage)

Обычно, гистограммы игнорируются, когда количество элементов отношения оказывается меньше числа шагов в гистограмме. Это - логика, которая определяет вероятность того, что гистограмма и далее будет описывать отношение. Это расширение применяет гистограмму в оценке количества элементов независимо от оценки количества элементов для отношения.
Предположение основного ограничения (Base Containment Assumption)

Обычно, при соединении двух отношений мы предполагаем, что для Х определенных кодовых точек (которые относятся к тому же самому ключевому диапазону, что и отношение R) при соединении с Y уникальных точек того же самого ключевого диапазона, что и в отношении S, всегда найдётся пара MIN(X,Y). Такое допущение называют "Простое ограничение". Мы предполагаем, что наименьшее число из уникальных точек кода соотносится с точками кода на другой стороне. Это моделирование игнорирует относительную совокупность уникальных точек кода в основных формах R и S, и также игнорирует любую фильтрацию, которая была применена к основными формами R и S перед соединением. Основное ограничение основывается на предположение, что ограничение применяется только к основным отношениям и использует вероятностные методы вычисления степени соединения. Кроме того, подразумевается, что модели фильтров применены правильно, так как их поведение очень отличается от ортогональных фильтров.
Углублённое изменение отображения плотности (Comprehensive Density Remapping)

Плотность (Density) - это мера числа дубликатов значений для каждого уникального значения. Обычно, после CONVERT-тации столбцов довольно незначительное число плотностей для этих столбцов заново отображается как новые определения столбцов. Обратите внимание, что такие операции, как конвертация довольно редко меняют плотность столбца. Когда задействовано это расширение моделирования, все такие повторные отображения будут обязательно сделаны, что делает возможным последующее использование плотности для целей оценки количества элементов. В некоторых случаях, это может привести к чрезмерной утилизации памяти.
Углублённое согласование плотности (Comprehensive Density Matching)

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

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

Флаги трассировки, рекомендованные Майкрософт для эталонного теста производительности TPC-E

gladchenko — Mon, 24 Aug 2009 10:05:00 GMT

По материалам статьи: Microsoft SQL Server 2008 TPC-E Trace Flags
Вольный перевод Александра Гладченко

Наиболее часто используемым способом изменения поведения SQL Server является выставление флагов трассировки. Следующие флаги трассировки поддерживаются в настоящее время Майкрософт для публикации результатов тесов производительности TPC-E. Если сотрудники Майкрософт рекомендуют Вам использовать другие флаги трассировки, которые не представлены в списке ниже, пожалуйста, сообщите об этом Джейми Редингу (Jamie.Reding@Microsoft.com) или Чарльзу Левину (Charles.Levine@Microsoft.com) до того, как вы опубликуете использование этих флагов.
Единственными поддерживаемыми для SQL Server 2008 флагами трассировки для TPC-E являются флаги: -T661 -T834 -T3502 -T8744.
Единственным поддерживаемыми для SQL Server 2008 параметрами запуска сервера для теста TPC-E являются параметры: -c -E -x, которые хорошо описаны в BOL.

661: Disable the ghost record removal process

Флаг трассировки 661 отключает системный процесс удаления фантомных записей. Фантомные записи появляются в результате исполнения операций удаления, после которых удалённые записи могут оставаться в файле как фантомные записи. Через некоторое время, удаленные записи вычищаются процессом удаления фантомных записей. Когда этот процесс отключается, удаленные записи не вычищаются. Поэтому, место, которое занимают удаленные записи, не высвобождается. Это влияет на занимаемое данными место и на производительность операций просмотра.
Флаг трассировки 661 всегда действует в контексте всего сервера, т.е. имеет глобальный контекст. Вы можете включать флаг трассировки 661 при запуске сервера или в пользовательском сеансе.

834: Use Microsoft Windows large-page allocations for the buffer pool

Флаг трассировки 834 применяется в SQL Server 2008 для включения механизма распределения буферному пулу больших страницы памяти, которыми умеют оперировать последние версии Microsoft Windows. У разных аппаратных платформ может быть разный размер страниц, он может изменяться от 2 до 16 Мбайт. Большие страницы распределяются при запуске и сохраняются на протяжении всей жизни процесса. Флаг трассировки 834 повышает производительность, увеличивая эффективность TLB буфера процессоров.
Флаг трассировки 834 применим только к 64-битным версиям SQL Server 2008. Включить флаг трассировки 834 может только та учётная запись, для которой разрешена локальная политика "Lock pages in memory". Включать флаг трассировки 834 можно только при запуске SQL Server.
Флаг трассировки 834 может препятствовать запуску сервера, если память сильно фрагментирована и это мешает распределению больших страниц. Поэтому, флаг трассировки 834 безопаснее использовать на серверах, которые обслуживают только SQL Server 2008.
Для получения более подробной информации о поддержке больших страниц Windows, перейдите на следующую страницу сайта Microsoft Developer Network (MSDN): Large-Page Support.

3502: Log Database Checkpoint Start and End times in the SQL Server ErrorLog

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

8744: Disable pre-fetching for ranges

Флаг трассировки 8744 отключает предварительную выборку для таких операторов, как "Nested Loops". Неуместное использование этого флага может спровоцировать дополнительные физические чтения, при реализации плана с оператором "Nested Loops".
Когда флаг трассировки 8744 включён при запуске сервер, он получает глобальный контекст. Когда он включен в сеансе пользователя, контекст ограничивается сеансом.

Флаги трассировки, рекомендованные Майкрософт для эталонного теста производительности TPC-C

gladchenko — Fri, 21 Aug 2009 15:07:00 GMT

По материалам статьи: Microsoft SQL Server 2005 TPC-C Trace Flags
Вольный перевод Александра Гладченко

652: Disable page pre-fetching
Флаг трассировки 652 отключает предварительную выборку страниц во время полных просмотров. Если флаг трассировки 652 включается при запуске SQL Server, он получает глобальный контекст. Если он взводится в пользовательском сеансе, то контекст ограничивается сеансом.

661: Disable the ghost record removal process
Флаг трассировки 661 отключает системный процесс удаления фантомных записей. Фантомные записи появляются в результате исполнения операций удаления, после которых удалённые записи могут оставаться в файле как фантомные записи. Через некоторое время, удаленные записи вычищаются процессом удаления фантомных записей. Когда этот процесс отключается, удаленные записи не вычищаются. Поэтому, место, которое занимают удаленные записи, не высвобождается. Это влияет на занимаемое данными место и на производительность операций просмотра.
Флаг трассировки 661 всегда действует в контексте всего сервера, т.е. имеет глобальный контекст. Вы можете включать флаг трассировки 661 при запуске сервера или в пользовательском сеансе.

834: Use Microsoft Windows large-page allocations for the buffer pool
Флаг трассировки 834 применяется в SQL Server 2005 для включения механизма распределения буферному пулу больших страницы памяти, которыми умеют оперировать последние версии Microsoft Windows. У разных аппаратных платформ может быть разный размер страниц, он может изменяться от 2 до 16 Мбайт. Большие страницы распределяются при запуске и сохраняются на протяжении вей жизни процесса. Флаг трассировки 834 повышает производительность, увеличивая эффективность TLB буфера процессоров.
Флаг трассировки 834 применим только к 64-битным версиям SQL Server 2005. Включить флаг трассировки 834 может только та учётная запись, для которой разрешена локальная политика "Lock pages in memory". Включать флаг трассировки 834 можно только при запуске SQL Server.
Флаг трассировки 834 может препятствовать запуску сервера, если память сильно фрагментирована и это мешает распределению больших страниц. Поэтому, флаг трассировки 834 безопаснее использовать на серверах, которые обслуживают только SQL Server 2005.
Для получения более подробной информации о поддержке больших страниц Windows, перейдите на следующую страницу сайта Microsoft Developer Network (MSDN): Large-Page Support.

836: Use the max server memory option for the buffer pool
Флаг трассировки 836 нужен для того, чтобы размер буферного пула при запуске SQL Server 2005 устанавливался на основе значения параметра глобальной конфигурации "max server memory", вместо того, чтобы использовать в качестве ориентира размер физической памяти. Вы можете использовать флаг трассировки 836, чтобы уменьшить число описателей буфера, которые распределяются при запуске службы в 32-разрядном режиме Address Windowing Extensions (AWE). Это позволяет предоставить в памяти больше места для заимствованных из буферного пула страниц.
Флаг трассировки 836 применяется только с 32-разрядным версиям SQL Server 2005, которым разрешено распределение страниц через окно AWE. Включить флаг трассировки 836 можно только при запуске.

1228 и 1229 - Enable lock partitioning and disable lock partitioning
По умолчанию, секционирование блокировок становится возможным, когда сервер имеет 16 или более процессоров. Иначе, секционирование блокировок заблокировано. Флаг трассировки 1228 включает секционирование блокировок для двухпроцессорных и более систем. Для отключения секционирования блокировок используется флаг трассировки 1229.
Секционирование блокировок полезно на мультипроцессорных серверах, где для некоторых таблиц очень высоки нормы блокировок. Включить флаги трассировки 1228 и 1229 можно только при запуске.

2301: Enable advanced decision support optimizations
Флаг трассировки 2301 включает дополнительную оптимизацию, которая улучшает работу запросов систем поддержки принятия решений, характерных большим объёмом затрагиваемых запросом данных.
Когда флаг трассировки 2301 включается при запуске сервера, он получает глобальный контекст, иначе, он будет иметь контекст сеанса.

Флаги трассировки, которые отключают некоторые кольцевые буферы

Кольцевой буфер - внутренний диагностический механизм SQL Server 2005, который может использоваться для сбора дополнительной информации о сервере. Как правило, эту информацию используют для исследования проблем в работе сервера. Посмотреть накапливаемую кольцевыми буферами информацию можно с помощью динамического административного представления sys.dm_os_process_memory.
Отключение кольцевых буферов в целом приводит к повышению производительности. Однако, отключение кольцевых буферов исключает возможность использования диагностической информации для передачи в службу поддержки Microsoft и может помешать успешному поиску решения возможных проблем.
Представленные ниже флаги трассировки отключают разные кольцевые буферы.

8011: Disable the ring buffer for Resource Monitor
Флаг трассировки 8011 отключает сбор дополнительной диагностической информации, относящейся к Resource Monitor. Информация этого кольцевого буфера можете использовать для диагностики состояния исчерпания памяти. Флаг трассировки 8011 всегда применяется ко всему серверу и имеет глобальный контекст. Вы можете включить флаг 8011 при запуске или в сеансе пользователя.

8012: Disable the ring buffer for schedulers
Флаг трассировки 8012 отключает кольцевой буфер планировщиков. Информация этого кольцевого буфера можете использовать для диагностики проблем планирования потоков процессорам. Например, информацию этого кольцевого буфера можно использовать для выявления проблем, проявляющихся как потеря отклика от SQL Server 2005. Включать флаг трассировки 8012 можно только при запуске сервера.

8018 and 8019: Disable the exception ring buffer and disable stack collection for the exception ring buffer Этот кольцевой буфер хранит данные о последних 256 исключениях, которые были зафиксированы для узла. Каждая запись содержит немного информации об ошибке и трассировку стека. Запись добавляется кольцевым буфером в момент срабатывания исключения.
Флаг трассировки 8018 отключает создание кольцевого буфера, и после этого информация об исключения не сохраняется. Флаг трассировки 8019 отключает сбор трассировок стека, когда фиксируется информация об исключении. Флаг 8019 нельзя использовать без предварительного включения флага трассировки 8018. Отключение этого кольцевого буфера затрудняет диагностику связанных с проблемами исключений, т.е. с внутренними ошибками сервера. Включить флаги трассировки 8018 и 8019 можно только при запуске сервера.

8020: Disable working set monitoring
SQL Server 2005 использует информацию о размере рабочего множества, когда возникает необходимость интерпретировать глобальные сигналы операционной системы о состояния оперативной памяти. Флаг трассировки 8020 исключает из этой интерпретации информацию о размере рабочего множества. Этот флаг трассировки применяется только к обычным страницам памяти. Например, этот флаг трассировки не относится к заблокированным страницам или большим страницам.
Флаг трассировки 8020 может быть полезен, когда операционная система постоянно урезает рабочее множество обслуживающего SQL Server 2005 сервера, и когда сервер неспособен высвобождать память из-за большой рабочей нагрузки. В таком случае, попытки мониторинга приведут к напрасному расходованию циклов процессора. Флаг трассировки 8020 нужно использовать с осторожностью и только после глубокого тестирования на реальных рабочих нагрузках. Кроме того, использовать флаг трассировки 8020 нужно после тщательного выбора значения глобального параметра "max server memory". Неуместное использование этого флага может привести к сильным листаниям.
Включить флаг трассировки 8020 можно только при запуске сервера.

8744: Disable pre-fetching for ranges
Флаг трассировки 8744 отключает предварительную выборку для таких операторов, как "Nested Loops". Неуместное использование этого флага может спровоцировать дополнительные физические чтения, при реализации плана с оператором "Nested Loops".
Когда флаг трассировки 8744 включён при запуске сервер, он получает глобальный контекст. Когда он включен в сеансе пользователя, контекст ограничивается сеансом.

Настройка Windows Server 2008/2003 x64 для обслуживания SQL Server 2008

gladchenko — Wed, 19 Aug 2009 14:37:00 GMT

По состоянию на 2009 год

Эта статья - вольная интерпретация рекомендаций: Microsoft, IBM, HP, Dell, QLogic, LSI, EMC, ACER, Bull, Fujitsu, Hitachi, NEC и Unisys. Некоторые рекомендуемые настройки требуют отдельного, обстоятельного разговора, и потому не включены в эту статью, а найти эти рекомендации можно в моём блоге.

Материал подготовил Александр Гладченко.

Обновление установки Windows

После установки Windows Server 2008 x64, установите последний сервисный пакет обновлений (Service Pack), обязательно установите выходившие после сервисного пакета исправления безопасности и последние версии драйверов и аппаратных прошивок.

Настройка файла подкачки Windows

Размер файла подкачки Windows в случае размещения на сервере только SQL Server не играет такой важной роли, как в типовых сценариях. SQL Server старается избегать листания. Размер файла подкачки можно выбрать небольшим, чтобы его было достаточно для формирования мини-дампов. Если сервер также обслуживает приложения, которые нуждаются в файле подкачки, размер его стоит выбирать в полтора раза больше, чем размер физической памяти сервера, но не более 50Гб. В случае монопольного владения ресурсами SQL Server, размещать файл подкачки можно на том же диске, где базируется операционная система. Минимальная активность работы с файлом подкачки не будет создавать конкуренцию другим задачам. В Windows Server 2003 имеется несколько предлагаемых вариантов настройки файла подкачки, он может настраиваться жёстко, может отсутствовать вообще или его размер будет управляться операционной системой. В последнем случае, Windows создаст файл подкачки, размер которого на один Мегабайт будет превышать размер физической памяти сервера. Такой выбор обусловлен тем, что ровно столько места необходимо для создания полного дампа памяти после аварии с выдачей "синего экрана". Если вас такой дамп не интересует, можно взять управление размером файла подкачки в свои руки.

Настройка размера системных журналов

Размеры системных журналов стоит выбирать такими, чтобы их открытие для анализа или копирование по сети выполнялось за приемлемое время.

Настройка локальных политик Windows

Ограничьте размер системных дампов физической памяти размером 64 Гб, большой размер дампа непрактичен с точки зрения отладки. Можно оптимизировать управление памятью и существенно сократить листания с помощью установки локальной политики для пользователя, от имени которого запускается служба SQL Server. Политика называется "Lock pages in memory", располагается она в узле "User rights assignment" локальных политик. Оболочку менеджера политик можно вызвать из командной строки, набрав там gpedit.msc и нажав ввод.

Ещё одной полезной локальной пользовательской политикой является мгновенная инициализация файла данных: "Выполнение задач обслуживания тома" (Perform Volume Maintenance). Для файлов журналов эта политика неприменима. Однако, включение этой политики хоть и позволяет сделать процесс создания файла данных, его приращение или восстановление практически мгновенным, оно чревато повышением рисков безопасности, т.к. инициализация не будет сопровождаться перезаписью новых файлов или приращиваемых к ним областей нулями. Т.е. существует риск, что расположенная в этих областях информация будет доступна для чтения и к удаленной ранее информации смогут получить доступ неавторизированные участники. Дополнительную информацию по безопасности этой операции можно найти в главе SQL Server Books Online: "Инициализация файлов базы данных". Одной из альтернатив этому методу, также очень быстро позволяющей выполнять задачи создания и приращения файлов, является размещение файлов баз данных на "сырых" разделах (RAW).

Настройка Панели Управления Windows

Чтобы исключить снижение производительности в режиме энергосбережения, в оснастке "Электропитание" (Power Options) нужно выбрать режим "Высокая производительность" (High Performance). Стоит также убедиться, что сервер никогда не будет переключаться в спящий режим.

На закладке "Визуальные эффекты" можно отключить расходование процессорного времени на некритичные для работы сервера эффекты, выбрав режим "Обеспечить наилучшее быстродействие" (Adjust for best performance).

Настройка конфигурации системы

Отключите контроль учётных записей UAC. Для этого нажмите кнопку "Пуск" (Start), в списке "Программы" (All Programs) перейдите к папке "Администрирование" (Administrative Tools) и запустите утилиту "Конфигурация системы" (System Configuration). Отключить UAC можно на закладке "Сервис" (Tools), выбрав из списка средств задачу: "Отключите контроль учётных записей UAC". Для применения изменений необходима перезагрузка системы.

Настройка кэширования ввода-вывода

Для включения кэширования операционной системой операций ввода-вывода логических дисков воспользуйтесь оснасткой Disk Management или Device Manager, перейдя в ней в узел Disk Drives. Для каждого настраиваемого устройства логического диска нужно выбрать Свойства (Properties) и перейти на закладку Policies. Для индивидуальной настройки дисков лучше подходит оснастка Disk Management, там настройки выполняются из свойств дисков, которые вызываются в графической, нижней части окна оснастки. Включение чекбокса "Enable write caching on the disk" разрешает кэширование записи на диск. После пометки этого чекбокса становится доступен для пометки второй чекбокс: "Enable advanced performance". Включение обеих чекбоксов не только разрешает кэширование, но и заставляет операционную систему изымать из запросов ввода-вывода команды прямой записи на диск и сброса дискового кэша. Не рекомендуется включать эти чекбоксы если аппаратные кэши не имеют защиты от потери электропитания.

Если логический диск представлен внешним дисковым массивом, который оснащён аппаратным кэшем с батарейкой, можно не помечать второй чекбокс: "Enable advanced performance". Однако, пометка этого чекбокса может повысить производительность записи в журнал транзакций.

Для вступления в силу изменений настроек кэша дисков перезагрузки сервера не требуется.

Настройка параметров сетевых плат

В окне Local Area Connection Properties мастера Network Connections панели управления Windows можно вызвать окно параметров сетевой платы, если нажать кнопку Configure. В этом окне на закладке Advanced перечислены несколько параметров, некоторые из них могут существенно влиять на производительность обмена данными по сети.

Link Speed and Duplex

Лучшим решением зачастую является согласованный выбор полнодуплексного режима связи в свойствах сетевой платы и в свойствах порта сетевого коммутатора/концентратора. Это не означает, что выбор автоматической подстройки там и тут будет работать хуже, просто история помнит такие случаи…

Существует ещё ряд распространённых параметров, настройка которых может помочь поднять производительность сетевого интерфейса (например, Receive Buffers, Coalesce Buffers, Offload features и т.п.). Чтобы выдрать верные значения для подобных параметров, обратитесь к документации производителя сетевой платы.

Для того чтобы исключить возможность отключения сетевого интерфейса в целях оптимизации энергопотребления сервера, стоит на закладке Power Management убрать пометку чекбокса "Allow the computer to turn off this device to save power".

Отключение неиспользуемых протоколов

Для того чтобы убрать незначительную дополнительную нагрузку, создаваемую для обслуживания неиспользуемых сетевых протоколов, предлагается отключить или деинсталлировать такие протоколы. Например, первым кандидатом на отключение является TCP/IPv6, в силу пока ещё своей малой распространённости. Сделать это можно в окне Local Area Connection Properties мастера Network Connections панели управления Windows.

SQL Server также использует по умолчанию несколько протоколов, отключить лишние из них позволяет оснастка SQL Server Configuration Manager, в которой протоколы перечислены в узле "Сетевая конфигурация SQL Server".

В большинстве случаев и в свойствах сетевого интерфейса и в протоколах SQL Server достаточно ограничится использованием протокола TCP/IPv4.

Если же требуется поддерживать несколько протоколов, стоит задать приоритеты использования протоколов и их провайдеров в окне Advanced Settings мастера Network Connections.

Настройка антивирусного программного обеспечения

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

Необходимо настроить исключение сканирования файлов баз данных, журналов транзакций и резервных копий, которые типично имеют разрешения: mdf, ldf, ndf, bak и trn. Это позволит предотвратить повреждение этих файлов при попытке со стороны SQL Server их открытия, когда они уже открыты для проверки антивирусным ПО. Кроме того, необходимо принять меры для защиты каталогов полнотекстового поиска и содержащих данные Analysis Services от повреждений, связанных с активностью антивирусного программного обеспечения. Исключите также папку журналов SQL Server (MSSQL\Log), журнал ошибок открыт постоянно и в него может выводиться много событий. Если антивирусное ПО планируется использовать совместно с SQL Server работающем в кластере, нужно исключить сканирование кворум - диска и каталога: "c:\Windows\Cluster". Для получения более подробной информации о требованиях к настройкам антивирусного ПО обратитесь к статье базы знаний Майкрософт: Guidelines for choosing antivirus software to run on the computers that are running SQL Server.

Во избежание вмешательства в работу служб SQL Server и для предотвращения увеличения времени их запуска из-за сканирования антивирусным ПО, рекомендуется исключить из проверки службы SQL Server. Наиболее распространенными в использовании являются службы: sqlservr.exe, sqlagent.exe, sqlbrowser.exe и sqlwriter.exe

Дополнительные рекомендации: Рекомендации по использованию антивирусных программ для компьютеров под управлением Windows Server 2003, Windows 2000 и Windows XP.

Управление памятью в SQL Server

SQL Server, при необходимости, старается заполучить всю доступную ему оперативную память компьютера. Если установлено несколько экземпляров SQL Server, вероятна конкуренция за ресурсы памяти между менеджерами динамической памяти каждого экземпляра. До появления в SQL Server 2008 средств регулировки ресурсов, единственной возможностью снижения конкуренции за память между разными экземплярами была установка в глобальной конфигурации экземпляра SQL Server ограничений для максимального и минимального объемов используемой физической памяти. Кроме того, хорошей практикой считается оставлять не менее 10% оперативной памяти для нужд операционной системы.

Настройка параметров глобальной конфигурации SQL Server

affinity I/O mask

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

exec sp_configure 'affinity mask',0xFFFE
exec sp_configure 'affinity I/O mask',0x0001

Не обязательно для ввода-вывода выделять самое первое по порядку ядро. Это может быть ядро из того NUMA-узла, в домене близости которого находится адаптер ввода-вывода.

network packet size

Следующим параметром глобальной конфигурации, изменение которого может в некоторых случаях способствовать повышению производительности приложений баз данных, является "network packet size (B)". Увеличение размера сетевого пакета до 8192 Байт может позволить добиться выигрыша за счёт лучшего выравнивания размера пакета с размером страницы SQL Server, которая равна 8 КБ. Однако, следует учитывать, что значение этого параметра по умолчанию (4096Б), является лучшим для большинства приложений. Только тестирование позволит выбрать для этого параметра оптимальную установку.

Настройка ключей системного реестра Windows

Важно! Эта глава содержит сведения об изменении реестра. Перед изменением реестра необходимо создать его резервную копию. Убедитесь в том, что знаете, как восстановить реестр в случае возникновения проблемы. Дополнительные сведения о создании резервной копии, восстановлении и изменении реестра см. в следующей статье базы знаний Майкрософт: Сведения о реестре Windows для опытных пользователей.

UseLargePages

Включение поддержки больших страниц может оказаться полезным для тех систем с SQL Server x64, которые оснащение большим объёмом оперативной памяти. Большие страницы способствуют повышению производительности за счёт увеличения TLB буфера процессора. Большие страницы могут использоваться для буферного пула и для кодовых страниц SQL Server. Для включения больших страниц на уровне SQL Server нужно задать флаг трассировки -T834 (это можно сделать через стартовые параметры). Кроме того, следует добавить ключ системного реестра. Содержимое reg-файла для добавления показано ниже:

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Image File Execution Options\sqlservr.exe]
"UseLargePages"=dword:00000001

Для вступления изменений в силу потребуется перезагрузка системы.

LargeSystemCache/Size и IdleFrom0Delay

Установив значение LargeSystemCache в 0, тем самым устанавливается стандартный размер кэша файловой системы, который равен приблизительно 8 Мб, максимальный размер кэша файловой системы не будет превышать 512 Мб. Эта установка рекомендуется для таких программ, которые осуществляют кэширование памяти самостоятельно, и к таким программам относится SQL Server. Ниже показан reg-файла для задания такой установки:

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\Memory Management]
"LargeSystemCache"=dword:00000000

То, насколько агрессивно будет заниматься физическая память под задачи файлового кэша, зависит от установки следующего ключа реестра:

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\lanmanserver\parameters]
"Size"=dword:00000001

Возможные значения: 1-вяло, 2-сбалансировано, 3-агресивно. Для установок с малой нагрузкой на файловый кэш, вполне достаточно 1.. Другие установки свойственны файловым серверам разного масштаба, впрочем, для тестов TPC-E часто выбирают 3.

Отключить режим экономии энергии, который тоже может замедлять некоторые операции, можно с помощью ключа IdleFrom0Delay. Сделать это можно так:

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\Memory Management]
"IdleFrom0Delay"=dword:00000000

IoPageLockLimit, DisablePagingExecutive и DontVerifyRandomDrivers

В одном из документов по оптимизации мне попадалась следующая формула: "IoPageLockLimit = (RAMMb - 65) * 1024". С помощью ключа IoPageLockLimit можно повлиять на то, сколько байт система будет читать или писать на логичекский диск за один раз.

Когда оперативной памяти предостаточно, с помощью установки DisablePagingExecutive можно не позволять SQL Server вытеснять в файл подкачки компоненты драйверов привилегированного и непривилегированного режимов, как и компоненты самого ядра ОС. Установка DontVerifyRandomDrivers в единицу позволяет сэкономить несколько процессорных циклов за счёт отключения отладочной проверки драйверов.

Вот как могут выглядеть значения этих ключей на практике:

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\Memory Management]
"DisablePagingExecutive"=dword:00000001
"DontVerifyRandomDrivers"=dword:00000001
"IoPageLockLimit"=dword:00d9bc00

CountOperations

Параметр CountOperations позволяет отключить сбор данных по некоторым счётчикам производительности, которые относятся к запросам ввода-вывода дисковой подсистемы и сетевых интерфейсов. Чтобы это сделать, нужно в ключе системного реестра "I/O System" установить значение 0 для следующего параметра:

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\I/O System]
"CountOperations"=dword:00000000

Требуется перезагрузка.

NumberOfRequests и MaximumSGList

Эта пара ключей системного реестра предназначена для управления драйвером минипорта в момент инициализации последнего.

Увеличивая значение NumberOfRequests может способствовать повышению производительности обслуживания в Windows запросов дискового ввода-вывода, адресованных логическим дискам, и бывает эффективно только если эти логические диски являются аппаратными RAID-массивами, которые обладают возможностью распараллеливания запросов ввода-вывода. Рекомендованное значение можно найти в документации производителя FC-адаптера или RAID-контроллера. Увеличивать значение нужно осторожно, т.к. большое значение может привести даже к отказу системы. Например, для HBA адаптера QLogic, управляемого драйвером "QLogic Fibre Channel Miniport Driver", в документации не рекомендуется превышать значение 150. Новое значение вступает в силу после перезагрузки системы или, в некоторых случаях, достаточно перезапустить адаптер (заблокировать/разблокировать).

Ключ MaximumSGList позволяет изменять используемый по умолчанию размер пакета передачи данных по шине (64Кб), который актуален для команд интерфейса SCSI. Если установить значение 255, то размер передаваемого одной командой объёма данных будет равняться мегабайту. Современные адаптеры умеют объединять до 265 сегментов данных, каждый по 4096 байт, что в сумме может дать размер одной передачи до 1048576 байт. Этот параметр широко используется для повышения эффективности использования ленточных накопителей, а также для оптимизаций таких задач SQL Server, которые оперируют большими запросами ввода-вывода, например, резервное копирование и восстановление.

В описаниях тесов TPC-C встречается установка обоих ключей в значение 255, как это показано в примере ниже:

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\ql2300\Parameters\Device]
"DriverParameter"=""
"BusType"=dword:00000006
"NumberOfRequests"=dword:000000ff
"MaximumSGList"=dword:000000ff
"CreateInitiatorLU"=dword:00000001
"DriverParameters"="UseSameNN=1;buschange=0"

IdlePrioritySupported

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

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Enum\SCSI\Disk&Ven_IBM&Prod_1726-4xx__FAStT\5&22c73432&0&000000\Device Parameters\Classpnp]
"IdlePrioritySupported"=dword:00000000

Ключ Classpnp скорее всего придётся добавить. Подобные тонкие настройки были мной замечены у IBM в тестах TPC-E.

TCPWindowSize

В Windows 2008 этот ключ больше не используется. В более ранних версиях увеличение размера окна может повысить эффективность сетевого трафика. Рекомендуемое значение (64240) для ключа TCPWindowSize представлено ниже:

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters]
"TCPWindowSize"=dword:0000faf0

Подробности можно узнать в статье Базы Знаний Майкрософт: Description of Windows 2000 and Windows Server 2003 TCP Features.

Если размер окна в 64240 Байт тоже ограничивает производительность сети, существует возможность увеличения размера окна до одного Гигабайта. Ключ реестра TCP1323Opts разрешает динамическое мастабирование окна. Рекомендуемым значением для этого ключа является: 0x3.

Для дальнейшей оптимизации производительности сетевых интерфейсов попробуйте использовать следующие ключи системного реестра Windows, описание которых легко найти в Базе Знаний Майкрософт: TCPMaxConnectRetransmissions, TCPMaxDataRetransmissions, TCPTimedWaitDelay, MaxUserPort, MaxHashTableSize, NumTcbTablePartitions, TcpAckFrequency, MTU и EnablePMTUDiscovery.

MSMQ 3.0 в Кластере

gladchenko — Thu, 25 Jun 2009 09:14:00 GMT

Здесь будет идти речь о кластере Windows Server 2003 Enterprise Edition и о запуске в кластере службы MSMQ. MSMQ, как кластерный ресурс, зависит от двух кластерных ресурсов: сетевого имени и физического диска. Зависимость от сетевого имени позволяет идентифицировать кластерную группу по IP адресу, и позволяет клиентам обращаться к группе как к виртуальному серверу. Физический диск нужен для хранилища сообщений и очередей.

Без кластера, клиенты из сети должны подключаться к серверу по имени или IP адресу, подключаясь к приложению или службе. Служба кластера позволяет создать виртуальные сервера. В дополнение к клиентам MSMQ, работающим со стандартным, не кластерным сервером MSMQ, который обслуживается на узле кластера, написанное для работы в составе кластера приложение MSMQ может связаться с сервером MSMQ, который работает в контексте виртуального сервера. Виртуальный сервер MSMQ, это кластерная группа, состоящая из кластерного ресурса MSMQ и его зависимых ресурсов, о которых уже упоминалось выше. Виртуальный сервер MSMQ не привязан с определенному компьютеру, и в случае отказа он будет перемещён на другой узел, который станет обслуживать клиентов. Очереди создаются на виртуальном сервере, и сообщения можно посылать в очереди виртуального сервера, используя обычный синтаксис VirtualServerName\QueueName.

При создании и настройке MSMQ в кластере, нужно установить MSMQ 3.0 на каждом узле. Кластер должен быть создан до установки MSMQ 3.0 на любом из узлов кластера. MSMQ должен быть установлен до создания кластерного ресурса MSMQ, и набор компонент на каждом узле должен быть одним и тем же.

После установки MSMQ 3.0 на всех узлах кластера можно приступать к созданию кластерного ресурса MSMQ. Можно создавать несколько ресурсов MSMQ, но работа таких ресурсов выходит за рамки поддержки Майкрософт. Поэтому, желательно создавать только один ресурс MSMQ и потом его не переименовывать. После выбора ресурса физического диска в группу MSMQ (он должен быть доступен всем узлам кластера), на этом диске будет создано хранилище очередей, которое желательно создать в папке \msmq\storage. После создания хранилища, местоположение папки хранилища изменять не следует.

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

Если в процессе установки ресурса MSMQ возникнут проблемы, обратитесь к файлу журнала кластера, который обычно расположен по этой ссылке: %systemroot%\Cluster\cluster.log.

Для создания и настройки кластерного ресурса и группы MSMQ в качестве виртуального сервера выполните следующие действия:

Нажмите Пуск (Start), выберете пункт Программы (All Programs), пункт Администрирование (Administrative Tools), и затем запустите программу Cluster Administrator.
В пункте Open Connection to Cluster нужно выбрать соответствующее имя кластера.
В дереве кластерных групп нужно выбрать ту кластерную группу, которой должен принадлежать ресурс MSMQ.
После выбора группы, в пункте меню File нужно выбрать New, а затем щелкнуть Resource.
В мастере New Resource Wizard заполните поля Name и Description, и выберете Message Queuing в списке Resource type. Потом нажмите Next.
В поле Group укажите ту группу, которой Вы хотите, чтобы принадлежал ресурс MSMQ.
На закладке возможных владельцев, добавьте те узлы, которые смогут обслуживать ресурс MSMQ.
На странице зависимостей (Dependencies в Available resources) добавьте ресурс сетевого имени и физического диска.
Нажмите Finish.
В дереве кластерных групп нужно выбрать Resources, а затем в окне подробностей выделить ресурс MSMQ.
В меню File, выберите Bring Online.
В дереве кластерных групп нужно выбрать ту кластерную группу, которой должен принадлежит ресурс MSMQ. Нажать New, а затем щелкнуть Resource, чтобы запустился мастера создания нового ресурса.
В текстовом поле Resource Name введите название: "Computer Management".
В списке Resource Type, выберите Generic Application, и затем нажмите Next.
В поле Group укажите ту группу, которой принадлежит ресурс MSMQ.
На закладке Possible Owners, выберите возможных владельцев и нажмите Next.
На странице зависимостей добавьте ресурс сетевого имени и ресурс MSMQ, который Вы перед этим создавали. Нажмите Next.
В командной строке введите: mmc compmgmt.msc
В Current Directory введите: %windir%\system32
Нужно выбрать Use Network Name для имени компьютера, и Allow application to interact with desktop. Нажмите Next, а затем Finish.
В дереве кластерных групп нужно выбрать Resources, а затем в окне подробностей выделить ресурс Computer Management, который Вы только что создали.
В меню File, выберите Bring Online.

Для управление виртуальным сервером, Вы должны подключиться к нулевой сессии, т.е. Console Session (Для подключения через RDP к Session 0 нужно запустить следующую команду: %systemroot%\system32\mstsc.exe /Admin). Также для управления очередями MSMQ в кластерной среде можно использовать утилиту MMCV.exe. Для получения более подробной информации об этой утилите ознакомьтесь со статьёй How to use the Mmcv.exe utility to manage clustered Message Queuing resources. Однако, мне чаще всего хватало тривиальной оснастки mmc, правда для подключения именно к виртуальной очереди MSMQ следует явно подключиться в Computer Management к виртуальному имени, от которого зависит кластерный ресурс MSMQ. После настройки MSMQ для работе в качестве виртуальной службы кластера, не забывайте дать необходимые права на саму виртуальную службу тем учётным записям пользователей, которые будут создавать очереди, а также раздать права к тем очередям, которые будут вами созданы.

Если установка и настройка MSMQ в кластере выполнена правильно, на каждом из узлов кластера должна появиться вторая, дополнительная служба MSMQ. Службы MSMQ локальные для каждого узла не обязательно должны быть запущены. Если Вы не планируете их использование в своих приложениях, можно отменить их автоматический запуск. Для виртуальной службы MSMQ достаточно того, что необходимые компоненты были установлены на каждом из узлов кластера.

Если необходимо, чтобы в случае административного или из-за отказа перемещения ресурсов MSMQ на другой узел, содержимое очереди не очищалось, создавайте очередь транзакционной и обрамляйте отправку сообщений транзакциями (в терминах Windows, а не MSSQL).

Полезные ссылки:

SQL Server: Методика тестирования дисковой подсистемы

gladchenko — Tue, 09 Jun 2009 18:25:00 GMT

(на примере полки с 14 одинаковыми дисками)

Введение

В качестве утилиты для эталонного тестирования дисковой подсистемы используется разработанная Майкрософт программа SQLIO, подробное описание которой представлено в статье Эталонный тест дисковой подсистемы SQLIO.

В методике используется ограниченный набор параметров вызова SQLIO. Давайте рассмотрим назначение параметров на примере:

sqlio -kR -s300 -b64 -f1 -i2000000 -o1 -t1 -R1 -LP -a0xf –BN

Параметр -k определяет, будет ли производиться чтение (R) или запись (W). Параметр -s задаёт продолжительность теста, указываемую в секундах. Параметр -b задаёт размер блока ввода-вывода в килобайтах. Параметр -f определяет число блоков в строке. Параметр –i определяет число строк. С помощью параметров -b, -f и -i

Рис.1.

Параметр -o указывает количество отправляемых в одном потоке запросов ввода-вывода, т.е. глубину очереди. Практика показывает, что программа SQLIO при глубине очереди 64 и выше может вести себя нестабильно, поэтому число 64 не превышалось. Параметр -t задаёт число используемых в тесте потоков, максимальное значение 256. В настоящей методике этот параметр не превышает числа ядер процессоров. Параметр -R задаёт номера LUN сырых (RAW) разделов дисков. Параметр –L задаёт таймер теста, в методике используется таймер процессоров. Параметр -a задаёт маску используемых в тесте процессоров. Параметр –B используется для отключения аппаратного и программного кэширования (кэш дисков и дисковых контроллеров будет отключён, если ими такая возможность поддерживается).

В отчёте по каждому из единичных тестов, выполненных утилитой SQLIO, кроме численных параметров рабочей нагрузки данного теста указаны и вычисленные показатели производительности. Кроме метрик задержки, можно увидеть две величины: IOs/sec и MBs/sec. Первая является ни чем иным, как IOPS (Input-Output Operations Per Second), и показывает количество операций ввода-вывода в секунду, которые было обработано тестируемой дисковой подсистемой. Эта величина лучше всего характеризует производительность обслуживания коротких запросов, характерных для OLTP-нагрузки (8KB). Если приложение, для которого тестируется дисковая подсистема, использует в своей работе преимущественно такую нагрузку, может оказаться, что сравнения полученных в разных конфигурациях результатов стоит делать по этой метрике. Один диск на сегодняшний день может обслуживать от 50 IOs/sec для SATA и до 200 IOs/sec для FC дисков.

Второй метрикой является производительность передачи данных, так называемый Traffic Throughput. Это основная метрика настоящей методики, поскольку большинству приложений баз данных характерны укрупнённые запросы (например, упреждающее чтение способно использовать запросы ввода-вывода в 64 и 128 КБ).

Этап №0. Подготовка

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

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

Этап №1. Калибровка дисков

Задача: На этом этапе мы должны убедиться, что используемые диски работоспособны, определить разброс скоростей чтения и записи по дискам, чтобы потом учитывать возможности каждого диска при распределении дисков в массивах.

Возникает резонный вопрос, почему бы не воспользоваться стандартными в таких случаях стресс - тестами, которые, как правило, предоставляются вендором. Объяснение простое. Чаще всего мне доводилось работать с серверами удалённо, а в таких условиях не всегда удаётся добиться оперативной реакции обслуживающего аппаратную часть персонала. В таких случаях проще сделать быстрый тест на "вшивость". К тому же, не всегда тесты охватывают сразу все уровни системы, они могут ограничится только дисками или тестировать контроллер вместе с дисками. Существуют также специализированные тесты, которые позволяют тестировать систему в целом и определять её пригодность к использованию для того - либо иного приложения. У Майкрософт, к слову, есть подобный стресс - тест, который позволяет протестировать дисковую систему и сервер. Называется эта утилита: SQLIOSim.

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

С помощью поставляемых с дисковым контроллером специализированных утилит, конфигурируем все диски полки как 14 массивов RAID0, каждый из которых должен состоять из одного диска, размер каждого массива выбирается равным всему доступному размеру диска, размер блока низкоуровневой разметки (размер сегмента) выбираем равным 64Кб, политики кэширования должны исключать кэширование чтения и записи. В некоторых контроллерах дисковых массивов выбор размера сегмента может быть ограничен несколькими предопределёнными значениями. Можно встретить рекомендованные для типовых конфигураций значения, например, для баз данных предлагают установить размер блока в 128Кб, а для хранения видеофильмов задать 256Кб. В этих случаях резонно выбрать рекомендованные вендором значения.
С помощью оснастки управления дисками, входящей в состав mmc-консоли управления компьютером, и системной утилиты DISKPART необходимо создать для каждого физического диска полки RAW-раздел (без форматирования NTFS) величиной на весь диск, и без присвоения буквы диска (буквы присваивать можно, но это не обязательно, к тому же, букв в алфавите может оказаться меньше числа дисков). Большие диски (более двух Терабайт) может потребоваться предварительно перевести в состояние Online, и конвертировать в GPT (GUID Partition Table). Для выравнивания начального смещения за счёт MBR используйте следующие команды DISKPART:

SELECT DISK=1
CREATE PARTITION PRIMARY ALIGN=128

В этом примере выбран диск 1 и смещение установлено в 128Кб. Выбор смещения зависит от размера сегмента.

Установить программу SQLIO. Везде по тексту настоящей статьи местоположение программы sqlio.exe выбрано следующее: C:\SQLIO\ sqlio.exe
Подготовьте командный файл, который будет запускать программу sqlio.exe в разных режимах для каждого диска и сохранять результаты в файлы. Пример командного файла можно найти в Приложении 1.
Запустите командный файл на исполнении, а потом сведите собранные в файлы результаты в общую таблицу, для дальнейшего сравнения и анализа.

Пример аппаратной конфигурации дисков представлен в Таблице 1.

Таблица 1. Конфигурация дисковых массивов для калибровки дисков.

Номер диска	Тип массива	Размер	Имя устройства	Политика чтения	Политика записи	Политика кэша	Размер блока полосы
1:0	RAID-0	136.62GB	Windows Disk 0	без упреждения	прямая запись	Direct I/O	64 KB
1:1	RAID-0	136.62GB	Windows Disk 1	без упреждения	прямая запись	Direct I/O	64 KB
1:5	RAID-0	136.62GB	Windows Disk 2	без упреждения	прямая запись	Direct I/O	64 KB
0:9	RAID-0	136.62GB	Windows Disk 3	без упреждения	прямая запись	Direct I/O	64 KB
0:10	RAID-0	136.62GB	Windows Disk 4	без упреждения	прямая запись	Direct I/O	64 KB
0:11	RAID-0	136.62GB	Windows Disk 5	без упреждения	прямая запись	Direct I/O	64 KB
0:12	RAID-0	136.62GB	Windows Disk 6	без упреждения	прямая запись	Direct I/O	64 KB
0:13	RAID-0	136.62GB	Windows Disk 7	без упреждения	прямая запись	Direct I/O	64 KB
0:14	RAID-0	136.62GB	Windows Disk 8	без упреждения	прямая запись	Direct I/O	64 KB
0:15	RAID-0	136.62GB	Windows Disk 9	без упреждения	прямая запись	Direct I/O	64 KB
1:2	RAID-0	136.62GB	Windows Disk 10	без упреждения	прямая запись	Direct I/O	64 KB
1:3	RAID-0	136.62GB	Windows Disk 11	без упреждения	прямая запись	Direct I/O	64 KB
1:4	RAID-0	136.62GB	Windows Disk 12	без упреждения	прямая запись	Direct I/O	64 KB
1:8	RAID-0	136.62GB	Windows Disk 13	без упреждения	прямая запись	Direct I/O	64 KB

На Рисунке 2 показано, как должны выглядеть 14 дисков, подготовленные к тестированию.

Рис.2.

Ниже представлен сокращённый пример командного файла для запуска калибровочного теста. В этом файле каждый вызов программы sqlio.exe имеет одинаковые аргументы, кроме аргумента R, который изменяется от 1 до 14. Каждый диск тестируется на чтение и, после 30 секундной паузы, на запись (аргумент k). Время тестирования каждого диска 300 секунд (аргумент s), размер блока 64Кб (аргумент b), Число блоков в строке равно единице (аргумент f), число строк равно 2000000 (аргумент i), число очередей тоже равно единице (аргумент o), число потоков равно единице (аргумент t). Суммарный размер файла рабочей нагрузки на каждом диске составляет 128Гб, что почти совпадает с размером диска.

sqlio -kR -s300 -b64 -f1 -i2000000 -o1 -t1 -R1 -LP -a0xf -BN > R01-b64-f1-i2000000-o1-t1.log

timeout /T 30

sqlio -kW -s300 -b64 -f1 -i2000000 -o1 -t1 -R1 -LP -a0xf -BN > W01-b64-f1-i2000000-o1-t1.log

timeout /T 30

sqlio -kR -s300 -b64 -f1 -i2000000 -o1 -t1 -R2 -LP -a0xf -BN > R02-b64-f1-i2000000-o1-t1.log

timeout /T 30

sqlio -kW -s300 -b64 -f1 -i2000000 -o1 -t1 -R2 -LP -a0xf -BN > W02-b64-f1-i2000000-o1-t1.log

timeout /T 30

……

sqlio -kR -s300 -b64 -f1 -i2000000 -o1 -t1 -R13 -LP -a0xf -BN > R13-b64-f1-i2000000-o1-t1.log

timeout /T 30

sqlio -kW -s300 -b64 -f1 -i2000000 -o1 -t1 -R13 -LP -a0xf -BN > W13-b64-f1-i2000000-o1-t1.log

timeout /T 30

sqlio -kR -s300 -b64 -f1 -i2000000 -o1 -t1 -R14 -LP -a0xf -BN > R14-b64-f1-i2000000-o1-t1.log

timeout /T 30

sqlio -kW -s300 -b64 -f1 -i2000000 -o1 -t1 -R14 -LP -a0xf -BN > W14-b64-f1-i2000000-o1-t1.log

Ниже представлен пример отчёта о тесте операций записи для первого диска:

sqlio v1.5.SG

calibrating timestamp counter for latency timings... done (MHz measured at 2359)

1 thread reading for 300 secs from file 1:

                        using 64KB IOs over 64KB stripes with 2000000 IOs per run

                        enabling multiple I/Os per thread with 1 outstanding

                        buffering set to not use file nor disk caches (as is SQL Server)

                        affinity mask is 0xf (15)

initialization done

CUMULATIVE DATA:

throughput metrics:

IOs/sec:   999.67

MBs/sec:    66.91

latency metrics:

Min_Latency(ms): 0

Avg_Latency(ms): 0

Max_Latency(ms): 802

histogram:

ms: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24+

%: 87 13 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

В этом отчёте нас интересует метрика скорости чтения, именно эту метрику мы будем сводить в общий график (MBs/sec: 66.91). Метрика IOs/sec практически линейно зависит от MBs/sec, поскольку в каждом из тестов у нас будет фиксированный размер запроса ввода-вывода. Метрики для задержки запроса ввода-вывода (latency) могут быть интересны вкупе с MBs/sec для сравнения разных дисковых подсистем. Нас такое сравнение не интересует, поэтому метрики MBs/sec вполне достаточно.

Автоматизировать извлечение из файлов с результатами тестов необходимых для дальнейших оценок значений показателей производительности можно, например, с помощью PowerShell. В примере ниже показана команда, которая сохраняет в CSV-файле имена файлов и соответствующее им значение MBs/sec:

(Select-String -Pattern MBs/sec: -Path *.log | format-table `

{$_.Filename -replace ".log", ""}, `

{($_.Line).replace(‘.’, ‘,’) -replace "MBs/sec: ", ";"} `

-HideTableHeaders) | Out-File SQLIO.csv -Encoding ASCII –Append

Если результаты сильно выбиваются из общей массы, желательно для такого диска повторить измерения, существенно (например, в два раза) увеличив время теста (аргумент s). На полученные результаты может сильно влиять посторонняя активность на сервере. Старайтесь не подключаться к консоли сервера или посещать его в терминальном режиме, пока не будет закончен очередной пакет тестов. Кроме того, постарайтесь отключить на время тестирования любую другую постороннюю активность на сервере.

На Рисунке 3 показан результат калибровочного тестировании 13 дисков.

Рис. 3.

Наиболее важной задачей нагрузочного тестирования является выявление дисков, характеристики которых существенно отличаются от остальных. На Рисунке 2 диски практически идентичны, как по записи, так и по чтению. Однако, при тестировании большого числа дисков вероятность столкнуться с «подозрительными» дисками много выше. Например, такой пример показан на Рисунке 4. Обратите внимание на четырнадцатый диск.

Рис. 4.

Совсем не обязательно, что в плохих или, наоборот, невероятно хороших результатах повинен сам диск. Причиной может стать неисправность дискового контроллера, терминатора шины или электрических цепей шины дисковой «полки».

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

Если позволяет время, можно проверить не только последовательные чтение/запись, но и случайный выбор блоков для ввода-вывода.

Особо хочется остановиться на вопросах документирования результатов измерений. Обратите внимание на заголовок таблиц каждого из представленных ранее двух результатов. Это строки:

sqlio -kRW -s300 -f1 -i2000000 -o1 -b64 -t1 -LP -R(2-14) -a0xff

sqlio -kRW -s300 -f1 -i2000000 -o1 -b64 -t1 -LP -R(0-13) -a0xff

Мне видится наиболее удобным и практичным именно такой вид описания тестовой конфигурации. Суть состоит в том, чтобы просто скопировать из командного файла строку запуска программы SQLIO и внести изменение в написание изменяемых параметров. В данном случае для параметра –R в скобках был задан диапазон изменения порядковых номеров дисков. В первом случае это диски со второго по четырнадцатый, а во втором случае – это диски с нулевого по тринадцатый. Подобные описатели конфигурации будут встречаться далее в этой статье для демонстрации результатов измерений.

Этап №2. Масштабирование дисков

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

Наша задача понять, сколько дисков (шпинделей) способны захватить практически всю полосу пропускания. Это поможет при выборе числа дисков в массиве. Кроме того, этот тест позволяет убедиться, что дисковые контроллеры (а в наших тестах их два) и пассивное оборудование работает исправно.

Производительность отдельных компонент дисковой подсистемы в данной методике не исследуется, хотя понятно, что абстрагироваться от этого полностью невозможно. В предлагаемой методике мы смотрим на дисковую подсистему, условно, как на "чёрный ящик". Ограничения, а также разного рода ошибки, могут быть совершенно в разных местах. Чтобы начать разговор об этом с администраторами дисковой подсистемы, нужно иметь аргументы. Масштабирование позволяет получить на руки факты именно из этой области. Кроме того, поскольку мы хотим получить в итоге некий эталон производительности, подобные измерения бывают весьма полезны.

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

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

Рис.5.

Для документирования подобного приращения числа дисков я использую сочетание символов «à». Т.о. для обозначения приращения дисков, которое начинается с диска под номером 2 в нумерации операционной системы и заканчивается обоймой из тринадцати дисков по диск номер 14 включительно, можно использовать следующую запись: -R(2->14).

В командном файле это выглядит примерно так:

sqlio -kR -s180 -b64 -f1 -i2000000 -o1 -t1 -R2 -LP -a0xf -BN > R2-b64-f1-i2000000-o1-t1.log

timeout /T 30

sqlio -kW -s180 -b64 -f1 -i2000000 -o1 -t1 -R2 -LP -a0xf -BN > W2-b64-f1-i2000000-o1-t1.log

timeout /T 30

sqlio -kR -s180 -b64 -f1 -i2000000 -o1 -t1 -R2,3 -LP -a0xf -BN > R23-b64-f1-i2000000-o1-t1.log

timeout /T 30

sqlio -kW -s180 -b64 -f1 -i2000000 -o1 -t1 -R2,3 -LP -a0xf -BN > W23-b64-f1-i2000000-o1-t1.log

timeout /T 30

… результаты сокращены…

sqlio -kR -s180 -b64 -f1 -i2000000 -o1 -t1 -R2,3,4,5,6,7,8,9,10,11,12,13 -LP -a0xf -BN > R2345678910111213-b64-f1-i2000000-o1-t1.log

timeout /T 30

sqlio -kW -s180 -b64 -f1 -i2000000 -o1 -t1 -R2,3,4,5,6,7,8,9,10,11,12,13 -LP -a0xf -BN > W2345678910111213-b64-f1-i2000000-o1-t1.log

timeout /T 30

sqlio -kR -s180 -b64 -f1 -i2000000 -o1 -t1 -R2,3,4,5,6,7,8,9,10,11,12,13,14 -LP -a0xf -BN > R234567891011121314-b64-f1-i2000000-o1-t1.log

timeout /T 30

sqlio -kW -s180 -b64 -f1 -i2000000 -o1 -t1 -R2,3,4,5,6,7,8,9,10,11,12,13,14 -LP -a0xf -BN > W234567891011121314-b64-f1-i2000000-o1-t1.log

Получив информацию о том, какова реальная пропускная способность вашей дисковой подсистемы, вы можете спланировать дальнейшие тестовые конфигурации. Выбор тестируемых конфигураций может быть обусловлен вашими бизнес - требованиями к доступности и готовности серверов, а также отводимыми на тестирование сроками (полный цикл тестирования согласно данной методике может занимать порядка 7 суток круглосуточной работы тестового сервера). Зная, какое количество шпинделей потенциально может «запрудить» шину или контроллер, вы можете выбрать, сколько дисков оптимально собирать в один массив. Кроме того, в целях оптимизации времени тестов, данная методика предполагает допущение, что сервер баз данных, как и утилита SQLIO, балансирует нагрузку между дисками лучше, чем контроллер дискового массива. Как вы помните, база данных может состоять из нескольких файлов, и SQL Server будет резервировать в этих файлах пространство под новые страницы пропорционально размеру файлов. Учитывая эту особенность, мы ограничим число создаваемых массивов только некоторыми избранными конфигурациями, которые в лучшей мере используют преимущества балансировки ввода-вывода с помощью SQL Server. Т.е. это будут те конфигурации, которые не противоречат здравому смыслу и предлагают максимальное количество файлов. Ещё одним допущением для данной методики является то, что в каждой из тестовых конфигураций число шпинделей остаётся неизменным. Это нужно для того, чтобы сравнивать производительность разных массивов при равной стоимости оборудования.

Лучше всего рассмотреть вариант подобной тестовой схемы на примере, с графическими изображениями сайзинга дисков в массиве. Поскольку у нас уже собрана самая простая, и, во многих случаях, самая эффективная конфигурация дисков в массивы, начнём с неё. Похожая конфигурация показана на рисунке 6, и условно обозначается noRAID.

Рис.6.

Современные дисковые контроллеры часто не предусматривают возможности конфигурирования отдельных дисков, однако, оставляют возможность создания RAID0 из одного диска.

Следующие конфигурации, показанные на рисунках 7 и 8, представляют RAID1 и RAID0.

Рис.7.

Рис.8.

Конфигурации массивов RAID5 и RAID10 тоже внешне очень похожи, у них будет одинаковое количество логических дисков (LUN).

Рис.9.

Рис.10.

Каждая из показанных на рисунках конфигурация используется для прогона тестов, которые описаны на этапе 4.

Этап №3. Выбор размера сегмента

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

В этой статье мы будем полагаться на то, что бизнес - требования наших приложений указывают на необходимость обеспечения высокой производительности запросов упреждающего чтения. Как известно, упреждающее чтение характерно тем, что размер запроса ввода – вывода чаще всего равняется 64 Кб. SQL Server пытается считывать данные не страницами, а экстентами, разумеется, если размер запроса соответствующий и фрагментация данных позволяет это сделать. Мы допускаем, что фрагментацию удаётся поддерживать в приемлемых рамках, а оптимизация запросов ввода-вывода, размер которых близок к размеру страницы 8 Кб, не представляется необходимой, поскольку такие запросы будут обслуживаться практически одинаково в разных конфигурациях в силу своих малых размеров. Также, мы будем опираться на очень распространённое бизнес-правило, что время резервного копирования и восстановления базы данных из резервной копии должны быть минимальными. В своих измерениях Вы можете полагаться на иные требования и правила. Чтобы помочь Вам сориентироваться в выборе размеров нагрузки, тут будет уместно привести Таблицу 2, в которой показано соотношение разных операций и размеров запросов.

Таблица 2. Характеристики для основных типов нагрузки ввода – вывода SQL Server

Характер нагрузки	Доступ: случайный / последовательный	Преобладает: чтение / запись	Размер запроса ввода-вывода
Журнал транзакций OLTPсистемы	последовательный	запись	512 Б - 64 КБ
Файлы данныхOLTPсистемы	случайный	чтение - запись	8 КБ
Массовая вставка	последовательный	запись	от 8 КБ до 256 КБ
Упреждающее чтение, просмотр индекса	последовательный	чтение	от 8 КБ до 256 КБ
Резервное копирование	последовательный	чтение / запись	1 МБ
Отложенная запись	последовательный	запись	от 128 КБ до 2 МБ
Восстановление из копии	последовательный	чтение / запись	64 КБ
Контрольная точка	последовательный	запись	от 8 КБ до 128 КБ
CREATE DATABASE	последовательный	запись	512 КБ
CHECKDB	последовательный	чтение	8 КБ - 64 КБ
DBREINDEX	последовательный	чтение / запись	чтение: от 8 КБ до 256 КБ запись: от 8 КБ до 128 КБ
SHOWCONTIG	последовательный	чтение	8 KБ - 64 КБ

Как правило, выбор размера сегмента не является сложной задачей и, зачастую, предсказуем. Например, ниже, на Рисунке 11 показана весьма распространённая зависимость производительности одного диска от размера сегмента.

Рис.11.

Многие специалисты рекомендуют устанавливать размер блока для форматирования NTFS раздела операционной системы Windows равным 64 Кб и делать такого же размера размер сегмента при монтировании дискового массива. Глядя на представленный Рисунком 11 график, становится понятно, откуда взялась эта рекомендация.

Э тап №4. Сравнение производительности разных типов дисковых массивов

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

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

Каждый уровень RAID будет подвержен пяти тестовым сериям измерения скорости чтения и записи, для разных размеров блока. Размер блока (который задаётся параметром –b) будет принимать следующий набор значений: 1024, 512, 256, 64, 8. Как вы, наверное, заметили, размеры блока соответствуют размерам основных типов запросов ввода-вывода. Выбор размеров блока для тестирования важен, поскольку не стоит включать в тест те размеры запросов ввода – вывода, которые не важны, или не характерны для ваших приложений. Например, если в вашей промышленной среде существуют достаточно продолжительные окна для проведения технологических планов обслуживания баз данных, тогда можно исключить из теста размер блока в 128Кб. Если время создания базы данных тоже не критично (например, если вы используете RAW-партиции), можно убрать тест с размером блока 512Кб.

Кроме изменения размера блока (под блоком будем понимать размер запроса ввода-вывода), данная методика предполагает изменение ещё нескольких, критически важных характеристик рабочей нагрузки. Нам нужно оценить производительность не только для одного запроса, но и в условиях относительно глубоких очередей запросов ввода-вывода, а также распараллеливания этих запросов. Глубина очереди будет увеличиваться до 64. Шаг приращения величины очереди тоже стоит выбирать таким, какой характерен для ваших приложений или, как это будет показано в нашем примере, это могут быть отдельные, характерные для ваших задач значения. Мы будем использовать следующие параметры очереди: о1, о2, о4, о8, о16, о32, о64.

Число потоков у нас будет изменяться от одного до четырёх, ровно столько на тестовом сервере имеется ядер процессора. Синхронно с числом потоков будет изменяться страйп-фактор. Здесь, под числом страйпов (страйп-фактор) понимается число блоков в строке файла нагрузки. Значение переменной основного цикла программы (число страйпов в файле нагрузки) тоже будет меняться, оно вычисляется каждый раз таким образом, чтобы при текущем размере блока и числе блоков в страйпе, размер файла нагрузки был близок к ёмкости диска. Поскольку число вычислений значений параметра i (основной цикл) достаточно велико, лучше заранее подготовить небольшие таблички, охватывающую все выбранные для тестирования варианты параметров и конфигурации. Например, на рисунке 12 представлена подобная таблица.

Рис.12.

Здесь, в ячейке A1 указан размер раздела диска (в данном случае у всех дисков он одинаков). Количество строк файла нагрузки, помещаемого на каждый из задействованных дисков, вычисляется делением размера раздела в Кб на размер блока (b), потом на число потоков (t) блоков в страйпе (f).

Настоящая методика использует несколько эмпирических допущений, которые существенно уменьшают объём выполняемых тестов. Рисунок 12 как раз и демонстрирует одно из таких допущений. Т.е. мы тестируем не все возможные варианты сочетаний параметров запуска SQLIO, а только те, которые перечислены в таблице. Т.е. Значения для параметров t и f могут быть 1,2,4,8,16. Поскольку максимальное значение для параметра t рекомендуется избирать не выше числа ядер процессоров (а в пример на рисунке 12 их число было 16). Значение f не должно превышать значение t, диапазон изменения этих параметров будет именно такой, как вы видите на рисунке. В колонке A, начиная со строки 2, заданы размеры запросов ввода вывода, которые характерны для приложения. Эти значения используются для параметра b. В таблице не хватает только одного из определяющих конфигурацию каждого теста параметра, это глубина очереди, задающаяся параметром o. Как и в нашем случае, в примере на рисунке 12 использовались следующие параметры очереди: о1, о2, о4, о8, о16, о32, о64.

Документирование конфигурации проще показать и подробно разобрать на следующем примере:

f(1,2,4) -i(1950,975,487) -o(1-64) -b1024 -t(1,2,4) -LP -R2,3,4 -a0xf

Здесь, размер блока был установлен в 1024Кб. Число блоков в страйпе файла нагрузки изменялось синхронно с числом потоков, т.е. значения были равны, и составляли: 1, 2 и 4. В зависимости от последнего числа изменяется число страйпов файла рабочей нагрузки. В этом тесте для мегабайтного размера блока значения такие: i1950, i975, i487. Глубина очереди изменялась так, как я показывал ранее, от единицы до 64.

В Таблице 3 показаны результаты теста, полученные для разных значений изменяемых параметров.

Таблица 3. RAID1:

Чтение	f1-t1	f2-t2	f4-t4
o1	373,23	371,05	345,67
o2	373,49	326,56	331,87
o4	373,45	325,20	330,36
o8	373,01	331,09	318,53
o16	371,91	321,52	281,54
o32	368,80	290,96	264,18
o64	351,11	276,80	262,61
	2585,00	2243,18	2134,76	6962,94
			Среднее:	331,57
Запись	f1-t1	f2-t2	f4-t4
o1	185,52	307,15	290,45
o2	307,73	259,25	257,31
o4	336,09	328,68	255,48
o8	337,55	254,93	241,74
o16	335,02	245,59	209,88
o32	322,65	219,26	214,20
o64	287,12	215,73	211,50
	2111,68	1830,59	1680,56	5622,83
			Среднее:	267,75

В таблицах с четвёртой по седьмую представлены значения для других размеров блока. Поскольку подобные тесты проводятся для всех тестируемых уровней RAID, и каждый такой тест содержит по пять таблиц, тут мы ограничимся демонстрацией результатов только для RAID1.

Таблица 4. RAID1: f(1,2,4) -i(3900,1900,970) -o(1-64) -b512 -t(1,2,4) -LP -R2,3,4 -a0xf

Чтение	f1-t1	f2-t2	f4-t4
o1	373,15	372,56	361,55
o2	373,13	261,15	313,54
o4	372,56	268,08	307,82
o8	373,04	235,48	253,12
o16	373,07	237,75	237,83
o32	371,31	236,25	237,08
o64	367,76	230,83	231,77
	2604,02	1842,10	1942,71	6388,83
			Среднее:	304,23
Запись	f1-t1	f2-t2	f4-t4
o1	156,97	265,91	265,18
o2	268,31	251,29	206,90
o4	332,29	325,32	180,68
o8	340,85	249,96	182,40
o16	332,81	190,25	172,63
o32	328,80	185,35	173,92
o64	317,41	183,24	176,61
	2077,44	1651,32	1358,32	5087,08
			Среднее:	242,24

Таблица 5. RAID1: f(1,2,4) -i(500000,250000,125000) -o(1-64) -b256 -t(1,2,4) -LP -R2,3,4 -a0xf

Чтение	f1-t1	f2-t2	f4-t4
o1	374,15	243,60	248,95
o2	375,00	309,89	290,07
o4	375,03	306,97	291,95
o8	374,98	313,63	313,85
o16	375,03	306,96	304,26
o32	374,97	304,35	234,59
o64	374,12	301,15	255,86
	2623,28	2086,55	1939,53	6649,36
			Среднее:	316,64
Запись	f1-t1	f2-t2	f4-t4
o1	121,68	172,75	167,27
o2	191,64	311,50	261,88
o4	313,80	328,33	157,86
o8	331,46	332,86	145,59
o16	336,23	326,75	160,41
o32	334,63	193,98	159,45
o64	330,98	186,63	160,22
	1960,42	1852,80	1212,68	5025,90
			Среднее:	239,33

Таблица 6. RAID1: f(1,2,4) -i(2000000,1000000,500000) -o(1-64) -b64 -t(1,2,4) -LP -R2,3,4 -a0xf

Чтение	f1-t1	f2-t2	f4-t4
o1	348,86	167,24	371,95
o2	371,32	279,76	338,58
o4	370,44	202,74	344,97
o8	370,53	223,38	371,38
o16	374,59	205,17	323,17
o32	374,90	170,97	191,85
o64	374,86	156,27	171,86
	2585,50	1405,53	2113,76	6104,79
			Среднее:	290,70
Запись	f1-t1	f2-t2	f4-t4
o1	107,37	135,34	147,57
o2	141,84	154,48	169,75
o4	165,56	170,44	174,16
o8	187,45	192,70	173,17
o16	193,43	193,40	179,14
o32	193,92	193,75	176,27
o64	194,66	193,26	176,17
	1184,23	1233,37	1196,23	3613,83
			Среднее:	172,09

Таблица 7. RAID1: f(1,2,4) -i(16000000,8000000,4000000) -o(1-64) -b8 -t(1,2,4) -LP -R2,3,4 -a0xf

Чтение	f1-t1	f2-t2	f4-t4
o1	123,89	101,73	87,55
o2	169,70	116,12	91,55
o4	184,15	118,78	87,39
o8	211,35	129,39	81,27
o16	216,18	111,26	91,28
o32	185,17	121,52	94,14
o64	172,73	123,32	94,16
	1263,17	822,12	627,34	2712,63
			Среднее:	129,17
Запись	f1-t1	f2-t2	f4-t4
o1	33,37	44,20	36,90
o2	53,05	37,09	53,80
o4	68,42	68,16	78,35
o8	79,19	78,15	78,50
o16	79,48	78,59	73,41
o32	79,35	75,58	87,38
o64	88,29	87,56	87,42
	481,15	469,33	495,76	1446,24
			Среднее:	68,87

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

На Рисунке 13 показан график средних значений производительности троек массивов для разных уровней RAID. Разными цветами обозначены размеры блоков. Шкала показывает производительность в Мб/сек.

Рис.13.

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

При выборе другого оборудования или в условиях другого характера нагрузки, например, OLTP, график вполне может стать другим. На Рисунке 14 показан график для другой аппаратной платформы, у которой можно было создавать массивы RAID10.

Рис.14.

На рисунках 14 и 15 показаны результаты для варианта прямого подключения дисковой подсистемы. На рисунке 16 показаны результаты тестирования дисковой подсистемы и сервера другого вендора, и в варианте подключения SAN.

Рис.15.

Анализируя подобные графики, вы без труда поймёте, какие уровни RAID для каких файлов и типов рабочей нагрузки вам следует использовать, чтобы обеспечить требования к производительности, предъявляемые бизнес – правилами для ваших приложений. Получив данные об эффективности различных уровней RAID в ваших условиях, вы можете провести тестирование окончательного варианта промышленной системы, смонтировав все необходимые дисковые массивы. Получив такую оценку, вы будете точно знать, чего следует ожидать от вашей дисковой подсистемы.

Выводы

Сегодня типичные объёмы обслуживаемых СУБД данных настолько велики, что для дисковых подсистем уже недостаточно нескольких дисков, всё чаще, типовое приложение оперирует дисковой полкой в 14 или 15 дисков, а объёмы дисковых массивов достигают Терабайта. В таких условиях задачи сайзинга становятся очень важны, и грамотное распределение файлов вкупе с правильным выбором дисковых массивов позволяют существенно повысить производительность приложений, а также добиться требуемых значений производительности без лишних издержек энергопотребления.

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

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

Приложение 1. Пример командного файла.

SQLIO_noRAID.zip

Благодарности

Хочу выразить благодарность Ирине Наумовой, которая предложила более изящное решение по замене точки в сценарии PowerShell.

В тему

Методика тестов СХД с помощью IOmeter

Tips for DBA: Log Flush Performance

gladchenko — Mon, 08 Jun 2009 12:41:00 GMT

Одной из распространённых задач систем с высокой транзакционной загрузкой является определение того, достаточно ли производительна подсистема ввода-вывода, обслуживающая журнал транзакций. Часто «узким местом» становиться дисковая подсистема, используемая в качестве долговременного носителя для файла журнала транзакций обслуживаемой SQL Server базы данных. Одним из важных параметров дисковой подсистемы является время доступа к данным на диске. Современным дисковым подсистемам характерно время доступа порядка 1 – 5 ms. Проверить, какое время доступа у используемой для размещения файла журнала транзакций дисковой подсистемы можно с помощью административного динамического представления: sys.dm_os_wait_stats (Transact-SQL). Данные в этом представлении накапливаются с момента последнего запуска службы SQL Server, поэтому, рекомендуется очистить эту статистику. Сделать это можно следующей командой:

DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR);
GO

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

SELECT      (wait_time_ms - signal_wait_time_ms) / waiting_tasks_count AS [Время отклика долговременного носителя журнала (ms)]
              ,    max_wait_time_ms AS [Максимальное время ожидания (ms)]
FROM        sys.dm_os_wait_stats
WHERE       wait_type = 'WRITELOG' AND waiting_tasks_count > 0;

Здесь:

wait_time_ms - общее время ожидания данного типа в миллисекундах. Это время включает в себя время signal_wait_time_ms.
signal_wait_time_ms - разница между временем сигнализации ожидающего потока и временем начала его выполнения.
waiting_tasks_count - число ожиданий данного типа. Этот счетчик наращивается каждый раз при начале ожидания.
max_wait_time_ms - максимальное время ожидания данного типа.

В публичном документе Майкрософт: «Diagnosing Transaction Log Performance Issues and Limits of the Log Manager» (автор: Mike Ruthruff) рекомендовано чтобы время отклика долговременного носителя журнала было в диапазоне от 1ms до 5ms.

Tips for DBA: Percentage of work completed for DBA commands

gladchenko — Thu, 16 Apr 2009 05:31:00 GMT

Как вы наверняка знаете, повторное исполнение команды KILL для сеанса показывает процент завершения отката прерванной инструкции. Это замечательная возможность, которая позволяет сберечь нервы администратора баз данных. Однако, есть и другие долгоиграющие команды, процент завершения которых тоже интересно было бы отслеживать. Ну, например, примерно таким же образом, как сообщает о прогрессе своей работы команда BACKUP. Вашему вниманию предлагается сценарий, который позволяет отслеживать прогресс исполнения следующих команд:

ALTER INDEX REORGANIZE
AUTO_SHRINK с ALTER DATABASE
BACKUP DATABASE
CREATE INDEX
DBCC CHECKDB
DBCC CHECKFILEGROUP
DBCC CHECKTABLE
DBCC INDEXDEFRAG
DBCC SHRINKDATABASE
DBCC SHRINKFILE
KILL (Transact-SQL)
RESTORE DATABASE
UPDATE STATISTICS

В сценарии использованы следующие административные представления и функции:

sys.dm_os_tasks
sys.dm_os_threads
sys.dm_exec_connections
sys.dm_exec_sql_text
sys.dm_os_waiting_tasks
sys.dm_exec_requests

Текст сценария:

-- Процент исполнения длительного запроса ввода-вывода SELECT ost.session_id , DB_NAME(ISNULL(s.dbid,1)) AS dbname , er.command , er.percent_complete , er.status , osth.os_thread_id , ost.pending_io_count , ost.scheduler_id , osth.creation_time , ec.last_read , ec.last_write , s.text , owt.exec_context_id , owt.wait_duration_ms , owt.wait_type FROM master.sys.dm_os_tasks AS ost JOIN master.sys.dm_os_threads AS osth ON ost.worker_address = osth.worker_address AND ost.pending_io_count > 0 AND ost.session_id IS NOT NULL JOIN master.sys.dm_exec_connections AS ec ON ost.session_id = ec.session_id CROSS APPLY master.sys.dm_exec_sql_text(ec.most_recent_sql_handle) AS s JOIN master.sys.dm_os_waiting_tasks AS owt ON ost.session_id = owt.session_id AND owt.wait_duration_ms > 0 JOIN master.sys.dm_exec_requests AS er ON ost.session_id = er.session_id AND er.percent_complete > 0 ORDER BY ost.session_id GO

Tips for DBA: Logical Disk FreeSpace Notification

gladchenko — Wed, 15 Apr 2009 13:46:00 GMT

В SQL Server 2008 с помощью службы SQL Server Agent и PowerShell можно достаточно просто соорудить задание, которое будет заглядывать в метаданные WMI локального или удалённого сервера, и сообщать по электронной почте, в случае если свободное место на указанном диске перешагнуло заданный порог. Ниже представлен облегчённый концепт сценария подобного задания (расписаний в нём нет и данные берутся по локальному серверу). Вам нужно будет заменить фиктивный адрес на реальный и указать почтовый профиль, если нельзя воспользоваться профилем по умолчанию.

USE [msdb] GO DECLARE @jobId BINARY(16) EXEC msdb.dbo.sp_add_job @job_name=N'Наблюдение за местом на диске С: текущего сервера', @enabled=1, @notify_level_eventlog=0, @notify_level_email=0, @notify_level_netsend=0, @notify_level_page=0, @delete_level=0, @category_name=N'[Uncategorized (Local)]', @owner_login_name=N'sa', @job_id = @jobId OUTPUT EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'1', @step_id=1, @cmdexec_success_code=0, @on_success_action=1, @on_success_step_id=0, @on_fail_action=3, @on_fail_step_id=0, @retry_attempts=0, @retry_interval=0, @os_run_priority=0, @subsystem=N'PowerShell', @command=N'$RemoteHost = "." $FreeSpace=((gwmi CIM_LogicalDisk -computerName $RemoteHost -filter "DeviceID = ''C:''").FreeSpace)/(1024*1024*1024) IF ($FreeSpace -lt 1000) { $conn = new-object system.data.oledb.oledbconnection $connstring = "provider=sqloledb;data source=.;initial catalog=tempdb;integrated security=SSPI" $conn.connectionstring = $connstring $conn.open() $cmd = New-Object system.data.oledb.oledbcommand $cmd.connection = $conn $FreeSpace = "''"+$FreeSpace+"''" $sqlquery = "EXEC msdb.dbo.sp_send_dbmail @recipients=''AlexanderGladchenko@domen.com'',@subject=$FreeSpace" $cmd.commandtext = $sqlquery $cmd.executenonquery() > NULL $cmd = New-Object system.data.oledb.oledbcommand $conn.close() } Else {$FreeSpace}', @database_name=N'master', @flags=0 EXEC msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)' GO