ИНСТРУКЦИИ: Устранение проблем с производительностью приложений при работе с SQL Server

Переводы статьи Переводы статьи
Код статьи: 224587 - Vizualiza?i produsele pentru care se aplic? acest articol.
Развернуть все | Свернуть все

В этой статье

Аннотация

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

Профилировщик SQL


Профилировщик SQL – это эффективное средства для разрешения проблем производительности приложений SQL Server 7.0 или более поздней версии. Профилировщик SQL позволяет просто записывать все события, происходящие на сервере в условиях обычной нагрузки, и предоставляет сведения о них. Использование профилировщика SQL вместе с системным монитором Microsoft Windows NT и несколькими простыми запросами для определения возникновения блокировки позволяет получить необходимую информацию для разрешения практически всех проблем производительности.

Объекты для наблюдения

1. Настройте профилировщик SQL для записи трассы. Для этого выполните следующие действия.
  1. Откройте профилировщик SQL.
  2. В меню Tools (Сервис) выберите пункт Options (Параметры).
  3. Убедитесь, что установлены параметры All Event Classes (Все классы событий) и All Data Columns (Все столбцы данных).
  4. Нажмите кнопку ОК.
  5. Создайте новую трассу.
  6. В меню File (Файл) выберите команду New (Создать), а затем Trace (Трасса).
  7. На вкладке General (Общие) укажите имя трассы и файл для записи данных.
  8. На вкладке Events (События) добавьте к вашей трассе следующие типы событий.

    Свернуть эту таблицуРазвернуть эту таблицу
    ЗаголовокСобытие для добавленияОписание
    CursorsCursorPrepareУказывает, что посредством ODBC, OLEDB или DB-Library был подготовлен для использования курсор для оператора SQL.
    Error and WarningMissing Column StatisticsУказывает, что статистика по столбцам, которая могла бы быть использована оптимизатором, была недоступна. В столбце Text (Текст) показан список столбцов с отсутствующей статистикой. Это событие вместе с событием Misc: Auto-UpdateStats указывает, что был установлен параметр Auto Create Statistics (Автоматическое создание статистики).
    Misc.AttentionУказывает, что клиентом был отправлен сигнал предупреждения.
    Misc.Auto-UpdateStatsУказывает на установку параметра Auto Update Statistics (Автоматическое обновление статистики).
    Misc.Exec Prepared SQLУказывает, что один или несколько ранее подготовленных операторов Transact-SQL были выполнены посредством ODBC, OLE DB или DB-Library.
    Misc.Execution PlanПоказывает дерево плана для выполненного оператора Transact-SQL.
    Misc.Prepare SQLУказывает, что один или несколько операторов Transact-SQL были подготовлены для использования приложением, использующим ODBC, OLE DB или DB-Library.
    Misc.Unprepare SQLУказывает, что один или несколько подготовленных операторов Transact-SQL были выведены из состояния готовности приложением, использующим ODBC, OLE DB или DB-Library.
    SessionsConnectУказывает на установку нового подключения.
    SessionsDisconnectУказывает на прерывание подключения клиентом.
    SessionsExisting ConnectionУказывает на наличие подключения при начале трассы профилировщика SQL.
    Stored ProceduresSP: CompletedУказывает на завершение выполнения хранимой процедуры.
    Stored ProceduresSP: RecompileУказывает на перекомпилирование хранимой процедуры во время выполнения.
    Stored ProceduresSP: StartingУказывает на запуск выполнения хранимой процедуры.
    Stored ProceduresSP: StmtCompletedУказывает на завершение выполнения оператора в хранимой процедуре.
    TSQL:SQL:BatchCompletedУказывает на завершение выполнения пакета Transact-SQL. В столбце Text (Текст) отображается выполненный оператор.
    TSQL:SQL:StmtCompletedУказывает на завершение выполнения оператора Transact-SQL. В столбце Text (Текст) отображается выполненный оператор.
    TSQL:RPC:CompletedУказывает на завершение выполнения удаленного вызова процедуры.
  9. Если ваше приложение получает сообщения об ошибках времени ожидания или происходят другие события, препятствующие выполнению проблемных операторов, необходимо также включить следующие события.

    Свернуть эту таблицуРазвернуть эту таблицу
    TSQL:SQL:BatchStartingУказывает на запуск выполнения пакета Transact-SQL. В столбце Text (Текст) отображается выполняемый оператор.
    TSQL:SQL:StmtStartingУказывает на запуск оператора Transact-SQL. В столбце Text (Текст) отображается выполняемый оператор.
    TSQL:RPC:StartingУказывает на запуск удаленного вызова процедуры.
    Stored ProceduresSP: StmtStartingУказывает на запуск выполнения оператора в хранимой процедуре.


    Это обеспечивает возможность просмотра оператора, выполнявшегося в момент превышения времени ожидания
  10. На вкладке Data Columns (Столбцы данных) убедитесь, что выбраны следующие столбцы.

    Для SQL Server 2000

    Start Time

    End Time

    LoginSid

    SPID

    Event Class

    TextData

    IntegerData

    BinaryData

    Duration

    CPU

    Reads

    Writes

    Application Name

    NT User Name

    DBUserName


    Для SQL Server 7.0

    Start Time

    End Time

    Connection ID

    SPID

    Event Class

    Text

    Integer Data

    Binary Data

    Duration

    CPU

    Reads

    Writes

    Application Name

    NT User Name

    SQL User Name

Дополнительные сведения об использовании программы SQL Profiler приведены в электронной документации по SQL Server 7.0 и SQL Server 2000.


2. Вспользуйтесь системным монитором для сбора данных счетчиков Windows NT и SQL Server. Для этого выполните следующие действия.
  1. Запустите системный монитор Windows NT.
  2. В меню Вид выберите Журнал.
  3. В меню Параметры выберите Журнал.
  4. Укажите имя и место расположения файла для записи журнала счетчиков производительности. Можно установить необходимый интервал обновления.
  5. В меню Правка выберите Добавить в журнал.
  6. Добавьте все объекты, как объекты Windows NT, так и объекты SQL Server.
  7. Для запуска журнала в меню Параметры выберите Журнал, затем нажмите кнопку Запуск журнала.

Для получения дополнительных сведений щелкните следующий номер статьи базы знаний Майкрософт:
150934 Создание журнала системного монитора для устранения неполадок NT (Эта ссылка может указывать на содержимое полностью или частично на английском языке)
(данная ссылка может указывать на материал полностью или частично на английском языке)
3. Проверьте наличие блокировки.

Для определения возникновения блокировки запустите системную хранимую процедуру sp_who:
exec sp_who
Результат будет содержать столбец blk. В выходных данных проверьте наличие ненулевых записей, указывающих на наличие блокировки. Запускайте эту процедуру периодически при снижении производительности.

Примечание. Запуск системной хранимой процедуры sp_who – это проверка существования блокировки. Как правило, только сведений о наличии блокировки недостаточно для полного устранения проблемы блокировки. Для получения дополнительных сведений щелкните следующий номер статьи базы знаний Майкрософт:
251004 ИНФО: Наблюдение за блокировкой в SQL Server 7.0 (Эта ссылка может указывать на содержимое полностью или частично на английском языке)
(эта ссылка может указывать на содержимое полностью или частично на английском языке)

Запуск приложения в условиях обычной загрузки

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


Интерпретация результатов

  1. Проверьте наличие блокировки.

    Если столбец blk в выходном файле sp_who имеет ненулевое значение, в системе существует блокировка. При блокировке одними процессами других время выполнения заблокированных процессов увеличивается. Для получения дополнительных сведений щелкните следующий номер статьи базы знаний Майкрософт:
    224453 ИНФО: Общие сведения о проблемах блокировки в SQL Server 7.0 и 2000 и их устранение (Эта ссылка может указывать на содержимое полностью или частично на английском языке)
    (эта ссылка может указывать на содержимое полностью или частично на английском языке)
  2. Проанализируйте выходные данные профилировщика SQL.

    Эффективный просмотр данных профилировщика SQL исключительно важен для разрешения проблем производительности. Важнее всего понимать, что не требуется изучать все собранные данные. Будьте избирательны. Профилировщик SQL предоставляет возможности, позволяющие эффективно изучать собранные данные. На вкладках Properties (Свойства) (выберите Properties (Свойства) в меню File (Файл) профилировщика SQL можно ограничить отображаемые данные с помощью удаления столбцов данных или событий, группировки (упорядочения) по столбцам данных и применения фильтров. Поиск определенных значений можно выполнить во всей трассе или только в определенном столбце (в меню Edit (Правка) выберите Find (Поиск). Можно также сохранить данные профилировщика SQL в таблице SQL Server (в меню File (Файл) выберите Save As (Сохранить как) и выберите Trace Table (Таблица трассировки), а затем выполнять к ней запросы SQL.

    Фильтры необходимо применять только к ранее сохраненному файлу трассы. Выполнение этих действий для активной трассы может привести к потере данных, собранных после запуска трассы. Преждем чем продолжить, сохраните активную трассу в файле или таблице (в меню File (Файл) выберите Save As (Сохранить как), затем снова откройте ее (в меню File (Файл) выберите Open (Открыть). При работе с сохраненным файлом трассы отфильтрованные данные не удаляются окончательно, а просто не отображаются. При необходимости можно добавлять и удалять события и столбцы данных для изменения области поиска.

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

    Сгруппируйте трассу по классу событий.

    а. В меню File (Файл) выберите Properties (Свойства).

    б. На вкладке Data Columns (Столбцы данных) с помощью кнопки "Вверх" поместите Event Class (Класс событий) в заголовок Groups (Группы), а с помощью кнопки "Вниз" удалите все остальные столбцы в заголовке Groups (Группы).

    в. Нажмите кнопку ОК.

    Группировка по столбцу класса событий позволяет отобразить тип событий, происходящих в SQL Server, и их частоту. Найдите в этом столбце следующие события.

    SP:RECOMPILE

    Указывает на перекомпиляцию хранимой процедуры во время выполнения. Несколько событий перекомпиляции указывают на то, что SQL Server использует ресурсы не на выполнение запроса, а на его компиляцию.

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


    Attention

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

    Пользователь явно отменил запрос или завершил работу приложения.

    -или-

    Время ожидания запроса было превышено.

    Появление сигналов предупреждения может означать медленное выполнение определенных запросов.

    Для получения дополнительных сведений щелкните следующий номер статьи базы знаний Майкрософт:
    243589 ИНСТРУКЦИИ. Устранение проблем медленного выполнения запросов в SQL Server 7.0 или более поздней версии (Эта ссылка может указывать на содержимое полностью или частично на английском языке)
    (эта ссылка может указывать на содержимое полностью или частично на английском языке) Для определения запроса, получившего сигнал предупреждения, отмените группировку трассы по столбцам данных, а затем примените фильтрацию по идентификатору системного процесса (SPID), получившего сигнал (на вкладке Filters (Фильтры) для параметра SPID установите значение x). Событие SQL:StmtStarting, SQL:BatchStarting или SP:StmtStarting, непосредственно предшествующее сигналу, – это запрос, время ожидания которого было превышено или который был отменен. Чтобы найти его, можно выполнить поиск события Attention в столбце Event Class (Класс событий) (в меню Edit (Правка) выберите Find (Найти).

    PREPARE SQL и EXEC PREPARED SQL

    Событие Prepare SQL указывает, что один или несколько операторов Transact-SQL были подготовлены для использования приложением, использующим ODBC, OLE DB или DB-Library. Событие Exec Prepared SQL указывает, что приложение использовало существующий подготовленный оператор для выполнения команды.

    Сравните количество возникновений этих двух событий. В идеале приложение должно подготовить оператор SQL один раз и выполнить его несколько раз. Это экономит затраты оптимизатора на компиляцию нового плана при каждом выполнении оператора. Поэтому количество событий Exec Prepared SQL должно быть гораздо больше количества событий Prepare SQL. Если число событий Prepare SQL приблизительно соответствует числу событий Exec Prepared SQL, это может означать, что приложение использует модель подготовки/выполнения не лучшим образом. Рекомендуется не подготавливать оператор, если он будет использоваться всего один раз. Дополнительные сведения о подготовке операторов SQL приведены в разделе «Подготовка операторов SQL» электронной документации по SQL Server 7.0.

    Если число событий Exec Prepared SQL не превышает числа событий Prepare SQL в 3–5 раз, приложение может использовать модель подготовки/выполнения неэффективно. Для получения дополнительных сведений щелкните следующий номер статьи базы знаний Майкрософт:
    243588 ИНСТРУКЦИИ. Устранение проблем с производительностью специальных запросов (Эта ссылка может указывать на содержимое полностью или частично на английском языке)
    (эта ссылка может указывать на содержимое полностью или частично на английском языке)
    В SQL Server 2000 лишние циклы обращения для подготовки/выполнения будут устранены, поэтому коэффициент 3-5 не настолько жесткий. Однако он по-прежнему может быть хорошим правилом для проверки и повторного использования подготовленного плана.

    Missing Column Statistics

    Это событие указывает, что статистические сведения, которые могли использоваться оптимизатором для создания лучшего плана запроса, были недоступны. Это означает, что для запроса отсутствуют полезные индексы по крайней мере для одной таблицы. Помимо отсутствия полезного индекса, SQL Server не имеет даже статистических сведений о столбцах, использовавшихся для принятия обоснованного решения по плану запроса. В результате может быть создан не самый оптимальный план запроса. При наличии этих событий проверьте запрос и созданный план выполнения, затем ознакомьтесь со следующей статьей базы знаний Майкрософт для получения сведений о действиях по улучшению производительности этого запроса:
    243589 ИНСТРУКЦИИ. Устранение проблем медленного выполнения запросов в SQL Server 7.0 или более поздней версии (Эта ссылка может указывать на содержимое полностью или частично на английском языке)

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

    Если эти классы событий отсутствуют, следущим действием является определение причины траты времени.

    Сгруппируйте выходной файл трассы по продолжительности.

    а. В меню File (Файл) выберите Properties (Свойства).

    На вкладке Data Columns (Столбцы данных) с помощью кнопки "Вверх" поместите Duration (Продолжительность) в заголовок Groups (Группы), а с помощью кнопки "Вниз" удалите все остальные столбцы в заголовке Groups (Группы).

    в. На вкладке Events (События) удалите все группы, кроме TSQL и Stored Procedures (Хранимые процедуры).

    г. Нажмите кнопку ОК.

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

    При наличии небольшого числа запросов с большой длительностью ознакомьтесь со следующей статьей базы знаний Майкрософт:
    243589 ИНСТРУКЦИИ. Устранение проблем медленного выполнения запросов в SQL Server 7.0 или более поздней версии (Эта ссылка может указывать на содержимое полностью или частично на английском языке)
    Если длительность отдельных запросов является небольшой, но таких запросов несколько, и значение счетчика Компиляций SQL/с в выходном файле монитора производительности (описывается далее) является высоким, ознакомьтесь со следующей статьей базы знаний Майкрософт:
    243588 ИНСТРУКЦИИ. Устранение проблем с производительностью нерегламентированных запросов (Эта ссылка может указывать на содержимое полностью или частично на английском языке)
    Изучите оставшиеся столбцы данных:

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

    При высокой загрузке ЦП выполните группировку по ЦП, чтобы определить запросы с набольшей загрузкой ЦП. В столбце Text (Текст) выполните поиск строки «hash» (хэширование) или «merge» (слияние), чтобы найти план выполнения запроса, использующий эти типы соединения. Они больше нагружают ЦП и память, чем соединение вложенными циклами, которое, как правило, интенсивно использует ввод-вывод.

    Если дисковые операции ввода-вывода являются узким местом, выполните группировку по операциям чтения и записи. Просмотрите поля Application Name (Имя приложения), NT User Name (Имя пользователя NT) и SQL User Name (Имя пользователя SQL), чтобы изолировать источник продолжительного запроса

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

    Поле Connection ID (Идентификатор подключения) позволяет убедиться в том, что изучаются одни и те же сеансы для определенного клиента. Идентификатор SPID не может этого гарантировать, поскольку пользователь мог быть отключен, а новый подключенный пользователь мог получить такой же SPID.

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

    Монитор производительности позволяет определить общие узкие места системы. SQL Server и приложение могут работать должным образом, но для компьютера может быть недостаточно электропитания, памяти или других ресурсов, или же определенные счетчики могут свидетельствовать о проблемах работы приложения и SQL Server. Как минимум, необходимо проверить следующие счетчики.

  • Объект: Process (Процесс)

    Счетчик: Processor (Процессор)

    Экземпляр: SQL Server

  • Объект: Processor (Процессор)

    Счетчик: % загруженности процессора

    Экземпляр: проверьте все экземпляры процессора

  • Объект: Physical Disk (Физический диск)

    Счетчик: Avg. Disk Queue Length (Средняя длина очереди диска)

    Экземпляр: проверьте все экземпляры физического диска

  • Объект: SQL Server:SQL Statistics (SQLServer: статистика SQL)

    Счетчик: SQL Compilations/sec (Компиляций SQL/с)
Определите тенденцию в интервале временни, в котором произошло снижение производительности: что увеличилось первым? Ограничен ли компьютер возможностями процессора или дискового ввода-вывода? Эти сведения вместе с выходными данными профилировщика, описанными ранее в данной статье, помогут сократить проблемные области. Причиной проблем высокой загрузки ЦП может быть большое число перекомпиляций хранимых процедур, перекомпиляции специальных запросов или интенсивное использование соединений слиянием и хэшированием. Для определения верного плана действий необходимо следовать статьям, указанным ранее в данной статье. Большая длина очереди диска может свидетельствовать о необходимости увеличения системной памяти или улучшения дисковой подсистемы.

Свойства

Код статьи: 224587 - Последний отзыв: 29 декабря 2007 г. - Revision: 4.1
Информация в данной статье относится к следующим продуктам.
  • Microsoft SQL Server 7.0 Standard Edition
Ключевые слова: 
kbhowto kbhowtomaster kbinfo kbproductlink KB224587

Отправить отзыв

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com