Wie Wartung einer Azure SQL Datenbank Geld spart – Messen von Fragmentierung, etwas Aufräumen und starten von Defragmentierung

Hallo @all,

Cloud-Technologien nehmen einem Arbeit ab, beschleunigen Arbeitsweisen und man kommt schnell zum Ziel. Doch alles kann auch die Cloud nicht machen. Ich hatte ein Website-Monitoring-Tool entwickelt und im Einsatz. D.h. das Tool hat eine Website angefordert und das Ergebnis in ausgewerteter Form abgespeichert. Als Speichertort hatte ich mich für eine Azure SQL Datenbank entschieden.

Warum eine Azure SQL DB? Ganz einfach für Auswertung mit SQL und Excel war extreme praktisch und die Datenbank-Preise waren überschaubar. (<10 €/Monat und später lag ich bei ca. 25 €/Monat als die DB eine Größe von 9.5 GB hatte). Mein Tool hatte folgendes gemacht. Alle 500 Millisekunden eine Anfrage zur Website produziert und das Ergebnis (HTTP-Response) in der Datenbank gespeichert.

Das hat über einen längeren Zeitraum (erst 10 Tage dann 20 Tage und mittlerweile über 30 Tage) die Datenbank in eine ordentliche Größe gebracht.

Aktuell ist die Datenbank 9,2 GB groß und ich möchte etwas aufräumen. Mittels SQL-Mitteln kann man noch  aktuellere Daten bekommen. Man kann sich direkt zur Master-Datenbank verbinden und den Ressourcenverbrauch messen. Die DB hat eine Größe von 9.5 GB. Man sieht mein Tool arbeitet fleißig und pumpt die Datenbank auf. :-)

Doch wo liegen die großen Datenberge genau in der Datenbank? Ich möchte nämlich dort unwichtigen Informationen löschen:

 -- connect tot TestDb 
-- Identify large SQL objects in Azure SQL Database, like a large Table 
select 
    o.name, 
    sum(part.reserved_page_count)*8.0 as 'size in kB' , 
    (sum(part.reserved_page_count)*8.0/1024) as 'size in MB' 

from 
    sys.objects o, 
    sys.dm_db_partition_stats part 
where 
        part.object_id = o.object_id 
group by 
    o.name 
order by 'size in kB' desc

        Wie man sehen kann ist alles in einer Table gespeichert… *wenn das nicht mal eine BigTable ist und ich weiß, das ist kein schönes DB-Design* ;-) 

In der Tabelle sind aktuell 4.5 Mio Zeilen enthalten, Tendenz steigend, da mein Tool noch fleißg weiterläuft.

Zum Aufräumen werde ich die Tabelle von überflüßigen Daten befreien mit einem normalen Delete-Statement. Um zu verhindern, das Schreibzugriffe zu lange die Tabelle blockieren, lösche ich in 1000-Blöcken mit einer While-Schleife.  (ca. 3.5 Mio Zeilen werden in Summe gelöscht)

 WHILE (select count(*) from EndpointMonitoring 
            where 
                    timetaken < 100 
                and timestamputc <= '2014-02-28 23:59:59.999' 
                and timestamputc >= '2014-02-01 00:00:00.000') > 0 
BEGIN 
    delete top(1000) from endpointmonitoring 
    where 
         timetaken < 100 
     and timestamputc <= '2014-02-28 23:59:59.999' 
     and timestamputc >= '2014-02-01 00:00:00.000' 

    print 'loop: ' + cast(@i as varchar(10)) 
    set @i = @i+1 
 END

….

Diese Operation wird ca. 75 % der Daten in der Tabelle löschen. D.h. es wird eine Weile dauern….

Wer hätte das gedacht fast 3 Stunden:

Während des Löschens kann man erkennen wie die Datenbank Ressourcen verbraucht:

(in der Master DB ausführen)

 select * from sys.resource_stats 
where database_name = 'TestDb' 
order by start_time desc

Das Löschen produziert Last für die CPU und es wird kräftig geschrieben.  

Durch das Löschen ist die Datenbank noch nicht direkt kleiner geworden. D.h. eine Kostenersparnis ist noch nicht passiert. In der Datenbank ist dafür die Fragmentierung gestiegen.   

Schauen wir uns doch mal die Fragmentierung an:

 -- identify Fragmentation, in case index is fragmented higher then 10 % -> rebuild or reorganize 
-- fine tuning: 
--                - fragmentation 10 % - 30 % -> reorganize 
--                - fragmentation > 30 % -> rebuild 
SELECT 
  DB_NAME() AS DBName 
  ,OBJECT_NAME(ps.object_id) AS TableName 
  ,i.name AS IndexName 
  ,ips.index_type_desc 
  ,ips.avg_fragmentation_in_percent 
  FROM sys.dm_db_partition_stats ps 
  INNER JOIN sys.indexes i 
  ON ps.object_id = i.object_id 
  AND ps.index_id = i.index_id 
  CROSS APPLY sys.dm_db_index_physical_stats(DB_ID(), ps.object_id, ps.index_id, null, 'LIMITED') ips 
  ORDER BY ips.avg_fragmentation_in_percent desc

Man kann erkennen, der Index ist weit über einer 10 %-Fragmentierung und Bedarf einer Defragmentierung.

Ok. Dann starten ich das Aufräumen /die Defragmentierung:

 -- start defragmentation 
DECLARE @TableName varchar(255) 
DECLARE TableCursor CURSOR FOR 
 ( 
     SELECT '[' + IST.TABLE_SCHEMA + '].[' + IST.TABLE_NAME + ']' AS [TableName] 
     FROM INFORMATION_SCHEMA.TABLES IST 
     WHERE IST.TABLE_TYPE = 'BASE TABLE' 
 ) 
  OPEN TableCursor 
  FETCH NEXT FROM TableCursor INTO @TableName 
  WHILE @@FETCH_STATUS = 0 
  BEGIN 
     PRINT('Rebuilding Indexes on ' + @TableName) 
     Begin Try 
     EXEC('ALTER INDEX ALL ON ' + @TableName + ' REBUILD with (ONLINE=ON)') 
  End Try 
  Begin Catch 
     PRINT('Cannot do rebuild with Online=On option, taking table ' + @TableName+' down for douing rebuild') 
     EXEC('ALTER INDEX ALL ON ' + @TableName + ' REBUILD') 
  End Catch 
  FETCH NEXT FROM TableCursor INTO @TableName 
  END 
  CLOSE TableCursor 
  DEALLOCATE TableCursor

Nach 16 Minuten war auch hier die Fragmentierung wieder im grünen Bereich:

Um zum Schluss sieht die Datenbank so viel besser aus:

Die Fragmentierung ist bis auf einen Index auf ein gesundes Maß gesunken.

Notiz: die Tabellen hat ein Feld TargetURL und ist vom Typ nvarchar(255) und hier kommt zum Tragen das ein Online-Index-Rebuild Grenzen hat. Ab nvarchar(50+) wird kein rebuild online durchgeführt. Das ist eine Eigenschaft von SQL Server.

Zum nachjustieren kann man noch den Index-Rebuild klassisch durchführen bspw. der Operation Online=OFF und die letzten Bytes herauskitzeln.

Schaut ich mir die BigTable-“EndPointMonitoring” an, diese hat jetzt nur noch die Größe von 2,2 GB, nicht mehr 9,5 GB.

Die gesamte Datenbank sollte somit ebenfalls auf diese Niveau gesunken sein. Hier kann es etwas Zeit kosten bis das Portal die Daten aktualisiert hat. Nach ca. 20 min. habe ich die Daten im DB-Manager-Portal so vorgefunden:

DB-Größe: 1,78 GB :-)

Und Kurz darauf auch in der sys.Resource_Stats-View:

Unter dem Strich hat sich die Wartung für mich folgendermaßen gelohnt:

1. ich habe meine Wartungsskripte erstellt

2. eine Azure SQL DB von 9 GB kostet pro Monat ca. 31 € und nach der Wartung nur noch ca. 10 €/Monat.

[Quelle: https://www.windowsazure.com/en-us/pricing/calculator/?scenario=data-Management]

Meine alten Daten habe ich noch vorrätig, als DB-Backup in einem Azure-Storage-Account und sollte ich diese Daten noch einmal brauchen dann spiele ich diese in eine Azure SQL Datenbank ein für den temporären Gebrauch. Es ist also ähnlich wie ein  Archiv.

Und was kostet das Backup im Azure Storage Account?

Dateigröße: 9 GB –> nicht mal 1 €/Monat.

Wer es ganz genau wissen möchte?

25 GB = 1,31 €  -> D.h. es sollten ca. 0,48 €/Monat sein.

[Quelle: https://www.windowsazure.com/en-us/pricing/calculator/?scenario=data-management]

Viel Spaß mit den Skripten und beim Geld sparen.

Liebe Grüße

Patrick