Top-Themen

AppleEntwicklungHardwareInternetLinuxMicrosoftMultimediaNetzwerkeOff TopicSicherheitSonstige SystemeVirtualisierungWeiterbildungZusammenarbeit

Aktuelle Themen

Administrator.de FeedbackApache ServerAppleAssemblerAudioAusbildungAuslandBackupBasicBatch & ShellBenchmarksBibliotheken & ToolkitsBlogsCloud-DiensteClusterCMSCPU, RAM, MainboardsCSSC und C++DatenbankenDatenschutzDebianDigitiales FernsehenDNSDrucker und ScannerDSL, VDSLE-BooksE-BusinessE-MailEntwicklungErkennung und -AbwehrExchange ServerFestplatten, SSD, RaidFirewallFlatratesGoogle AndroidGrafikGrafikkarten & MonitoreGroupwareHardwareHosting & HousingHTMLHumor (lol)Hyper-VIconsIDE & EditorenInformationsdiensteInstallationInstant MessagingInternetInternet DomäneniOSISDN & AnaloganschlüsseiTunesJavaJavaScriptKiXtartKVMLAN, WAN, WirelessLinuxLinux DesktopLinux NetzwerkLinux ToolsLinux UserverwaltungLizenzierungMac OS XMicrosoftMicrosoft OfficeMikroTik RouterOSMonitoringMultimediaMultimedia & ZubehörNetzwerkeNetzwerkgrundlagenNetzwerkmanagementNetzwerkprotokolleNotebook & ZubehörNovell NetwareOff TopicOpenOffice, LibreOfficeOutlook & MailPapierkorbPascal und DelphiPeripheriegerätePerlPHPPythonRechtliche FragenRedHat, CentOS, FedoraRouter & RoutingSambaSAN, NAS, DASSchriftartenSchulung & TrainingSEOServerServer-HardwareSicherheitSicherheits-ToolsSicherheitsgrundlagenSolarisSonstige SystemeSoziale NetzwerkeSpeicherkartenStudentenjobs & PraktikumSuche ProjektpartnerSuseSwitche und HubsTipps & TricksTK-Netze & GeräteUbuntuUMTS, EDGE & GPRSUtilitiesVB for ApplicationsVerschlüsselung & ZertifikateVideo & StreamingViren und TrojanerVirtualisierungVisual StudioVmwareVoice over IPWebbrowserWebentwicklungWeiterbildungWindows 7Windows 8Windows 10Windows InstallationWindows MobileWindows NetzwerkWindows ServerWindows SystemdateienWindows ToolsWindows UpdateWindows UserverwaltungWindows VistaWindows XPXenserverXMLZusammenarbeit
GELÖST

SQL 2008 reservierter Speicher für Tabelle 10 mal größer als Tabelleninhalt

Frage Entwicklung Datenbanken

Mitglied: Rene1976

Rene1976 (Level 1) - Jetzt verbinden

18.06.2014, aktualisiert 24.06.2014, 2771 Aufrufe, 9 Kommentare

Hallo,

wir haben eine Bilddatenbank auf SQL Server 2008.
Es sind ca. 40.000 Bilder als image Wertetyp in der Tabelle stBilder.

Jetzt ist mir aufgefallen das die reservierte Bereich für die Tabelle bei ca. 120 GB liegt, während der tatsächlich verbrauchte Platz laut SQL Statistik nur bei ca. 17 GB liegt.

Frage:
Wie kann man eine einzelne Tabelle verkleinern?
Warum ist da so ein großer Unterschied?
Bei keiner anderen Tabelle liegt die Differenz zwischen reservierten und tatsächlichen Speicherverbrauch so hoch.

Ich habe schon versucht die komplette DB zu verkleinern, leider ohne Erfolg.
Das Backup ist leider durch diese Tabelle auch bei ca. 160 GB (mit Kompression).

Kann man überhaupt eine einzelne Tabelle verkleinern?
Warum verkleinert der SQL Server nicht die Tabelle wenn ich die ganze DB verkleinern will?

Liegt das an dem Wertetyp Image?

Zur Info:
In der Datenbank liegen Bilder von der Größe von 100 KB bis ca. 50 MB pro Bild.

Besten Dank.

Rene
Mitglied: MadMax
23.06.2014 um 12:33 Uhr
Hallo Rene,

ob Dein Problem am Typ image liegt kann ich Dir zwar nicht sicher sagen, aber es wäre denkbar. Der Datentyp image ist auf jeden Fall veraltet und Du solltest ihn zu varbinary (max) ändern. Der kann dann auch mit normalen SQL-Befehlen bearbeitet werden und braucht keine Pointer mehr.

Gruß, Mad Max
Bitte warten ..
Mitglied: Rene1976
23.06.2014 um 14:54 Uhr
Hi Max,

danke für dein Feedback.
Das Image ein alter Datentyp ist wußte ich schon.
Für neue Datentypen verwenden wir auch nur noch ausschließlich varbinary (max).

Ich kann mir aber nicht vorstellen, dass das am Datentyp Image liegt, oder?

Gruß,

Rene
Bitte warten ..
Mitglied: Biber
23.06.2014, aktualisiert um 19:57 Uhr
Moin Rene1976,

Zitat von Rene1976:

Hi Max,

danke für dein Feedback.
Das Image ein alter Datentyp ist wußte ich schon.
Für neue Datentypen verwenden wir auch nur noch ausschließlich varbinary (max).

Ich kann mir aber nicht vorstellen, dass das am Datentyp Image liegt, oder?
Doch, und an gewissen PraktikantInnen, die beim SQLServer 2008 (ff) drei Vorgaben als "Architektur" oder "Konzept" bezeichnen.

Architektur-Vorgabe 1) Die Pagesize, also die kleinste physische Einheit, die von der DB-Engine angefordert werden kann ist auch die einzig mögliche: immer 8KByte. Niemals 4 KByte ( für die vielen kleinen Fixdatentabellen, die jedes Datenmodell hat) , niemals 32 MByte oder 2 GByte, nur weil vielleicht mal ein Datensatz so lang werden könnte..

Architektur-Vorgabe 2) Ein Datensatz, eine data row, wie der Franzose sagt, kann niemals auf zwei oder mehr Pages verteilt werden - eine data row MUSS immer in eine Page passen. Aber: mehrere Datensätze können in eine Page gespeichert werden.

Die Datentypen ntext, text und eben image passen aber nun mal NICHT (immer) in eine Page von 8 Kbyte Größe - ein image darf ja bis zu 2 GByte groß sein.

Merkt ja keiner - dass regeln die Jungs und Mädels eben intern - in einer data row wird eben nicht das "image" gespeichert, sondern nur ein 16-Bit-Pointer, der auf einen ausserhalb der Tabelle liegenden Speicherklumpen verweist. Der kann dann auch bis zu 2 GByte gross sein. Allerdings gilt hier auch: für jeden 16-Bit-Pointer muss minimal eine Page in der kleinsten Einheit, die das System verwalten kann, mitgeschleppt werden. Wenn du in deinem image-Datenfeld ein kleines *.gif-Bildschen von 345 Byte speicherst und die kleinste Pagesize 8KByte sind, dann hast da ein bisschen Verschnitt dabei.
[Edit]
Und da ein 16-Bit-Pointer nicht etwa auf einen Dateinamen o.ä. verweist, sondern nur auf die "Root-Page" des Speicherklumpens, geht es von da aus dann als Pointer-zu-Page-mit-Pointer-zu-Page-mit-Pointer-zu-Page...etc äusserst effizient weiter, bis die bis zu 2 GByte image irgendwo irgendwie untergebracht sind. Dh. 1MB-Image-Daten werden auf 128 Pages verwaltet, 10 MByte auf 1280 Seiten usw.
Kann man/frau so implementieren.... andere Datenbanken speichern dann doch lieber ""Pfad+Dateiname" in den DB-Tabellen und "Dateien" im Filesystem des OS ab (zum Beispiel Oracle mit->OCS)
[/Edit]

Architektur-Vorgabe 3)
Was das richtig Ungeschickte dabei ist: standardmäßig werden alle image (bzw text, ntext)-Felder unabhängig von ihrer Größe IMMER AUSSERHALB der Tabelle gespeichert, also auch die eben erwähnten 345 Byte eines .gif-Bildchens. Auf deutsch: für jedes benutzte image-Feld deiner Tabelle kannst du noch eine Betriebssystem-Page dazurechnen bei deiner Speicherbelegung.

Die Vorgabe 3 ist richtig richtig Panne - aber gatesseidank ist diese dritte wenigstens änderbar.
Suchmaschine mal nach der stored procedure "sp_tableoption".
Damit kannst du zB mit dem Ausführen von
sp_tableoption N'DeinLustigerTabellenName, 'text in row', '2000';
... einstellen, dass erst ab einer Grösse von >= 2000 Byte ein Image-Feldinhalt "extern" gespeichert wird - alle kleineren bleiben "innerhalb" der oben erwähnten 8-KByte Datenbank -data row, die ja ohnehin schon belegt ist durch den Datensatz.

Musst du halt ein bisschen "Verschnittminimierung" betreiben:
Wenn dein Datensatz OHNE das Image-Feld 500 Byte lang ist und
-> du den dritten Parameter der stored procedure auf 3000 setzt --> dann ist eine Page mit 2 Datensätzen zu je 500 Byte+Image-Feld=3500 Byte voll und du hast Verschnitt in der Größe 8 KByte minus 7500 Byte. Und jedes Image > 3000 Byte wird "extern" gespeichert.
-> du den dritten Parameter der stored procedure auf 7500 setzt --> dann ist eine Page mit 1 Datensatz zu je 8000 voll und du hast Verschnitt in der Größe 8 KByte minus 8000 Byte. Und nur jedes Image > 7500 Byte wird extern gespeichert.
--> du den dritten Parameter der stored procedure auf 1000 setzt --> dann ist eine Page mit 5 Datensatzen zu je 1500 voll und du hast Verschnitt in der Größe 8 KByte minus 7500 Byte. Aber jedes mistige Image > 1000 Byte wird extern gespeichert, auf einer (Betriebssystem-) Page, die definitiv unsinnig größer ist als 1000 Byte.


Schlussbemerkung:
- geh wech von diesen image-Datentypen. Jetzt.
- und lies auch einmal quer zum Thema "Datenkompression"/"compressed data". (obwohl SQLServer da nicht der Brüller ist)

Grüße
Biber
Bitte warten ..
Mitglied: MadMax
LÖSUNG 23.06.2014, aktualisiert 24.06.2014
Hallo Rene,

möglicherweise hilft Dir der Befehl dbcc cleantable.

Ansonsten mach mal folgendes:
Leg eine Kopie von Deiner Tabelle an: select * into TabelleKopie from Tabelle
Wenn image unschuldig ist und irgendein Müll in der Tabelle schlummert, dann sollte die Kopie jetzt kleiner sein.
Ist die Kopie nicht kleiner, dann änder den Datentyp auf varbinary (max) und prüf dann nochmal.

Wenn die Kopie der Tabelle schon kleiner ist, dann könntest Du auch auf diese Art Dein Problem lösen:
1. Fremdschlüssel auf die Originaltabelle entfernen
2. Originaltabelle löschen
3. Kopie umbenennen, das ist jetzt Deine neue Tabelle
4. alle Eigenschaften, Indexe, Constraints, ... wieder auf der Tabelle anlegen

Und trotzdem solltest Du dann bald mal den alten Datentyp loswerden.

Gruß, Mad Max
Bitte warten ..
Mitglied: Rene1976
24.06.2014 um 16:16 Uhr
Hallo,

danke für eure Antworten.
Ich habe mittlerweile auch einen Test gemacht.
Neue Tabelle angelegt und dort alle image Felder durch varbinary(max) ersetzt.

Dann mit insert into die komplette Tabelle in die neue Tabelle kopiert (alles innerhalb der selben Datenbank).

Danach habe ich wieder den SQL Bericht "Datenträgerverwendung der obersten Tabellen" aufgerufen.

Bei der neuen Tabelle hat der reservierte Bereich mit dem tatsächlich verbrauchte Platz fast übereingestimmt, so wie man es von anderen Tabellen gewohnt ist.

Aber beim Blick auf die alte Tabelle habe ich jetzt festgestellt das die Anzahl der Datensätze in der Statistik nicht mit der neuen Tabelle übereinstimmt, trotz insert into.

Ich habe daraufhin ein select count (*) über beide Tabellen laufen lassen.
Als Ergebnis kam bei beiden Tabellen die gleiche Anzahl von Datensätzen raus (die Werte stimmen mit der neuen Tabelle überein).

Ich gehe mittlerweile davon aus, dass die SQL Server Statistik der alten Bildtabelle stBilder nicht stimmt.

Frage:
Wie kann ich die SQL Server Statistik für eine bestimmt Tabelle, bzw. für die ganze DB löschen und dann neu erstellen lassen?

Noch ein Feedback zu Biber:
Unsere Bilddatenbank stammt noch aus der Zeit von SQL Server 2000.
Es gab schon immer die Diskussion ob man Bilder in die Datenbank oder ins Filesystem packt.
Wir hatten uns damals für die Datenbank entschieden und dafür auch eine getrennte extra Datenbank nur für Bilder angelegt.
Der entscheidende Vorteil für uns war damals, dass alle Bilder leicht mit dem SQL Server Tools gesichert und wiederhergestellt werden können.

Der nächste Schritt von MS war dann Filestream. Soweit ich noch weiß konnte man den Filestream in der ersten Version (SQL Server 2008, oder???) nicht zusammen mit der DB sichern.
Soweit ich weiß geht das heute mit SQL 2012, oder?
Somit würde Filestream die Vorteile beider Welten vereinen.
Wir haben noch nicht mit Filestream gearbeitet.

Kennt jemand aus der Erfahrung Vor- und Nachteile von Filestream im SQL Server 2012?
Gehen alle Abfragen, Indizes etc. so wie mit einer Tabelle?
Wie performant ist der Filestream?
Lohnt es sich auf Filestream umzusteigen, wenn man derzeit alle Bilder in der DB hat?
Wie schaut es hier mit dem Speicherverbrauch aus?

Besten Dank.

Rene
Bitte warten ..
Mitglied: MadMax
LÖSUNG 25.06.2014, aktualisiert 26.06.2014
Hallo Rene,

was verstehst Du unter "SQL Server Statistik"? Wenn Du irgendwelche Berichte meinst, die Daten stammen wahrscheinlich aus irgendwelchen Systemtabellen, die kannst Du also nicht einfach mal platt machen und neu aufbauen.

Für die Fragen zum Filestream solltest Du einen neuen Thread erstellen, weil Du diesen hier bereits abgeschlossen hast und deswegen wahrscheinlich nicht mehr allzuviele reinschauen.

Gruß, Mad Max
Bitte warten ..
Mitglied: Rene1976
26.06.2014, aktualisiert um 12:10 Uhr
Zitat von MadMax:

was verstehst Du unter "SQL Server Statistik"? Wenn Du irgendwelche Berichte meinst, die Daten stammen wahrscheinlich
aus irgendwelchen Systemtabellen, die kannst Du also nicht einfach mal platt machen und neu aufbauen.


Mit SQL Server Statistik meine ich die Standard Berichte vom SQL Server, in diesem Fall den Statistikbericht
"Datenträgerverwendung der obersten Tabellen" den man über das Kontextmenü des DB-Knotens im Management-Studio aufrufen kann.

Dieser Bericht stimmt nicht mit den dem Ergebnis des select count (*) Abfrage mit der gleichen Tabelle überein.
Es scheint so, als ob der SQL Server die Berichte nicht real time erstellt sondern irgendwo aus einer Systemtabelle holt und diese in dem Fall nicht richtig turnusmäßig aktualisiert wird.

Kann man eine Aktualisierung für eine bestimmte Tabelle oder die ganze DB erzwingen?

Gruß Rene
Bitte warten ..
Mitglied: MadMax
LÖSUNG 26.06.2014, aktualisiert um 14:44 Uhr
Hallo Rene,

die angezeigten Daten stammen aus der Systemtabelle sys.dm_db_partition_stats. Systemtabelle ist eigentlich falsch, das schimpt sich dynamische Verwaltungssicht. Aktualisieren kannst Du die nicht. Und wenn sie nicht stimmt, dann wäre es ein Fehler im SQL Server, hast Du das aktuelle SP drauf?

Außer einem neueren SP oder Verwendung von varbinary könnte also nur noch MS Euer Problem lösen.

Gruß, Mad Max
Bitte warten ..
Mitglied: Rene1976
26.06.2014 um 14:44 Uhr
Hallo Max,

danke für den Hinweis. Das aktuelle SP habe ich drauf.
Wie gesagt, wir wissen jetzt, dass wenn man die Tabelle kopiert (und gleichzeitig image durch varbinary ersetzt) die neue Tabelle richtig ist.

Wir werden dies dann in den nächsten Tagen tun.

Bis dann.

Rene
Bitte warten ..
Neuester Wissensbeitrag
Humor (lol)

Linkliste für Adventskalender

(3)

Information von nikoatit zum Thema Humor (lol) ...

Ähnliche Inhalte
Windows 10
gelöst Installationsproblem sql server 2014 auf windos 10 rechner (23)

Frage von jensgebken zum Thema Windows 10 ...

Datenbanken
gelöst Täglicher Export einer View aus MS SQL 2008 in Excel oder CSV (9)

Frage von Arafat zum Thema Datenbanken ...

Windows Server
MS SQL 2008 R2 - Dienst beendet sich (5)

Frage von PASUbs zum Thema Windows Server ...

Windows Server
SQL Fehler Beim Installieren von SQL 2008 R2 auf einem Windows 2012 Server (6)

Frage von Sv-Schneider zum Thema Windows Server ...

Heiß diskutierte Inhalte
Router & Routing
gelöst Ipv4 mieten (22)

Frage von homermg zum Thema Router & Routing ...

Exchange Server
gelöst Exchange 2010 Berechtigungen wiederherstellen (20)

Frage von semperf1delis zum Thema Exchange Server ...

Windows Server
DHCP Server switchen (20)

Frage von M.Marz zum Thema Windows Server ...

Hardware
gelöst Negative Erfahrungen LAN-Karten (19)

Frage von MegaGiga zum Thema Hardware ...