Рекомендации по настройке запроса

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

Тем не менее, для повышения производительности запросов можно сделать следующее.

• Увеличить объем памяти. Это решение может быть особенно полезным, если на сервере выполняется много сложных запросов и некоторые из этих запросов выполняются медленно.

• Использовать более одного процессора. Несколько процессоров позволяют компоненту Database Engine применять параллельные запросы. Дополнительные сведения см. в разделе Параллельная обработка запросов.

• Заново составить запрос. Примите во внимание следующие факторы.

  • Если в запросе используются курсоры, определите, может ли запрос курсора быть составлен с применением курсора более эффективного типа (например опережающего курсора) или есть возможность применить единый запрос. Производительность единых запросов обычно выше, чем у курсорных операций. Набор курсорных инструкций обычно представляет собой внешний цикл, в котором каждая строка внешнего цикла обрабатывается один раз с использованием внутренней инструкции, поэтому рекомендуется применять вместо них либо инструкцию GROUP BY или CASE, либо вложенный запрос. Дополнительные сведения см. в разделах Типы курсора (компонент Database Engine) и Основные принципы запросов.

  • Если в приложении используется цикл, то помещать его рекомендуется внутри запроса. Часто приложение содержит цикл, в котором присутствует параметризованный запрос, выполняемый многократно и требующий полного обхода через сеть между компьютером, на котором выполняется приложение, и SQL Server. Вместо этого создайте единый, более сложный запрос с использованием временной таблицы. Требуется только один полный обход через сеть, и единый запрос может быть лучше оптимизирован с помощью оптимизатора запросов. Дополнительные сведения см. в разделах Процедурный язык Transact-SQL и Переменные языка Transact-SQL.

  • Не следует использовать несколько псевдонимов одной таблицы в том же запросе для имитации пересечения индекса. В этом более нет необходимости, так как SQL Server автоматически анализирует пересечение индекса и может использовать несколько индексов по одной таблице в том же запросе. Рассмотрим образец запроса:

    SELECT * FROM lineitem 
    WHERE partkey BETWEEN 17000 AND 17100 AND
        shipdate BETWEEN '1/1/1994' AND '1/31/1994'
    

    SQL Server может использовать индексы как по столбцу partkey, так и по столбцу shipdate, а затем отыскать хэш-совпадения между двумя подмножествами, чтобы получить пересечение индекса.

  • Используйте параметризацию запросов, чтобы создать возможность повторного использования кэшированных планов выполнения запроса. Если у нескольких запросов совпадают хэш-значения и хэш-значения планов запросов, можно повысить производительность, создав один параметризованный запрос. Вызов одного запроса с различными значениями параметра вместо вызова нескольких запросов с литеральными значениями позволяет повторно использовать кэшированный план выполнения запроса. Дополнительные сведения см. в разделах Поиск и настройка сходных запросов с помощью хэширования запросов и планов запросов и Кэширование и повторное использование плана выполнения.

    Если изменять приложение нельзя, того же результата можно добиться с помощью шаблонов структур планов с принудительной параметризацией. Дополнительные сведения см. в разделе Указание механизма параметризации запросов с помощью структур плана.

  • Подсказки в запросах следует применять только при необходимости. Запросы, составленные с использованием рекомендаций и выполняемые с прежними версиями SQL Server, следует протестировать без указания рекомендаций. Рекомендации могут помешать оптимизатору запросов выбрать лучший план выполнения. Дополнительные сведения см. в разделе SELECT (Transact-SQL).

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

• Используйте параметр конфигурации регулятор запросов. С помощью параметра конфигурации регулятор запросов можно предотвратить поглощение системных ресурсов долго выполняющимися запросами. Установка параметра по умолчанию разрешает выполнение всех запросов, независимо от их длительности. Однако можно настроить регулятор запросов на ограничение максимального числа секунд, предоставляемых для выполнения всем запросам для всех соединений или только запросам для определенных соединений. Регулятор запросов основывается на ожидаемой стоимости запроса, а не на действительно истекшем времени, поэтому во время выполнения не возникает дополнительной нагрузки. Длительные запросы останавливаются регулятором запросов до их запуска, вместо того чтобы выполнять их до достижения какого-либо предопределенного предела. Дополнительные сведения см. в разделах Параметр query governor cost limit и SET QUERY_GOVERNOR_COST_LIMIT (Transact-SQL).

• Оптимизируйте повторное использование планов запроса из кэша планов. Компонент Database Engine кэширует планы запросов для возможного повторного использования. Если план запроса не сохранен в кэше, то повторно использовать его нельзя. Вместо этого некэшированные планы запросов необходимо компилировать каждый раз при их выполнении, что приводит к падению производительности. Следующие параметры инструкции SET языка Transact-SQL запрещают повторное использование сохраненных в кэше планов запросов. Пакет Transact-SQL, содержащий эти параметры SET со значением ON, не может использовать планы запросов совместно с таким же пакетом, который был скомпилирован с параметрами SET со значением OFF.

  SET ANSI_NULL_DFLT_OFF	SET ANSI_NULL_DFLT_ON
  SET ANSI_NULLS	SET ANSI_PADDING
  SET ANSI_WARNINGS	SET ARITHABORT
  SET CONCAT_NULL_YIELDS_NULL	SET DATEFIRST
  SET DATEFORMAT	SET FORCEPLAN
  SET LANGUAGE	SET NO_BROWSETABLE
  SET NUMERIC_ROUNDABORT	SET QUOTED_IDENTIFIER
  SET TEXTSIZE

  Кроме того, на повторное использование кэшированных планов запросов влияет параметр SET ANSI_DEFAULTS, так как с его помощью можно изменить параметры ANSI_NULLS, ANSI_NULL_DFLT_ON, ANSI_PADDING, ANSI_WARNINGS, CURSOR_CLOSE_ON_COMMIT, IMPLICIT_TRANSACTIONS и QUOTED_IDENTIFIER SET. Следует отметить, что большинство параметров SET, которые можно изменить с использованием SET ANSI_DEFAULTS, перечислены как параметры SET, влияющие на повторное использование планов запросов.

  Некоторые из этих параметров SET можно изменить следующими способами:

  • используйте хранимую процедуру sp_configure для изменений на уровне сервера. Дополнительные сведения см. в разделе sp_configure (Transact-SQL);

  • используйте предложение SET инструкции ALTER DATABASE. Дополнительные сведения см. в разделе ALTER DATABASE (Transact-SQL);

  • измените настройки соединения OLE DB и ODBC. Дополнительные сведения см. в разделе Конфигурация клиентской сети.

Примечание

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

См. также

Справочник

Выражение CASE (Transact-SQL)

Основные понятия

Основы вложенных запросов