NOCOUNT EINSTELLEN

Ich musste lange nach NOCOUNT suchen, um echte Benchmark-Werte zu finden. Ich dachte mir, ich würde eine kurze Zusammenfassung geben.

• Wenn Ihre gespeicherte Prozedur einen Cursor verwendet, um viele sehr schnelle Vorgänge ohne zurückgegebene Ergebnisse auszuführen, kann es für NOCOUNT OFF etwa zehnmal so lange dauern, bis sie eingeschaltet ist. ¹ Dies ist der schlimmste Fall.
• Wenn Ihre gespeicherte Prozedur nur eine einzige schnelle Operation ohne zurückgegebene Ergebnisse ausführt, führt die Einstellung von NOCOUNT ON zu einer Leistungssteigerung von 3%. ² Dies entspricht einer typischen Einfügungs- oder Aktualisierungsprozedur.
• Wenn Ihre gespeicherte Prozedur Ergebnisse zurückgibt (d. H., Sie wählen etwas aus), nimmt der Leistungsunterschied mit der Größe der Ergebnismenge ab.

• Wenn SET NOCOUNT auf ON gesetzt ist, wird die Anzahl (die die Anzahl der von einer Transact-SQL-Anweisung betroffenen Zeilen angibt) nicht zurückgegeben. Wenn SET NOCOUNT auf OFF gesetzt ist, wird der Zählerstand zurückgegeben. Sie wird mit jeder SELECT-, INSERT-, UPDATE- und DELETE-Anweisung verwendet.

• Die Einstellung von SET NOCOUNT wird zur Ausführungs- oder Laufzeit und nicht zur Analysezeit festgelegt.

• SET NOCOUNT ON verbessert die Leistung von gespeicherten Prozeduren (SP).

• Syntax: SET NOCOUNT {ON | AUS }

Beispiel für SET NOCOUNT ON:

Beispiel für SET NOCOUNT OFF:

Ich denke, zu einem gewissen Grad handelt es sich hierbei um ein Problem der DBA vs. Entwickler. 

Als Entwickler meistens würde ich sagen, verwenden Sie es nicht, es sei denn, Sie müssen es absolut positiv tun - weil die Verwendung Ihres ADO.NET-Codes (wie von Microsoft dokumentiert) beschädigt werden kann. 

Und ich glaube, als DBA sind Sie eher auf der anderen Seite - verwenden Sie ihn wann immer möglich, es sei denn, Sie müssen die Verwendung wirklich verhindern.

Wenn Ihre Entwickler jemals "RecordsAffected" verwenden, das vom ADO.NET-Methodenaufruf ExecuteNonQuery zurückgegeben wird, haben Sie Probleme, wenn alle SET NOCOUNT ON verwenden, da in diesem Fall ExecuteNonQuery immer 0 zurückgibt.

Siehe auch Peter Brombergs Blogbeitrag und prüft seine Position.

Es kommt also darauf an, wer die Maßstäbe setzt :-) 

Marc

Wenn Sie sagen, dass Sie möglicherweise auch andere Clients haben, gibt es Probleme mit dem klassischen ADO, wenn SET NOCOUNT nicht auf ON gesetzt ist. 

Eine Erfahrung, die ich regelmäßig erlebe: Wenn eine gespeicherte Prozedur eine Reihe von Anweisungen ausführt (und somit eine Anzahl von "xxx Zeilen betroffen" -Meldungen zurückgegeben werden), scheint ADO dies nicht zu handhaben und wirft den Fehler "Kann die ActiveConnection-Eigenschaft eines Recordset-Objekts, dessen Quelle ein Command-Objekt ist. "

Ich befürworte es in der Regel, es auf ON zu setzen, es sei denn, es gibt einen wirklich wirklich guten Grund, dies nicht zu tun. Sie haben vielleicht den wirklich wirklich guten Grund gefunden, den ich in mehr lesen muss.

Auf die Gefahr hin, die Dinge komplizierter zu gestalten, empfehle ich eine etwas andere Regel als alle, die ich oben sehe: 

• Setzen Sie immer NOCOUNT ON an der Spitze einer proc, bevor Sie in der proc arbeiten, aber also immer SET NOCOUNT OFF, bevor Sie Datensätze aus der gespeicherten proc zurückgeben. 

Also "nocount im Allgemeinen beibehalten, außer wenn Sie tatsächlich eine Ergebnismenge zurückgeben". Ich kenne keine Möglichkeiten, wie dies jeden Client-Code beschädigen kann. Dies bedeutet, dass der Client-Code nie etwas über die internen Proc-Werte der Proc wissen muss, und es ist nicht besonders aufwendig.

In Bezug auf die Auslöser, die NHibernate brachen, hatte ich diese Erfahrung aus erster Hand. Grundsätzlich erwartet NH, wenn ein UPDATE ausgeführt wird, eine bestimmte Anzahl betroffener Zeilen. Durch Hinzufügen von SET NOCOUNT ON zu den Triggern erhalten Sie die Anzahl der Zeilen wieder auf das, was NH erwartet hat, wodurch das Problem behoben wird. Ja, ich würde es definitiv empfehlen, es für Trigger auszuschalten, wenn Sie NH verwenden. 

Bei der Verwendung in SPs ist es eine Frage der persönlichen Vorlieben. Ich hatte immer die Reihenzählung deaktiviert, aber andererseits gibt es keine wirklichen Argumente.

Anders gesagt, sollten Sie wirklich in Erwägung ziehen, sich von der SP-basierten Architektur zu entfernen, dann werden Sie diese Frage nicht einmal haben.

SET NOCOUNT ON;

Diese Codezeile wird in SQL verwendet, um die Anzahl der Zeilen, die bei der Ausführung der Abfrage betroffen sind, nicht zurückzugeben. Wenn wir nicht die Anzahl der betroffenen Zeilen benötigen, können wir dies verwenden, da dies zur Einsparung von Speichernutzung und zur Erhöhung der Ausführungsgeschwindigkeit der Abfrage beitragen würde.

Ich wollte mich vergewissern, dass 'SET NOCOUNT ON' weder ein Netzwerkpaket noch einen Roundtrip speichert

Ich habe einen Test-SQLServer 2017 auf einem anderen Host verwendet (ich habe eine VM verwendet) 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 Dann verfolgte ich Pakete auf Port 1433 mit dem Tool ' Wireshark ': ' Capture-Filter'-Schaltfläche -> 'Port 1433'

exec procNoCount

dies ist das Antwortpaket: 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

dies ist das Antwortpaket: 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

In Zeile 40 sehe ich "07", die Anzahl der betroffenen "Zeilen" .__, die im Antwortpaket enthalten ist. Kein zusätzliches Paket.

Es hat jedoch 13 zusätzliche Bytes, die gespeichert werden könnten, aber es lohnt sich wahrscheinlich nicht mehr, als die Spaltennamen zu reduzieren (z. B. 'ManagingDepartment' auf 'MD').

Ich sehe also keinen Grund, es für die Leistung zu verwenden

ABER Wie bereits erwähnt, kann es ADO.NET Brechen, und ich bin mit Python auf ein Problem gestoßen: MSSQL2008 - Pyodbc - Vorheriges SQL war keine Abfrage

Also wohl noch eine gute Angewohnheit ...

SET NOCOUNT ON; Der obige Code stoppt die von der SQL Server Engine generierte Nachricht im Ergebnisfenster nach der Ausführung des DML/DDL-Befehls.

Warum machen wir das? Da die SQL Server Engine einige Ressourcen benötigt, um den Status abzurufen und die Nachricht zu generieren, wird dies als Überlastung der SQL Server Engine angesehen.

Ich kann nicht wissen, wie SET NOCOUNT ON zwischen Client und SQL getestet wird. Daher habe ich ein ähnliches Verhalten für den SET-Befehl "SET TRANSACTION ISOLATION LEVEL READ UNCIMMITTED" getestet.

Ich habe einen Befehl von meiner Verbindung gesendet, der das Standardverhalten von SQL geändert hat (READ COMMITTED), und der Befehl wurde für die nächsten Befehle geändert .. Wenn ich die ISOLATION-Stufe in einer gespeicherten Prozedur änderte, wurde das Verbindungsverhalten nicht geändert der nächste Befehl.

Aktuelle Schlussfolgerung

1. Das Ändern von Einstellungen in einer gespeicherten Prozedur ändert die Standardeinstellungen der Verbindung nicht.
2. Das Ändern der Einstellung durch Senden von Befehlen mithilfe von ADOCOnnection ändert das Standardverhalten.

Ich denke, das ist relevant für andere SET-Befehle wie "SET NOCOUNT ON".

Ich weiß, es ist eine ziemlich alte Frage. aber nur zum Update.

Die beste Möglichkeit, "SET NOCOUNT ON" zu verwenden, ist, sie als erste Anweisung in SP abzulegen und unmittelbar vor der letzten SELECT-Anweisung wieder auf OFF zu setzen.

Ein Ort, an dem SET NOCOUNT ON wirklich helfen kann, ist der Ort, an dem Sie Abfragen in einer Schleife oder einem Cursor ausführen. Dies kann zu viel Netzwerkverkehr führen.

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

Wenn Sie die Client-Statistik in SSMS aktivieren, zeigt eine Ausführung von EXEC NoCountOn und EXEC NoCountOff, dass auf dem NoCountOff-Server ein zusätzlicher Datenverkehr von 390 KB vorhanden war:

Wahrscheinlich nicht ideal, um Abfragen in einer Schleife oder einem Cursor durchzuführen, aber wir leben auch nicht in einer idealen Welt :)

if (keine Zählung setzen == aus)

{ dann werden Daten über die Anzahl der betroffenen Datensätze gespeichert} }

Manchmal können auch die einfachsten Dinge einen Unterschied machen. Eines dieser einfachen Elemente, die Bestandteil jeder gespeicherten Prozedur sein sollten, ist SET NOCOUNT ON. Diese eine Codezeile, die sich am Anfang einer gespeicherten Prozedur befindet, deaktiviert die Nachrichten, die SQL Server an den Client sendet, nachdem jede T-SQL-Anweisung ausgeführt wurde. Dies wird für alle SELECT-, INSERT-, UPDATE- und DELETE-Anweisungen ausgeführt. Diese Informationen sind praktisch, wenn Sie eine T-SQL-Anweisung in einem Abfragefenster ausführen. Wenn jedoch gespeicherte Prozeduren ausgeführt werden, müssen diese Informationen nicht an den Client zurückgegeben werden.

Durch das Entfernen dieses zusätzlichen Overheads aus dem Netzwerk kann die Gesamtleistung Ihrer Datenbank und Anwendung erheblich verbessert werden.

Wenn Sie immer noch die Anzahl der Zeilen benötigen, die von der ausgeführten T-SQL-Anweisung betroffen sind, können Sie weiterhin die Option @@ROWCOUNT verwenden. Durch die Ausgabe eines SET NOCOUNT ON funktioniert diese Funktion (@@ROWCOUNT) weiterhin und kann in Ihren gespeicherten Prozeduren immer noch verwendet werden, um festzustellen, wie viele Zeilen von der Anweisung betroffen waren.