Табличные указания (Transact-SQL)

Статья
09/10/2014

Табличные подсказки переопределяют поведение оптимизатора запросов по умолчанию на время выполнения инструкции языка обработки данных (DML) указанием способа блокировки, одного или более индексов, операции обработки запроса, например просмотра таблицы или поиска в индексе, или других параметров. Табличные указания задаются в предложении FROM инструкции DML и относятся только к таблицам и представлениям, на которые ссылается это предложение.

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

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

Область применения:

Значок ссылки на раздел Синтаксические обозначения в Transact-SQL

Синтаксис

WITH  ( <table_hint> [ [, ]...n ] )

<table_hint> ::= 
[ NOEXPAND ] { 
    INDEX  ( index_value [ ,...n ] ) | INDEX =  ( index_value )    | FORCESEEK [( index_value ( index_column_name  [ ,... ] ) ) ]
  | FORCESCAN
  | FORCESEEK
  | HOLDLOCK 
  | NOLOCK 
  | NOWAIT
  | PAGLOCK 
  | READCOMMITTED 
  | READCOMMITTEDLOCK 
  | READPAST 
  | READUNCOMMITTED 
  | REPEATABLEREAD 
  | ROWLOCK 
  | SERIALIZABLE 
  | SPATIAL_WINDOW_MAX_CELLS = integer
  | TABLOCK 
  | TABLOCKX 
  | UPDLOCK 
  | XLOCK 
} 

<table_hint_limited> ::=
{
    KEEPIDENTITY 
  | KEEPDEFAULTS 
  | HOLDLOCK 
  | IGNORE_CONSTRAINTS 
  | IGNORE_TRIGGERS 
  | NOLOCK 
  | NOWAIT
  | PAGLOCK 
  | READCOMMITTED 
  | READCOMMITTEDLOCK 
  | READPAST 
  | REPEATABLEREAD 
  | ROWLOCK 
  | SERIALIZABLE 
  | TABLOCK 
  | TABLOCKX 
  | UPDLOCK 
  | XLOCK 
}

Аргументы

WITH ( <table_hint> ) [ [, ]...n ]
Табличные указания, за некоторыми исключениями, поддерживаются в предложении FROM только в случае, когда указания заданы с ключевым словом WITH. Табличные указания также необходимо заключать в скобки.

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

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

Следующие табличные указания можно задавать как с ключевым словом WITH, так и без него: NOLOCK, READUNCOMMITTED, UPDLOCK, REPEATABLEREAD, SERIALIZABLE, READCOMMITTED, TABLOCK, TABLOCKX, PAGLOCK, ROWLOCK, NOWAIT, READPAST, XLOCK и NOEXPAND. Если такие табличные указания задаются без ключевого слова WITH, указания следует задавать отдельно. Например:

FROM t (TABLOCK)

Если указание задано с другим параметром, его необходимо использовать с ключевым словом WITH:

FROM t WITH (TABLOCK, INDEX(myindex))

Между табличными указаниями рекомендуется ставить запятые.

Важно!
Разделение указаний пробелами является устаревшим. В будущей версии Microsoft SQL Server этот компонент будет удален. Не используйте его при работе над новыми приложениями и как можно быстрее измените приложения, в которых он в настоящее время используется.

Разделение указаний пробелами является устаревшим. В будущей версии Microsoft SQL Server этот компонент будет удален. Не используйте его при работе над новыми приложениями и как можно быстрее измените приложения, в которых он в настоящее время используется.

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

NOEXPAND
Указывает, что при обработке запроса оптимизатором запросов никакие индексированные представления не расширяются для доступа к базовым таблицам. Оптимизатор запросов обрабатывает представление так же, как и таблицу с кластеризованным индексом. Подсказка NOEXPAND применяется только для индексированных представлений. Дополнительные сведения см. в подразделе «Примечания».

INDEX (index_value [,... n ] ) | INDEX = ( index_value**)**
Синтаксис INDEX() указывает имя или идентификатор одного или более индексов, используемых при обработке инструкции оптимизатором запросов. Альтернативный синтаксис «INDEX =» позволяет задать отдельное значение индекса. Для каждой таблицы можно задать только одно указание индекса.

Если имеется кластеризованный индекс, аргумент INDEX(0) приводит к просмотру кластеризованного индекса, а INDEX(1) — к просмотру или поиску по кластеризованному индексу. Если кластеризованного индекса нет, аргумент INDEX(0) приводит к просмотру таблицы, а INDEX(1) интерпретируется как ошибка.

Если в отдельном списке указаний используются несколько индексов, повторяющиеся индексы пропускаются, а остальные используются для получения строк из таблицы. Порядок индексов в указании индекса имеет значение. Несколько указаний индекса также принудительно выполняют операции И с индексами, и оптимизатор запросов применяет столько условий, сколько возможно для каждого из индексов, к которым он получает доступ. Если коллекция индексов с подсказками не включает все указанные в запросе столбцы, то выборка для получения остальных столбцов выполняется после того, как компонентом Компонент SQL Server Database Engine будут получены все индексированные столбцы.

Примечание
Если указание индекса, ссылающееся на несколько индексов, используется в таблице фактов в соединении типа «звезда», оптимизатор не учитывает индекс и возвращает предупреждение. Кроме того, выполнение операции ИЛИ с индексами также не разрешено для таблицы с заданным указанием индекса.

Если указание индекса, ссылающееся на несколько индексов, используется в таблице фактов в соединении типа «звезда», оптимизатор не учитывает индекс и возвращает предупреждение. Кроме того, выполнение операции ИЛИ с индексами также не разрешено для таблицы с заданным указанием индекса.

Максимальное число индексов в табличном указании равно 250 некластеризованным индексам.

KEEPIDENTITY
Применяется только в инструкции INSERT, когда параметр BULK используется с OPENROWSET.

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

Важно!
Если файл данных не содержит значений столбца идентификаторов таблицы или представления, а столбец идентификаторов не является последним в таблице, этот столбец необходимо пропустить. Дополнительные сведения см. в разделе Использование файла форматирования для пропуска поля данных (SQL Server). Если столбец идентификаторов успешно пропущен, то оптимизатор запросов автоматически назначает уникальные значения для столбца идентификаторов в импортируемые строки таблицы.

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

Пример использования данного указания в инструкции INSERT… Описание инструкции SELECT * FROM OPENROWSET(BULK...) см. в разделе Сохранение значений идентификаторов при массовом импорте данных (SQL Server).

Дополнительные сведения о проверке идентифицирующего значения для таблицы см. в разделе DBCC CHECKIDENT (Transact-SQL).

KEEPDEFAULTS
Применяется только в инструкции INSERT, когда параметр BULK используется с OPENROWSET.

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

Пример использования данного указания в инструкции INSERT… Описание инструкции SELECT * FROM OPENROWSET(BULK...) см. в разделе Сохранение значений NULL или использование значений по умолчанию при массовом импорте данных (SQL Server).

FORCESEEK [ **(index_value(**index_column_name [ ,... n ] )) ]
Указывает, что в качестве пути доступа к данным таблиц или представлений оптимизатор запросов использует только операцию поиска в индексе. Начиная с SQL Server 2008 R2 с пакетом обновления 1 (SP1) также могут указываться параметры индекса. В таком случае оптимизатор запросов будет использовать при выполнении операций поиска в индексе по указанному индексу как минимум все указанные столбцы индекса.

index_value
Имя или значение идентификатора индекса. Указывать идентификатор индекса 0 (куча) нельзя. Чтобы получить имя или идентификатор индекса, запросите представление каталога sys.indexes.
index_column_name
Это имя столбца индекса, включаемого в операцию поиска. Указание FORCESEEK с параметрами индекса аналогично использованию FORCESEEK с указанием INDEX. Но более эффективного контроля над путем доступа, который использует оптимизатор запросов, можно добиться указанием и индекса, в котором следует провести поиск, и столбцов индекса, которые предполагается использовать в операции поиска. При необходимости оптимизатор может задействовать дополнительные столбцы. Например, если указан некластеризованный индекс, то оптимизатор может в дополнение к указанным столбцам выбрать использование ключевых столбцов кластеризованного индекса.

Подсказка FORCESEEK может быть указана следующим образом.

Синтаксис	Пример	Описание
Без указания INDEX или индекса	FROM dbo.MyTable WITH (FORCESEEK)	Оптимизатор запросов использует только операции поиска в индексе для доступа к таблицам или представлениям через любой подходящий индекс.
В сочетании с подсказкой INDEX	FROM dbo.MyTable WITH (FORCESEEK, INDEX (MyIndex))	Оптимизатор запросов будет использовать при доступе к таблице или представлению через указанный индекс только операции поиска по индексу.
Параметризация посредством указания индекса и столбцов индекса	FROM dbo.MyTable WITH (FORCESEEK (MyIndex (col1, col2, col3)))	Оптимизатор запросов будет использовать при выполнении поиска по указанной таблице или представлению индексу как минимум указанные столбцы индекса.

При использовании указания FORCESEEK (с указанием индексов или без них) руководствуйтесь следующими рекомендациями.

Указание может задаваться как табличное указание или как указание запроса. Дополнительные сведения об указаниях запросов см. в разделе Указания запросов (Transact-SQL).
Чтобы применить FORCESEEK к индексированному представлению, должно быть также задано указание NOEXPAND.
Указание может применяться не более одного раза для каждой таблицы или представления.
Указание не может задаваться для удаленного источника данных. Если FORCESEEK задается вместе с указанием индекса, то возвращается ошибка 7377, а если FORCESEEK используется без указания индекса, то возвращается ошибка 8180.
Если из-за подсказки FORCESEEK план не был найден, возвращается ошибка 8622.

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

Указание нельзя задать для таблицы, которая является целевой для инструкции INSERT, UPDATE или DELETE.
Указание не может задаваться в сочетании с указанием INDEX или другим указанием FORCESEEK.
Должен быть указан хотя бы один столбец, который должен быть ведущим ключевым столбцом.
Можно указывать дополнительные столбцы индекса, однако пропускать ключевые столбцы нельзя. Например, если указанный индекс содержит ключевые столбцы a, b и c, то правильный синтаксис должен содержать FORCESEEK (MyIndex (a)) и FORCESEEK (MyIndex (a, b). Неправильный синтаксис может содержать FORCESEEK (MyIndex (c)) и FORCESEEK (MyIndex (a, c).
Порядок следования имен столбцов, заданный в указании, должен соответствовать порядку столбцов в упоминаемом индексе.
Столбцы, не входящие в определение ключа индекса, указывать нельзя. Например, в некластеризованном индексе могут быть указаны только определенные в индексе ключевые столбцы. Столбцы кластеризованного ключа, автоматически включаемые в индекс, указывать нельзя, однако они могут использоваться оптимизатором.
Оптимизированный для памяти xVelocity индекс columnstore не может указываться в качестве параметра индекса. Возвращается ошибка 366.
Изменение определения индекса (например, добавление или удаление столбцов) может потребовать внесения изменений в запросы, ссылающиеся на этот индекс.
Указание запрещает оптимизатору использовать пространственные индексы и XML-индексы таблицы.
Указание не может задаваться в сочетании с указанием FORCESCAN.
Для секционированных индексов столбец секционирования, неявно добавляемый SQL Server, не может быть задаваться в указании FORCESEEK.

Внимание!
Указание FORCESEEK с параметрами ограничивает число планов, которые могут быть использованы оптимизатором, в отличие от указания FORCESEEK без параметров. Из-за этого может чаще возникать ошибка «Невозможно сформировать план». В будущих выпусках внутренние изменения оптимизатора могут привести к увеличению числа этих планов.

Указание FORCESEEK с параметрами ограничивает число планов, которые могут быть использованы оптимизатором, в отличие от указания FORCESEEK без параметров. Из-за этого может чаще возникать ошибка «Невозможно сформировать план». В будущих выпусках внутренние изменения оптимизатора могут привести к увеличению числа этих планов.

FORCESCAN
Это указание, введенное в SQL Server 2008 R2 с пакетом обновления 1 (SP1), указывает оптимизатору запросов, что в качестве пути доступа к упоминаемой таблице или представлению следует использовать только операцию просмотра индекса. Указание FORCESCAN может оказаться полезным в тех запросах, где оптимизатор недооценивает число затрагиваемых строк и выбирает операцию поиска, а не просмотра. В этом случае объем памяти, выделенный для данной операции, будет недостаточным, что повлияет на производительность запроса.

Указание FORCESCAN может быть указано с указанием INDEX или без него. В сочетании с указанием индекса (INDEX = index_name, FORCESCAN) оптимизатор запросов рассматривает пути доступа просмотра через указанный индекс при доступе к упоминаемой таблице. Указание FORCESCAN может задаваться с указанием индекса INDEX(0) для принудительного просмотра базовой таблицы.

Для секционированных таблиц и индексов указание FORCESCAN применяется после устранения секционирования посредством вычисления предиката запроса. Это означает, что просмотр выполняется только по оставшимся секциям, а не по всей таблице.***

Указание FORCESCAN имеет следующие ограничения.
- Указание нельзя задать для таблицы, которая является целевой для инструкции INSERT, UPDATE или DELETE.
- Указание не может быть задаваться с более чем одним указанием индекса.
- Указание запрещает оптимизатору использовать пространственные индексы и XML-индексы таблицы.
- Указание не может задаваться для удаленного источника данных.
- Указание не может задаваться в сочетании с указанием FORCESEEK.
HOLDLOCK
Равнозначен аргументу SERIALIZABLE. Дополнительные сведения об аргументе SERIALIZABLE см. далее в этом разделе. Аргумент HOLDLOCK применяется только к таблице или представлению, для которых он задан, и только на время транзакции, определенной в использующей его инструкции. Аргумент HOLDLOCK нельзя использовать в инструкции SELECT, включающей параметр FOR BROWSE.
IGNORE_CONSTRAINTS
Применяется только в инструкции INSERT, когда параметр BULK используется с OPENROWSET.

Указывает, что при операции массового импорта будут пропускаться какие-либо ограничения на таблицу. По умолчанию INSERT проверяет Ограничения уникальности и проверочные ограничения и Ограничения первичных и внешних ключей. Если для операции массового импорта задан параметр IGNORE_CONSTRAINTS, инструкция INSERT будет пропускать ограничения в целевой таблице. Обратите внимание, что нельзя отключить ограничения UNIQUE, PRIMARY KEY или NOT NULL.

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

Однако при пропуске ограничений CHECK и FOREIGN KEY после операции каждое пропущенное ограничение помечается как is_not_trusted в представлении каталога sys.check_constraints или sys.foreign_keys. Рано или поздно придется проверить всю таблицу на соответствие ограничениям. Если таблица не была пустой перед операцией массового импорта, затраты на повторную проверку ограничений могут превысить затраты от применения ограничений CHECK и FOREIGN KEY к добавочным данным.
IGNORE_TRIGGERS
Применяется только в инструкции INSERT, когда параметр BULK используется с OPENROWSET.

Указывает, что при операции объемного импорта не будут учитываться какие-либо триггеры, определенные для таблицы. По умолчанию для инструкции INSERT применяются триггеры.

Аргумент IGNORE_TRIGGERS следует использовать только в случае, когда приложение не зависит от каких-либо триггеров и важно максимизировать производительность.

NOLOCK
Равнозначен аргументу READUNCOMMITTED. Дополнительные сведения об аргументе READUNCOMMITTED см. далее в этом разделе.

Примечание
Для инструкций UPDATE и DELETE. В будущей версии Microsoft SQL Server этот компонент будет удален. Избегайте использования этого компонента в новых разработках и запланируйте изменение существующих приложений, в которых он применяется.

Для инструкций UPDATE и DELETE. В будущей версии Microsoft SQL Server этот компонент будет удален. Избегайте использования этого компонента в новых разработках и запланируйте изменение существующих приложений, в которых он применяется.

NOWAIT
Указывает компоненту Компонент Database Engine вернуть сообщение сразу после наложения блокировки на таблицу. Аргумент NOWAIT равнозначен указанию SET LOCK_TIMEOUT 0 для конкретной таблицы. Указание NOWAIT не работает, если также используется указание TABLOCK. Для прерывания запроса без ожидания при использовании указания TABLOCK предварите запрос следующим: SETLOCK_TIMEOUT 0;.
PAGLOCK
Применяет блокировку страниц вместо стандартной блокировки строк или ключей, а также вместо блокировки отдельной таблицы. По умолчанию используется режим блокировки, соответствующий операции. При указании блокировок в транзакциях, выполняемых с уровнем изоляции SNAPSHOT, они применяются только в том случае, когда подсказка PAGLOCK используется в сочетании с другими табличными подсказками, требующими блокировки, например UPDLOCK или HOLDLOCK.

READCOMMITTED
Указывает, что операции чтения соответствуют правилам для уровня изоляции READ COMMITTED путем использования блокировки или управления версиями строк. Если параметр базы данных READ_COMMITTED_SNAPSHOT установлен в значение OFF, компонент Компонент Database Engine устанавливает совмещаемую блокировку по мере чтения данных и снимает блокировку при завершении операции чтения. Если значение параметра базы данных READ_COMMITTED_SNAPSHOT равно ON, компонент Компонент Database Engine не накладывает блокировок и использует управление версиями строк. Дополнительные сведения об уровнях изоляции см. в разделе SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

READCOMMITTEDLOCK
Указывает, что операции чтения соответствуют правилам для уровня изоляции READ COMMITTED путем использования блокировки. Компонент Компонент Database Engine накладывает совмещаемые блокировки по мере чтения данных и снимает их после завершения операции чтения вне зависимости от значения параметра базы данных READ_COMMITTED_SNAPSHOT. Дополнительные сведения об уровнях изоляции см. в разделе SET TRANSACTION ISOLATION LEVEL (Transact-SQL). Это указание не может задаваться в целевой таблице инструкции INSERT, в таком случае возвращается ошибка 4140.
READPAST
Указывает, что компонент Компонент Database Engine не считывает строки и страницы, заблокированные другими транзакциями. Если указан аргумент READPAST, то блокировки уровня строк будут пропускаться. Компонент Компонент Database Engine будет пропускать строки вместо блокировки текущей транзакции до тех пор, пока блокировки не будут сняты. Например, предположим, что в таблице T1 есть один целочисленный столбец со значениями 1, 2, 3, 4, 5. Если транзакция A изменит значение 3 на 8, но еще не будет зафиксирована, то инструкция SELECT * FROM T1 (READPAST) возвратит значения 1, 2, 4, 5. Параметр READPAST главным образом используется для устранения конфликта блокировок при реализации рабочей очереди, использующей таблицу SQL Server. Средство чтения очереди, использующее аргумент READPAST, пропускает прошлые записи очереди, заблокированные другими транзакциями, до следующей доступной записи очереди, не дожидаясь, пока другие транзакции снимут свои блокировки.

Аргумент READPAST можно задать для любой таблицы, к которой обращается инструкция UPDATE или DELETE, и к любой таблице, на которую ссылается предложение FROM. Если аргумент READPAST задан в инструкции UPDATE, он применяется только при считывании данных для идентификации подлежащих обновлению записей вне зависимости от того, где он указан в инструкции. Аргумент READPAST для таблиц из предложения INTO инструкции INSERT задать нельзя. Операции чтения, в которых используется аргумент READPAST, не блокируются. Операции обновления или удаления, использующие аргумент READPAST, могут блокироваться либо при считывании внешних ключей или индексированных представлений, либо при изменении вторичных индексов.

Аргумент READPAST можно указывать только в транзакциях, выполняемых на уровнях изоляции READ COMMITTED или REPEATABLE READ. При указании подсказки READPAST в транзакциях, выполняемых с уровнем изоляции SNAPSHOT, она должна использоваться в сочетании с другими табличными подсказками, требующими блокировки, например UPDLOCK или HOLDLOCK.

Табличная подсказка READPAST не может быть указана, если параметр базы данных READ_COMMITTED_SNAPSHOT установлен в ON и выполняется одно из следующих условий.
- Уровень изоляции транзакции сеанса имеет значение READ COMMITTED.
- В запросе также указана табличная подсказка READCOMMITTED.
Чтобы в этих случаях задать указание READPAST, удалите табличное указание READCOMMITTED (если оно существует) и включите в запрос табличное указание READCOMMITTEDLOCK.

Примечание
Для инструкций UPDATE и DELETE. В будущей версии Microsoft SQL Server этот компонент будет удален. Избегайте использования этого компонента в новых разработках и запланируйте изменение существующих приложений, в которых он применяется.

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

Указания READUNCOMMITTED и NOLOCK применяются только к блокировкам данных. Все запросы, включая запросы с указаниями READUNCOMMITTED и NOLOCK, получают блокировку Sch-S (стабильность схемы) в процессе компиляции и выполнения. Поэтому запросы блокируются, если параллельная транзакция удерживает в таблице блокировку Sch-M (изменение схемы). Например, операция языка DDL получает блокировку Sch-M, прежде чем изменяет данные схемы. Все параллельные запросы, включая выполняемые с указаниями READUNCOMMITTED или NOLOCK, блокируются при попытке получить блокировку Sch-S. И наоборот, запрос, удерживающий блокировку Sch-S, блокирует параллельную транзакцию, которая пытается получить блокировку Sch-M.

Подсказки READUNCOMMITTED и NOLOCK для таблиц, измененных операциями вставки, обновления или удаления, указать нельзя. Оптимизатор запросов SQL Server не учитывает подсказки READUNCOMMITTED и NOLOCK в предложении FROM, применяемые к целевой таблице инструкции UPDATE или DELETE.

Примечание
Поддержка использования подсказок READUNCOMMITTED и NOLOCK в предложении FROM, применяемом к целевой таблице инструкции UPDATE или DELETE, будет удалена в следующей версии SQL Server. Следует избегать использования этих указаний в таком контексте в новой разработке и запланировать изменение приложений, использующих их в настоящий момент.

Поддержка использования подсказок READUNCOMMITTED и NOLOCK в предложении FROM, применяемом к целевой таблице инструкции UPDATE или DELETE, будет удалена в следующей версии SQL Server. Следует избегать использования этих указаний в таком контексте в новой разработке и запланировать изменение приложений, использующих их в настоящий момент.

Минимизировать состязание блокировок во время защиты транзакций от «грязных» чтений незафиксированных изменений данных можно следующими способами.

Уровень изоляции READ COMMITTED с параметром базы данных READ_COMMITTED_SNAPSHOT, установленным в значение ON.
Уровень изоляции моментального снимка (SNAPSHOT).

Дополнительные сведения об уровнях изоляции см. в разделе SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

Примечание
Если выдается сообщение об ошибке 601 при заданном параметре READUNCOMMITTED, ее следует разрешить так же, как и ошибку взаимоблокировки (1205), и затем повторить инструкцию.

REPEATABLEREAD
Указывает, что просмотр выполняется с той же семантикой блокировки, что и транзакция, запущенная на уровне изоляции REPEATABLE READ. Дополнительные сведения об уровнях изоляции см. в разделе SET TRANSACTION ISOLATION LEVEL (Transact-SQL).
ROWLOCK
Указывает, что вместо блокировки страниц или таблиц применяются блокировки строк. При указании блокировок строк в транзакциях, выполняемых на уровне изоляции SNAPSHOT, они применяются только в случае, когда подсказка ROWLOCK используется в сочетании с другими табличными подсказками, требующими блокировки, например UPDLOCK или HOLDLOCK.
SPATIAL_WINDOW_MAX_CELLS = integer
Указывает максимальное количество ячеек, используемых для тесселяции геометрического или географического объекта. number — значение от 1 до 8192.

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

Этот параметр работает и в ручной и в автоматической тесселяции сетки.
SERIALIZABLE
Равнозначен аргументу HOLDLOCK. Накладывает дополнительные ограничения на совмещаемую блокировку: удерживает ее до завершения транзакции вместо снятия блокировки сразу после того, как таблица или страница данных больше не требуется, независимо от того, завершена ли транзакция. Просмотр выполняется с той же семантикой, что и транзакция, запущенная на уровне изоляции SERIALIZABLE. Дополнительные сведения об уровнях изоляции см. в разделе SET TRANSACTION ISOLATION LEVEL (Transact-SQL).
TABLOCK
Указывает, что полученная блокировка применяется на уровне таблицы. Тип полученной блокировки зависит от того, какая инструкция выполняется. Например, инструкция SELECT может потребовать совмещаемой блокировки. Если указано TABLOCK, то совмещаемая блокировка применяется ко всей таблице, а не на уровне строк или страниц. Если также указано HOLDLOCK, то блокировка таблицы удерживается до конца транзакции.

Во время импорта данных в кучу с помощью инструкции INSERT INTO <целевая_таблица> SELECT <столбцы> FROM <исходная_таблица> можно включить оптимизированное ведение журнала и блокировки для инструкции, задав для целевой таблицы указание TABLOCK. Кроме того, для базы данных должна быть задана простая модель восстановления или модель восстановления с неполным протоколированием. Дополнительные сведения см. в разделе Инструкция INSERT (Transact-SQL).

При использовании с поставщиком больших наборов строк OPENROWSET для импорта данных в таблицу подсказка TABLOCK позволяет нескольким клиентам параллельно загружать данные в целевую таблицу с оптимизацией записи в журнал и блокировки. Дополнительные сведения см. в разделе Предварительные условия для минимального ведения журнала массового импорта данных.
TABLOCKX
Указывает, что к таблице применяется монопольная блокировка.
UPDLOCK
Указывает, что блокировки обновления применяются и удерживаются до завершения транзакции. UPDLOCK получает блокировки обновления для операций чтения только на уровне строк или страниц. Если UPDLOCK используется в сочетании с TABLOCK или по какой-либо другой причине уже получена блокировка на уровне таблицы, то вместо них будет получена монопольная (X) блокировка.

Если указано UPDLOCK, то указания уровня изоляции READCOMMITTED и READCOMMITTEDLOCK не учитываются. Например, если уровень изоляции в данном сеансе установлен в SERIALIZABLE и в запросе указано (UPDLOCK, READCOMMITTED), то указание READCOMMITTED не учитывается и транзакция будет выполняться на уровне изоляции SERIALIZABLE.
XLOCK
Указывает, что монопольные блокировки применяются и удерживаются до завершения транзакции. Если при этом указан аргумент ROWLOCK, PAGLOCK или TABLOCK, монопольная блокировка применяется к соответствующему уровню гранулярности.

Замечания

Табличные указания пропускаются, если доступ к таблице не предусмотрен планом запроса. Это может быть вызвано тем, что оптимизатор вообще отказался от доступа к таблице или вместо этого получает доступ к индексированному представлению. В последнем случае доступ к индексированному представлению можно предотвратить с помощью указания запроса OPTION (EXPAND VIEWS).

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

Указания блокировки ROWLOCK, UPDLOCK и XLOCK, накладывающие блокировку уровня строки, могут накладывать блокировки на ключи индекса вместо фактических строк данных. Например, если для таблицы имеется некластеризованный индекс, а инструкция SELECT обрабатывается покрывающим индексом с использованием указания блокировки, блокировка накладывается на ключ покрывающего индекса вместо строки данных в базовой таблице.

Если таблица содержит вычисляемые столбцы, которые вычисляются выражениями или функциями, получающими доступ к столбцам других таблиц, то в таких таблицах табличные подсказки не используются и не распространяются. Например, в запросе задано табличное указание NOLOCK для таблицы. В этой таблице есть столбцы, вычисляемые с помощью сочетания выражений и функций, получающих доступ к столбцам другой таблицы. При доступе к таблицам, на которые ссылаются выражения и функции, табличное указание NOLOCK не используется.

SQL Server не разрешает более одного табличного указания из каждой из следующих групп в каждой из таблиц в предложении FROM.

Указания степени гранулярности: PAGLOCK, NOLOCK, READCOMMITTEDLOCK, ROWLOCK, TABLOCK или TABLOCKX.
Указания уровня изоляции: HOLDLOCK, NOLOCK, READCOMMITTED, REPEATABLEREAD, SERIALIZABLE.

Указания отфильтрованного индекса

Отфильтрованный индекс можно использовать в качестве табличной подсказки, но, если он не распространяется на все выбранные запросом строки, оптимизатор запросов выдаст ошибку 8622. Далее приводится пример недопустимого указания отфильтрованного индекса. В примере создается отфильтрованный индекс FIBillOfMaterialsWithComponentID, который затем используется как подсказка индекса для инструкции SELECT. Предикат отфильтрованного индекса содержит строки данных для ComponentID со значениями 533, 324 и 753. Предикат запроса также содержит строки данных для ComponentID со значениями 533, 324 и 753, но расширяет результирующий набор на ComponentID со значениями 855 и 924, которые отсутствуют в отфильтрованном индексе. Поэтому оптимизатор запросов не может использовать подсказку отфильтрованного индекса и формирует ошибку 8622. Дополнительные сведения см. в разделе Создание отфильтрованных индексов.

USE AdventureWorks2012;
GO
IF EXISTS (SELECT name FROM sys.indexes
    WHERE name = N'FIBillOfMaterialsWithComponentID' 
    AND object_id = OBJECT_ID(N'Production.BillOfMaterials'))
DROP INDEX FIBillOfMaterialsWithComponentID
    ON Production.BillOfMaterials;
GO
CREATE NONCLUSTERED INDEX "FIBillOfMaterialsWithComponentID"
    ON Production.BillOfMaterials (ComponentID, StartDate, EndDate)
    WHERE ComponentID IN (533, 324, 753);
GO
SELECT StartDate, ComponentID FROM Production.BillOfMaterials
    WITH( INDEX (FIBillOfMaterialsWithComponentID) )
    WHERE ComponentID in (533, 324, 753, 855, 924);
GO

Оптимизатор запросов не учитывает указание индекса, если в параметрах SET отсутствуют значения, необходимые для отфильтрованных индексов. Дополнительные сведения см. в разделе CREATE INDEX (Transact-SQL).

Использование подсказки NOEXPAND

Аргумент NOEXPAND применяется только для индексированных представлений. Индексированное представление — это представление с созданным на нем уникальным кластеризованным индексом. Если запрос содержит ссылки на столбцы, присутствующие как в индексированном представлении, так и в базовых таблицах, а оптимизатор запросов определяет, что использование индексированного представления является лучшим методом выполнения запроса, оптимизатор будет использовать индекс представления. Эта функциональная возможность называется сопоставлением индексированного представления. Автоматическое использование индексированного представления оптимизатором запросов поддерживается только в определенных выпусках SQL Server. Сведения о функциях, поддерживаемых различными выпусками SQL Server, см. в разделе Функции, поддерживаемые выпусками SQL Server 2012.

Однако, чтобы оптимизатор учитывал индексированные представления для согласования или использовал индексированное представление, обращение к которому производится с подсказкой NOEXPAND, нужно установить в ON следующие параметры SET.

ANSI_NULLS	ANSI_WARNINGS	CONCAT_NULL_YIELDS_NULL
ANSI_PADDING	ARITHABORT1	QUOTED_IDENTIFIERS

1 Параметр ARITHABORT неявным образом получает значение ON, когда в ON устанавливается значение параметра ANSI_WARNINGS. Поэтому менять этот параметр вручную не обязательно.

Кроме того, параметр NUMERIC_ROUNDABORT нужно установить в OFF.

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

Использование табличного указания в качестве указания запроса

Табличные указания могут быть указаны в качестве указаний запроса с помощью предложения OPTION (TABLE HINT). Табличные подсказки рекомендуется использовать в качестве подсказок в запросах только в контексте структуры плана. Для нерегламентированных запросов эти указания следует задавать как табличные указания. Дополнительные сведения см. в разделе Указания запросов (Transact-SQL).

Разрешения

Для указаний KEEPIDENTITY, IGNORE_CONSTRAINTS и IGNORE_TRIGGERS требуются разрешения ALTER на таблицу.

Примеры

А.Использование подсказки TABLOCK для указания метода блокировки

В следующем примере показано, как на таблицу Production.Product накладывается совмещаемая блокировка, удерживаемая до завершения инструкции UPDATE.

USE AdventureWorks2012;
GO
UPDATE Production.Product
WITH (TABLOCK)
SET ListPrice = ListPrice * 1.10
WHERE ProductNumber LIKE 'BK-%';
GO

Б.Использование указания FORCESEEK для указания операции поиска в индексе

В следующем примере показано использование указания FORCESEEK без указания индекса, предписывающее оптимизатору запросов выполнять операцию поиска в индексе для таблицы Sales.SalesOrderDetail.

USE AdventureWorks2012;
GO
SELECT *
FROM Sales.SalesOrderHeader AS h
INNER JOIN Sales.SalesOrderDetail AS d WITH (FORCESEEK)
    ON h.SalesOrderID = d.SalesOrderID 
WHERE h.TotalDue > 100
AND (d.OrderQty > 5 OR d.LineTotal < 1000.00);
GO

В следующем примере указание FORCESEEK с индексом предписывает оптимизатору запросов выполнить операцию поиска по указанному индексу и столбцу индекса.

USE AdventureWorks2012; 
GO
SELECT h.SalesOrderID, h.TotalDue, d.OrderQty
FROM Sales.SalesOrderHeader AS h
    INNER JOIN Sales.SalesOrderDetail AS d 
    WITH (FORCESEEK (PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID (SalesOrderID))) 
    ON h.SalesOrderID = d.SalesOrderID 
WHERE h.TotalDue > 100
AND (d.OrderQty > 5 OR d.LineTotal < 1000.00); 
GO

В.Использование указания FORCESCAN для назначения операции просмотра индекса

В следующем примере показано использование указания FORCESCAN, предписывающего оптимизатору запросов принудительно выполнить операцию просмотра в таблице Sales.SalesOrderDetail.

USE AdventureWorks2012; 
GO
SELECT h.SalesOrderID, h.TotalDue, d.OrderQty
FROM Sales.SalesOrderHeader AS h
    INNER JOIN Sales.SalesOrderDetail AS d 
    WITH (FORCESCAN) 
    ON h.SalesOrderID = d.SalesOrderID 
WHERE h.TotalDue > 100
AND (d.OrderQty > 5 OR d.LineTotal < 1000.00);

См. также

Справочник

OPENROWSET (Transact-SQL)

Указания (Transact-SQL)

Указания запросов (Transact-SQL)

Поделиться через