Schlagwort: SQL 2005

Auf einer Datenbank in einem MS-SQL 2005 Server (32bit) verursachte ein simpler DETETE FROM tbl… in einem meiner Programme die folgende Fehlemeldung:

Der Abfrageprozessor hatte während der Abfrageoptimierung zu wenig Stapelspeicherplatz. Vereinfachen Sie die Abfrage.

Das Ganze war etwas mysteriös, und auch in den Newsgroups und im Netz gab es keine vernünftige Antwort. Also habe ich kurzerhand eine Supportanfrage an Microsoft gestellt.

Hier in Kurzform die Problematik:

• Wir haben eine sehr komplexe Datenstruktur, die der Anwender mit der Hilfe eines Tabellen-Generators selbst manipulieren und definieren kann. Die Folge in einem Fall:
  Es gab zu einer Tabelle über 400 Fremdschlüssel.
• Besagte Fremdschlüssel sollten garantieren, dass aus dieser Tabelle, die für Datensatz Klassifikationen verwendet wurde, keine verwendeten Datensätze gelöscht werden können. 
• An dem Statement selbst gab es nichts zu optimieren, das war wirklich nur ein einfaches Statement im Stile von DELETE FROM tbl WHERE Id=4711

In Kurzform die Antwort von Microsoft:

• Das Problem ist nachvollziehbar.
• Das Problem ist hausgemacht und liegt an der Anzahl der Fremdschlüssel.
• Einen Fix bzw. eine Änderung oder Parameter mit denen man das Verhalten ändern, oder erweitern kann, gibt es nicht und ist nicht geplant.
• Der Speicherblock, der hier zu klein wird, ist bei einem MS SQL-2005 Server in der 32bit Version 512KB groß, auf einer 64bit Installation ist besagter Speicherblock viermal so groß, d.h. 2MB!
  Der Fehler tritt auf einem 64bit Server und der gleichen DB-Struktur nicht auf, dafür aber später 😉 , wenn noch mehr Schlüssel verwendet werden.

Einen kleinen bissigen Kommentar kann ich mir hierzu aber nicht verkneifen, denn ganz blind bin ich in dieses Problem nicht hinein gelaufen 😈

Hier der entsprechende Link zu Spezifikationen der maximalen Kapazität für SQL Server 2005, und dort lesen wir folgendes:

Verweise auf Fremdschlüsseltabellen pro Tabelle 253

– und in der-

Fußnote 4:
Auch wenn eine Tabelle eine unbeschränkte Anzahl von FOREIGN KEY-Beschränkungen enthalten kann, beträgt das empfohlene Maximum 253. In Abhängigkeit von der Hardwarekonfiguration, die SQL Server hostet, kann das Angeben weiterer Fremdschlüssel den Abfrageoptimierer bei der Verarbeitung stark beanspruchen.

Diese Information deckt sich mit der englischen Dokumentation.

Meine Interpretation dazu ist die folgende:
Es kann den Abfrageoptimierer stark beanspruchen wenn man so einen Konstrukt baut, allerdings war und ist mir das egal. Von mir aus kann die Ausführung Minuten dauern. Der Fall des Löschens in dieser Tabelle ist die seltene Ausnahme. Das Problem ist hier, dass die Ausführung gar nicht möglich ist. Hier steht nicht, dass ich nicht mehr als 253 Schlüssel verwenden darf! Wenn dem so ist, müsste dieser Satz klar umformuliert werden, oder es müsste bereits beim Anlegen eines weiteren Fremdschlüsselverweises eine Fehlermeldung kommen.
De facto steht hier: „Auch wenn eine Tabelle eine unbeschränkte Anzahl…“
Die Operation sollte fehlerfrei durchgeführt werden können, was aber nicht so ist.

Besagte Supportanfrage wurde geschlossen ❗

Bleibt noch Abschließendes hinzuzufügen:

• Auch der MS-SQL Server 2008 zeigt das gleiche Verhalten.
• Man erhält den selben Fehler auch im MS-SQL 2000, dort heißt er dann
  Interner Fehler des Abfrageprozessors: Im Abfrageprozessor wurde bei der Ausführung ein unerwarteter Fehler festgestellt.

Es gibt noch ein paar nette Tabellen und Abfragen die einem Informationen über die physikalische Größe eine SQL-Datenbank liefern. Denn sowohl die Daten-Datei als auch die Protokolldatei können über nicht allokierten Speicherbereich verfügen. Wenn man also wissen will, wie viel Platz wirklich noch verfügbar ist, muss auch dieser noch verfügbare Platz mit einbezogen werden.

Es sind also noch zu ergänzen:

1. Sehr einfach ist die dbo.sysfiles zu verwenden. Auch über diese Tabelle erhält man schnell die Größe der Protokoll- und Datendatei in 8kb Blöcken.
2. sp_spaceused liefert einem die komplette Größe der Datendatei und die Größe des nicht verwendeten Bereiches. sp_spaceused liefert übrigends zwei Resultsets wenn man es ohne Objektnamen aufruft! Nicht wundern.
3. Und last but not least DBCC SQLPERF(LOGSPACE), dass die selbe Information für die Protokolldatei liefert. D.h. Größe der Protokolldatei in MB und den prozentual benutzten Speicherbereich. 

PS: Wie man so etwas herausbekommt. ❓
Nun man kann die MSDN lesen, aber man kann es sich auch einfacher machen. 😉
Ich habe einfach den SQL Profiler angeworfen, dann den SQL Server Enterprise Manager gestartet. Im Enterprise Manager habe ich die Taskpad Ansicht gewählt und einfach mal F5 gedrückt. Der Profiler hat mir dann gezeigt was der Enterprise Mangager so abfragt um die relevanten Daten zu ermitteln.

Kann man den MS-SQL Server abfragen ob es noch genug Plattenplatz gibt?

Manche Operationen kosten sehr viel Platz, besonders wenn man Datenbanken umstrukturiert. Da kann es leicht sein, dass gleich 100% mehr Platz benötigt wird. Zum einen weil die Protokolldatei evtl. immens wächst und zum zweiten weil einfach die Tabellen stark anwachsen weil zum Beispiel neue Indexe hinzukommen oder Spalten auf Unicode umgestellt werden.

Kann man also irgendwie den freien, zur Verfügung stehenden Platz ermitteln und entsprechende Berechnungen anstellen?

Ja! Es geht.

1. Ausgangspunkt ist hier zum einen die dokumentierte System Tabelle sysdatabases, die den Namen der Datenbank, und den Namen physikalischen mdf-Datei liefert.
2. Nun benötigen wie noch die aktuelle Größe, die wir ohne Probleme mit der Storedprocedure sp_databases ermitteln können, die Namen, die Größe in KB und eine NULL Spalte zu dieser Datenbank liefert.
3. Den dritten und letzten Baustein für die Aufgabe liefert die nicht dokumentierte Funktion master..xp_fixeddrives, die in der ersten Spalte den Laufwerkbuchstaben liefert und in der zweiten den freien Speicherplatz in MB.

Wie man jetzt errechnet ob eine Größenänderung um 75% noch abgedeckt wird, bleibt dem Leser überlassen. Nett nicht! 🙂

❗ BTW: Diese Funktionen laufen verifiziert auf MS-SQL 2000 und auch auf MS-SQL 2005 Servern inkl. der Express-Edition.

Link Useful undocumented extended stored procedures