Отдел новостей 12 мая 2012, 08:33

Краткий user guide по идентификации проблем с производительностью баз данных на SQL Server 2005-2008

О чем этот пост

Ни для кого не секрет, что производительность приложения зависит от множества различных факторов, начиная с серверов, структуры БД, кода самого приложения и ещё кучи всего. В этом посте я попыталась описать последовательность действий, средства, утилиты и прочие штуки, которые я использую для идентификации проблем с производительностью баз данных на SQL Server 2000-2008. Всё, что здесь описано, нажито непосильным трудом и долгими годами. Это не значит, что я противник других СУБД, так сложилось, что большинство моих проектов именно на MS SQL Server’ах. Многие скрипты, утилиты и прочее имеют свои авторские права и лицензионные соглашения, поэтому прошу учитывать это, если будете использовать. Ну и не судите строго :)

Кому это будет полезно?

Всем, кто занимается нагрузочным тестированием, администрированием, поддержкой, разработкой приложений с БД на SQL Server 2005-2008. С SQL Server 2000 это так же может быть совместимо, но некоторые запросы придется подкорректировать.

Пример, когда это может быть использовано

Есть некое .NET приложение, использование ресурсов которым на сервере приложения под нагрузкой нормально, но при этом зашкаливает время отклика приложения и растет очередь обработки запросов («ASP.NET\Requests Queued» performance counter). Если профилирование приложения (к примеру, dotTrace профайлером) не дал никаких результатов или явно указывает на проблемы со временем выполнения запросов к БД.

Использование системных ресурсов SQL Server’ом

Об этом уже много песен спето на различных ресурсах, отмечу только, что обязательным для анализа здесь будут использование ЦПУ, памяти, дисковой подсистемы, пропускной способности сети. Краткая и очень хорошая шпаргалка по идентификации проблем есть здесь. Статья MSDN на эту тему (SQL Server 2005, актуально для SQL Server 2008).

Сбор статистики ожиданий ресурсов на SQL Server

• Рестарт SQL Server’a, чтобы очистить системные таблицы, хранящие статистику ожиданий ресурсов или очистить командой DBCC SQLPERF("sys.dm_os_wait_stats",CLEAR); • Запуск нагрузочного теста ещё раз, чтобы собрать статистику. В случае поддержки или администрирования БД нужно просто подождать пиковой активности пользователей, работающих с системой и после этого уже анализировать полученные данные. • Далее выполняем запрос

SELECT wait_type , waiting_tasks_count , wait_time_ms , max_wait_time_ms , signal_wait_time_ms , CASE WHEN wait_type = ‘OLEDB’ THEN ‘Optimize the query on the linked server’ WHEN wait_type = ‘ASYNC_NETWORK_IO’ THEN ‘Check clients submitting queries that produce large result sets’ WHEN wait_type = ‘CXPACKET’ THEN ‘Queries running in parallel and therefore expensive, Identify queries and attempt to tune’ WHEN wait_type = ‘PREEMPTIVE_OS_WRITEFILEGATHER’ THEN ‘Long Autogrow events, try plan and manually grow data and log files’ WHEN wait_type = ‘PREEMPTIVE_OS_AUTHENTICATIONOPS’ THEN ‘Problem talking to a Domain Controller, check with Infrastructure team to fix connectivity issues’ WHEN wait_type = ‘WRITELOG’ THEN ‘Poor I/O System performance writing to t-log, try tune the log file and check with Infrastructure team to improve IO system’ WHEN wait_type = ‘SOS_SCHEDULER_YIELD’ THEN ‘High Concurrency and competition for CPU time, Tune application code or Request Infrastructure to add more cores’ WHEN wait_type LIKE ‘LCK_M_%’ THEN ‘Look at Index Tuning and Isolation Levels’ WHEN wait_type LIKE ‘PAGEIOLATCH_%’ THEN ‘I/O Latency, Check with Infrastructure team to investigate and improve’ ELSE ‘Check with PROD DBA what this means’ END AS [Action Required] INTO #CheckWaitTypes FROM sys.dm_os_wait_stats WHERE ( wait_type IN ( ‘PREEMPTIVE_OS_WRITEFILEGATHER’, ‘PREEMPTIVE_OS_AUTHENTICATIONPS’, ‘ASYNC_NETWORK_IO’, ‘CXPACKET’, ‘OLEDB’, ‘SOS_SCHEDULER_YIELD’, ‘WRITELOG’ ) OR wait_type LIKE ‘LCK_M_%’ OR wait_type LIKE ‘PAGEIOLATCH_%’ ) AND wait_time_ms 0 ORDER BY wait_time_ms DESC SELECT * FROM #CheckWaitTypes DROP TABLE #CheckWaitTypes

• Полученные результаты будут содержать виды ожиданий, общее время ожиданий и краткое руководство к действиям. Описания видов ожиданий и «что с этим делать» можно найти тут.

Настройки SQL Server’a

Настроек очень много, но я коснусь только одной: max degree of parallelism (MAXDOP). Если в статистике ожиданий ресурсов присутствует CXPACKET, то значение MAXDOP параметра выбрано не оптимально. В частности в MS SQL Server 2008 он по умолчанию установлен в 0, что рекомендовано по большей части для OLAP БД. Проверить установленное значение параметра можно следующей командой: sp_Configure ‘max degree of parallelism’ или посмотреть в advansed properties SQL Server’a. Как подобрать оптимальное значение именно для вашей системы можно найти здесь. Описание данного параметра и вида ожидания можно найти тут.

Проверка на deadlocks

Не всегда приложение настроено таким образом, что при дедлоке в базе данных, эта информация будет донесена до пользователя, помещена в лог или же отображена в ответе сервера. Поэтому поиск и проверка на дедлоки в таких ситуациях необходима на всякий случай. Значение счетчика производительности SQLServer:Locks\Number of Deadlocks/sec покажет, есть ли дедлоки в системе, но не отразят естественно, на чем именно и почему. Ни для кого не секрет, что использование SQL Profiler’a во время нагрузочного теста или же в реальной эксплуатации системы может отразиться на результатах тестирования. Поэтому самым простым и безболезненным способом отлавливания дедлоков, на мой взгляд, является создание job’a на SQL Agent’e, который будет собирать информацию по дедлокам и сохранять графы дедлоков в файл. Скрипт для создания job’a можно найти тут. К слову, там же есть скрипты для идентификации и анализа блокировок. Что делать с полученными графами дедлоков, решать Вам. Или же этим будут заниматься разработчики БД, или же вы сами будете анализировать полученную информацию. При работе с анализом дедлоков также полезно знать информации об уровнях снапшот изоляции. Информацию можно найти тут. Замечание: Надо сказать, что если администратор сервера БД позволяет установить SQL Agent и использовать этот Job, то я использую его всегда, вне зависимости есть проблемы или нет. Это задание не увеличивает использование ресурсов на sql server’e и позволяет по окончании теста открыть файлик, убедиться, что там пусто и порадоваться. Это намного упрощает жизнь, т.к. воспроизведение дедлоков не самая простая задача. Так же был опыт внедрения такого способа вылавливания дедлоков на реальных системах.

Получение и анализ информации о фрагментации индексов

На больших БД, при наличии большого количества индексов, зачастую фрагментация очень сильно влияет на время выполнения запроса. С помощью этого скрипта можно получить информацию по индексам, фрагментация которых составляет более 30% и количество страниц которых больше 100:

SELECT db.name AS databaseName , ps.OBJECT_ID AS objectID , ps.index_id AS indexID , ps.partition_number AS partitionNumber , ps.avg_fragmentation_in_percent AS fragmentation , ps.page_count FROM sys.databases db INNER JOIN sys.dm_db_index_physical_stats (NULL, NULL, NULL , NULL, N'Limited') ps ON db.database_id = ps.database_id WHERE ps.index_id > 0 AND ps.page_count > 100 AND ps.avg_fragmentation_in_percent > 30 OPTION (MaxDop 1);

Если имеются индексы, фрагментация которых более 30%, то это тоже может быть причиной длительного выполнения запросов. Что нужно делать: перестраивать индексы. Я обычно перестраиваю с помощью процедуры dba_indexDefrag_sp (Author: Michelle Ufford, http://sqlfool.com). Скрипт для создания процедуры можно найти тут. Замечание: А вообще, дефрагментация индексов должна быть одной из составляющих обслуживания БД реальных систем.

Избыточные индексы

Иногда бывает так, что разработчики БД перестраховались и создали толпу индексов, которые в реальной эксплуатации (или в процессе тестирования) не используются для выборки данных. Но такие индексы всегда изменяются при вставке (INSERT) или обновлении (UPDATE) данных таблицы, что существенно замедляет работу БД, особенно если в процессе работы происходит много вставок и обновлений данных в таблицы с неиспользуемыми индексами. Здесь можно найти скрипт, который дает возможность выбрать ТОП 25 индексов, отсортированных по убыванию количества использований для выборки данных (Author: Pinal Dave): http://blog.sqlauthority.com/2011/01/04/sql-server-2008-unused-index-script-download/. Если вы уверены, что все возможные сценарии используются, и эти индексы действительно только обновляются, то их можно смело удалять. Это заметно облегчит жизнь БД при вставке и обновлении данных.

Пересмотр планов выполнения запросов

Если все вышеперечисленные танцы с бубнами не дали никаких результатов, то все же остается самое печальное: неоптимизированные запросы, отсутствующие индексы и все, что с этим связано. Для поиска ТОП 20 запросов по общему времени их выполнения (суммарное время выполнения таких запросов) с планами можно воспользоваться следующим скриптом, скрипт можно видоизменить и сортировать данные по среднему времени выполнения [average_duration]:

SELECT TOP 20 db_name(qp.dbid)as [database_name], object_schema_name(qp.objectid, qp.dbid)+'.'+object_name(qp.objectid, qp.dbid) as [object_name], SUBSTRING(qt.text, (qs.statement_start_offset/2)+1, ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(qt.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)+1) as [text_data] , qs.execution_count, convert(money, (qs.total_elapsed_time))/(execution_count*1000)as [average_duration], qs.total_elapsed_time, qs.last_elapsed_time, qs.min_elapsed_time, qs.max_elapsed_time, qp.query_plan FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp WHERE qt.encrypted=0 and db_name(qp.dbid)='Имя Вашей БД' ORDER BY qs.total_elapsed_time DESC

Замечание: Если оптимизатор SQL Server’a считает очевидным, что необходимо добавить какие-то индексы, то эти рекомендации будут содержаться в плане выполнения запроса.

Утилита для анализа трейса SQL Profiler‘a

Если дело все же дошло, до использования SQL Profiler‘a, то тут поможет очень хорошая бесплатная утилита для анализа трейса с SQL-Server‘a – ClearTrace. Данная утилита поддерживает трейсы с SQL Server‘ов 2000-2008, позволяет группировать похожие запросы по определенному признаку (приложение, логин, сервер и т.д.), предоставляет как среднюю, так и суммарную статистику по времени выполнения запросов, использованию ЦПУ и много других радостей.

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