Set nocount on что это

SQL-Ex blog

На прошлой неделе я пересматривал статью и обнаружил, что мне нужна инфомация об использовании NOCOUNT в качестве стандартного средства при написании хранимых процедур. Быстрый поиск в интернете нашел мою старую статью. Видимо, меня спросили, можно ли включить NOCOUNT для конкретной базы данных SQL Server. Как предполагалось в статье, это не было возможным. Опция NOCOUNT устанавливается для всего экземпляра, для конкретного подключения или в коде T-SQL.

Поскольку эта статья была написана задолго до появления новейшего оператора ALTER DATABASE SCOPED CONFIGURATION, я понадеялся, что теперь стало возможным включить NOCOUNT для базы данных. Оказалось, что нет, т.к. перечисленные здесь допустимые опции не включают NOCOUNT. Печально.

Но, конечно, я все же попытался.

Но мне не удалось.

На самом деле не удалось.

Я попытался включить NOCOUNT для моего экземпляра SQL 2019, и никак. Вообще.

Провал

Используя код из моей предыдущей статьи, вы можете включить NOCOUNT для экземпляра, установив user option в 512:

EXEC sys.sp_configure 'user options','512' 
GO 
RECONFIGURE 
GO

Теперь откройте новое окно запроса в SQL Server Management Studio (SSMS), установите вывод результатов в тексте, чтобы проще их просматривать, и выполните запрос. Тогда вы увидите это:

Это не то поведение, которое ожидалось! Моей первой реакцией было, что я что-то не так сделал. Я решил попробовать в Azure Data Studio (ADS), подключившись и выполнив запрос:

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

И графический интерфейс SSMS также подтверждает это:

Поворот

Прежде чем двигаться дальше, я хочу отметить, что SET NOCOUNT OFF — одна из тех ужасных фраз, с которыми мы сталкиваемся в сфере технологий, когда наш мозг вынужден дважды думать о том, что мы делаем. Следует уволить того, кто так его назвал. Простое SET ROWRESULTS ON|OFF было бы значительно проще и понятней.

Как бы то ни было, я потратил время, пытаясь выяснить, что происходит. Я могу вручную включить и выключить NOCOUNT внутри T-SQL и увидеть число обработанных строк (или нет). Я проверял и перепроверял все, что приходило мне в голову, и чувствовал себя потерявшим рассудок. Я начал спрашивать себя, как мне удалось стать сертифицированным специалистом в SQL Server.

Я имею в виду, что это простое изменение конфигурации. Это не ракетная техника.

Поэтому я делаю то, что сделал бы любой другой в этой ситуации.

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

Река

В конце концов я решил снова открыть ноут попытаться опять. Я могу все воспроизвести. Поэтому я обратился к нескольким друзьям, наблюдали ли они также подобные проблемы. Один из них, Karen López (@datachick), задал мне несколько наводящих вопросов. Эти вопросы заставили меня подумать о других способах проверки. Я внезапно понял, что могу проверить опции, установленные в моем текущем подключении:

DECLARE @options INT 
SELECT @options = @@OPTIONS 
PRINT @options 
IF ( (1 & @options) = 1 ) PRINT 'DISABLE_DEF_CNST_CHK' 
IF ( (2 & @options) = 2 ) PRINT 'IMPLICIT_TRANSACTIONS' 
IF ( (4 & @options) = 4 ) PRINT 'CURSOR_CLOSE_ON_COMMIT' 
IF ( (8 & @options) = 8 ) PRINT 'ANSI_WARNINGS' 
IF ( (16 & @options) = 16 ) PRINT 'ANSI_PADDING' 
IF ( (32 & @options) = 32 ) PRINT 'ANSI_NULLS' 
IF ( (64 & @options) = 64 ) PRINT 'ARITHABORT' 
IF ( (128 & @options) = 128 ) PRINT 'ARITHIGNORE' 
IF ( (256 & @options) = 256 ) PRINT 'QUOTED_IDENTIFIER' 
IF ( (512 & @options) = 512 ) PRINT 'NOCOUNT' 
IF ( (1024 & @options) = 1024 ) PRINT 'ANSI_NULL_DFLT_ON' 
IF ( (2048 & @options) = 2048 ) PRINT 'ANSI_NULL_DFLT_OFF' 
IF ( (4096 & @options) = 4096 ) PRINT 'CONCAT_NULL_YIELDS_NULL' 
IF ( (8192 & @options) = 8192 ) PRINT 'NUMERIC_ROUNDABORT' 
IF ( (16384 & @options) = 16384 ) PRINT 'XACT_ABORT'

Выполнение вышеприведенного кода вернуло следующий результат для моего подключения:

И тут меня осенило. Мое подключение не имело включенного NOCOUNT! Я не хочу сказать, что был сильно удивлен, но это помогло увидеть, что отсутствует. Я решил открыть подключение с помощью SQLCMD и посмотреть на поведение по умолчанию для этого подключения. Разумеется, NOCOUNT включен, как и ожидалось:

Моя строка подключения не имела дополнительных опций, и поведение NOCOUNT соблюдается. Это ожидаемое поведение для экземпляра.
Теперь мне нужно проверить, что происходит под капотом при подключении к SQL Server с использованием SSMS или ADS. Используя сессию по умолчанию xEvents, я захватил строку подключения, переданную при подключении из ADS, и нашел эту драгоценность:

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

Пока оставлю свой сертификат.

Выводы

Я не знаю, баг это или фича. Но это несомненно раздражающее поведение для конечного пользователя, подобного мне. Но если я устанавливаю SET NOCOUNT для SQL server, я ожидаю, что она применяется для всех пользователей, подключающихся с этого момента. Поскольку поведение других опция кажется соответствующим, должно быть что-то не так с NOCOUNT.

Не важно, как подключаются пользователи. SSMS и ADS должны соответствовать настройкам сервера. Я подозреваю, что другие инструменты, вероятно, используют тот же код, что и SSMS и ADS, а это означает, что вам следует дважды проверить фактическую строку подключения, используемую в вашем приложении. Это могло бы объяснить неожиданное поведение.

Обратные ссылки

Нет обратных ссылок

Оптимизация хранимых процедур в SQL Server

Доброго дня, хабрачеловек. Сегодня я бы хотел обсудить с вами тему хранимых процедур в SQL Server 2000-2005. В последнее время их написание занимало львиную долю моего времени на работе и чего уж тут скрывать – по окончанию работы с этим делом осталось достаточно информации, которой с удовольствием поделюсь с тобой %пользовательимя%.
Знания, которыми я собираюсь поделиться, к сожалению,(или к счастью) не добыты мной эмперически, а являются, в большей степени, вольным переводом некоторых статей из буржуйских интернетов.
Итак, как можно понять из названия речь пойдет об оптимизации. Сразу оговорюсь, что все действия, которые я сейчас буду описывать, действительно дают существенный(некоторые больший, некоторые меньший) прирост производительности.
Данная статья не претендует на полное раскрытие темы оптимизации, скорее это собрание практик, которые я применяю в своей работе и могу ручаться за их эффективность. Поехали!

1. Включай в свои процедуры строку — SET NOCOUNT ON: С каждым DML выражением, SQL server заботливо возвращает нам сообщение содержащее колличество обработанных записей. Данная информация может быть нам полезна во время отладки кода, но после будет совершенно бесполезной. Прописывая SET NOCOUNT ON, мы отключаем эту функцию. Для хранимых процедур содержащих несколько выражений или\и циклы данное действие может дать значительный прирост производительности, потому как колличество трафика будет значительно снижено.

CREATE PROC dbo.ProcName
AS
SET NOCOUNT ON;
—Здесь код процедуры
SELECT column1 FROM dbo.TblTable1
—Перключение SET NOCOUNT в исходное состояние
SET NOCOUNT OFF;
GO

2. Используй имя схемы с именем объекта: Ну тут думаю понятно. Данная операция подсказывает серверу где искать объекты и вместо того чтобы беспорядочно шарится по своим закромам, он сразу будет знать куда ему нужно пойти и что взять. При большом колличестве баз, таблиц и хранимых процедур может значительно сэкономить наше время и нервы.

SELECT * FROM dbo.MyTable —Вот так делать хорошо
— Вместо
SELECT * FROM MyTable —А так делать плохо
—Вызов процедуры
EXEC dbo.MyProc —Опять же хорошо
—Вместо
EXEC MyProc —Плохо!

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

4. Используй IF EXISTS (SELECT 1) вместо IF EXISTS (SELECT *): Чтобы проверить наличие записи в другой таблице, мы используем выражение IF EXISTS. Данное выражение возвращает true если из внутреннего выражения возвращается хоть одно изначение, не важно «1», все колонки или таблица. Возращаемые данные, в принципе никак не используются. Таким образом для сжатия трафика во время передачи данных логичнее использовать «1», как показано ниже:

IF EXISTS (SELECT 1 FROM sysobjects
WHERE name = ‘MyTable’ AND type = ‘U’)

5. Используй TRY-Catch для отлова ошибок: До 2005 сервера после каждого запроса в процедуре писалось огромное колличество проверок на ошибки. Больше кода всегда потребляет больше ресурсов и больше времени. С 2005 SQL Server’ом появился более правильный и удобный способ решения этой проблемы:

BEGIN TRY
—код
END TRY
BEGIN CATCH
—код отлова ошибки
END CATCH

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

P.S.
Мой первый пост, не судите строго.

SET NOCOUNT ON statement usage and performance benefits in SQL Server

Include Client Statistics with SET NOCOUNT ON

Have you ever noticed SET NOCOUNT ON statement in T-SQL statements or stored procedures in SQL Server? I have seen developers not using this set statement due to not knowing it. In this article, we will explore why it is a good practice to use SET NOCOUNT ON with T-SQL statements. We will also learn the performance benefit you can get from it.

Introduction

Before we explore this statement, let’s create a sample table in the SQL Server database with the following script.

USE SQLShackDemo ;
CREATE TABLE tblEmployeeDemo
PRIMARY KEY ,
EmpName NVARCHAR ( 50 ) ,
Gender NVARCHAR ( 10 ) ,
Let’s insert a few records data in this table using the following script.
USE [ SQLShackDemo ]

INSERT [ dbo ] . [ tblEmployeeDemo ] ( [ Id ] , [ EmpName ] , [ Gender ] ) VALUES ( 1 , N ‘Grace’ , N ‘Female’ )

INSERT [ dbo ] . [ tblEmployeeDemo ] ( [ Id ] , [ EmpName ] , [ Gender ] ) VALUES ( 2 , N ‘Gordon’ , N ‘Male’ )

INSERT [ dbo ] . [ tblEmployeeDemo ] ( [ Id ] , [ EmpName ] , [ Gender ] ) VALUES ( 3 , N ‘Jaime’ , N ‘Female’ )

INSERT [ dbo ] . [ tblEmployeeDemo ] ( [ Id ] , [ EmpName ] , [ Gender ] ) VALUES ( 4 , N ‘Ruben’ , N ‘Female’ )

INSERT [ dbo ] . [ tblEmployeeDemo ] ( [ Id ] , [ EmpName ] , [ Gender ] ) VALUES ( 5 , N ‘Makayla’ , N ‘Male’ )

INSERT [ dbo ] . [ tblEmployeeDemo ] ( [ Id ] , [ EmpName ] , [ Gender ] ) VALUES ( 6 , N ‘Barry’ , N ‘Female’ )

INSERT [ dbo ] . [ tblEmployeeDemo ] ( [ Id ] , [ EmpName ] , [ Gender ] ) VALUES ( 7 , N ‘Ramon’ , N ‘Male’ )

INSERT [ dbo ] . [ tblEmployeeDemo ] ( [ Id ] , [ EmpName ] , [ Gender ] ) VALUES ( 8 , N ‘Douglas’ , N ‘Male’ )

INSERT [ dbo ] . [ tblEmployeeDemo ] ( [ Id ] , [ EmpName ] , [ Gender ] ) VALUES ( 9 , N ‘Julian’ , N ‘Female’ )

INSERT [ dbo ] . [ tblEmployeeDemo ] ( [ Id ] , [ EmpName ] , [ Gender ] ) VALUES ( 10 , N ‘Sarah’ , N ‘Female’ )

The output

Once you execute the command, you get the following message in SSMS. You get ‘1-row affected’ message for each row. Suppose you insert a million rows in the table and for each record, you get this message. As you can see, it is not a useful message and does not provide relevant information. Now, let’s select the records from this table.

FROM tblEmployeeDemo ;

Result tab shows the record from the table
The messages tab gives the number of rows affected message

Result and message tab in SSMS

Let’s create a stored procedure to get the records from the specified table.

CREATE PROCEDURE SP_tblEmployeeDemo
FROM tblEmployeeDemo ;

Execute this stored procedure, and you get a similar output in the following screenshot as well.

Exec SP_tblEmployeeDemo

Output of a stored procedure in the results and message tab

SET NOCOUNT ON/OFF statement controls the behavior in SQL Server to show the number of affected rows in the T-SQL query.

SET NOCOUNT OFF – By default, SQL Server shows the number of affected rows in the messages pane
SET NOCOUNT ON – We can specify this set statement at the beginning of the statement. Once we enable it, we do not get the number of affected rows in the output

Let’s try running the previous queries with NOCOUNT statement.

TRUNCATE TABLE tblEmployeeDemo ;
SET NOCOUNT ON
USE [ SQLShackDemo ]