SET NOCOUNT ON Verwendung

Question

Inspiriert durch diese Frage wo es unterschiedliche Ansichten über SET NOCOUNT gibt...

Sollten wir SET NOCOUNT ON für SQL Server verwenden? Wenn nicht, warum nicht?

Was es bewirkt Edit 6, am 22. Juli 2011

Es unterdrückt die Meldung "xx Zeilen betroffen" nach jeder DML. Dies ist eine Ergebnismenge, und wenn sie gesendet wird, muss der Client sie verarbeiten. Es ist winzig, aber messbar (siehe Antworten unten)

Bei Triggern usw. erhält der Client mehrere "xx betroffene Zeilen", was bei einigen ORMs, MS Access, JPA usw. alle möglichen Fehler verursacht (siehe nachstehende Änderungen).

Hintergrund:

Die allgemein anerkannte beste Praxis (dachte ich bis zu dieser Frage) ist die Verwendung von SET NOCOUNT ON in Triggern und gespeicherten Prozeduren in SQL Server. Wir verwenden es überall, und ein kurzer Blick auf Google zeigt, dass auch viele SQL Server MVPs damit einverstanden sind.

Laut MSDN kann dies eine .net SQLDataAdapter .

Nun bedeutet dies für mich, dass der SQLDataAdapter auf völlig einfache CRUD-Verarbeitung beschränkt ist, weil er erwartet, dass die Meldung "n Zeilen betroffen" übereinstimmt. Also, ich kann nicht verwenden:

• IF EXISTS, um Duplikate zu vermeiden (Meldung, dass keine Zeilen betroffen sind) Hinweis: mit Vorsicht zu verwenden
• WHERE NOT EXISTS (weniger Zeilen als erwartet)
• Herausfiltern trivialer Aktualisierungen (z. B. keine tatsächlichen Datenänderungen)
• Führen Sie vorher einen Tabellenzugriff durch (z. B. Protokollierung)
• Komplexität oder Denormlisierung ausblenden
• usw.

In der Frage sagt marc_s (der sich mit SQL auskennt), dass man es nicht verwenden soll. Dies unterscheidet sich von dem, was ich denke (und ich halte mich auch für einigermaßen kompetent in SQL).

Es ist möglich, dass ich etwas übersehe (bitte weisen Sie mich auf das Offensichtliche hin), aber was denken Sie da draußen?

Hinweis: Es ist Jahre her, dass ich diesen Fehler gesehen habe, da ich SQLDataAdapter heutzutage nicht mehr verwende.

Bearbeitet nach Kommentaren und Fragen:

Edit: Weitere Gedanken...

Wir haben mehrere Clients: ein Client kann einen C# SQLDataAdaptor verwenden, ein anderer nHibernate aus Java. Diese können auf unterschiedliche Weise beeinflusst werden mit SET NOCOUNT ON .

Wenn Sie gespeicherte Prozeduren als Methoden betrachten, dann ist es schlechtes Benehmen (Anti-Pattern), davon auszugehen, dass eine interne Verarbeitung auf eine bestimmte Weise für Ihre eigenen Zwecke funktioniert.

Bearbeiten 2: a Trigger, der nHibernate bricht Frage , wobei SET NOCOUNT ON kann nicht eingestellt werden

(und nein, es handelt sich nicht um ein Duplikat von diese )

Edit 3: Noch mehr Informationen, dank meines MVP-Kollegen

• KB 240882 Problem, das Verbindungsabbrüche bei SQL 2000 und früheren Versionen verursacht
• Demo der Leistungssteigerung

Bearbeiten 4: 13. Mai 2011

Bricht Linq 2 SQL auch, wenn nicht angegeben?

Bearbeiten 5: 14 Jun 2011

Bricht JPA, Stored Proc mit Tabellenvariablen: Unterstützt JPA 2.0 SQL Server-Tabellenvariablen?

Bearbeiten 6: 15 Aug 2011

Das SSMS-Datengitter "Zeilen bearbeiten" erfordert SET NOCOUNT ON: Aktualisierungs-Trigger mit GROUP BY

Bearbeiten 7: 07 Mär 2013

Ausführlichere Informationen von @RemusRusanu:
Macht SET NOCOUNT ON wirklich einen so großen Unterschied in der Leistung aus?

Answer 1

Ok, jetzt habe ich nachgeforscht, hier ist der Deal:

Im TDS-Protokoll, SET NOCOUNT ON spart nur 9 Bytes pro Abfrage während der Text "SET NOCOUNT ON" selbst satte 14 Bytes groß ist. Ich dachte immer, dass 123 row(s) affected vom Server im Klartext in einem separaten Netzwerkpaket zurückgegeben wurde, aber das ist nicht der Fall. Es handelt sich vielmehr um eine kleine Struktur namens DONE_IN_PROC eingebettet in die Antwort. Es handelt sich nicht um ein separates Netzwerkpaket, so dass keine Roundtrips verschwendet werden.

Ich denke, man kann das Standard-Zählverhalten fast immer beibehalten, ohne sich um die Leistung zu sorgen. Es gibt jedoch einige Fälle, in denen die Berechnung der Anzahl der Zeilen im Voraus die Leistung beeinträchtigen würde, z. B. bei einem reinen Vorwärtscursor. In diesem Fall könnte NOCOUNT eine Notwendigkeit sein. Ansonsten besteht absolut keine Notwendigkeit, dem Motto "NOCOUNT verwenden, wo immer es möglich ist" zu folgen.

Hier ist eine sehr detaillierte Analyse über die Bedeutungslosigkeit von SET NOCOUNT Umgebung: http://daleburnett.com/2014/01/everything-ever-wanted-know-set-nocount/

Answer 2

Es hat mich viel Mühe gekostet, echte Benchmark-Zahlen zu NOCOUNT zu finden, also dachte ich mir, ich gebe eine kurze Zusammenfassung.

• Wenn Ihre gespeicherte Prozedur einen Cursor verwendet, um viele sehr schnelle Operationen ohne zurückgegebene Ergebnisse durchzuführen, kann NOCOUNT OFF etwa 10 Mal so lange dauern wie NOCOUNT ON. 1 Dies ist das Worst-Case-Szenario.
• Wenn Ihre gespeicherte Prozedur nur eine einzige schnelle Operation ohne zurückgegebene Ergebnisse durchführt, ist die Einstellung NOCOUNT ON könnte eine Leistungssteigerung von etwa 3 %. 2 Dies entspräche einer typischen Einfüge- oder Aktualisierungsprozedur. (In den Kommentaren zu dieser Antwort finden Sie eine Diskussion darüber, warum dies nicht immer schneller ist).
• Wenn Ihre gespeicherte Prozedur Ergebnisse zurückgibt (d.h. Sie SELECT etwas), wird der Leistungsunterschied proportional mit der Größe der Ergebnismenge abnehmen.

Answer 3

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

• Die Einstellung von SET NOCOUNT wird zur Ausführungs- oder Laufzeit gesetzt und nicht zur Parse-Zeit.

• SET NOCOUNT ON verbessert die Leistung von Stored Procedures (SP).

• Syntax: SET NOCOUNT { ON | OFF }

Beispiel für SET NOCOUNT ON:

Beispiel für SET NOCOUNT OFF:

Answer 4

Ich denke, bis zu einem gewissen Grad ist es eine Frage von DBA und Entwickler.

Als Entwickler würde ich sagen, verwenden Sie es nicht, es sei denn, Sie unbedingt müssen - weil es Ihren ADO.NET-Code brechen kann (wie von Microsoft dokumentiert).

Und ich schätze, dass Sie als DBA eher auf der anderen Seite stehen - verwenden Sie es, wann immer es möglich ist, es sei denn, Sie müssen seine Verwendung wirklich verhindern.

Wenn Ihre Entwickler jemals die "RecordsAffected" verwenden, die von ADO.NET's ExecuteNonQuery Methode aufruft, haben Sie ein Problem, wenn jeder SET NOCOUNT ON da ExecuteNonQuery in diesem Fall immer 0 zurückgibt.

Siehe auch Peter Brombergs Blogbeitrag und überprüfen Sie seine Position.

Es läuft also darauf hinaus, wer die Standards setzt :-)

Marc

Answer 5

Wenn Sie sagen, dass Sie auch andere Clients haben könnten, gibt es Probleme mit klassischem ADO, wenn SET NOCOUNT nicht auf ON gesetzt ist.

Eine Erfahrung, die ich regelmäßig mache: Wenn eine gespeicherte Prozedur eine Reihe von Anweisungen ausführt (und somit eine Reihe von "xxx Zeilen betroffen"-Meldungen zurückgegeben werden), scheint ADO dies nicht zu verarbeiten und gibt den Fehler aus "Die ActiveConnection-Eigenschaft eines Recordset-Objekts, das ein Command-Objekt als Quelle hat, kann nicht geändert werden."

Ich plädiere daher generell dafür, sie einzuschalten, es sei denn, es gibt eine wirklich wirklich Sie haben vielleicht den wirklich sehr guten Grund gefunden, den ich noch genauer nachlesen muss.