Speicherplatz von Datenbanken

[gerdschluckebier]

Wenn ich aus einer MS SQL Server 2005 Datenbank Tabellen mit DROP TABLE entferne, wird der Speicherplatz der mdf-Datei nicht kleiner.
Wie kan man das Problem lösen?
Vielen Dank
Gerd Schluckebier

[Bernhard Geyer]

Du mußt die Datenbank reorganisieren. Ansonsten wird dieser Platz verwendet die nächsten DB-Vergrößerungen (die Zeit kosten) zu vermeiden in dem dieser leere Platz verwendet wird.

[O. Helper]

Wie kan man das Problem lösen?

Wieso ist das ein Problem?
Das ist ein ganz normales Verhalten und ich wünsche es mir auch nicht anderes.

1. Der freigewordene Speicherplatz wird wiederverwendet. Es liegt einfach in der Natur von Datenbanken, das ständig neue Daten hinzukommen, die werden in den ersten freien Bereich geschrieben, also wieder aufgefüllt.

2. Erwartest Du wirklich, das nach einem DROP eine Tabelle die DBMS nun anfängt zu rattern, nur um die Datenbanken zu verkleinern und dabei 100% IO Last erzeugt, wodurch der gesamte restliche Betrieb zum Stillstand kommt? Ich definitiv nicht.

Es gibt die DB-Option "Autoshrink" und auch demn DBCC Befehl für einen Shrink.
Aber: MS und alle Experten sagen: Never shrink a database; NEVER!!!

Warum ist ganz einfach:
Beispiel: Du hast eine Datenbank und legst eine große Tabelle A an und anschließen die große Tabelle B. Die stehen nun schön hintereinander in der Datenbankdatei, ein Full-Table-Scan könnte sauber "geradeaus" durchgehen, der Fragmentierungsgrad liegt bei 0%; super.

So, nun löscht Du Tabelle A, der vordere Teil wird frei und Du startest den Shrink an.
Das geht nun ganz stupide vor, es nimmt den letzte belegten Block vom Ende und packt in an den Anfang der Datenbank-Datei, nimmt den nächsten belegten...usw.
Ergebnis: Es dreht Dir die Tabelle einmal von links nach rechts, Fragmentierungsgrad 99% und Deine Performance geht runter.

Übrigens, es ist eine gängige Vorgehensweise, gleich zu Anfang eine große Datenbank-Datei anzulegen, dadurch fragmentiert die DB bereits auf Dateiebene nicht so und man erspart sich die regelmäßige automatische Dateivergrößerung, die auch jedesmal IO Last mit sich bringt.

[gfoidl]

Hallo,

kann man eine DB defragmentieren? Wenn ja wie?

Möglich wäre ein Skrip wie zB in http://michael-schwarz.blogspot.com/...-indizies.html. Aber gibts es keine eingebaute Funktion?

mfG Gü

[O. Helper]

Hallo gfoidl,

das Script kenne ich, es steht auch in der BOL. Es defragementiert die Indizes mittels DBCC INDEXDEFRAG, also gewichtet die B-Trees neu.

Eine "eingebaute Funktion" gibt es fast, Du kannst über einen Wartungsplan die (alle) Indizes neu erstellen lassen.
Das sollte man aber nur für Zeiten einplanen, wenn niemand aktiv ist.
Zieht man jemanden den Indez unter den Füssen weg, rumst es.
Ab SQL2005 kann man Indizes auch ONLINE erstellen lassen; habe ich aber noch keine Erfahrungswerte mit.

Was die Fragmentierung der Daten betrifft, hilft Dir das aber nur bei Tabellen, die einen CLUSTERED INDEX haben, da die Seiten dann ja physikalisch zusammen schreiben.
Man erkennt es ganz gut, nach so einem Lauf wächst die DB und man hat mehr "unused space" => EXEC sp_spaceused;

Hier ein Beispiel-Script, um das auch mal zu belegen (ich kann ja viel erzählen; stimmen muss es ja nicht ) und Ihr auch experimentieren könnt; viel Spass beim Testen.

Es legt eine DB + 2 Tables an, genau das oben beschrieben Szenario. Bei mir rappelt es ca. 4 min; man kann die Loops für füllen der Tabellen auch kleiner setzen.

Achtet vor allem bei der Ausgabe von DBCC SHOWCONTIG auf die Spalte "LogicalFragmentation" vor/nach SHRINK und nach INDEXDEFRAG.

[highlight=SQL]-- Keine unnötigen Rückmeldungen, bitte
SET NOCOUNT ON;
GO
-- DB mit Default-Werten für Fragmentierung-Demo anlegen
CREATE DATABASE EF_Fragment;
GO
-- Auf neue DB switchen
USE EF_Fragment
GO
-- Erste große Tabelle anlegen; CHAR(250) um mir das füllen zu sparen
CREATE TABLE TabelleA (ID int NOT NULL, String char(250));
GO
-- Non-Clustered Index auf PK => Heap-Tabelle
ALTER TABLE TabelleA
ADD CONSTRAINT PK_TabelleA PRIMARY KEY NONCLUSTERED (ID);
GO
-- Nun befüllen mit 250.000 Datensätze; das dauert etwas
DECLARE @iLoop int;
SET @iLoop = 0;
WHILE @iLoop < 250000
BEGIN
INSERT INTO TabelleA VALUES (@iLoop, 'TabelleA');
SET @iLoop = @iLoop + 1;
END
GO
-- Nächste Tabelle, nur diesemal
-- mit CLUSTERED Index als PK
CREATE TABLE TabelleB (ID int NOT NULL, String char(250))
GO
-- Clustered Index auf PK
ALTER TABLE TabelleB ADD CONSTRAINT
PK_TabelleB PRIMARY KEY CLUSTERED (ID);
GO
-- Nun befüllen mit 250.000 Datensätze
-- Dauert bei mir 1
DECLARE @iLoop int;
SET @iLoop = 0;
WHILE @iLoop < 250000
BEGIN
INSERT INTO TabelleB VALUES (@iLoop, 'TabelleB');
SET @iLoop = @iLoop + 1
END
-- Defrag-Status beider Tables anzeigen
DBCC SHOWCONTIG('TabelleA') WITH TABLERESULTS
DBCC SHOWCONTIG('TabelleB') WITH TABLERESULTS
GO
-- Beachtet den Unterschied!!!
SET NOCOUNT OFF -- Jetzt möchte ich wieder Meldungen
GO
-- So, nun die böse Sache: DROP + SHRINK
DROP TABLE TabelleA;
GO
DBCC SHRINKDATABASE(0); -- Shrink
DBCC UPDATEUSAGE(0); -- und Stats aktualisieren
GO
DBCC SHOWCONTIG('TabelleB') WITH TABLERESULTS
GO
-- Index defrag
DBCC INDEXDEFRAG(0, 'TabelleB', 'PK_TabelleB')
DBCC SHOWCONTIG('TabelleB') WITH TABLERESULTS

-- Und die DB wieder abräumen
DROP TABLE TabelleB
GO

USE [master]
GO
DROP DATABASE EF_Fragment;
GO
[/HIGHLIGHT]

Bei Heap's (Tabellen ohne CLUSTERED INDEX) hilft es nichts, da laboriere ich auch noch, wie ich es hin bekommen.
Wenn ich was habe, melde ich mich; das wird aber noch weit aufwendiger.

[gfoidl]

Hallo Olaf,

danke! Ist sehr aufschlussreich

mfG Gü

[O. Helper]

Passend zum Thema:

http://blogs.sqlserverfaq.de/Lists/B...ost.aspx?ID=82

[gfoidl]

Dazu ein Paar Fragen:

Eine DB kann mit mehreren Dateien angelegt werden.

• Hat es Vorteile mehrer Dateien zu verwenden?
• Muss ich dann beim erstellen der Tabelle definieren in welche Datei die Tabelle gespeichert werden soll?

mfG Gü

[O. Helper]

Kurz gesagt, es bringt was, wenn ...
Lang formuliert wird es wieder ein ganzer Aufsatz; vielleicht habe ich am Wochenende Zeit&Muse dazu.

Zunächst einmal, wie funktioniert es.
Je Datenbank kann man neben der PRIMARY beliebig viele Dateigruppen anlegen.
Je Dateigruppe kann man wiederum beliebig viele Dateien anlegen.

Bei der Tabellen-/Indexanlage kannst Du je
- Daten allgemein
- BLOB
- Index
festlegen, in welcher Dateigruppe (nicht Datei!) es gespeichert werden soll (hat man die Entenscheiß-Edition, dann auch noch je Partition).
Kleine Ausnahme: Ein CLUSTERED INDEX muss immer in der gleichen Dateigruppe sein, wie die Tabelle (ist logisch).

[highlight=SQL]CREATE TABLE Tabelle (...,
CONSTRAINT PKName PRIMARY KEY NONCLUSTERED
(...) ON Dateigruppe_PKIndex
) ON Dateigruppe_Tabelle
TEXTIMAGE_ON Dateigruppe_Blob[/Highlight]


Was bringt das:
- Man kann die Dateien über mehrere Festplatten und somit die I/O Last verteilen; je mehr Spindeln in paralleler Verwendung, desto höher die I/O Performanz
- Man kann eine Dateigruppe auf ReadOnly setzen, um z.B. historische oder Referenzdaten zu schützen
- Man kann Dateigruppen einzeln sichern/rücksichern, also z.B. die zuvorige ReadOnly nur einmal im Monat sichern, der Rest täglich. Das spart Backup-Resourcen.


Siehe auch hier:
http://entwickler-forum.de/showthread.php?t=50157

[gfoidl]

Lang formuliert wird es wieder ein ganzer Aufsatz; vielleicht habe ich am Wochenende Zeit&Muse dazu.

Nicht nötig - deine Kurzinfo ist informativ genug.

Danke!

mfG Gü