Поиск узких мест ввода-вывода для MS SQL Server

Поиск узких мест ввода-вывода для MS SQL Server

Копипаст из http://msmvps.com/blogs/irinanaumova/archive/2011/05/06/1792775.aspx

По материалам статьи Tibor Nagy: How to Identify I/O Bottlenecks in MS SQL Server - 17.03.2011

Проблема

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

Решение

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

Performance Monitor

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

PhysicalDisk Object: Avg. Disk Queue Length. Этот счетчик показывает среднее число запросов чтения и записи, которые были поставлены в очередь для указанного физического диска. Чем выше это число, тем большее дисковых операций ожидает ввода-вывода. Если это значение во время пиковой нагрузки на SQL Server частенько превышает двойку, следует задуматься о необходимости принятия адекватных мер. Если используется несколько дисков, показания счётчика нужно разделить на число дисков в массиве и убедиться, не превышает ли результирующее значение число 2. Например, у Вас есть 4 диска и длина очереди диска 10, искомая глубина очереди находится следующим образом: 10/4 = 2,5, это и будет значением, которое нужно анализировать, а не 10.

Avg. Disk Sec/Read и Avg. Disk Sec/Write показывают среднее время чтения и записи данных на диск. Хорошо, если это значение не превышает 10 ms, но все еще приемлемо, если значение меньше 20 ms. Значения, превышающие этот порог, требуют исследования возможностей оптимизации.

Physical Disk: %Disk Time - время, которое диск был занят обслуживанием запросов записи или чтения. Это значение должно быть ниже 50%.

Disk Reads/Sec и Disk Writes/Sec - показатель уровня загруженности диска операциями чтения - записи. Значение должно быть меньше 85% от пропускной способности диска, поскольку при превышении этого порога время доступа увеличивается по экспоненте.
Пропускную способность диска можно определить постепенно увеличивая нагрузку на систему. Одним из способов определения пропускной способности дисковой подсистемы является использование специализированной утилиты SQLIO. Она позволяет определить ту точку, где пропускная способность перестаёт расти при дальнейшем увеличении нагрузки.

При выборе конфигураций RAID можно использовать следующие формулы вычисления числа операций ввода-вывода (I/Os), приходящихся на один диск:

Raid 0: I/O на диск = (чтений + записей) / число дисков массива
Raid 1: I/O на диск = [чтений + (записей *2)] / 2
Raid 5: I/O на диск = [чтений + (записей *4)] / число дисков массива
Raid 10: I/O на диск = [чтений + (записей *2)] / число дисков массива

Вот пример вычисления количества операций ввода-вывода на диск для RAID 1 на основе значений счетчиков:

Disk Reads/sec = 90
Disk Writes/sec = 75
Формула для ввода-вывода на RAID-1 массив является [чтений + (записей*2)] / 2 или [90 + (75*2)] / 2 = 120 I/Os на диск.

Динамические административные представления

Есть полезные динамические административные представления (DMV), с помощью которых можно выявить узкие места ввода-вывода.
Специальный тип ожидания краткой блокировки для операции ввода-вывода (I/O latch) имеет место тогда, когда задача переходит в состояние ожидания завершения кратковременной блокировки буфера, находящегося в состоянии обслуживания запроса ввода-вывода. В зависимости от типа запроса, это приводит к появлению ожиданий с именами PAGEIOLATCH_EX или PAGEIOLATCH_SH. Длительные ожидания могут указывать на проблемы с дисковой подсистемой. Чтобы посмотреть статистику таких ожиданий можно использовать системное представление sys.dm_os_wait_stats. Для того, что бы определить наличие проблем ввода-вывода, нужно посмотреть значения waiting_task_counts и wait_time_ms при нормальной рабочей нагрузке SQL Server и сравнить их со значениями, полученными при ухудшении производительности.

select * from sys.dm_os_wait_stats
where wait_type like
'PAGEIOLATCH%'
ORDER
BY wait_type asc

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

select
db_name(database_id),
file_id,
io_stall,
io_pending_ms_ticks,
scheduler_address
from sys.dm_io_virtual_file_stats (NULL, NULL) iovfs,
sys.dm_io_pending_io_requests as iopior
where iovfs.file_handle = iopior.io_handle

Дисковая фрагментация

Я рекомендую регулярно проверять уровень фрагментации и конфигурацию дисков, используемых экземпляром SQL Server.
Фрагментация файлов на разделе NTFS может стать причиной существенной потери производительности. Диски должны регулярно дефрагментироваться. Исследование показывают, что в некоторых случаях диски, подключаемые из сетей SAN, менее производительны, если их файлы дефрагментированы, т.е. эти СХД оптимизированы под случайный ввод-вывод. Прежде чем устранять файловую фрагментацию, стоит выяснить, как она сказывается на производительности работы SAN.
Фрагментация индексов также может стать причиной повышения нагрузки ввода-вывода на NTFS, но на это влияют уже другие условия, отличные от тех, что существенны для SAN, оптимизированных для случайного доступа.

Конфигурация дисков / Best Practices

Как правило, для повышения производительности, файлы журналов кладут на отдельные физические диски, а файлы данных размещают на других физических дисках. Ввод-вывод для высоко нагруженных файлов данных (включая tempDB) носит случайный характер. Ввод-вывод для файла журнала транзакций носит последовательный характер, кроме случаев отката транзакций.
Встроенные в шасси сервера (локальные) диски можно использовать только для файлов журнала транзакций, потому что они хорошо ведут себя при последовательном вводе-выводе, а при случайном вводе-выводе ведут себя плохо.
Файлы данных и журналов должны размещаться на разных дисковых массивах, у которых используются разные наборы физических дисков. В большинстве случаев, когда решение должно укладываться в не большой бюджет, я рекомендую размещать файл журнала транзакций на массиве RAID1, собранном из локальных дисков. Файлы данных БД лучше разместить на внешней системе хранения в сети SAN, так, чтобы к используемым для данных физическим дискам доступ получал только SQL Server, что позволит контролировать обслуживание его запросов и получать достоверные отчёты загрузки дисковой подсистемы. От подключения дисковых подсистем напрямую к серверу лучше отказаться.
Кэширование записи должно быть включено везде, где только это возможно, и вы должны удостовериться, что кэш защищен от перебоев в питании и других возможных отказов (независимая батарея подпитки кэша на контроллере).
Во избежание появления узких мест ввода-вывода для OLTP систем, лучше не смешивать нагрузки, характерные для OLTP и OLAP. Кроме того, удостоверьтесь, что серверный код оптимизирован и, где это необходимо, созданы индексы, которые тоже позволяют избавиться от ненужного ввода-вывода.

Дополнительные материалы

Как справиться с PAGELATCH при больших INSERT-нагрузках
SQL Server: Методика тестирования дисковой подсистемы
Tips for DBA: выравнивание кластеров NTFS и блоков RAID-массивов
Tips for DBA: Статистика I/O файлов баз данных
Минимальные требования к размещению файлов пользовательских баз данных
Дефрагментация баз данных SQL Server с помощью утилиты Diskeeper
Правда о дефрагментации
Ввод-вывод

Published Fri, May 6 2011 18:01 by Ирина Наумова

Filed under: SQL Server

ZX Spectrum в России: большой путь маленького компьютера - Компьютерра-Онлайн

ZX Spectrum в России: большой путь маленького компьютера - Компьютерра-Онлайн

Автоматический перезапуск службы агента сервера 1С Предприятия 8

Автоматический перезапуск службы агента

сервера 1С Предприятия 8.

   

Известно, что при функционировании рабочих процессов сервера 1С Предприятия 8.1 rphost.exe существует следующая проблема: из-за особенностей платформы, с подключением каждого нового клиента к рабочему процессу, увеличивается размер занимаемой рабочим процессом оперативной памяти.  При этом, при отключении клиента, занимаемый при подключении объем не освобождается. Так же объем памяти, занимаемый рабочим процессом может увеличиваться при выполнении программного кода. Данное поведение называется “Утечками памяти” или “Memory Leaks”. В итоге, когда объем занимаемой памяти процессом rphost.exe вырастает больше опеределенного предела (в моем случае это было >1Gb), рабочий процесс безусловно перезапускается. Понятно, что клиенты, подключенные к этому процессу, получают сообщение, что соединение с сервером разорвано, прекращают работу и вынуждены переподключаться. В зависимости от количества пользователей и количества запущенных рабочих процессов, данная проблема возникает с той или иной частотой. В моем случае - пользователей порядка 60 и один рабочий процесс, подобная ситуация возникала до 5 раз в неделю. Т.е. ни о какой стабильной работе системы 1С Предприятие в этом случае речь не идет. 

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

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

    Поскольку служба агента сервера 1С Предприятия представляет собой стандартную службу, в арсенале Windows есть стандартный способ остановки и запуска служб из командной строки командами net stop и net start. Достаточно эти команды лишь включить в bat-файл и настроить шедулер на запуск bat-файла по расписанию, я настроил перезапуск один раз в сутки, в ночное время. Следует иметь ввиду, что если перезапускать рабочие процессы в рабочее время, все подключенные к серверу 1С Предприятия клиенты будут отключены!

Кроме того, чтобы немного разнести по времени команды остановки и запуска службы, будем использовать известную утилиту sleep.exe, которую легко найти в Сети.

   

Примерный текст bat-файла restart1c.bat:

rem @echo off
rem \\----- начало скрипт остановки и запуска агента сервера 1С Предприятия----\\
set logfile="stopstartlog.txt"
set timeout=20
echo %date% %time% >>%logfile%
net stop "1C:Enterprise 8.1 Server Agent" >>%logfile%
c:\scripts\sleep %timeout%
echo %date% %time% >>%logfile%
net start "1C:Enterprise 8.1 Server Agent" >>%logfile%
c:\scripts\sleep %timeout%
rem \\----- конец скрипт остановки и запуска агента сервера 1С Предприятия----\\

* logfile - файл stopstartlog.txt, куда будут записываться результаты выполнения команд, размещается в том же каталоге, что и сам bat-файл;
** timeout - время в секундах;
*** c:\scripts - каталог, где предполагается разместить программу sleep.exe, bat-файл и лог-файл;

Из этого же bat-файла можно сразу после перезапуска процессов запускать скрипт бэкапа средствами 1С Предприятия. В этом случае у вас гарантированно не будет подключен ни один клиент.

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

Установка и обновление «1С Предприятие v8″ с помощью групповых политик (GPO)

17.04.2009 автора kvazar

При установке или обновлении программы 1С Предприятие многие администраторы сталкиваются с невозможностью корректного выполнения этих заданий с помощью групповых политик. Наиболее распространена ошибка 1720:
Product: 1C:Enterprise 8.1 — Error 1720.There is a problem with this Windows Installer package. A script required for this install to complete could not be run. Contact your support personnel or package vendor. Custom action customDetectPrevVersion script error -2147467259, Msi API Error: ProductInfo,Product,Attribute Line 7, Column 5
Данная ошибка вызвана некорректной работой механизма обновления программы, то есть мы не можем выполнить установку свежего билда поверх установленной предыдущей версии.
Для выполнения обновления необходимо вручную отредактировать установочный файл msi до создания групповой политики. Для этого мы используем бесплатный инструмент компании Microsoft для редактирования файлов msi под названием Orca. Эта утилита входит в состав  Microsoft Windows Software Development Kit (SDK), а такжe её можно скачать отдельно здесь.
Итак:

1. Скачиваем и устанавливаем Orca;
2. Открываем программой файл 1CEnterprise 8.1.msi
3. Находим раздел «CustomAction» и в нем параметр «customDetectPrevVersion«. Удаляем этот параметр, сохраняем изменения;
4. Копируем в общедоступную сетевую папку дистрибутив, который установлен на текущий момент (если мы обновляем билд) и новую версию 1С с измененным нами файлом msi. Копируем, естественно, в разные папки

Теперь нам необходимо создать групповую политику и создать в разделе «Установка программм» два пакета установки – старой (например 8.1.11)и новой (8.1.13) версий (рис.2).

Затем в свойствах пакета установки новой версии 1С нам необходимо указать, что данный пакет выполняет обновление старой версии 8.1.11 (рис.3). После назначения политики может потребоваться дополнительная перезагрузка компьютера, так как не синхронизированы удаление старой и установка новой версий ПО.

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

Лучшие методы: оптимизация индексов в SQL Server 2005

Stefano Demiliani (оригинал: Best practice when optimizing indexes on SQL Server 2005)
Перевод Моисеенко С.И.

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

Я вижу каждый день, как многие администраторы баз данных планируют оптимизацию индексов посредством написанных скриптов T-SQL или через стандартный SQL Maintenance Plan, но они не подозревают, что фактически сам SQL Server 2005 позволяет Вам "настраивать" этот процесс.

SQL Server 2005 предлагает опцию (ONLINE = ON or OFF), чтобы помочь настроить производительность и требования параллелизма при создании или перестройке индекса. С новой возможностью Online Indexing (ONLINE=ON) Вы можете продолжать выполнять запросы и операции с таблицей, индекс которой перестраивается, в то время как автономная индексация (ONLINE=OFF) блокирует таблицу.

Важно помнить: онлайновое создание или перестройка индекса (ONLINE=ON), обеспечивая максимальный параллелизм, потребляет больше ресурсов и требует больше времени на выполнение!!

В помощь управлению временным пространством во время операций с индексом SQL Server предоставляет еще одну опцию: SORT_IN_TEMPDB. SQL Server использует временное хранилище для сортировки и других промежуточных задач во время создания или перестройки индекса. Для этого временного хранилища может использоваться пользовательская база данных или база данных TEMPDB.

Когда опция SORT_IN_TEMP оператора CREATE INDEX или ALTER INDEX установлена в значение OFF (принимается по умолчанию), для временного хранилища используется пользовательская база данных. Когда опция SORT_IN_TEMP включена (ON), временное хранилище будет находиться в базе данных TEMPDB.

Вот рекомендации для лучшей стратегии создания/перестройки индекса (непосредственно от Microsoft). Я рекомендую распечатать ее:

Убедитесь, что TEMPDB находится на дисковой подсистеме, которая обеспечивает достаточную пропускную способность ввода/вывода, и что TEMPDB является достаточно большой, чтобы предоставить временное пространство, которое потребуется для операции создания или восстановления индекса. По умолчанию TEMPDB создается в каталоге Data в папке, куда установлен SQL Server (например, C:\SQL2005\MSSQL.1\MSSQL\Data). При такой конфигурации в TEMPDB может не оказаться достаточно места для обеспечения адекватной пропускной способности ввода/вывода. Поэтому лучшим методом является перемещение TEMPDB на носитель с достаточным количеством свободного пространства и производительностью после установки SQL Server. Кроме того, имейте в виду, что база данных TEMPDB - это общий ресурс для всего экземпляра SQL Server. Вам следует учитывать активность всех пользовательских баз данных, которые могут использовать TEMPDB, при планировании действий с TEMPDB.

Чтобы достичь наименьшего времени на создание или перестройку индекса, используйте автономный режим (ONLINE=OFF). Однако это предотвращает любой доступ пользователей к данной таблице на все время создания или перестройки индекса.

Чтобы достичь наименьшего влияния на доступ пользователей к таблице, используйте опцию онлайн (ONLINE=ON). Однако такая операция онлайн занимает больше времени и использует большее пространство в TEMPDB по сравнению с автономной операцией.

Чтобы использовать наименьший объем памяти в TEMPDB во время перестройки кластерного индекса, используйте автономный вариант (ONLINE=OFF). Однако это повлияет на параллелизм, поскольку доступ к таблице предотвращается на все время перестройки индекса.

Чтобы использовать наименьшее количество памяти в TEMPDB во время перестройки некластерного индекса, используйте онлайновый режим (ONLINE=ON). Онлайновая перестройка также обеспечивает лучший параллелизм, но потребует большего времени на выполнение.

Если для таблицы имеются транзакции, которые выполняются параллельно с онлайновым созданием или перестройкой индекса, Вы должны планировать дополнительное место в TEMPDB для хранения версий.

Включение режима AWE на SQL Server 2005

По умолчанию, сразу после установки, этот режим не включен.
Целесообразно включение данного режима, если у вас операционка 32-х битная и оперативной памяти на сервере более 4 Гб. В моем случае это MS Server 2003 Enterprise и 8Гб ОЗУ.
При этом, сервер выполняет роль одновременно и сервера баз данных (MS SQL 2005), и сервера приложений (1C Предприятие 8.1, 4 рабочих процесса).
Методика следующая:
- редактируем boot.ini - в строке загрузки добавляем опцию /PAE
- меняем в Properties SQL Server настройки минимум и максимум используемой памяти. В моем случае это 1Гб и 4Гб соответственно. Цифры мин и макс расчитаны с помощью методики, любезно предложенной г-ном Вячеславом Гилевым (http://gilev.blogspot.com)
- перезапускаем службу, в running values SQL Server видим, что значения мин и макс изменились
- включаем AWE
- перезапускаем сервер (reboot, а не просто перезапуск службы)
Примерно так.
Далее наблюдаем следующее:
- в диспетчере задач процесс sqlsrvr занимает намного меньше памяти, чем до включения AWE (в моем случае - 200Мб против 1,7Гб до того)
- с помощью Performance Monitor, значения из группы SQL Memory manager, счетчики Target Server Memory и  Total Server Memory отражают реальную картину: сколько памяти выделено и используется SQL-сервером.
Вместе с тем наблюдаю эффект увеличения размера свап-файла MS Server 2003. Вероятно, этот эффект как-то связан c использованием AWE.

Оперативный бэкап средствами SQL 2005 (продолжение)

Коллеги, требуется внести небольшую ремарку к пункту 1.2

"1.2. Непосредственно подключение логического диска (T-SQL):




EXEC xp_cmdshell 'net use N: \\SRV-nas\1c_backup'

GO"

Дело в том, что описываемую команду нужно выполнять каждый раз после рестарта сервера или службы SQL Server. Иначе SQL "забывает", куда именно нужно складывать наши бэкапы и, как следствие, выдает ошибку при выполнении Maintenance Plan.