 |
|
|
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Почему MS SQL медленно работает? Ищем причины
Почему MS SQL медленно работает? «Сервер тормозит! Ничего не выполняется! Весь отдел не может работать!» – не таков ли ночной кошмар многих администраторов Microsoft SQL Server? Увы, порой скорость работы этой базы данных падает без видимых причин. Что же случилось? Работа администратора базы данных Microsoft SQL Server порой бывает легка и необременительна. В самом деле, если настроена регулярная архивация, вдумчиво прописаны права пользователей, везде стоит последняя версия клиентского приложения, то заняться бывает нечем. Но увы, рано или поздно звонит (а то и прибегает) какой-нибудь «Главный Пользователь» и громко жалуется на то, что «всё тормозит и ничего не работает». При этом беглый анализ ситуации показывает, что вроде бы всё в порядке, сервисы запущены, в логах ошибок нет. То есть всё работает. Но очень медленно. И приходится администратору базы данных, а то и программисту из службы техподдержки брать универсальные инструменты Microsoft – EM (SQL Server Enterprise Manager) и QA (SQL Query Analyzer) и браться за решение проблемы производительности Microsoft SQL Server. Мой опыт показывает, что причин внезапных «торможений» не так уж и много.
Все примеры в данной статье рассмотрены на базе Microsoft SQL Server 2000. Основные причины замедления работы будут актуальны и для 2005-й версии, но способы решения для них могут заметно отличаться (впрочем, в 2005-й наверняка появятся свои собственные, уникальные способы работать медленно). Результаты DBCC-запросов и структура системных таблиц могут для них заметно отличаться, но общая логика работы остаётся прежней. Причина первая. Ошибки в индексах Они встречаются чаще всего. Когда программист создаёт индекс на таблице базы данных, таблица чаще всего или совсем пуста, или заполнена несколькими тестовыми строчками. Проверить эффективность выбранных индексов в данной ситуации практически невозможно. Поэтому индексируемые поля выбираются или наугад, или в соответствии со стандартными подсказками Microsoft SQL Server. Например, EM автоматически создаёт кластерный индекс по суррогатному первичному ключу, что довольно редко является идеальным решением. Из проблем производительности эта наиболее приятная, так как простое изменение схемы базы данных позволяет совершить маленькое чудо. Причина вторая. Неправильные планы запросов Microsoft SQL Server обладает очень неплохим оптимизатором запросов. Но и он иногда ошибается. Типичная причина ошибок – запрос с параметрами. Представьте себе, что у вас есть таблица заказов с полями Data и Status. И вас интересуют невыполненные заказы за последние полгода. Если вы попытаетесь получить информацию через запрос с параметрами, то сервер, скорее всего, выберет индекс по дате. Просто потому, что Status, как правило, одинаков для всех («Выполнен»), а вот дата принимает огромное число разных значений, и условие по ней может вернуть как большой, так и очень маленький результирующий набор. Мы-то знаем, что нас интересуют только невыполненные заказы, которых совсем мало, и при их поиске индекс по статусу сработал бы великолепно. Но серверу на момент компиляции запроса не известно, какое значение мы подставим в параметр @Status – «Выполнен» или «Не выполнен». Поэтому он не может использовать индекс по статусу, так как в том случае, когда параметр равен «Выполнен», этот индекс бесполезен. Вместо этого сервер предпочтёт индекс по дате, который даст более-менее приемлемую скорость в любом случае. Таким образом, упускается возможность использовать индекс по статусу, позволяющий мгновенно выбрать невыполненные заказы. И неэффективный план выполнения заставляет систему задуматься в самом неожиданном месте, где в нормальных условиях сервер зависать не должен. Отмечу, что не только запрос с параметрами может порождать неудачные планы запросов. Не менее часто встречаются сбои из-за нехватки памяти и некорректной статистики содержимого таблицы. Причина третья. Распределённые блокировки Microsoft SQL Server обеспечивает изоляцию транзакций с помощью блокировок строк, страниц и таблиц базы данных. При этом если два потока пытаются установить несовместимые типы блокировок на один и тот же объект, то одному из них придётся ждать, пока второй не закончит работу с этим объектом. Таким образом замедляется работа как отдельных процессов, так и всей системы. Иногда возникает такая ситуация, что первый процесс надолго захватил объект А, второй захватил B и ждёт, пока освободится А, третий захватил важный для всех объект C и пытается обработать B, а еще огромное множество процессов ждёт С, порой захватив еще что-нибудь (рис. 1).
Рисунок 1. Распределённая блокировка При этом создаётся впечатление, что не работает вообще ничего. Порой не получается даже запустить EM, так как блокировки есть на используемых им системных таблицах. Такую ситуацию я называю распределённой блокировкой, и это одна из очень неприятных проблем. В огромной куче заблокировавших друг друга процессов бывает довольно сложно найти тот, который во всём виноват. А им не всегда является самый первый процесс. В рассмотренной ситуации виноват скорее тот, кто захватил С. Причина четвертая. Дедлоки (Deadlocks) Особый случай блокировок. Знакомая всем ситуация – процесс 1 захватил объект А и ждёт доступа к Б, а процесс 2 захватил Б и ждёт доступа к А (рис. 2). Если бы не система обнаружения дедлоков, они бы прождали вечно. К счастью, MS SQL Server хорошо умеет обрабатывать эту ситуацию, и поэтому они обычно проявляют себя не в виде замедления работы, а в виде частых ошибок заданий (jobs) и откатов клиентских транзакций. Появление сообщений о дедлоках означает, что со стратегией блокировок не всё в порядке и распределённая блокировка уже где-то рядом.
Рисунок 2. Deadlock Когда дедлок происходит на двух отдельных объектах базы данных из-за доступа к ним в разном порядке (как изображено на рис. 2), тогда он весьма просто находится и проблема снимается. Хуже бывает, когда дедлок происходит внутри одной таблицы – если процессы сначала работали в разных её частях, а потом каждый захотел заглянуть на территорию другого. В этом случае дедлок может возникнуть даже без явной транзакции, во время самого обычного обновления большой таблицы. Причина пятая. Проблемы с памятью, диском и процессором Бывает и так, что в базе идеально настроены все индексы, запросы выполняются по оптимальным планам, взаимных блокировок процессов нет вообще – а работа всё равно происходит медленно. Увы, любая система в конце концов упирается в «железные» ограничения, в размер оперативной памяти, в пропускную способность и объём жестких дисков, в максимальную производительность процессора. Исчерпание любого из этих ресурсов может вызывать замедление работы. Проще всего обнаруживается (да, пожалуй, и лечится) нехватка места на жёстком диске. Причём последствия этой нехватки могут быть просто катастрофическими. Например, прекращение работы резервного копирования, остановка сервисов, работающих с диском, падение сервиса SQL и наконец аварийная остановка операционной системы без возможности нормальной загрузки. В результате можно потерять базу данных и все последние данные резервного копирования, так что за этим нужно следить постоянно, и лучше в автоматическом режиме. Исчерпание пропускной способности дисков вызывает только замедление работы, но зато справиться с нею гораздо сложнее, приходится задуматься о серьёзной реорганизации дискового массива. Проблема с недостатком оперативной памяти внешне выглядит очень похоже, с единственной разницей, что проблема касается не только SQL-сервера, но и всей операционной системы (так как современные компьютеры всегда стараются скомпенсировать недостаток памяти за счёт дискового пространства, серьёзно нагружая при этом подсистему ввода-вывода). Процессор, как ни странно, очень редко бывает камнем преткновения в работе MS SQL и никогда не приводит к резкому замедлению. Если загрузка процессора подскочила до 100% внезапно, то это не исчерпание ресурса процессора, а появление какого-то некорректно работающего приложения. Перед тем как принимать решение о необходимости масштабного обновления, обязательно надо проверить, всё ли в порядке с тем оборудованием, что уже есть. Мне известны случаи, когда случайно отключенный кэш записи на контроллере дискового массива приводил к падению скорости работы всей системы в четыре раза. И нельзя забывать, что проблемы на железном уровне могут быть следствием одной из предыдущих причин. Например, неправильный план запроса может создать большую нагрузку на жесткий диск, что не справится никакой RAID. Поэтому аппаратной частью имеет смысл заниматься только в том случае, если исключены все остальные причины. Поиск причин проблемы Итак, давайте вернёмся к исходной ситуации. Мы имеем недовольных пользователей и сервер, который работает очень медленно. С какой стороны начинать? Первое, что надо сделать – это определить масштабы происшествия (рис. 3). Если пользователи жалуются, что не работает вообще ничего, всё стоит, приложение не запускается, то, скорее всего, мы имеем дело с распределённой блокировкой или же с аппаратными проблемами (во втором случае жалобы будут менее интенсивными, зато гораздо более продолжительными, так как распределённая блокировка обычно когда-нибудь да заканчивается, а вот железные проблемы сами по себе наверняка не пройдут). Значит, в первую очередь надо проверить это направление. Совсем другая ситуация возникает, когда жалуются на какой-то конкретный процесс или запрос. Например, слишком медленно работает тарификация, слишком долго выставляются счета, тогда как как всё остальное практически в порядке. В этой ситуации стоит поискать проблемный запрос, работающий не с тем индексом или по неудачному плану. Глобальное замедление работы Давайте рассмотрим различные методы анализа ситуации, когда замедляется работа всей системы. Общий анализ блокировок в системе Каждый администратор знает, что блокировки на сервере определяются процедурой sp_lock. Но вот скажите честно, что можно понять, глядя на выводимый ею результат...
На её основе можно разве что сформулировать утверждение «блокировки на сервере есть». Если вы регулярно запускаете sp_lock, то вы можете оценить лишь среднее число блокировок и убедиться, что сейчас их «больше», «меньше» или «как обычно». Для более серьёзного анализа такая информация бесполезна. Распределённая блокировка часто возникает при обычном числе строк в sp_lock. К счастью, у нас есть возможность обработать результаты этого запроса и получить полную картину блокировок в системе. Для этого достаточно сохранить результаты запроса во временной таблице и собрать данные из системных таблиц: Скрипт 1 if ( select object_id( 'tempdb..#LOCK' ) ) is not null drop table #LOCK go -- В вашей версии SQL Server формат временной таблицы может быть другим, проверьте вывод sp_lock. create table #LOCK ( spid int , dbid int , ObjId int , IndId int , Type varchar(4) , Resource varchar(16) , Mode varchar(8) , Status varchar(6) ) insert into #LOCK exec sp_lock -- Какие процессы ожидают захвата каких ресурсов. select count(*) as LockCnt, left(o.name, 32 ) as ObjectName , l.Status, l.Type, l.Mode, l.spid , max(Resource) as SampleResource , left(max(ss.loginame), 16 ) as loginname , max(ss.cmd) as cmd , max(ss.lastwaittype) as lastwaittype , left(max(ss.program_name), 32 ) as program_name , left(max(ss.hostname), 32) as hostname , max(ss.program_name) as full_program_name from #LOCK l , sysobjects o with (nolock) , master..sysprocesses ss with (nolock) where o.id = l.ObjId and l.spid = ss.spid and l.Status not in ( 'GRANT', 'CNVT') and l.dbid = db_id() group by o.name, l.Status, l.Type, l.Mode, l.spid -- Кто мешает выполняться процессам из предыдущего запроса, кто держит их ресурсы. select count(*) as LockCnt, left(o.name, 32 ) as ObjectName , l.Status, l.Type, l.Mode, l.spid , max(Resource) as SampleResource , left(max(ss.loginame), 16 ) as loginname , max(ss.cmd) as cmd , max(ss.lastwaittype) as lastwaittype , left(max(ss.program_name), 32 ) as program_name , left(max(ss.hostname), 32) as hostname , max(ss.program_name) as full_program_name from #LOCK l , sysobjects o with (nolock) , master..sysprocesses ss with (nolock) where o.id = l.ObjId and l.spid = ss.spid and l.dbid = db_id() and l.Status in ( 'GRANT', 'CNVT') and exists ( select * from #LOCK l2 where l2.Status not in ( 'GRANT', 'CNVT') and l2.dbid = db_id() and l.ObjId = l2.ObjId and l.Resource = l2.Resource ) group by o.name, l.Status, l.Type, l.Mode, l.spid
Как правило, бывает достаточно несколько раз выполнить скрипт 1, чтобы полностью оценить картину – какие процессы блокируют какие ресурсы и кому при этом мешают. Я не стану приводить полное описание полей запроса, так как это простое соединение (join) результата sp_lock с системными таблицами sysobjects (анализируются только блокировки на текущей БД) и master..sysprocesses, их поля описаны в BOL (Books Online), да и сами по себе они довольно очевидны. Хочу сразу предупредить о возможной проблеме. Sp_lock даёт мгновенный снимок ситуации. К тому времени, когда выполнение добирается до sysobjects и master..sysprocesses, уже может не оказаться тех процессов, что создавали блокировки, зато могут появиться новые процессы (причём с тем же spid), и запрос даст неправильный результат. Особенно это актуально, когда система «живёт», и в ней в каждый момент времени появляется и исчезает большое число процессов и выполняется много запросов. Но в случае распределённой блокировки или же просто сложной ситуации нужные вам процессы останутся на месте. Хуже того, я советую не принимать решений по первому же результату. Выполните этот запрос несколько раз, чтобы понять, какие процессы были заблокированы случайно и ненадолго, а какие стабильно присутствуют и являются основой распределённой блокировки. Тот же самый скрипт 1 поможет разобраться в насыщенной блокировками базе данных даже в отсутствие распределённой блокировки. Бывает так, что работать с базой можно, но процессы часто ждут завершения работы друг-друга, и общая скорость работы заметно понижается. Этим данная ситуация отличается от распределенной блокировки, когда «создаётся впечатление, что ничего вообще не работает. Тогда запрос о картине блокировок приходится делать многократно, возможно, даже потребуется вынести в отдельное задание выполнение запросов и сохранение результатов в специальной таблице, чтобы потом проанализировать статистику и понять, какие процессы чаще всего «цепляются». Анализ блокировок отдельного процесса Тот же метод сохранения блокировок во временной таблице может быть использован и для анализа текущего состояния отдельного процесса. Конечно, надёжнее всего отследить его работу с помощью профайлера. Но бывает так, что профайлер не может помочь, ведь он показывает текст запроса только в момент начала или окончания его обработки. А что делать, если начало мы уже пропустили, а конец будет неизвестно когда, так как процесс подвис? Кроме того, далеко не всегда есть время (или возможность) запустить и настроить профайлер. В этой ситуации может помочь разобраться вот такой скрипт (выполняемый после сохранения блокировок в #LOCK): Скрипт 2 select count(*) as LockCnt, left(o.name, 32 ) as ObjectName , l.Status, l.Type, l.Mode, l.spid , max(Resource) as SampleResource , left(max(ss.loginame), 16 ) as loginname , max(ss.cmd) as cmd , max(ss.lastwaittype) as lastwaittype , left(max(ss.program_name), 32 ) as program_name , left(max(ss.hostname), 32) as hostname , max(ss.program_name) as full_program_name from #LOCK l , sysobjects o with (nolock) , master..sysprocesses ss with (nolock) where o.id = l.ObjId and l.spid = ss.spid and l.dbid = db_id() and l.spid = @spid --and program_name like '%0x08989EF05DAC704E94F6D25A2EB2FB75%' group by o.name, l.Status, l.Type, l.Mode, l.spid Как видите, он просто возвращает список всех блокировок, которые осуществляет данный процесс. Если вам известен spid процесса, то можно в условии использовать прямо его. При желании вам доступен весь спектр полей master..sysmessges, позволяющий отобрать процесс по имени приложения, логину пользователя, названию клиентского компьютера или любым другим сочетанием условий. Результат этого запроса позволяет понять, к каким таблицам ваш процесс сейчас осуществляет доступ. Часто этого бывает достаточно, чтобы оценить точку выполнения с точностью до строки кода. Распределённая блокировка и sp_who2 Прежде чем переходить к следующей части, нельзя не вспомнить более известный способ поиска головного процесса в распределённой блокировке. Это – системная процедура sp_who2. Она показывает в поле BlkBy важнейшую информацию – ожидает ли конкретный процесс завершения работы другого, и какого именно. По результатам выполнения sp_who2 довольно легко найти тот spid, который никем не заблокирован, но при этом блокирует всех остальных. Я предпочитаю обработанный вывод sp_lock, так как он показывает, не только какой процесс является причиной блокировок, но и какие объекты являются причиной столкновения интересов. Ведь порой бывает, что вносить исправления надо вовсе не в головной процесс, а в один из заблокированных им (например, добавить в их код подсказку nolock, чтобы они больше не зависели от головного процесса). Но если вам сложно оказалось разобраться в огромном списке ожидающих и ожидаемых процессов, возвращаемых скриптом 1, то sp_who2 может вам помочь. Учитывайте только, что при совместном использовании скрипта 1 и sp_who2 их стоит запускать в одном и том же пакете (batch), чтобы минимизировать время между снимками состояния блокировок и состояния процессов, иначе они могут отображать существенно разные картины. Как определить содержание процесса по его номеру? Итак, тем или иным способом вы нашли spid процесса, который виноват в замедлении работы. Но обычно по spid сложно понять, что же этот процесс делает и как изменить его, чтобы устранить проблему. Единственный случай, когда spid вам реально пригодится, если процесс запущен с вашей же машины под вашим логином и из приложения Query Analyzer. Тогда вам останется просто перебрать все открытые окна QA в поисках нужного. (В окне QA идентификатор процесса написан в статусной строке, это число в скобках после имени пользователя.) В любых других случаях надо искать более конкретную информацию о том, кто же является хозяином процесса. В первую очередь посмотрите на master..sysprocesses (самые полезные поля из нее сразу выводятся скриптом 1). Как правило, вы увидите имя пользователя, компьютера и приложения, запустившего нужный вам процесс. Очень часто этого бывает достаточно. Если приложение называется как-то вроде «Job 0x08989EF05DAC704E94F6D25 A2EB2FB75 Step 2» – значит, за этот процесс отвечает задание сервера. Его «настоящее имя» (то есть то, которое можно увидеть в папке <ИмяСервера>/Management/SQL Server Agent/Jobs приложения EM) узнать очень просто: выполните в QA запрос: select convert( varbinary(30), job_id), name from msdb..sysjobs order by name и найдите в списке код нужного вам задания. Если на вашем сервере задания играют существенную роль, вам скоро надоест делать поиск по этому списку, и вы, вероятно, захотите сразу видеть имя нужной вам задачи в выводе скрипта. Но тут имеется небольшая проблема – когда вы делаете запрос в QA, он автоматически преобразует для вас varbinary в нужную форму. А чтобы сделать то же самое программно, понадобится небольшая процедура: Скрипт 3 create function dbo.uniqueidentifier_to_varchar ( @u uniqueidentifier ) returns varchar(34) as begin declare @binary varbinary(16), @i int , @res varchar(34), @byte smallint , @hex char(2) select @binary = convert( varbinary(16), @u ) , @i = 1, @res = '0x' while ( @i <= 16 ) begin select @byte = substring(@binary, @i, 1) select @hex = case when @byte/16 < 10 then char( ascii('0') + @byte/16 ) else char( ascii('A') + @byte/16 - 10 ) end + case when @byte%16 < 10 then char( ascii('0') + @byte%16 ) else char( ascii('A') + @byte%16 - 10 ) end select @res = @res + @hex, @i = @i + 1 end return @res end Теперь можно найти нужную задачу напрямую, например, так: select name from msdb..sysjobs where dbo.uniqueidentifier_to_varchar( job_id ) = "0x07E7726D989CCA4E9103F874F473C2AF" или так: select * from master..sysprocesses with (nolock) , msdb..sysjobs with (nolock) where program_name like '%' + dbo.uniqueidentifier_to_varchar(job_id) + '%' -- Этот запрос возвращает всю информацию о запущенных в данный момент заданиях, включая их названия, sp_id, -- время запуска и остальную информацию из master..sysprocesses и msdb..sysjobs Но всё же я не рекомендую использовать функцию dbo.uniqueidentifier_to_varchar(job_id) в повседневной работе, т.к. она довольно медленная. Как вариант для быстрого анализа связи задания и его имени разумнее будет заполнить специальную табличку с двумя полями (uniqueidentifier и varchar(34)) и использовать её в запросах, обновляя по мере необходимости. Это чревато рассинхронизацией данных, однако задания довольно редко создаются, так что вполне можно работать, обновляя табличку по мере необходимости. Если всего вышерепечисленного оказалось недостаточно, попробуйте выполнить: dbcc inputbuffer( <Номер процесса> ) Этот dbcc-запрос покажет, какой именно код выполняет процесс, указанный в качестве его параметра. Помните, что размер буфера ограничен и что-нибудь «многоэтажное» может оказаться почти полностью за его рамками. Но того, что есть, обычно оказывается достаточно, чтобы понять, «откуда ветер дует». Читатель может заметить, что предлагаемые мною скрипты не добавляют никакой новой информации. Практически всё это можно получить с помощью EM (/Management/current Activity). На это я могу сказать только одно – если вам так удобнее, используйте EM! Образы мышления людей, предпочитающих табличное и древовидное представления, очень отличаются друг от друга. Но и тем, кто предпочитает дерево, мой запрос может пригодиться. Ведь EM не отличается высокой скоростью работы на медленных соединениях, и вам придётся очень долго ждать реакции (и не получится обновлять представление достаточно часто, чтобы увидеть динамику происходящего). Кроме того, запрашивая информацию об объектах, EM создаёт блокировки на таблицах. А это значит, что в случае глобальной распределённой блокировки, затрагивающей системные таблицы, он сам попадает в число заблокированных процессов и не способен сообщить вообще ничего (cкрипты же специально на этот случай обращаются к системным таблицам с nolock). Еще одна особенность EM по сравнению с приведёнными скриптами – он не ограничен текущей базой данных и показывает картину процессов всего сервера (сами решайте, плюс это минус). Чтобы получить доступ к блокировкам на нескольких конкретных базах данных сразу, в скрипт достаточно будет добавить несколько разных таблиц sysobjects, но сделать его полностью универсальным можно только с помощью представления (view) или динамического запроса (см. таблицу). Сравнение двух методов анализа блокировок в системе
Давайте вернёмся к ситуации с глобальным замедлением работы сервера. Диагностика проблем на аппаратном уровне Представьте, вы выполняете запрос, показывающий состояние блокировок, а он говорит, что блокировок на базе данных нет! Несколько последующих запросов выдают ту же самую картину, а сообщения о проблемах продолжаются. Увы, в этом случае нет однозначного рецепта. Начать стоит с проверки разнообразных логов – начиная с журналов заданий и SQL Server и заканчивая журналами Windows. Возможно, причиной замедления работы явился какой-то ресурсоёмкий процесс, вроде резервного копирования, перестройки индексов или проверки физической целостности базы данных. Если сервер работает на пределе мощности, то такие процессы способны в разы замедлить его реакцию. Для выявления самых ресурсоёмких процессов можно использовать запрос, подобный следующему: Скрипт 4 select top 10 physical_io/(datediff( second, login_time, dateadd( second, 2, getdate()) ) ) , physical_io, spid , datediff( second , login_time , dateadd( second, 2, getdate()) ) , program_name, * from master..sysprocesses order by physical_io/(datediff( second , login_time , dateadd( second, 2, getdate()) ) ) desc Его действие основано на том, что для каждого процесса сервер ведёт счётчик использования основных ресурсов – памяти, диска, процессора. Сами по себе показания счётчика дают немного, и при упорядочении по ним вперёд вырываются системные процессы – те, что работают с момента включения системы и до момента её выключения. Но если поделить значение счётчика на время жизни процесса, мы получим интенсивность использования ресурсов, что даёт неплохую возможность найти самый нагружающий сервер процесс. Только не стоит обращать внимание на короткоживущие процессы, на секунду выпрыгивающие наверх и исчезающие при повторном запросе. Они попадают в лидеры только из-за маленького времени жизни и впоследствии не оказывают большого влияния на сервер. Кроме physical_io, можно точно так же строить запросы на основе полей cpu и memusage. Если и здесь не получилось найти виновного, то придётся спускаться на аппаратный уровень (а даже если и получилось, то надо учитывать, что когда ресурсов достаточно, никакой dbcc checkdb не должен замедлять работу сервера). Этот вопрос, вообще говоря, выходит за рамки данной статьи, и администратору базы данных порой стоит делегировать его специалистам по серверному оборудованию. Скажу лишь только, что состояние дисков и памяти сказывается на работе сервера баз данных чаще, чем хотелось бы, причём довольно часто это выглядит именно как падение производительности. Простое уменьшение места на дисках очень сильно замедляет работу (а потом делает её невозможной). Если RAID выполняет интеграцию нового диска в массив, то он не только работает в несколько раз медленнее, но и находится в очень нестабильном состоянии (если сбой питания для обычного жесткого диска слегка неприятен, то для RAID-массива в состоянии перестройки практически смертелен). Сбои в памяти чреваты целым спектром проблем от простого замедления до появления ошибочных данных и полного разрушения базы. Процессор тоже может влиять на поведение сервера непредсказуемым образом. Однажды мне пришлось решать следующую проблему. Все процессы начинали работу в хорошем темпе. Однако спустя некоторое время скорость работы падала в несколько раз. Ситуацию спасло отключение Hyper Threading. Возможно, оптимизатор параллельных процессов Microsoft SQL Server принимал «сдвоенный» процессор за два отдельных и соответственно планировал запросы. В решении аппаратных проблем порой помогают счётчики производительности (performance counters). Они пригождаются и для решения специфических для SQL задач, так как сервер позволяет наблюдать огромное число своих параметров. Мне часто приходилось работать в ситуации, когда я не имела доступа к удалённой консоли, да и просто не могла выполнить Windows-логин на сервер (или это было долго и неудобно), но имела права на выполнение xp_ cmdshell. В этой ситуации можно более-менее комфортабельно смотреть на счётчики производительности с помощью утилиты typeperf. Она входит в Microsoft Windows NT Workstation 4.0 Resource Kit, Windows 2000 Resource Kit или в Windows XP. Пример выполнения этой процедуры из QA приведён в скрипте 5. Результат её работы можно посмотреть в разделе «Пример работы утилиты typeprf». Скрипт 5 -- Для того чтобы посчитать процессоры и логические диски и получить список доступных счётчиков, раскомментируйте -- следующие запросы: exec master..xp_cmdshell 'typeperf -qx \Processor ' и exec master..xp_cmdshell 'typeperf -qx \PhysicalDisk' exec master..xp_cmdshell 'typeperf -sc 10 "\Processor(_Total)\% Processor Time" "\PhysicalDisk(1 D:)\% Disk Time" / "\PhysicalDisk(1 D:)\Avg. Disk Queue Length" "\PhysicalDisk(1 D:)\% Idle Time" "\SQLServer:Buffer manager\Checkpoint pages/sec" ' Даже если у вас есть все права доступа и соответствующие утилиты, я всё-таки рекомендую поставить typeperf на сервер. Может статься так, что у вас будет очень ограниченное время на анализ ситуации, и тогда доступ к Windows-счётчикам без стандартной утилиты администрирования Perfomance monitor вам очень пригодится. Очевидно, что подобным же образом можно смотреть журналы любых приложений (очень рекомендую стандартные GNU-утлиты tail, head и grep), в том числе Windows логи и даже логи Sql Server (из EM до них порой не добраться). Итак, в первой части статьи мы рассмотрели методы локализации проблем производительности Microsoft SQL Server в случае глобальнного замедления работы. Нам осталось рассмотреть, как найти проблемную точку в заранее известном процессе, локализовать дедлоки, и обсудить методы решения обнаруженных проблем. Этим вопросам будет посвящена вторая часть статьи. Приложение Пример анализа блокировок Проанализируем результат работы скрипта 1 (вывод см. ниже). Думаю, читатель оценит уровень его информативности (особенно в сравнении с результатом sp_lock).
Безобидная разделяемая блокировка Мне часто встречалась одна характерная ошибка, и я хочу предостеречь от нее вас. А именно – при анализе блокировок полностью не принимаются во внимание блокировки типа S (Share, разделяемая) и IS (Intent Share, разделяемая блокировка намерения). Считается, что эти блокировки «безобидные». Такой вывод делается потому, что блокировки S от разных процессов совместимы между собой, и много приложений могут рассматривать объект одновременно, в противоположность X-блокировкам, которые захватывают объект в личное пользование и несовместимы ни с чем. Но нельзя забывать о том, что S-блокировки не совместимы с X. Например, если какую-то таблицу постоянно дописывают или обновляют множество процессов, то один пользователь, решивший выполнить сканирование таблицы и заполучивший на нее табличную S-блокировку, способен полностью застопорить работу всей системы с помощью «безобидной» блокировки на чтение. Поэтому, например, правилом хорошего тона является всегда писать nolock при сканировании большой и часто обновляемой таблицы в рабочей базе данных. Единственной по-настоящему безобидной блокировкой является Sch-S, означающая запрет на изменение структуры таблицы и ничего больше. Если процесс наложил такую блокировку, значит, он читает её в nolock-режиме или его уровень изоляции транзакции допускает «грязное чтение», что по сути означает то же самое. Пример работы утилиты typeperf Cистема прилично загружена, средняя длина очереди составляет 1-2 пакета, тогда как в нормальном состоянии она близка к нулю. Пора подумать о модернизации, так как время простоя дисков уже маленькое (диски простаивают от 50 до 5% времени, т.е. иногда они загружены на 95%). Но прямо сейчас они не должны замедлять работу, небольшие резервы еще есть. Контрольная точка (Checkpoint) проходит стабильно за 1-2 секунды, значит, производительность записи на диск достаточная. Стоит проверить счётчики, ориентированные именно на чтение.
|
||||||||||||||||||||||||||||||||
ЮЛИЯ ШАБУНИО
Комментарии отсутствуют
| Добавить комментарий |
|
Комментарии могут оставлять только зарегистрированные пользователи |
|
