"Timeout abgelaufen" bei Abfrage des Verbindungsservers

Kürzlich hatte ich das Problem, dass alle meine Abfragen gegen einen Verbindungsserver auf ein Timeout liefen. Es hat sich hierbei um einen Datenabgleich zwischen zwei Datenbanken auf unterschiedlichen Servern gehandelt. Den einen Server (S2) habe ich zuvor als Verbindungsserver auf dem anderen (S1) eingerichtet. Danach habe ich auf dem Server S1 folgendes SQL-Statement ausgeführt.

Statement:

INSERT INTO dbo.NeueTabelle

SELECT * FROM [Server2].[DatenbankAlt].[dbo].AlteTabelle

WHERE ID NOT IN (SELECT ID FROM dbo.NeueTabelle)

AND Imei IN (SELECT Imei FROM dbo.VertragsTabelle)

AND Erstellungsdatum > '29.11.2009 00:00:00'

Datenmengen:

Quelltabelle (AlteTabelle) = 60 Mio.

Zieltabelle (NeueTabelle) = 55 Mio.

Nach kurzer Zeit des Wartens wurde mir dann folgende Fehlermeldung ausgeworfen:

Der OLE DB-Anbieter 'SQLNCLI10' für den Verbindungsserver 'Server2' hat die Meldung 'Abfragetimeout abgelaufen' zurückgeben.

Meldung 7399, Ebene 16, Status 1, Zeile 4

Der OLE DB-Anbieter 'SQLNCLI10' für den Verbindungsserver 'Server2' hat einen Fehler gemeldet. Die Ausführung wurde vom Anbieter beendet, da ein Ressourcenlimit erreicht wurde.

Meldung 7421, Ebene 16, Status 2, Zeile 4

Das Rowset kann nicht vom OLE DB-Anbieter 'SQLNCLI10' für den Verbindungsserver 'Server2' abgerufen werden.

Abhilfe:

Da bei mir sichergestellt ist, dass das Statement nur einmalig läuft und nach Beendigung das Timeout wieder gesetzt werden kann, habe ich dieses kurzfirstig wie folgt deaktiviert bzw. auf unbegrenzt gesetzt.

Remote Login Timeout mithilfe dieses Codes auf 30 Sekunden festlegen:

sp_configure 'remote login timeout', 30

go

reconfigure with override

go

Remote Query Timeout auf 0 (unbegrenztes Warten) mithilfe dieser Code festgelegt wird:

sp_configure 'remote query timeout', 0

go

reconfigure with override

go

SQL-Statements blockweise verarbeiten und somit Tablelocks verhindern

Wenn man mit großen Datenmengen arbeiten muss und Abfragen viel Last erzeugen oder sogar komplette Tabellen sperren, so kann man dieses umgehen indem man die Daten blockweise verarbeitet. Hierzu gibt es einen einfachen Trick.

1. SET ROWCOUNT 500
2. insert_more:
3. INSERT INTO dbo.TabelleZiel
4. SELECT * FROM [TestDB].[dbo].TabelleQuelle
5. WHERE ID NOT IN (SELECT ID FROM dbo.TabelleZiel)
6. AND ErstellDatum > '29.11.2009 00:00:00'
7. IF @@ROWCOUNT > 0 GOTO insert_more
8. SET ROWCOUNT 0

Mit dem Befehl RowCount legt man die pro Block zu verarbeitende Anzahl an Datensätzen fest.
Hier ist es wichtig einen geeigneten Wert zu finden. Sollte man die Blöcke zu groß einstellen, so wird doch wieder die komplette Tabelle gesperrt. 500 Datensätze ist hier schon ein gängiger Wert, welcher eigentlich immer verwendet werden kann.

Im Beispiel sieht man eine Goto-Schleife zwischen Zeile 2 und 7.
zwischen diesen Zeilen kann man dann das auszuführende Statement eintagen.
Das Beispiel zeigt ein umfangreicheres Insert. Hier könnte genauso gut ein einfaches Delete stehen.

Wichtig ist auch, dass nach der Verarbeitung die Blockverarbeitung wieder deaktiviert wird.
Dieses geschieht in Zeile 8. Der Wert 0 steht für eine unbegranzte Menge an Datensätzen.

Die Blockverarbeitung ist etwas langsamer als die direkte Komplettverarbeitung. Jedoch ist sie Ressourcen schonender und erzeugt keine Table-Locks.