Как повлиять на оптимизатор запросов MS SQL Server

Существуют следующие флаги трассировки

флаг трассировки Microsoft Knowledge Base article доступны в
4199 974006 Cumulative Update 6 for SQL Server 2005 Service Pack 3;
Cumulative Update 7 for SQL Server 2008;
Cumulative Update 7 for SQL Server 2008 Service Pack 1;
SQL Server 2008 R2 и более поздних версиях.
2335 2413549 SQL Server 2005 и более поздних версиях.
2340 2009160 SQL Server 2005 и более поздних версиях.
2389, 2390 Нет SQL Server 2005 и более поздних версиях. Для понимания вопроса в SQL Server 2005 окружения смотрите 929278.
4136 980653 Cumulative Update 9 for SQL Server 2005 Service Pack 3;
Cumulative Update 7 for SQL Server 2008 Service Pack 1;
Cumulative Update 2 for SQL Server 2008 R2 и более поздних версиях.
4137 2658214 Cumulative Update 8 for SQL Server 2008 Service Pack 2;
Cumulative Update 7 for SQL Server 2008 Service Pack 3;
Cumulative Update 5 for SQL Server 2008 R2 Service Pack 1;
Cumulative Update 1 for SQL Server 2012 и более поздних версиях.
4138 2667211 Cumulative Update 13 for SQL Server 2008 R2;
Cumulative Update 7 for SQL Server 2008 R2 Service Pack 1;
Cumulative Update 1 for SQL Server 2008 R2 Service Pack 2;
Cumulative Update 2 for SQL Server 2012 и более поздних версиях.

Следующие флаги трассировки эффективны в Microsoft SQL Server 2014:

Trace Flag Description
9481 Когда используете SQL Server 2014 с уровнем совместимости базы по умолчанию 120. Флаг трассировки 9481 для принудительного использования оптимизатора запросов версии 70 (the SQL Server 2012 version) при создании нового плана запросов.
2312 Когда вы используете SQL Server 2014 с уровнем совместимости базы данных 110, т.е. уровнем совместимости с SQL Server 2012. Флаг трассировки 2312 для принудительного использования оптимизатора запросов версии 120 (the SQL Server 2014 версии) при создании нового плана запросов.

Так же есть флаг 8780 , позволяющий субд «больше думать над выбором оптимального плана запросов», но реальной пользы от него не известно.

В SQL Server 2014 новый механизм оценки количества элементов включается для всех вновь создаваемых баз данных. Однако при обновлении до SQL Server 2014 новый механизм оценки количества элементов не включается для существующих баз данных.
Чтобы обеспечить наилучшую производительность запросов, следуйте приведенным ниже рекомендациям по тестированию рабочей нагрузки с помощью нового механизма оценки количества элементов, прежде чем включать его в работающей системе.
  1. Обновите все существующие базы данных, чтобы они использовали новый механизм оценки количества элементов. ALTER DATABASE Compatibility Level (Transact-SQL) to set the database compatibility level to 120.» id=»mt12″ xml:space=»preserve»>Для этого выполните процедуру Уровень совместимости инструкции ALTER DATABASE (Transact-SQL) для установки уровня совместимости базы данных в значение 120.
  2. Запустите тестовую рабочую нагрузку с новым механизмом оценки количества элементов и устраните все выявленные проблемы производительности таким же образом, как это делается в настоящее время.
  3. Если после запуска рабочей нагрузки с новым механизмом оценки количества элементов (при уровне совместимости базы данных в 120) производительность конкретного запроса снизилась, то можно выполнить запрос с флагом трассировки 9481 для использования версии 70 ( SQL Server 2012 ) механизма оценки количества элементов. Enable plan-affecting SQL Server query optimizer behavior that can be controlled by different trace flags on a specific-query level.» id=»mt15″ xml:space=»preserve»>Описание выполнения запроса с флагом трассировки см. в статье базы знаний Включение оптимизатора запросов SQL Server, влияющего на план выполнения, которым можно управлять с помощью разных флагов трассировки на уровне конкретного запроса.
  4. ALTER DATABASE Compatibility Level (Transact-SQL) to set the database compatibility level to 110. » id=»mt16″ xml:space=»preserve»>Если не удается изменить все базы данных сразу, чтобы они использовали новый механизм оценки количества элементов, то можно использовать предыдущую версию механизма оценки количества элементов для всех баз данных, задав с помощью процедуры Уровень совместимости инструкции ALTER DATABASE (Transact-SQL) уровень совместимости баз данных в 110.
  5. Если рабочая нагрузка работает при уровне совместимости базы данных в 110 и требуется протестировать или выполнить определенный запрос с новым механизмом оценки количества элементов, то можно выполнить этот запрос с флагом трассировки 2312 для использования версии 120 (новой версии) механизма оценки количества элементов. Enable plan-affecting SQL Server query optimizer behavior that can be controlled by different trace flags on a specific-query level.» id=»mt18″ xml:space=»preserve»>Описание выполнения запроса с флагом трассировки см. в статье базы знаний Включение оптимизатора запросов SQL Server, влияющего на план выполнения, которым можно управлять с помощью разных флагов трассировки на уровне конкретного запроса.