SET NOCOUNT ON использование

Вдохновленный этим вопросом, где существуют разные взгляды на SET NOCOUNT ...

Должны ли мы использовать SET NOCOUNT ON для SQL Server? Если нет, то почему нет?

Что это делает Править 6, 22 июля 2011

Он подавляет сообщение «затронутые xx строки» после любого DML. Это набор результатов, и при отправке клиент должен его обработать. Это крошечный, но измеримый (см. Ответы ниже)

Для триггеров и т. Д. Клиент получит несколько «затронутых хх строк», и это вызывает всевозможные ошибки для некоторых ORM, MS Access, JPA и т. Д. (См. Правки ниже)

Фон:

Общепринятой практикой (я думал до этого вопроса) является использование SET NOCOUNT ONв триггерах и хранимых процедурах в SQL Server. Мы используем его повсюду, и быстрый гугл показывает, что многие MVP SQL Server тоже согласны.

MSDN говорит, что это может сломать .net SQLDataAdapter .

Теперь, для меня это означает, что SQLDataAdapter ограничен исключительно простой обработкой CRUD, потому что он ожидает, что сообщение «затронет n строк» ​​совпадает. Итак, я не могу использовать:

  • ЕСЛИ СУЩЕСТВУЕТ, чтобы избежать дубликатов (сообщение не затрагивает строки) Примечание: используйте с осторожностью
  • ГДЕ НЕ СУЩЕСТВУЕТ (меньше строк, чем ожидалось
  • Отфильтруйте тривиальные обновления (например, данные не изменяются)
  • Сделайте любой доступ к таблице раньше (например, ведение журнала)
  • Скрыть сложность или денормализация
  • так далее

В вопросе marc_s (кто знает его SQL) говорит, не используйте его. Это отличается от того, что я думаю (и я тоже считаю себя достаточно компетентным в SQL).

Возможно, я что-то упускаю (не стесняйтесь указывать на очевидное), но что вы, ребята, там думаете?

Примечание: прошло много лет с тех пор, как я видел эту ошибку, потому что в настоящее время я не использую SQLDataAdapter.

Редактирует после комментариев и вопросов:

Изменить: больше мыслей ...

У нас есть несколько клиентов: один может использовать C # SQLDataAdaptor, другой может использовать nHibernate из Java. На них можно по-разному влиять SET NOCOUNT ON.

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

Редактировать 2: триггер, ломающий вопрос nHibernate , где SET NOCOUNT ONне может быть установлен

(и нет, это не дубликат этого )

Редактировать 3: Еще больше информации, благодаря моему коллеге MVP

Изменить 4: 13 мая 2011

Ломает Linq 2 SQL тоже когда не указано?

Изменить 5: 14 июня 2011

Разбивает JPA, сохраненный процесс с табличными переменными: поддерживает ли JPA 2.0 табличные переменные SQL Server?

Изменить 6: 15 августа 2011

Сетка данных «Редактировать строки» SSMS требует SET NOCOUNT ON: Обновить триггер с помощью GROUP BY

Изменить 7: 07 марта 2013

Более подробные сведения от @RemusRusanu:
действительно ли SET NOCOUNT ON так сильно влияет на производительность

27.09.2009 14:52:29
@AlexKuznetsov: Каким будет подход «Threadsafe»? Конечно, чтения, выполненные в СУЩЕСТВУЮЩЕМ, все равно будут включены какие-либо невыполненные транзакции?
AnthonyWJones 14.10.2009 13:53:49
Команда SET NOCOUNT влияет только на отображение сообщения «(# recordable затронуты)», но не на базовое значение @@ ROWCOUNT или на сам механизм базы данных. ЕСЛИ СУЩЕСТВУЕТ, А КОГДА НЕ СУЩЕСТВУЕТ, будет работать независимо.
Jeremy S 27.10.2009 23:50:02
@ Джереми Сеги: извините за поздний ответ. Сообщение (затронутое #rows) - это инструмент клиента, интерпретируемый SSMS и т. Д., Однако есть пакет, отправленный с этой информацией. Конечно, я знаю, как работает @@ rowcount и т. Д., Но это не
gbn 12.03.2010 20:21:29
Не беспокойся. Лично я согласен с вашей точкой зрения; Я только что прокомментировал, что нет прямой корреляции между результатами конструкции IF / WHERE EXISTS и SET NOCOUNT. Я получаю последовательные результаты от этих конструкций независимо от NOCOUNT. Если у вас есть что-то, что говорит иначе, пожалуйста, отправьте это мне.
Jeremy S 12.03.2010 20:41:30
@ Джереми Сеги: вы правы: SET NOCOUNT ON только подавляет дополнительный пакет данных обратно клиенту. ЕСЛИ @@ ROWCOUNT и т. Д. Все это не затронуто. О, и это ломает SQLDataAdapters ... :-)
gbn 12.03.2010 20:44:24
15 ОТВЕТОВ
РЕШЕНИЕ

Хорошо, теперь я сделал свое исследование, вот сделка:

В протоколе TDS SET NOCOUNT ONсохраняет только 9 байтов на запрос, а сам текст «SET NOCOUNT ON» занимает целых 14 байтов. Раньше я думал, что это 123 row(s) affectedбыло возвращено с сервера в виде простого текста в отдельном сетевом пакете, но это не так. На самом деле это небольшая структура, называемая DONE_IN_PROCвстроенной в ответ. Это не отдельный сетевой пакет, поэтому никакие обходные пути не теряются.

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

Вот очень подробный анализ незначительности SET NOCOUNTнастройки: http://daleburnett.com/2014/01/everything-ever-wanted-know-set-nocount/

242
19.09.2018 21:24:02
Верно. Я использовал SET NOCOUNT ON всегда, но marc_s указал на ограничение SQLDataAdapter в другом вопросе.
gbn 10.10.2009 12:26:34
Спасибо. Байты или размер для меня не проблема, но клиент должен их обработать. Это - зависимость SQLDataAdapter, которая все еще изумляет меня хотя ...
gbn 11.10.2009 06:38:06
Спасибо за Ваш ответ. Я приму это из-за ваших расследований, которые вызвали больше информации и работы от меня. Я не согласен на накладные расходы, хотя: это может иметь значение, как показывают другие ответы. Ура, ГБН
gbn 16.10.2009 15:43:45
Это не количество байтов, это задержка
racingsnail 9.07.2013 20:20:45
В потоке сообщений TDS, например, когда в таблицу вставляются только значения. DONINPROC(RPC) или DONEсообщение (ПАРТИЯ) текутся с rowcountнабором затрагиваемых строк, в то время как done_countфлаг true, независимо от того , NO_COUNTнаходится ON. Зависит от реализации клиентской библиотеки в тех случаях, когда запрос содержит операторы SELECT или вызовы RPC, которые действительно выбирают, может потребоваться отключить подсчет ... Когда отключено, строки по-прежнему подсчитываются для оператора выбора, но флаг DONE_COUNTустановлен в false. Всегда читайте то, что предлагает ваша клиентская библиотека, поскольку она будет интерпретировать поток токенов (сообщений) вместо вас
Milan Jaric 31.07.2019 10:34:22

Я полагаю, что в какой-то степени это проблема DBA против разработчика.

Как разработчик в основном, я бы сказал, что не используйте его, если вы абсолютно не обязаны это делать - потому что его использование может нарушить ваш код ADO.NET (как задокументировано Microsoft).

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

Кроме того, если ваши разработчики когда-либо используют «RecordsActed», возвращаемое ExecuteNonQueryвызовом метода ADO.NET , у вас будут проблемы, если все будут использовать, SET NOCOUNT ONпоскольку в этом случае ExecuteNonQuery всегда будет возвращать 0.

Также посмотрите сообщение в блоге Питера Бромберга и проверьте его позицию.

Так что это действительно сводится к тому, кто устанавливает стандарты :-)

Марк

33
27.09.2009 14:56:24
Он говорит о простом CRUD: сетка данных, о которой он упоминает, может использовать xml для отправки нескольких строк, чтобы избежать
gbn 27.09.2009 15:00:57
Я предполагаю, что если вы никогда не используете SqlDataAdapters, и вы никогда не проверяете и не полагаетесь на число «затронутых записей», возвращаемое ExecuteNonQuery (например, если вы используете что-то вроде Linq-to-SQL или NHibernate), то у вас, вероятно, нет проблем используя SET NOCOUNT ON во всех сохраненных процессах.
marc_s 27.09.2009 15:09:20

Что касается триггеров, нарушающих NHibernate, у меня был этот опыт из первых рук. По сути, когда NH делает UPDATE, он ожидает, что затронуто определенное количество строк. Добавляя SET NOCOUNT ON к триггерам, вы возвращаете количество строк обратно к ожидаемому NH, тем самым устраняя проблему. Так что да, я бы определенно рекомендовал отключить его для триггеров, если вы используете NH.

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

С другой стороны, вам стоит подумать об отходе от архитектуры на основе SP, тогда у вас даже не возникнет этот вопрос.

5
11.10.2009 22:37:01
Я не согласен с уходом от хранимых процедур. Это означало бы, что у нас должен быть один и тот же SQL в двух разных клиентских кодах и доверять нашим клиентским кодировщикам. Мы разработчики БД. И ты не имеешь в виду "SET NOCOUNT ON "?
gbn 10.10.2009 12:57:50
@CodeBlend: просто Google это больше, чем вам когда-либо нужно. Однако ... stackoverflow.com/a/4040466/27535
gbn 7.01.2013 14:27:18

Если вы говорите, что у вас могут быть и другие клиенты, есть проблемы с классическим ADO, если для SET NOCOUNT не установлено значение ON.

Одна проблема, с которой я сталкиваюсь регулярно: если хранимая процедура выполняет несколько операторов (и, таким образом, возвращается число сообщений «затронуты строки xxx»), ADO, похоже, не справляется с этим и выдает ошибку «Невозможно изменить свойство ActiveConnection объекта Recordset» который имеет объект Command в качестве источника. "

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

12
13.10.2009 15:52:00

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

  • Если ваша хранимая процедура использует курсор для выполнения множества очень быстрых операций без возвращаемых результатов, отключение NOCOUNT может занять примерно в 10 раз больше времени, чем его включение. 1 Это худший вариант развития событий.
  • Если ваша хранимая процедура выполняет только одну быструю операцию без возвращаемых результатов, установка NOCOUNT ON может привести к увеличению производительности примерно на 3%. 2 Это будет соответствовать обычной процедуре вставки или обновления. (См. Комментарии к этому ответу для обсуждения того, почему это не всегда может быть быстрее.)
  • Если ваша хранимая процедура возвращает результаты (т. Е. Вы выбираете что-то), разница в производительности будет уменьшаться пропорционально размеру набора результатов.
86
2.08.2019 17:06:48
+1 за воздействие на курсор, это согласуется с моими наблюдениями
zvolkov 2.05.2011 15:52:23
Пункт 2 не является точным! Я имею в виду блог, на который он ссылается. Никогда не была! DONE и DONEPROC и DONEINPROC отправляются с одинаковым размером независимо от того, установлено ли NO_COUNT на ON или OFF. RowCount все еще там, как ULONGLONG (64 байта), и флаг DONE_COUNT все еще там, но значение бита равно 0. SQL-сервер будет подсчитывать количество строк в любом случае, даже если вам не интересно читать значение из токена DONE. Если вы читаете @@ ROWCOUNT, вы добавляете больше байтов в поток токенов либо в виде токена с возвращаемым значением, либо в виде еще одного колметаданных + токенов строк!
Milan Jaric 1.08.2019 10:02:44
@MilanJaric: Спасибо за это. Вы помогли мне понять, что я связал не ту статью. Ссылка теперь обновляется, и в статье приводятся убедительные аргументы, свидетельствующие о том, что при установке SET NOCOUNT ON можно слегка повысить производительность. Как вы думаете, есть проблемы с используемыми методами?
StriplingWarrior 1.08.2019 17:09:17
:) все еще неточно в отношении SET NOCOUNT OFF / ON, ошибка в том, что второго SP нет, SET NOCOUNT OFF;и поэтому они думают, что не получают дополнительные байты в ответ. Точный тест будет использоваться SET NOCOUNT ONв левой и SET NOCOUNT OFFправой хранимой процедуре. Таким образом, вы получите пакет TDS с DONEINPROC (SET NOCOUNT ...), снова десять DONEINPROC (INSERT statement), а затем RETURNVALUE(@@ROWCOUNT), затем RETURNSTATUS 0для sp и окончательно DONPROC. Ошибка есть, потому что второй sp не имеет SET NOCOUNT OFF в теле!
Milan Jaric 2.08.2019 16:18:05
Перефразируя то, что они нашли, но не поняли, так это то, что если у вас есть 1K запросов на выборку курсора, сначала сделайте один запрос, чтобы установить для NOCOUNT значение ON или OFF для соединения, а затем используйте то же соединение, чтобы вызвать выборку курсора 1K раз, чтобы сэкономить пропускную способность. Фактическое состояние соединения для NOCOUNT ON или OFF не повлияет на пропускную способность, это может просто запутать клиентскую библиотеку, например ADO.net или ODBC. Так что «не используйте SET NOCOUNT <WHATEVER>, если вы заботитесь о пропускной способности» :)
Milan Jaric 2.08.2019 16:22:24

Рискуя усложнить ситуацию, я призываю немного другое правило ко всем тем, что я вижу выше:

  • Всегда устанавливайте NOCOUNT ONв верхней части процедуры, прежде чем выполнять какую-либо работу в процедуре, но также всегда SET NOCOUNT OFFснова, прежде чем возвращать какие-либо наборы записей из сохраненного процесса.

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

9
22.07.2011 11:03:56
Спасибо. Конечно, вы можете получить количество строк из DataSet или потребляющего контейнера, но это может быть полезно. У нас не может быть триггеров на SELECT, поэтому это было бы безопасно: большинство ошибок клиента вызваны ложными сообщениями об изменениях данных.
gbn 22.07.2011 11:07:04
Проблема с этим правилом состоит в том, что его сложнее проверить, чем «SET NOCOUNT ON в верхней части процедуры?»; Интересно, могут ли такие инструменты анализа SQL, как Sql Enlight, проверять подобные вещи ... Добавление его в мой долгосрочный список задач для моего проекта форматера SQL :)
Tao 22.07.2011 11:20:18

Я не знаю, как проверить SET NOCOUNT ON между клиентом и SQL, поэтому я протестировал похожее поведение для другой команды SET "SET TRANSACTION ISOLATION LEVEL READ UNCIMMITTED"

Я отправил команду из моего соединения, меняющую поведение SQL по умолчанию (READ COMMITTED), и она была изменена для следующих команд. Когда я изменил уровень ISOLATION внутри хранимой процедуры, он не изменил поведение соединения для следующей команды.

Текущее заключение,

  1. Изменение настроек внутри хранимой процедуры не меняет настройки соединения по умолчанию.
  2. Изменение настроек путем отправки команд с использованием ADOCOnnection меняет поведение по умолчанию.

Я думаю, что это относится к другим командам SET, таким как "SET NOCOUNT ON"

-1
17.05.2012 06:51:32
Означает ли ваш пункт 1 выше, что вам действительно не нужно отключать SET NOCOUNT в конце, потому что это не влияет на глобальную среду?
funkymushroom 21.03.2014 15:25:47
Я не уверен, что это то, что он имел в виду под пунктом 1, но в моих тестах да, очевидно, что глобальная среда не затрагивается SET NOCOUNT ON внутри хранимой процедуры.
Doug 28.01.2016 19:35:25
Это был плохой выбор для сравнения, поскольку уровень изоляции явно связан с конкретной транзакцией, поэтому нет особой причины ожидать, что он будет соответствовать такой настройке, какNOCOUNT
IMSoP 23.03.2020 09:54:58
SET NOCOUNT ON;

Эта строка кода используется в SQL, чтобы не возвращать количество строк, затронутых при выполнении запроса. Если нам не требуется количество затронутых строк, мы можем использовать это, так как это поможет сэкономить память и увеличить скорость выполнения запроса.

1
17.05.2012 09:01:40
Обратите внимание, что @@ ROWCOUNT все еще установлен. SET NOCOUNT ON подавляет любой дополнительный ответ, который SQL Server отправляет клиенту. Смотрите принятый ответ выше, пожалуйста
gbn 17.05.2012 06:51:18
  • Когда параметр SET NOCOUNT включен, счетчик (указывающий на количество строк, затронутых оператором Transact-SQL) не возвращается. Когда SET NOCOUNT выключен, счет возвращается. Он используется с любым оператором SELECT, INSERT, UPDATE, DELETE.

  • Настройка SET NOCOUNT устанавливается во время выполнения или выполнения, а не во время анализа.

  • SET NOCOUNT ON улучшает производительность хранимых процедур (SP).

  • Синтаксис: SET NOCOUNT {ON | OFF}

Пример SET NOCOUNT ON:

введите описание изображения здесь

Пример SET NOCOUNT OFF:

введите описание изображения здесь

76
14.10.2012 16:20:26
Легко и быстро понять со скриншотами. Отличная работа. :)
shaijut 15.08.2018 14:07:54

если (не задавать счет == выкл)

{тогда он сохранит данные о количестве затронутых записей, поэтому снизит производительность} иначе {он не будет отслеживать запись изменений, следовательно, улучшит производительность}}

-1
8.12.2015 11:26:01

Я знаю, что это довольно старый вопрос. но только для обновления.

Лучший способ использовать «SET NOCOUNT ON» - это поместить его в качестве первого оператора в SP и снова установить OFF перед самым последним оператором SELECT.

0
5.08.2016 11:30:24

ВКЛЮЧИТЬ СЧЕТ; Приведенный выше код остановит сообщение, сгенерированное механизмом sql server, в появившемся окне результатов после выполнения команды DML / DDL.

Почему мы это делаем? Поскольку движок SQL-сервера требует некоторого ресурса для получения статуса и генерации сообщения, это рассматривается как перегрузка для движка Sql-сервера. Поэтому мы устанавливаем сообщение без учета.

1
18.04.2017 17:34:56

Иногда даже самые простые вещи могут иметь значение. Одним из таких простых элементов, которые должны быть частью каждой хранимой процедуры, является SET NOCOUNT ON. Эта строка кода, расположенная в верхней части хранимой процедуры, отключает сообщения, которые SQL Server отправляет обратно клиенту после выполнения каждого оператора T-SQL. Это выполняется для всех SELECT, INSERT, UPDATEи DELETEзаявления. Наличие этой информации удобно, когда вы запускаете инструкцию T-SQL в окне запроса, но когда выполняются хранимые процедуры, нет необходимости передавать эту информацию клиенту.

Удаление этих дополнительных служебных данных из сети может значительно повысить общую производительность вашей базы данных и приложения.

Если вам все еще нужно получить количество строк, на которые влияет оператор T-SQL, который выполняется, вы все равно можете использовать эту @@ROWCOUNTопцию. Выпуск SET NOCOUNT ONфункции this ( @@ROWCOUNT) все еще работает и может использоваться в ваших хранимых процедурах для определения количества строк, затронутых оператором.

-1
27.12.2018 04:55:18

Я хотел убедиться, что «SET NOCOUNT ON» не сохраняет ни сетевой пакет, ни передачу туда и обратно

Я использовал тест SQLServer 2017 на другом хосте (я использовал виртуальную машину) create table ttable1 (n int); insert into ttable1 values (1),(2),(3),(4),(5),(6),(7) go create procedure procNoCount as begin set nocount on update ttable1 set n=10-n end create procedure procNormal as begin update ttable1 set n=10-n end Затем я отслеживал пакеты на порту 1433 с помощью инструмента «Wireshark»: кнопка «фильтр захвата» -> «порт 1433»

exec procNoCount

это ответный пакет: 0000 00 50 56 c0 00 08 00 0c 29 31 3f 75 08 00 45 00 0010 00 42 d0 ce 40 00 40 06 84 0d c0 a8 32 88 c0 a8 0020 32 01 05 99 fe a5 91 49 e5 9c be fb 85 01 50 18 0030 02 b4 e6 0e 00 00 04 01 00 1a 00 35 01 00 79 00 0040 00 00 00 fe 00 00 e0 00 00 00 00 00 00 00 00 00

exec procNormal

это ответный пакет: 0000 00 50 56 c0 00 08 00 0c 29 31 3f 75 08 00 45 00 0010 00 4f d0 ea 40 00 40 06 83 e4 c0 a8 32 88 c0 a8 0020 32 01 05 99 fe a5 91 49 e8 b1 be fb 8a 35 50 18 0030 03 02 e6 1b 00 00 04 01 00 27 00 35 01 00 ff 11 0040 00 c5 00 07 00 00 00 00 00 00 00 79 00 00 00 00 0050 fe 00 00 e0 00 00 00 00 00 00 00 00 00

В строке 40 я вижу «07», это число «затронутых строк». Он включен в ответный пакет. Никакого дополнительного пакета.

Однако он имеет 13 дополнительных байтов, которые можно сохранить, но, вероятно, не стоит того, чтобы сократить имена столбцов (например, «ManagingDepartment» до «MD»)

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

НО, как уже упоминалось, это может сломать ADO.NET, и я также наткнулся на проблему с использованием Python: MSSQL2008 - Pyodbc - Предыдущий SQL не был запросом

Так что, наверное, хорошая привычка еще ...

3
1.03.2019 21:23:08

Одно место, которое SET NOCOUNT ONдействительно может помочь, - это когда вы делаете запросы в цикле или курсоре. Это может добавить много сетевого трафика.

CREATE PROCEDURE NoCountOn
AS
set nocount on
    DECLARE @num INT = 10000
    while @num > 0
    begin
       update MyTable SET SomeColumn=SomeColumn
       set @num = @num - 1
    end
GO


CREATE PROCEDURE NoCountOff
AS
set nocount off
    DECLARE @num INT = 10000
    while @num > 0
    begin
       update MyTable SET SomeColumn=SomeColumn
       set @num = @num - 1
    end
GO

Включение клиентской статистики в SSMS, прогон EXEC NoCountOnи EXEC NoCountOffпоказывает, что на NoCountOff был дополнительный трафик 390 КБ:

статистика клиентов

Вероятно, не идеально, чтобы делать запросы в цикле или курсоре, но мы не живем и в идеальном мире :)

1
10.04.2019 14:30:56