SQL Server: Минимизация дискового ввода/вывода

Оптимизация запросов и индексирования является эффективным способом снижения числа операций физического и логического ввода/вывода.

Выдержка из книги «SQL Server DMV Starter Pack» (Red Gate Books, 2010).

Гленн Берри, Луи Дэвидсон и Тим Форд

Существует постоянная потребность в минимизации числа операций физического и логического ввода/вывода. Набор связанных с вводом/выводом DMO-объектов позволяет в числе прочего исследовать физический ввод/вывод, происходящий в системе при записи и чтении данных с диска.

DMO-объекты в этой категории предоставляют наглядную картину ввода/вывода с точки зрения дисковой подсистемы. К примеру, они показывают нам, как операции ввода/вывода распределены по файлам на диске, в каких местах ввод/вывод становится узким местом, где происходят задержки ввода/вывода и т. п. Эту информацию можно использовать для оптимизации архитектуры дисковой подсистемы. Можно также собирать данные и использовать их в качестве фактического материала при запросе у руководства денег на приобретение дополнительных устройств хранения.

Естественно, что физический ввод/вывод неизбежен. SQL Server должен записывать данные приложений на диск. Он также должен записывать в журнал транзакций все операции вставки, обновления и удаления, а также массовые операции. Но прежде чем принимать быстрое решение, что требуется больше дискового пространства, вспомните, что очень многого в плане сокращении числа ненужных операций физического и логического ввода/вывода можно добиться за счет оптимизации запросов и индексирования.

При анализе нужно учитывать информацию о вводе/выводе, полученную из описанных в этой статье DMO-объектов (их название начинается с «sys.dm_io_»), а также данные других динамических административных представлений (DMV), которые так или иначе предоставляют сведения о вводе/выводе:

• sys.dm_exec_query_stats – стоимость ввода/вывода за время выполнения запроса
• sys.dm_exec_connections – ввод/вывод в текущем подключении
• sys.dm_exec_sessions – ввод/вывод в текущем сеансе
• sys.dm_os_workers – ввод/вывод для каждого рабочего потока

Если не указано иное, все эти сценарии работают в SQL Server 2005, 2008 и 2008 R2 и всем им требуется разрешение VIEW SERVER STATE.

Исследование узких мест дисковой подсистемы с использованием задержек ввода/вывода

DMV, которое мы здесь используем, называется sys.dm_io_virtual_file_stats и описано в электронной документации по SQL Server так: «Возвращает статистику ввода-вывода для данных и файлов журнала. Это динамическое административное представление заменяет функцию fn_virtualfilestats».

Это DMV-представление принимает два аргумента: database_id и file_id, то есть идентификаторы БД и файла. Оба они могут принимать значение NULL. В таком случае возвращается информация обо всех базах данных или всех файлах.

Надо иметь в виду, что это DMV накопительное. Иначе говоря, значения в столбцах данных постоянно накапливаются с момента последней перезагрузки сервера. Это означает, что для получения полезных данных надо предварительно измерить базовый уровень. После вычитания базовых показателей из текущих можно определить, где накапливаются операции ввода/вывода.

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

-- Запрос вычисляет среднюю задержку чтения, записи и каждой операции ввода/вывода -- для всех файлов базы данных. SELECT DB_NAME(database_id) AS [Database Name] , file_id , io_stall_read_ms , num_of_reads , CAST(io_stall_read_ms / ( 1.0 + num_of_reads ) AS NUMERIC(10, 1)) AS [avg_read_stall_ms] , io_stall_write_ms , num_of_writes , CAST(io_stall_write_ms / ( 1.0 + num_of_writes ) AS NUMERIC(10, 1)) AS [avg_write_stall_ms] , io_stall_read_ms + io_stall_write_ms AS [io_stalls] , num_of_reads + num_of_writes AS [total_io] , CAST(( io_stall_read_ms + io_stall_write_ms ) / ( 1.0 + num_of_reads + num_of_writes) AS NUMERIC(10,1)) AS [avg_io_stall_ms]FROM sys.dm_io_virtual_file_stats(NULL, NULL)ORDER BY avg_io_stall_ms DESC ;

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

Исследование узких мест дисковой подсистемы с использованием ожидания ввода/вывода

Здесь подразумевается немного другой подход к исследованию узких мест подсистемы ввода/вывода. Мы воспользуемся представлением sys.dm_io_pending_io_requests, о котором в электронной документации по SQL Server говорится так: «Возвращает по строке для каждого, ожидающего выполнения, запроса ввода-вывода в SQL Server».

Это DMV предоставляет снимок запросов на ввода/вывода на определенный момент времени, а именно на момент выполнения сценария:

-- Look at pending I/O requests by file SELECT DB_NAME(mf.database_id) AS [Database] , mf.physical_name ,r.io_pending , r.io_pending_ms_ticks , r.io_type , fs.num_of_reads , fs.num_of_writesFROM sys.dm_io_pending_io_requests AS r INNER JOIN sys.dm_io_virtual_file_stats(NULL, NULL) AS fs ON r.io_handle = fs.file_handle INNER JOIN sys.master_files AS mf ON fs.database_id = mf.database_id AND fs.file_id = mf.file_idORDER BY r.io_pending , r.io_pending_ms_ticks DESC ;

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

Последние два столбца возвращает накопительное число чтений и записи в файл с момента последней загрузки SQL Server (или с момента создания файла — используется самое последнее событие). Эта информация полезна при решении, какой уровень RAID использовать на том или ином диске. Например файлы, в которых активно происходит запись, обеспечат большую производительность при использовании RAID 10 LUN, чем RAID 5 LUN.

Знание относительного распределения операций чтения и записи для каждого файла позволяет осознанно размещать их в те или иные LUN. А это, в свою очередь, позволит оптимизировать запросы для обеспечения более высокой производительности.

Гленн Берри (Glenn Berry) — работает архитектором баз данных в компании NewsGator Technologies из города Денвер, штат Колумбия. Он обладает званием MVP в области SQL Server, и обладает целым набором сертификатов Microsoft, в том числе MCITP, MCDBA, MCSE, MCSD, MCAD и MCTS, что означает, что ему действительно нравится сдавать сертификационные экзамены.

Луи Дэвидсон (Louis Davidson)— работает в области ИТ уже более 16 лет в качестве разработчика и архитектора корпоративных баз данных. Он носил звание SQL Server MVP на протяжении шести лет и написал четыре книги по проектированию баз данных. В настоящее время он занимается разработкой архитектуры баз данных и немного администрированием БД в компании Christian Broadcasting Network, поддерживая работу филиалов в г. Вирджиния-Бич, штат Вирджиния, и г. Нешвилл, штат Теннеси.

Тимоти Форд (Timothy Ford) — обладает званием SQL Server MVP и занимается SQL Server уже более десяти лет. Он является основным администратором баз данных и экспертом по платформе SQL Server в компании Spectrum Health. С 2007 года его статьи по вопросам технологии публикуются на различных веб-сайтах, а также Тим ведет собственный блог (thesqlagentman.com), в котором рассказывает о SQL, а также об удаленной работе и профессиональных секретах разработчиков.