webentwicklung-frage-antwort-db.com.de

SQL Server SELECT INTO und Blockieren mit temporären Tabellen

In letzter Zeit versucht ein DBA uns zu sagen, dass wir die Syntax von nicht verwenden können

SELECT X, Y, Z
INTO #MyTable
FROM YourTable

Zum Erstellen temporärer Tabellen in unserer Umgebung, da diese Syntax eine Sperrung von TempDB für die Dauer der Ausführung der gespeicherten Prozedur verursacht. Nun habe ich eine Reihe von Dingen gefunden, die die Funktionsweise temporärer Tabellen, den Umfang der Ausführung, die Bereinigung und dergleichen detailliert beschreiben. Aber für mein Leben sehe ich nichts von Blockieren aufgrund ihrer Verwendung.

Wir versuchen Beweise dafür zu finden, dass wir CREATE TABLE #MyTable nicht für alle unsere temporären Tabellen ausführen müssen, aber keine Seite kann Beweise finden. Ich suche nach Einsichten SO die Leute haben.

Zusätzliche Information

Arbeitet derzeit mit SQL Server 2005 und wird bald SQL Server 2008 (Enterprise Edition) sein.

26
Mitchel Sellers

Dieser Rat hat sich in der Umgebung von für eine lange Zeit herumgesprochen.

Engpässe in SQL Server 6.5

Viele Leute verwenden eine SELECT ... INTO-Abfrage um eine temporäre Tabelle zu erstellen, etwas so was:

SELECT * INTO #TempTable FROM SourceTable

Während dies funktioniert, erstellt es Sperren gegen die Tempdb-Datenbank für die Dauer der SELECT-Anweisung (Eine ganze Weile, wenn Sie durch eine Menge Daten in der Quellentabelle durchlaufen, und noch länger, wenn der SELECT ... INTO am Anfang eines länger laufenden Explizits steht Transaktion) Solange die Sperre vorhanden ist, wird keine andere Benutzer können temporäre Tabellen erstellen. Das Der tatsächliche Ort des Engpasses ist ein Sperren für Tempdb-Systemtabellen. Später Versionen von SQL Server, das Sperren Das Modell hat sich geändert und das Problem ist vermieden werden.

Zum Glück war es nur ein Problem für SQL 6.5. Es wurde in 7.0 und höher behoben.

34
BradC

Dies wird wahrscheinlich lange Zeit herumschweben und die Taschen verschiedener "Berater" füttern. Wie alle Mythen hat es einen Kern der Wahrheit und eine Menge BS.

Die Wahrheit: SQL 2000 und frühere Versionen hatten bekannte Konfliktprobleme bei der Zuweisung von Ausdehnungen in Tempdb. In der Tat war der Konflikt in allen Datenbanken zutreffend, aber in Tempdb aufgrund einiger starker Tempdb-Nutzung deutlicher. Es ist in KB328551 dokumentiert:

Wenn die Tempdb-Datenbank stark ist verwendet, kann SQL Server auftreten Konflikt, wenn versucht wird, zuzuweisen Seiten. 

Aus der Systemtabelle sysprocesses Ausgabe, kann die Waitresource angezeigt werden als "2: 1: 1" (PFS-Seite) oder "2: 1: 3" (SGAM Seite). Abhängig vom Grad von Dies kann auch zu SQL führen Der Server scheint auf .__ nicht zu reagieren. kurze Zeiträume.

Diese Operationen verwenden stark Tempdb:
Wiederholtes Erstellen und Ablegen von temporärem Tabellen (lokal oder global).
Tabellenvariablen, die Tempdb zum Speichern verwenden Zwecke.
Arbeitstabellen, die mit .__ verknüpft sind. CURSORS.
Arbeitstabellen, die mit .__ verknüpft sind. eine ORDER BY-Klausel.
Arbeitstabellen, die einer GROUP BY-Klausel zugeordnet sind.
Mit HASH PLANS verknüpfte Arbeitsdateien. 

Schwere und bedeutende Verwendung dieser Aktivitäten können zum Konflikt führen Probleme.

In SQL Server 2000 SP3 wurde ein Ablaufverfolgungsflag -T1118 hinzugefügt, das SQL zwang, einen Round-Robin-Algorithmus für die Zuweisung gemischter Seiten zu verwenden. Dieser neue Algorithmus würde, wenn er mit der Praxis des Einsatzes von Tempdb auf einem Satz gleichgroßer Dateien (einer für jede CPU) korreliert, den Konflikt lindern. Das Ablaufverfolgungsflag ist in SQL 2005/2008 noch vorhanden, obwohl es viel weniger wahrscheinlich ist.

Alles andere an diesem Mythos ist so ziemlich BS.

  • führt die Verwendung von #temp-Tabellen zum Blockieren? Im schlimmsten Fall ist es erhöht die Konkurrenz unter Last in SQL 2000 und früheren Versionen, aber das ist weit davon entfernt zu sagen, dass es etwas blockiert. Sie müssten zuerst messen und feststellen, dass dies der Fall ist, und wenn ja, implementieren Sie die Korrekturmaßnahmen (ordnen Sie eine Tempdb-Datei pro CPU zu, machen Sie sie gleich groß, aktivieren Sie -T1118).
  • Blockiert select ... in #temp für die Dauer des select etwas? Nicht wirklich.
  • Blockiert select ... in #temp etwas für die Dauer der gespeicherten Prozedur, die select enthält? Auf keinen Fall. Wenn ich nur diesen Anspruch lese, brach ich in Lachen aus.

Für weitere Details gibt es diesen Artikel: Missverständnisse rund um TF1118 .

17
Remus Rusanu

Warum nicht folgendes machen?

SELECT X, Y, Z
INTO #MyTable
FROM YourTable
WHERE 1 = 2

Die Anweisung würde sofort ausgeführt - die temporäre Tabelle wird erstellt und mögliche Sperren werden vermieden. Dann könntest du wie üblich hineinstecken:

INSERT #MyTable
SELECT X, Y, Z
FROM YourTable
10
Eric Pelot

Sie können blockieren, wenn Sie in einer Transaktion #temp-Tabellen erstellen. Während dies im Allgemeinen nicht empfohlen wird, habe ich gesehen, dass dies eine Menge getan hat.

Das Blockieren dieser Ursachen gilt jedoch für einige Systemtabellen in tempdb, die keine anderen Verbindungen durch das Erstellen von temporären Tabellen beeinträchtigen (außer vielleicht für SQL-Versionen vor 2000?). Das bedeutet, dass die Ausführung von sp_spacesused auf tempdb blockiert, es sei denn, Sie legen fest, dass die Transaktionsisolationsstufe nicht festgeschrieben ist. Auch das Anzeigen von Eigenschaften in tempdb über SSMS schlägt fehl, und es tritt kein Timeout auf, da es die Isolierungsstufe "Read Commited Transaction" verwendet.

1
IainS

SELECT INTO #temp_table hält eine Shemasperre in tempdb für die Dauer der Anweisung, da ein Teil der durchgeführten Arbeit die Erstellung der Tabelle ist. Dies ist grundlegend anders, als zuerst die Tabelle mit CREATE TABLE #.... zu erstellen und dann ein set-basiertes INSERT auszuführen. SELECT INTO hat Vorteile gegenüber INSERT. Insbesondere wird der Vorgang minimal protokolliert, wenn das Wiederherstellungsmodell der Datenbank ein einfaches oder massenhaftes Protokoll ist.

0
Michael Lutz

Während SELECT INTO durch das Blockieren des Tempdb behoben wurde, würde ich beim Schreiben des Codes Vorsicht walten lassen, da einige Systemtabellen beim Testen blockiert werden.

Referenz: http://www.sqlservercentral.com/Forums/Topic1642797-2799-1.aspx

0
SeeCoolGuy

Ich würde sagen, dass ein fehlender Verschluss kein Verschluss bedeutet. Dies ist Ihr Beweis. Warum macht die Methode, in der die temporäre Tabelle erstellt wird (CREATE oder SELECT ... INTO), einen Unterschied beim Sperren von TempDB?

0
KM.

Wenn dies wahr wäre, hätte mssql Probleme, da jede große Abfrage Tempdb verwenden kann, um eine Kopie der Zeilen zu speichern. Dies ist häufig in den Abfrageplänen als Tabellenspool zu sehen oder kann vom HASH JOIN-Operator verwendet werden, wenn der Speicher für die Buckets nicht ausreicht.

Sie können sich Tabellenvariablen ansehen, die von mssql im Arbeitsspeicher gespeichert werden und zu tempdb wechseln, wenn sie zu groß werden.

DECLARE @foo TABLE (x int, y int, z int)
INSERT INTO @foo(x, y, z) SELECT x, y, z FROM YourTable

Natürlich sollten Sie zuerst prüfen, ob die temporäre Tabelle und die temporäre Kopie erforderlich sind. Wenn die Abfrage jedoch so komplex ist, dass die Verwendung einer temporären Tabelle weitaus lesbarer ist, kann sie auch komplex genug sein, damit sich eine temporäre Tabelle lohnt.

0
Chris Chilvers