Опубликован: 11.12.2006 | Доступ: свободный | Студентов: 5363 / 282 | Оценка: 4.42 / 3.86 | Длительность: 57:15:00

Лекция 35: Использование SQL Query Аnalyzer и SQL Profiler

Оптимизация операторов T-SQL

В предыдущих разделах вы изучали, как следить за выполнением операторов T-SQL с помощью утилиты Profiler и как определять план исполнения и метод доступа к данным с помощью Query Analyzer. Мы также рассматривали простой пример модифицирования оператора T-SQL для улучшения его производительности. Используя эти средства, вы можете теперь модифицировать многие типы операторов T-SQL, чтобы улучшить показатели их выполнения. В этом разделе мы рассмотрим несколько способов оптимизации операторов T-SQL, чтобы повысить их производительность или чтобы они использовали меньше системных ресурсов.

Оптимизация плана исполнения

Модифицирование плана исполнения может оказаться трудным делом, а создание лучшего плана исполнения, чем у оптимизатора запросов, может оказаться еще более трудным делом. Наиболее вероятны улучшения от внесения изменений в план исполнения операторов JOIN, GROUP BY, ORDER BY и UNION. Вы можете легко модифицировать эти операции, пробуя различные подсказки и просматривая результаты (см. раздел "Использование подсказок" далее). Изменяя подсказку и просматривая результаты в окне Query Analyzer, вы, возможно, найдете более эффективный вариант выполнения оператора.

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

Выбор метода доступа к базе данных

Как уже говорилось выше в этой лекции, методы доступа к данным – это, по сути, объекты, используемые системой SQL Server при выборке данных из базы данных. Анализируя свою базу данных и данные, которые она содержит, вам, возможно, удастся оптимизировать метод доступа к данным, когда целью оптимизации является снижение количества операций ввода-вывода.

Модификации метода доступа к данным, как и модификации плана исполнения, должны выполняться для каждого конкретного случая. Следующие рекомендации помогут вам в выборе более эффективного метода доступа к данным.

  • Используйте наиболее подходящий индекс.Использование наиболее подходящего индекса для операции является необходимым условием достижения наиболее высокой производительности. Наиболее подходящий индекс для определенной операции – это индекс, позволяющий наиболее быстро находить данные с использованием наименьшего числа операций ввода-вывода. Вы можете определить наиболее подходящий индекс, исходя из знания особенностей вашей базы данных и ее данных или используя утилиту Query Analyzer. Эта утилита позволяет вам опробовать различные сценарии, чтобы определить индекс, который позволяет считывать минимальное количество строк. (Напомним, что Query Analyzer просто оценивает количество возвращаемых строк; чтобы определить точное количество строк, вы должны использовать Profiler.)
    Примечание.Как уже говорилось в "Создание и использование индексов" , индексы очень важны для SQL Server, но они могут приводить к снижению производительности при неверном использовании. Следите за количеством индексов на одну таблицу – особенно при большом количестве операций для операторов INSERT, UPDATE и DELETE. Излишнее количество индексов приводит к снижению производительности для операций этого типа, поскольку модифицирование индексов сопряжено с дополнительной нагрузкой на систему.
  • Используйте охватывающие (covering) индексы. Использование охватывающих (covering) индексов, возможно, позволит вам обойтись без операции ввода-вывода в процессе поиска данных (см. "Создание и использование индексов" ). Вместо доступа к соответствующей таблице вам, возможно, удастся считывать необходимые данные из самого индекса.
  • Снижайте количество возвращаемых строк.Определите, насколько необходимы те или иные данные, возвращаемые в результате запросов. Модифицируйте запросы T-SQL, чтобы в них выполнялся доступ только к необходимым данным. Не считывайте строки, которые будут затем отброшены. Снижение количества строк, считываемых из базы данных, может быть достигнуто в результате повышения селективности запроса.
Использование подсказок

Вы можете изменять метод доступа к данным и план исполнения, модифицируя сам оператор T-SQL, но в случае ошибок этот метод может нарушить функциональные требования к данному оператору T-SQL. Более надежным методом оптимизации операторов T-SQL является использование подсказок. С помощью подсказок вы можете указывать оптимизатору запросов, какие операции он должен выполнять и какие объекты он должен использовать. В этом разделе вы узнаете о различных подсказках SQL Server и об их использовании.

Подсказки операций связывания

Подсказки операций связывания (join hints) используются, чтобы указывать оптимизатору запросов типы операций связывания, которые он должен выполнять. (Если в запросе не указано никакого типа, то оптимизатор запросов выбирает тип самостоятельно.) В SQL Server вы можете выполнять связывание вложенных цепочек, хеш-связывание, связывание слиянием и удаленное связывание. Вы указываете метод связывания с помощью следующих подсказок.

  • LOOP. Указывает связывание вложенных цепочек. При этом типе связывания для каждой строки внешней таблицы проверяется каждая строка внутренней таблицы на совпадение значений указанных полей.
  • HASH. Указывает хеш-связывание. При этом типе связывания одна таблица преобразуется как хеш-таблица. Другая таблица сканируется по одной строке, и для поиска совпадений используется хеш-функция.
  • MERGE. Указывается связывание с сортировкой слиянием. При этом типе связывания сортируется каждая таблица, и затем каждая строка каждой таблицы сравнивается с соответствующей строкой в убывающем порядке.
  • REMOTE. Указывает удаленное связывание. При этом типе связывания хотя бы одна их участвующих таблиц является удаленной.

Рассмотрим пример использования подсказки для операции связывания. Мы используем пример из раздела "Просмотр плана для операции связывания" выше в этой лекции и укажем следующую хеш-подсказку:

SELECT OrderID, CustomerID, Employees.EmployeeID, FirstName,
    	LastName, OrderDate
FROM Orders, Employees
WHERE Orders.EmployeeID = Employees.EmployeeID
OPTION (HASH JOIN)
Примечание. Подсказки для связывания являются взаимоисключающими – можно одновременно указывать только одну подсказку.

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

SELECT OrderID, CustomerID, Employees.EmployeeID, FirstName,
    	LastName, OrderDate
FROM Orders INNER HASH JOIN Employees
ON (Orders.EmployeeID = Employees.EmployeeID)

Использование подсказок для связывания является достаточно сложной темой, и не существует никаких правил, указывающих, когда использовать подсказки для связывания. Существует много причин для выбора определенной операции связывания, таких как предикаты, размер каждой таблицы при связывании и количество таблиц, участвующих в операции связывания. Чтобы определить, нужно ли изменять операцию связывания, лучше всего опробовать каждый тип связывания в Query Analyzer и посмотреть, какой из них дает меньшую стоимость. Но обычно наилучший способ для операции связывания выбирает оптимизатор запросов.

Константин Дементьев
Константин Дементьев
Россия, г. Мичуринск