Анализ запросов

Разбить на страницы

Показывать лекцию целиком

Вы научитесь:

отображать план выполнения SQL-сценария;

изменять схему базы данных из панели управления планом выполнения Execution Plan Pane;

отображать трассировку сервера для SQL-сценария;

отображать клиентскую статистику для SQL-сценария;

использовать мастер настройки индексов Index Tuning Wizard для оптимизации схемы базы данных.

Использование Query Analyzer для оптимизации производительности

В добавлении к панели редактирования Editor Pane, окно Query (Запрос) анализатора запросов SQL Server Query Analyzer предоставляет три дополнительных панели для анализа производительности отдельных запросов. Панель Execution Plan Pane содержит графическое представление задач, которые SQL Server будет обрабатывать для выполнения запроса. Панель Trace Pane показывает детальную информацию о выполнении запроса на стороне сервера, включая время и число операций чтения и записи. Панель Client Statistics Pane отображает информацию о выполнении запроса на стороне клиента, включая количество обращений и ответов от сервера и пропускную способность сети.

Планы выполнения

Панель планов выполнения Execution Plan Pane окна Query (Запрос) графически отображает последовательность выполнения вашего запроса SQL Server. На рис. 23.1 представлен план выполнения для простого оператора SELECT:

SELECT OilName, LatinName
FROM Oils
ORDER BY LatinName

(рис 23.1) Панель плана выполнения Execution Plan Pane окна Query (Запрос).

Совет. Информация, отображаемая в панели плана выполнения Execution Plan Pane, идентична тексту, отображаемому опцией SHOWPLAN базы данных, которая хорошо известна пользователям предыдущих версий SQL Server и все еще присутствует в SQL Server 2000. Если оператор SET SHOWPLAN_ALL ON выполняется как часть сценария в окне Query (Запрос), то результаты будут отображаться в панели сетки Grids Pane. Панель Execution Plan Pane отображает информацию в формате, который понятен большинству людей.

Панель Execution Plan Pane использует довольно большое количество значков для представления операций, которые может выполнить обработчик запросов. Значки описаны в документации SQL Server Books Online, но нет большой необходимости изучать их. Просто наведите курсор мыши на значок и удерживайте некоторое время на нем, после чего отобразится окно подсказки, описывающее не только действие, представляемое значком, но и некоторый объем полезной информации, такой как цена выполнения ввода/вывода I/O, цена загрузки процессора, число строк в операции и итоговая цена операции. Рис. 23.2 показывает окно подсказки для плана выполнения операции кластерного индексного сканирования Clustered Index Scan, представленного на рис. 23.1.

(рис 23.2) Окно подсказки для операции Clustered Index Scan.

Операции в плане выполнения исполняются слева направо. Окно подсказки для каждой стрелки, соединяющей операции, показывает число строк, выполненных в предыдущей операции и расчетный размер каждой строки, как показано на рис. 23.3.

(рис 23.3) Окно подсказки для соединительных стрелок.

Помимо отображения операций, которые SQL Server будет исполнять при выполнении определенного запроса, план выполнения также предоставляет механизм для оптимизации запроса. Используя контекстное меню панели плана выполнения Execution Plan Pane, вы можете обновлять статистику, используемую оптимизатором запросов при определении стратегии выполнения, и добавлять индексы для оптимизации производительности.

Отобразите план выполнения запроса

В панели редактирования Editor Pane анализатора запросов Query Analyzer введите следующий оператор Transact-SQL:

SELECT PlantParts.PlantPart, Count(Oils.OilName)
    AS NumberOfOils
FROM    Oils
        INNER JOIN PlantParts
            On Oils.PlantPartID = PlantParts.PlantPartID
GROUP BY PlantParts.PlantPart

В меню Query (Запрос) выберите Show Execution Plan (Показать план выполнения).

Примечание. Во время выполнения запроса панель Execution Plan Pane не отображается.

Для выполнения запроса в панели инструментов анализатора запросов Query Analyzer нажмите кнопку Execute Query (Выполнить запрос).

Query Analyzer выполнит запрос и отобразит результаты в панели сетки Grids Pane.

Выберите вкладку Execution Plan (План выполнения).

Добавьте индекс в панели Execution Plan Pane

В панели плана выполнения запроса Execution Plan Pane щелкните правой кнопкой мыши на значке, представляющем операцию Clustered Index Scan

. Если в итоге значение для этой операции будет составлять 63%, нам следует по возможности ее оптимизировать.

Из контекстного меню выберите Manage Indexes (Управление индексами). Query Analyzer отобразит диалоговое окно Manage Indexes (Управление индексами).

Нажмите кнопку New (Создать). Query Analyzer отобразит диалоговое окно Create New Index (Создание нового индекса).

Введите в качестве имени индекса Oils_PlantParts и выделите строку PlantPartID для включения ее в индекс.

Нажмите OK. Query Analyzer создаст индекс и отобразит его в диалоговом окне Manage Indexes (Управление индексами).

Закройте диалоговое окно Manage Indexes (Управление индексами).

Нажмите кнопку Execute Query (Выполнить запрос)

в панели инструментов анализатора запросов Query Analyzer, чтобы еще раз выполнить запрос.

В окне запроса выберите вкладку Execution Plan (План выполнения). Операция кластерного индексного сканирования Clustered Index Scan для таблицы Oils будет заменена операцией индексного поиска Index Seek, что в итоге приведет к уменьшению значения для этой операции с 63 процентов до 13.

Трассировка сервера

Вторая утилита Query Analyzer предоставляет возможности анализа производительности запроса через трассировку сервера. Панель Trace Pane показывает команды, которые выполняются на сервере во время исполнения запроса. Команды не соответствуют операциям в плане выполнения – ряд команд выполняется дополнительно, а реальные команды Transact-SQL не будут показаны столь же детально.

Совет. SQL Server 2000 также предоставляет другое средство для выполнения трассировки сервера - SQL Profiler. Утилиту SQL Profiler мы не будем рассматривать в этом курсе.

Отобразите трассировку сервера

Если вы закрыли окно Query (Запрос) после предыдущего упражнения, то снова откройте его и введите в панели редактирования Editor Pane следующий оператор Transact-SQL:

SELECT PlantParts.PlantPart, Count(Oils.OilName)
    AS NumberOfOils
FROM    Oils
        INNER JOIN PlantParts
            On Oils.PlantPartID = PlantParts.PlantPartID
GROUP BY PlantParts.PlantPart

В меню Query (Запрос) выберите Show Server Trace (Показать трассировку сервера).

Для выполнения запроса в панели инструментов анализатора Query Analyzer нажмите кнопку Execute Query (Выполнить запрос).

В окне Query (Запрос) выберите вкладку Trace (Трассировка).

Клиентская статистика

Последней утилитой для анализа запросов, предоставляемой окном Query (Запрос) Query Analyzer является панель клиентской статистики Client Statistics Pane, которая отображает выполнение запроса на стороне клиента.

Информация в панели клиентской статистики Client Statistics Pane делится на три раздела: Application Profile Statistics, в котором содержится информация о количестве выполненных операторов Transact-SQL и выполненных строках; Network Statistics, в котором содержится информация о сформированном трафике сети; Time Statistics, который помогает вам определить где происходит замедление: на клиенте или на сервере.

Совет. Раздел статистика сети Network Statistics, обеспечиваемая панелью клиентской статистики Client Statistics Pane, будет присутствовать, даже если вы подключены к локальному серверу.

Отобразите клиентскую статистику

SELECT PlantParts.PlantPart, Count(Oils.OilName)
    AS NumberOfOils
FROM    Oils
        INNER JOIN PlantParts
            On Oils.PlantPartID = PlantParts.PlantPartID
GROUP BY PlantParts.PlantPart

В окне Query (Запрос) выберите Show Client Statistics (Показать клиентскую статистику).

Нажмите кнопку Execute Query (Выполнить запрос)

в панели инструментов анализатора запросов Query Analyzer, чтобы еще раз выполнить запрос.

В окне Query (Запрос) выберите вкладку Statistics (Статистика).

Мастер настройки индексов Index Tuning Wizard

Анализатор запросов Query Analyzer предоставляет еще одну утилиту для оптимизации приложений вашей базы данных: мастер настройки индексов Index Tuning Wizard. Анализируя вашу базу данных и внося предложения по увеличению производительности, Index Tuning Wizard сохраняет огромное количество времени, которое вам пришлось бы затратить, осуществляя тестирование производительности методом проб и ошибок.

Использование мастера Index Tuning Wizard

Настройка базы данных не может выполняться в пустоте. Не имеет смысла вопрос, какая схема работает лучше в абсолютном выражении. Нас прежде всего интересует, какое сочетание представлений и индексов приведет к наиболее быстрому выполнению конкретных операций. По этой причине мастеру Index Tuning Wizard требуется предоставить определенные данные о загруженности, в качестве которых может выступать либо SQL-сценарий, либо данные трассировки сервера из SQL Profiler.

Вторая страница мастера требует, чтобы вы указали сервер и базу данных для анализа, а также предоставляет две дополнительных опции выбора: Keep All Existing Indexes (Не изменять существующие индексы) и Tuning Mode (Режим настройки). Опция режимов настройки Tuning Mode задает глубину анализа, выполняемого мастером. Сбросив установленный по умолчанию флажок Keep All Existing Indexes (Не изменять существующие индексы), вы позволите мастеру выдавать рекомендации относительно индексов, которые не играют роли для выбранной рабочей нагрузки. Следует проявлять осторожность этой опцией, поскольку, несмотря на то, что индексы могут и не вовлекаться в анализируемую рабочую нагрузку, они существенно сказываются на производительности некоторых других запросов, не затрагиваемых в тестировании.

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

Используйте мастер Index Tuning Wizard для настройки базы данных

В базе данных Aromatherapy раскройте папку Indexes таблицы Oils.

Выберите индекс Oil_PlantParts и нажмите кнопку Delete (Удалить). Query Analyzer запросит подтверждение об удалении индекса.

Нажмите OK. Query Analyzer удалит индекс.

Перейдите к окну Query (Запрос) и из меню Query (Запрос) выберите Index Tuning Wizard (Мастер настройки индексов). Query Analyzer отобразит первую страницу мастера настройки индексов Index Tuning Wizard.

Нажмите Next (Далее). Мастер отобразит страницу, приглашающую вас выбрать базу данных и режим настройки.

Убедитесь, что выбрана база данных Aromatherapy и затем выберите режим настройки Thorough (Полная).

Нажмите Next (Далее). Мастер отобразит страницу, приглашающую вас выбрать рабочую нагрузку для анализа.

Нажмите кнопку Advanced Options (Дополнительные параметры). Мастер отобразит диалоговое окно с параметрами настройки индексов.

Примите установленные по умолчанию значения настройки, нажав OK.

Примите установленные по умолчанию значения SQL Query Analyzer Selection, нажав кнопку Next (Далее). Мастер отобразит страницу, приглашающую вас выбрать таблицу для настройки.

Выберите для анализа таблицу Oils и установите значение в столбце Projected Rows на 1000.

Нажмите кнопку Next (Далее). Мастер отобразит свои рекомендации.

Нажмите кнопку Next (Далее). Мастер отобразит страницу, приглашающую вас выбрать опции выполнения.

Выберите Apply Changes (Применить изменения) и примите установленную по умолчанию опцию Execute Recommendations Now (Выполнить рекомендации сейчас).

Нажмите кнопку Next (Далее). Мастер отобразит последнюю страницу, позволяющую применить все сделанные вами установки.

Нажмите кнопку Finish (Готово). Мастер отобразит сообщение об успешном выполнении.

Краткое содержание

Чтобы	Сделайте следующее
Отобразить план выполнения сценария SQL	В меню Query (Запрос) выберите Show Execution Plan (Показать план выполнения) и выполните запрос.
Изменить схему базы данных из панели плана выполнения Execution Plan Pane	Щелкните правой кнопкой мыши на плане выполнения и выберите Manage Indexes (Управление индексами).
Отобразить трассировку сервера SQL-сценария	В меню Query (Запрос) выберите Show Server Trace (Показать трассировку сервера) и выполните запрос.
Отобразить клиентскую статистику для SQL-сценария	В меню Query (Запрос) выберите Show Client Statistics (Показать клиентскую статистику) и выполните запрос.
Использовать мастер настройки индексов Index Tuning Wizard для оптимизации схемы базы данных	В меню Query (Запрос) выберите Index Tuning Wizard (Мастер настройки индексов) и следуйте инструкциям мастера.

Страницы:

Вы научитесь:

отображать план выполнения SQL-сценария;

изменять схему базы данных из панели управления планом выполнения Execution Plan Pane;

отображать трассировку сервера для SQL-сценария;

отображать клиентскую статистику для SQL-сценария;

использовать мастер настройки индексов Index Tuning Wizard для оптимизации схемы базы данных.

Использование Query Analyzer для оптимизации производительности

Планы выполнения

SELECT OilName, LatinName
FROM Oils
ORDER BY LatinName

(рис 23.1) Панель плана выполнения Execution Plan Pane окна Query (Запрос).

(рис 23.2) Окно подсказки для операции Clustered Index Scan.

(рис 23.3) Окно подсказки для соединительных стрелок.

Отобразите план выполнения запроса

В панели редактирования Editor Pane анализатора запросов Query Analyzer введите следующий оператор Transact-SQL:

SELECT PlantParts.PlantPart, Count(Oils.OilName)
    AS NumberOfOils
FROM    Oils
        INNER JOIN PlantParts
            On Oils.PlantPartID = PlantParts.PlantPartID
GROUP BY PlantParts.PlantPart

В меню Query (Запрос) выберите Show Execution Plan (Показать план выполнения).

Примечание. Во время выполнения запроса панель Execution Plan Pane не отображается.

Query Analyzer выполнит запрос и отобразит результаты в панели сетки Grids Pane.

Выберите вкладку Execution Plan (План выполнения).

Добавьте индекс в панели Execution Plan Pane

. Если в итоге значение для этой операции будет составлять 63%, нам следует по возможности ее оптимизировать.

Нажмите кнопку New (Создать). Query Analyzer отобразит диалоговое окно Create New Index (Создание нового индекса).

Введите в качестве имени индекса Oils_PlantParts и выделите строку PlantPartID для включения ее в индекс.

Нажмите OK. Query Analyzer создаст индекс и отобразит его в диалоговом окне Manage Indexes (Управление индексами).

Закройте диалоговое окно Manage Indexes (Управление индексами).

Нажмите кнопку Execute Query (Выполнить запрос)

в панели инструментов анализатора запросов Query Analyzer, чтобы еще раз выполнить запрос.

Трассировка сервера

Отобразите трассировку сервера

SELECT PlantParts.PlantPart, Count(Oils.OilName)
    AS NumberOfOils
FROM    Oils
        INNER JOIN PlantParts
            On Oils.PlantPartID = PlantParts.PlantPartID
GROUP BY PlantParts.PlantPart

В меню Query (Запрос) выберите Show Server Trace (Показать трассировку сервера).

Для выполнения запроса в панели инструментов анализатора Query Analyzer нажмите кнопку Execute Query (Выполнить запрос).

В окне Query (Запрос) выберите вкладку Trace (Трассировка).

Клиентская статистика

Отобразите клиентскую статистику

SELECT PlantParts.PlantPart, Count(Oils.OilName)
    AS NumberOfOils
FROM    Oils
        INNER JOIN PlantParts
            On Oils.PlantPartID = PlantParts.PlantPartID
GROUP BY PlantParts.PlantPart