With nolock sql что это

7 вещей, которые разработчик должен знать о SQL Server

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

7. Производительность скалярных UDF оставляет желать лучшего

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

Посмотрите этот пост о принудительном использовании параллелизма – в частности, список того, что приводит к генерации «однопоточного» плана выполнения запроса. Скорее всего, использование скалярных UDF (прим. переводчика: а для серверов младше 2008 R2 и не только скалярных) приведёт к тому, что ваш запрос будет выполняться в одном потоке (*грустно вздыхает*).

Если вы хотите, чтобы ваш код использовался повторно, подумайте о хранимых процедурах и представлениях. (На самом деле, они могут привнести свои проблемы с производительностью, но я просто хочу направить вас на правильный путь как можно быстрее, а UDF, увы, таковым не является).

6. «WITH (NOLOCK)» не означает, что блокировок не будет вообще

На одном из этапов своей карьеры разработчика вы можете начать использовать хинт WITH (NOLOCK) повсеместно, поскольку с ним ваши запросы выполняются быстрее. Это не всегда плохо, но может сопровождаться неожиданными побочными эффектами, про которые Kendra Little рассказывала вот в этом видео. Я же сфокусируюсь только на одном из них.

Когда ваш запрос обращается к какой-либо таблице, даже с хинтом NOLOCK, вы накладываете блокировку стабилизации схемы (schema stability lock, Sch-S). Никто не сможет изменить эту таблицу или её индексы до тех пор, пока ваш запрос не завершится. Это не кажется серьёзной проблемой до тех пор, пока вам не понадобится удалить индекс, но вы не сможете этого сделать, поскольку люди постоянно работают с этой таблицей, находясь в полной уверенности, что не создают никаких проблем, поскольку они используют хинт WITH (NOLOCK).

Здесь нет «серебряной пули», но начните читать об уровнях изоляции SQL Server — я полагаю, что уровень изоляции READ COMMITTED SNAPSHOT будет наилучшим выбором для вашего приложения. Вы будете получать целостные данные с меньшим количеством проблем с блокировками.

5. Используйте три строки соединения в своём приложении

Я знаю, что сейчас у вас только один SQL Server, но поверьте мне, оно стоит того. Создайте три строки соединения, которые сейчас будут ссылаться только на один сервер, но потом, когда вы задумаетесь о масштабировании, у вас будет возможность использовать разные сервера «для обслуживания» каждой из этих строк.

4. Используйте промежуточную БД

Вероятно, вы используете БД для выполнения каких-то второстепенных задач – вычисления, сортировка, загрузка и т.д. Если вдруг эти данные пропадут, вы вряд ли сильно расстроитесь, но вот структура таблиц – это, конечно, другое дело. Сейчас вы делаете всё в «основной базе данных» вашего приложения.

Создайте отдельную базу данных, назовите её MyAppTemp, и делайте всё в ней! Поставьте ей простую модель восстановления и просто создавайте резервную копию раз в день. Не заморачивайтесь с высокой доступностью или аварийным восстановлением этой БД.

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

3. «Вчерашние» статьи и книги могут перестать быть актуальными сегодня.

SQL Server вышел уже больше десяти лет назад и за эти годы в нём произошло множество изменений. К сожалению, старые материалы не всегда обновляются, чтобы описать «сегодняшние» изменения. Даже свежие материалы из проверенных источников могут быть неправильными – вот, например, критика методики Microsoft по повышению производительности SQL Server. Microsoft Certified Master Jonathan Kehayias нашёл множество по-настоящему плохих советов в документе Microsoft.

Когда вы слышите что-то, что звучит как хороший совет, я предлагаю вам использовать стратегию, обратную стратегии доктора Фила. Доктор Фил говорит, что вы должны «проникнуться» любой идеей на протяжении 15 минут. Вместо этого, попробуйте возненавидеть её – постарайтесь опровергнуть то, что вы прочитали перед тем как применять это в продакшене. Даже если совет чертовски хорош, он может быть не очень-то и полезным на вашей системе. (Да, это относится и к моим советам).

2. Избегайте использования ORDER BY; сортируйте данные в приложении

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

UPD. Я получил множество комментариев о том, что приложение нуждается, например, только в десяти строках, вместо десяти миллионов строк, возвращаемых запросом. Да, конечно, если вы пишете TOP 10, вам нужна сортировка, но как на счёт того, чтобы переписать запрос так, чтобы он не возвращал кучу ненужных данных? Если же данных так много, что серверу приложений приходится тратить слишком много ресурсов на сортировку – так ведь и SQL Server выполняет ту же самую работу. Мы поговорим о том как находить такие запросы на вебинаре, ссылка на который есть в конце поста. Кроме того, помните, что я сказал «Избегайте использования ORDER BY», а не «Никогда не используйте ORDER BY». Я точно так же использую эту инструкцию – но, если я могу избежать этого на очень дорогом уровне баз данных, я стараюсь это сделать. Вот что означает «избегать».

(А это часть, в которой фанаты MySQL и PostgreSQL рассказывают о том как снизить стоимость лицензий, используя СУБД с открытым исходным кодом). (А в этой части вы ждёте, что я им остроумно отвечу, но я не буду этого делать. Если вы разрабатываете новое приложение и задумались о выборе БД, прочтите мой ответ на StackOverflow о том какая БД выдержит наибольшую нагрузку.)

1. У SQL Server есть встроенные инструменты для поиска узких мест, не влияющие на производительность

Динамические административные представления SQL Server (DMV) могут показать вам все места, пагубно влияющие на производительность, т.е.:

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

Источник

Дедлоки в SQL Server — часть 1: Что такое блокировки

Введение

В сложных, распределенных, высоконагруженных системах очень часто возникает проблема конкурентного доступа к данным, что приводит к использованию различного рода блокировок в запросах к БД. Кроме того, могут возникать взаимоблокировки, с которыми нужно бороться.

Мы начинаем серию публикаций, посвященную блокировкам в базах данных, в которых поделимся своим опытом, наработанном в нашей компании, по следующим вопросам:

– Какие виды блокировок использовали при разработке наших продуктов

– Какие средства диагностики и инструменты использовали при определении взаимоблокировок

– Как боролись с взаимоблокировками

В данной статье серии, мы рассмотрим блокировки, с которыми столкнулись при разработке программных продуктов.

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

Зачем нужны блокировки

В распределенных системах, использующих в качестве хранилища БД, могут возникать различные побочные эффекты при параллельном доступе к данным:

– Потерянное обновление (lost update) — две транзакции выполняют одновременно UPDATE для одной и той же строки, и изменения, сделанные одной транзакцией, затираются другой;

– «Грязное» чтение (dirty read) — это такое чтение, при котором могут быть считаны добавленные или изменённые данные из другой транзакции, которая впоследствии откатится;

– Неповторяющееся чтение (non-repeatable read) — проявляется, когда при повторном чтении в рамках одной транзакции, ранее прочитанные данные, оказываются изменёнными;

– Фантомное чтение (phantom reads) — можно наблюдать, когда одна транзакция в ходе своего выполнения несколько раз выбирает множество строк по одним и тем же критериям. При этом другая транзакция в интервалах между этими выборками добавляет или удаляет строки, или изменяет столбцы некоторых строк, используемых в критериях выборки первой транзакции, и успешно заканчивается. В результате получится, что одни и те же выборки в первой транзакции дают разные множества строк.

Современные СУБД имеют возможность решать подобные проблемы при помощи встроенных механизмов, например MS SQL Server делает это с помощью использования различных уровней изоляции транзакций:

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

Блокировки

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

Задача извлечения данных из очереди

Классической задачей, при котором используется блокировка, это задача извлечения элемента из очереди, c которой одномоментно может работать ограниченное количество клиентов. Доступ к такому элементу одновременно должен получить только один клиент очереди.

В нескольких наших продуктах используется очередь, хранимая в БД, доступ к которой должен быть синхронизирован между клиентами. Рассмотрим, как решалась данная задача в наших продуктах.

Представим, что у нас есть очередь задач для клиентов, которая хранится в БД. Каждую задачу может получить одновременно максимум N клиентов. Т.е. появляется связанность задачи с ее результатами, это связь 1-N. Это привносит дополнительную сложность, т.к. извлекаемые данные находятся в двух таблицах, связанных внешним ключом. Структура таблиц выглядит примерно следующим образом:

Task — таблица с задачами

TaskResult — таблица с результатами задач.

Для решения этой задачи мы использовали блокировку обновления (UPDLOCK) записей корневой таблицы Task совместно с хинтами ROWLOCK и READPAST.

Использование блокировки UPDLOCK гарантировало нам, что к задаче не смогут получить доступ другие клиенты, хинтом ROWLOCK мы рекомендовали MS SQL серверу использовать блокировку на уровне строки, READPAST — выбирал нам только незаблокированные другими транзакциями записи. Помимо извлечения данных, мы также во все операции обновления данных, как корневой, так и дочерней таблицы, включили хинты UPDLOCK и ROWLOCK.

Таким образом мы решили задачу конкурентного доступа к задачам клиентами.

Задача извлечения данных, которые параллельно изменяются

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

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

Указание хинта NOLOCK указывает SQL Server-у не использовать блокировки при выполнении данной транзакции, в результате чтение данных происходит быстро. Но здесь стоит отметить один момент — на самом деле все же один тип блокировки, даже при использовании хинта NOLOCK, будет устанавливаться. Это блокировка стабилизации схемы (schema stability lock, Sch-S). Но обычно редко во время выполнения бизнес-логики, затрагивающее работу с БД, требуется менять схему БД, поэтому данная блокировка не будет оказывать никакого влияния. Но, в тех системах, в которых все же меняется схема БД параллельно с доступом к данным БД, нужно учитывать данное обстоятельство и использовать другие механизмы блокировок.

Задача синхронизации доступа к модифицируемым данным

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

Рассмотрим пример на Рис.2. Допустим, у нас есть 2 параллельно выполняющиеся транзакции, которые выбирают вторую строку из таблицы Table для последующей модификации. Для того, чтобы предотвратить одновременный доступ к этой строке для модификации мы наложили блокировку обновления UPDLOCK на оператор выборки SELECT. Таким образом, доступ ко второй строке получит транзакция, первой наложившая блокировку обновления. Вторая же транзакция будет ожидать освобождения наложенной блокировки.

Управление эскалацией блокировок

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

Например, если у нас создано множество блокировок уровня строки, и все строки принадлежат одной странице, то сервер, в целях экономии ресурсов, может заменить их все одной блокировкой уровня страницы и далее до уровня всей таблицы (см. Рис. 3).

В наших продуктах мы также использовали управление опцией эскалации блокировок там, где это нужно. В одном из проектов мы столкнулись с эскалацией блокировок с уровня строк до более верхних уровней — страниц, экстентов. Эскалации возникали при высокой нагрузке на несколько связанных между собой таблиц. Транзакции блокировали друг друга и завершались по ошибке. Чтобы решить данную проблему мы запретили эскалацию блокировок для некоторых таблиц, которые ссылались на множество других таблиц.

После отключения эскалации блокировок, проблема с взаимными блокировками решилась. Общая пропускная способность системы тоже возросла за счет того, что значительно сократилось общее количество блокировок.

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

Взаимоблокировки

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

Рассмотрим простейший пример. Допустим, у нас есть таблица Students, состоящая из 2-х строк:

Параллельно запускаются 2 транзакции:

На операторе обновления мы получим блокировку в обеих транзакциях. Что же тут происходит? Оператор SELECT в обеих транзакциях накладывает блокировку обновления на вторую и первую строки соответственно. Затем, при выполнении оператора UPDATE в первой транзакции, процесс пытается наложить эксклюзивную блокировку на первую строку, но не может этого сделать, т.к. она заблокирована второй транзакцией и остается ждать его освобождения.

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

Обе транзакции ждут освобождения заблокированных друг другом ресурсов. Но на этом процессы не виснут. MS SQL Server с помощью встроенного менеджера блокировок определяет взаимные блокировки и разрешает их. Разрешает очень просто — жертвует одной из транзакций, т. е. попросту откатывает ее и возвращает ошибку. Вторая транзакция продолжит выполняться. Какая транзакция будет выбрана в качестве жертвы — определяет сам MS SQL Server, исходя из соображений производительности или же, определяется при помощи задания приоритета DEADLOCK_PRIORITY.

Заключение

В данной статье мы рассказали о блокировках и для чего они нужны. Если говорить вкратце — блокировки являются механизмом для реализации требования различных уровней изоляции транзакций.

Мы рассмотрели проблемы, которые приходилось решать на практике с помощью блокировок в многопользовательской конкурентной среде:

– это задача извлечения данных из очереди — когда одномоментно к данным имеет доступ только один клиент;

– это задача извлечения изменяемых данных с конкурентным доступом — когда клиенты одновременно обращаются к общим, часто изменяемым данным, но только для чтения;

– это задача синхронизации доступа к модифицируемым данным — когда необходимо получить доступ к данным с последующим изменением.

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

Ну и на десерт, мы затронули вкратце о взаимоблокировках — ситуации, при котором 2 процесса ждут освобождения заблокированного ресурса друг у друга.

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

Автор статьи: Николай Иванов, Старший Разработчик, ITA Labs

Источник

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

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

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

Применимо к:

Синтаксические обозначения в Transact-SQL

Синтаксис

Ссылки на описание синтаксиса Transact-SQL для SQL Server 2014 и более ранних версий, см. в статье Документация по предыдущим версиям.

Аргументы

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

index_value
Имя или значение идентификатора индекса. Указывать идентификатор индекса 0 (куча) нельзя. Чтобы получить имя или идентификатор индекса, запросите представление каталога sys.indexes.

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

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

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

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

Указание FORCESEEK с параметрами ограничивает число планов, которые могут быть использованы оптимизатором, в отличие от указания FORCESEEK без параметров. Это может привести к тому, что ошибка Plan cannot be generated возникает в нескольких случаях. В будущих выпусках внутренние изменения оптимизатора запросов могут привести к увеличению числа этих планов.

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

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

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

Указание FORCESCAN имеет следующие ограничения:

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 этот компонент будет удален. Избегайте использования этого компонента в новых разработках и запланируйте изменение существующих приложений, в которых он применяется.

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).

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

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

Табличное указание READPAST нельзя указать, если для параметра базы данных READ_COMMITTED_SNAPSHOT установлено значение ON и выполняется одно из следующих условий:

Чтобы в этих случаях указать подсказку READPAST, удалите табличную подсказку READCOMMITTED (если существует) и включите в запрос табличную подсказку READCOMMITTEDLOCK.

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. Следует избегать использования этих указаний в таком контексте в новой разработке и запланировать изменение приложений, использующих их в настоящий момент.

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

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

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

REPEATABLEREAD
Указывает, что сканирование выполняется с той же семантикой блокировки, что и транзакция, запущенная на уровне изоляции REPEATABLE READ. Дополнительные сведения об уровнях изоляции см. в разделе SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

ROWLOCK
Указывает, что вместо блокировки страниц или таблиц применяются блокировки строк. При указании блокировок строк в транзакциях, выполняемых на уровне изоляции SNAPSHOT, они применяются только в случае, когда подсказка ROWLOCK используется в сочетании с другими табличными подсказками, требующими блокировки, например UPDLOCK или HOLDLOCK. ROWLOCK нельзя использовать с таблицей, имеющей кластеризованный индекс columnstore. В следующем примере в приложении возвращается ошибка 651.

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

SNAPSHOT
Область применения: SQL Server 2014 (12.x) и более поздних версий.

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

SPATIAL_WINDOW_MAX_CELLS =
Область применения: SQL Server 2012 (11.x) и более поздних версий.
Указывает максимальное количество ячеек, используемых для тесселяции геометрического или географического объекта. — это число от 1 до 8192.

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

Этот параметр работает и в ручной и в автоматической тесселяции сетки.

TABLOCK
Указывает, что полученная блокировка применяется на уровне таблицы. Тип полученной блокировки зависит от того, какая инструкция выполняется. Например, инструкция SELECT может потребовать совмещаемой блокировки. При указании TABLOCK совмещаемая блокировка применяется ко всей таблице, а не на уровне строк или страниц. Если также указано HOLDLOCK, то блокировка таблицы удерживается до конца транзакции.

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

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

TABLOCKX
Указывает, что к таблице применяется монопольная блокировка.

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

Если указано UPDLOCK, то указания уровня изоляции READCOMMITTED и READCOMMITTEDLOCK не учитываются. Например, если уровень изоляции в данном сеансе установлен в SERIALIZABLE и в запросе указано (UPDLOCK, READCOMMITTED), то указание READCOMMITTED не учитывается и транзакция будет выполняться на уровне изоляции SERIALIZABLE.

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

Remarks

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

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

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

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

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

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

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

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

Аргумент NOEXPAND применяется только для индексированных представлений. Индексированное представление — это представление с созданным на нем уникальным кластеризованным индексом. Если запрос содержит ссылки на столбцы, присутствующие как в индексированном представлении, так и в базовых таблицах, а оптимизатор запросов определяет, что использование индексированного представления является лучшим методом выполнения запроса, то оптимизатор будет использовать индекс представления. Эта функциональная возможность называется сопоставлением индексированного представления. До SQL Server 2016 (13.x); с пакетом обновления 1 (SP1) автоматическое использование индексированного представления оптимизатором запросов поддерживали только определенные выпуски SQL Server. См. сведения о выпусках и поддерживаемых функциях SQL Server 2016, 2017 и 2019 (15.x).

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

База данных SQL Azure поддерживает автоматическое использование индексированного представления без указания NOEXPAND.

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

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

Чтобы оптимизатор запросов использовал индекс для индексированного представления, определите параметр NOEXPAND. Это указание можно использовать только в случае, если представление также названо в запросе. В SQL Server нет указания для принудительного использования определенного индексированного представления в запросе, в котором представление явно не названо в предложении FROM. При этом оптимизатор запросов может использовать индексированные представления, даже если запрос не обращается к ним напрямую. С помощью Компонент SQL Server Database Engine в индексированном представлении автоматически создается статистика только при использовании табличного указания NOEXPAND. Пропуск этой подсказки может привести к предупреждениям об отсутствующей статистике, которые невозможно разрешить, создав статистику вручную. Во время оптимизации запроса Компонент Database Engine будет использовать статистику представления, созданную автоматически или вручную, когда запрос напрямую ссылается на представление и используется указание NOEXPAND.

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

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

Разрешения

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

Примеры

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

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

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

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

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

Источник