SQL Server (και άλλες databases)

Απ: Πολύ αργό Differential Backup

KelMan — Sat, 28 Oct 2006 07:51:01 GMT

Teo wrote:

KelMan είπες ότι "Όσο μεγαλύτερο το fragmentation τόσο περισσότερες σελίδες έχεις στα indexes, άρα και όγκο δεδομένων, ακόμα και αν χρησιμοποιείς το 3%-4% των εγγραφών". Έχω μόνο insert - delete (τα select δεν επηρεάζουν) σε 4% του μεγέθους του πίνακα και με δεδομένο ότι ο cluster βάζει εγγραφές ανα ημερομηνία (getdate()),όλες οι εγγραφές δεν μπαίνουν στο τέλος του index ? Σβήνω πάντα τις πολύ παλιές (αφού είναι clustered index by datetime- σβήνονται τα πρώτα φύλλα του δέντρου). Με δεδομένο το παραπάνω fragmentation δεν πιστεύεις ότι πρέπει να πάει και να πάρει backup τις πρώτες και τις τελευταίες pages μόνο (τα πρώτα και τα τελευταία φύλλα του δέντρου)? Έχω λάθος?

Κάθε βράδυ, μετά το backup κάνω και update statistics.... Μήπως φταίει αυτό?

Χμμμμ... Τα indexes στον SQL Server είναι b-tree data structures, όπερ σημαίνει ότι πρέπει να είναι συνεχώς "balanced" (γι αυτό γίνονται τα page splits και κατεπέκταση το fragmentation, για να αναδιοργανώσει τα indexes). Γι αυτόν τον λόγο δεν είναι απαραίτητο ότι το differential θα κάνει backup μόνο τις πρώτες και τις τελευταίες σελίδες. Επίσης, υπάρχει κι ένας επιπρόσθετος λόγος. Το diff backup δεν παίζει σε επίπεδο σελίδας αλλά σε επίπεδο extent. Αν έστω μία εγγραφή σε μία από τις οκτώ σελίδες που αποτελούν ένα extent αλλάξει, τότε γίνεται backup όλο το extent

Ένας τρόπος να δεις πόσα extents έχουν γίνει flagged για diff backup είναι το DBCC PAGE. Στο βιβλίο της Kalen Delany "Inside SQL Server" μας λέει ότι η 6η σελίδα σε κάθε datafile έχει ένα bitmap του οποίου κάθε bit είναι κι από ένα flag για τα extents που ακολουθούν και δείχνει αν είναι dirty ή όχι. Αυτή η σελίδα ονομάζεται DCM (Differential Changed Map) και επαναλαμβάνεται κάθε 511.232 σελίδες για τα επόμενα extents, κοκ.

Παίζουμε στη Northwind. Ανοίγεις το Query Analyser και:

Αρχικά, κάνεις full backup. Θα σου εμφανίσει ένα μήνυμα του τύπου:

Processed 448 pages for database 'Northwind', file 'Northwind' on file 4.
Processed 2 pages for database 'Northwind', file 'Northwind_log' on file 4.
BACKUP DATABASE successfully processed 450 pages in 1.300 seconds (2.831 MB/sec).

Δηλαδή έκανε backup περίπου 56 extents. Κατόπιν, τρέξε το παρακάτω:

DBCC TRACEON (3604)
GO
DBCC PAGE('Northwind',1,6,3) WITH TABLERESULTS
GO

Το DBCC TRACEON (3604) γυρίζει το output του DBCC PAGE στην κονσόλα. To αποτέλεσμα του DBCC PAGE δίνει τα παρακάτω αποτελέσματα ()εμφανίζονται στο τέλος):

DIFF_MAP: Hea..., Offset 96 DIFF_MAP: Extent A... (1:0)        - (1:16)           CHANGED
DIFF_MAP: Hea..., Offset 96 DIFF_MAP: Extent A... (1:24)       - (1:56)       NOT CHANGED
DIFF_MAP: Hea..., Offset 96 DIFF_MAP: Extent A... (1:64)       - (1:88)           CHANGED
DIFF_MAP: Hea..., Offset 96 DIFF_MAP: Extent A... (1:96)       - (1:528)      NOT CHANGED

Δηλαδή έχουμε 2 extents changed κατόπιν 4 not changed, κατόπιν 3 changed και τέλος 54 not changed. Στη συνέχεια, τρέχουμε κάτι σαν το παρακάτω:

SELECT 'DBCC DBREINDEX('''+TABLE_NAME+''','''', 80)'
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE='BASE TABLE'

του οποίου χρησιμοποιούμε το οutput για να τρέξουμε DBCC DBREINDEX σε όλους τους πίνακες. Μόλις τελειώσει, ξαναδίνεις το DBCC PAGE

DIFF_MAP: Hea..., Offset 96 DIFF_MAP: Extent A... (1:0)        - (1:16)           CHANGED
DIFF_MAP: Hea..., Offset 96 DIFF_MAP: Extent A... (1:24)       - (1:32)       NOT CHANGED
DIFF_MAP: Hea..., Offset 96 DIFF_MAP: Extent A... (1:40)       -                  CHANGED
DIFF_MAP: Hea..., Offset 96 DIFF_MAP: Extent A... (1:48)       - (1:56)       NOT CHANGED
DIFF_MAP: Hea..., Offset 96 DIFF_MAP: Extent A... (1:64)       - (1:88)           CHANGED
DIFF_MAP: Hea..., Offset 96 DIFF_MAP: Extent A... (1:96)       -              NOT CHANGED
DIFF_MAP: Hea..., Offset 96 DIFF_MAP: Extent A... (1:104)      - (1:144)          CHANGED
DIFF_MAP: Hea..., Offset 96 DIFF_MAP: Extent A... (1:152)      - (1:160)      NOT CHANGED
DIFF_MAP: Hea..., Offset 96 DIFF_MAP: Extent A... (1:168)      -                  CHANGED
DIFF_MAP: Hea..., Offset 96 DIFF_MAP: Extent A... (1:176)      - (1:184)      NOT CHANGED
DIFF_MAP: Hea..., Offset 96 DIFF_MAP: Extent A... (1:192)      - (1:200)          CHANGED
DIFF_MAP: Hea..., Offset 96 DIFF_MAP: Extent A... (1:208)      -              NOT CHANGED
DIFF_MAP: Hea..., Offset 96 DIFF_MAP: Extent A... (1:216)      -                  CHANGED
DIFF_MAP: Hea..., Offset 96 DIFF_MAP: Extent A... (1:224)      - (1:232)      NOT CHANGED
DIFF_MAP: Hea..., Offset 96 DIFF_MAP: Extent A... (1:240)      - (1:272)          CHANGED
DIFF_MAP: Hea..., Offset 96 DIFF_MAP: Extent A... (1:280)      -              NOT CHANGED
DIFF_MAP: Hea..., Offset 96 DIFF_MAP: Extent A... (1:288)      - (1:496)          CHANGED
DIFF_MAP: Hea..., Offset 96 DIFF_MAP: Extent A... (1:504)      -              NOT CHANGED
DIFF_MAP: Hea..., Offset 96 DIFF_MAP: Extent A... (1:512)      - (1:520)          CHANGED
DIFF_MAP: Hea..., Offset 96 DIFF_MAP: Extent A... (1:528)      -              NOT CHANGED

Βλέπουμε ότι τώρα έχουμε 45 extents μαρκαρισμένα για diff backup - εκεί που είχαμε 5, και όλα αυτά χωρίς κανένα update operation. Κανονικά, όταν θα κάνεις τώρα το diff backup θα πρέπει το mini report που βγάζει να επιβεβαιώνει τα παραπάνω.

Μπορείς να κάνεις το ίδιο για να δεις τι γίνεται και με τo index defrag. Επίσης, αν τρέξεις το DBCC PAGE πριν το index defrag θα δεις αν όντως είναι μόνο τα πρώτα και τα τελευταία extents που επηρεάζονται.

Πάντως το καλύτερο που έχεις να κάνεις είναι να ρυθμίσεις το fill factor στα indexes έτσι ώστε να έχουν αρκετό ελεύθερο χώρο για να μην προκύπτει fragmentation. Έτσι, δεν θα είναι ανάγκη να κάνεις κάθε φορά rebuild/defrag τα indexes. Επίσης, δες και την περίπτωση να γυρίσεις τη βάση σε bulk-logged recovery model ώστε να κάνεις log file backups.

Απ: Πολύ αργό Differential Backup

KelMan — Fri, 27 Oct 2006 20:15:41 GMT

Αν κάνεις rebuild τα indexes τότε η διαδικασία "αγγίζει" σχεδόν όλα τα pages, καθώς επίσης τα clustered indexes και τα non-clustered indexes. Δοκίμασε να κάνεις απλώς defrag τα indexes μεταξύ των differential backups.

Απ: Πολύ αργό Differential Backup

Teo — Fri, 27 Oct 2006 19:11:19 GMT

Κι ένα ακόμη στοιχείο που ίσως βόηθήσει:

Κυριακή:Full Backup 15GB

Δευτέρα:το πρώτο Differential μετά το Full backup έχει μεγεθος 14,48GB

Τρίτη: το δεύτερο differential μετά το full 14,49GB.

Τα δύο differential έχουν πολύ μικρή διαφορά στο μέγεθός τους ενώ απέχουν μεταξύ τους 1 μέρα

Απ: Πολύ αργό Differential Backup

Teo — Fri, 27 Oct 2006 17:54:25 GMT

Ο χρόνος για το backup οφείλεται αποκλειστικά στη USB 1.1 port που έχει το μηχανάκι. Παίρνοντας τοπικό backup κάνει 20-30min για 9GB
Κάθε βράδυ τρέχει maintenance plan που κάνει rebuild όλους του indexes .

Όμως το Differential εξακολουθεί να είναι πολύ μεγάλο σε μέγεθος και δεν μπορώ να καταλάβω το γιατί. Η DBCC SHOWCONTIG στον μεγάλο πίνακα (ο οποίος έχει clustred index μια datetime column-αυτό απαιτούν οι ανάγκες της εφαρμογής-) έδειξε
Pages Scanned................................: 294644
- Extents Scanned..............................: 36838
- Extent Switches..............................: 37131
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 99.19% [36831:37132]
- Logical Scan Fragmentation ..................: 0.45%
- Extent Scan Fragmentation ...................: 7.09%
- Avg. Bytes Free per Page.....................: 738.0
- Avg. Page Density (full).....................: 90.88%

Εκ πρώτης όψεως όλα φαίνονται ok και δεν μπορώ να δικαιολογήσω το μέγεθος του differential backup. Κάμια ιδέα?

KelMan είπες ότι "Όσο μεγαλύτερο το fragmentation τόσο περισσότερες σελίδες έχεις στα indexes, άρα και όγκο δεδομένων, ακόμα και αν χρησιμοποιείς το 3%-4% των εγγραφών". Έχω μόνο insert - delete (τα select δεν επηρεάζουν) σε 4% του μεγέθους του πίνακα και με δεδομένο ότι ο cluster βάζει εγγραφές ανα ημερομηνία (getdate()),όλες οι εγγραφές δεν μπαίνουν στο τέλος του index ? Σβήνω πάντα τις πολύ παλιές (αφού είναι clustered index by datetime- σβήνονται τα πρώτα φύλλα του δέντρου). Με δεδομένο το παραπάνω fragmentation δεν πιστεύεις ότι πρέπει να πάει και να πάρει backup τις πρώτες και τις τελευταίες pages μόνο (τα πρώτα και τα τελευταία φύλλα του δέντρου)? Έχω λάθος?

Κάθε βράδυ, μετά το backup κάνω και update statistics.... Μήπως φταίει αυτό?

Απ: Πολύ αργό Differential Backup

nistam — Fri, 27 Oct 2006 16:47:52 GMT

Πέρα απ'οσα είπαν οι συνάδελφοι, μήπως έχεις δίσκο USB 2.0 συνδεδεμένο σε πόρτα 1.1; Οχι, ότι 15GΒ μεταφέρονται γρήγορα ακόμα και σε 2.0, αλλά σίγουρα, εάν όντως συμβαίνει αυτό που σου λέω, θα βελτιώσεις αρκετά έως ικανοποιητικά την κατάσταση, εάν τον συνδέσεις σε πόρτα 2.0. Οπως είπε και ο papadi, δοκίμασε να κάνεις ένα copy της βάσης που θές να αντιγράψεις, θέτοντάς την πρωτα offline. Η πάρτην backup στο δίσκο και μετά αντίγραψε το αρχείο αυτό για δοκιμή.

Απ: Πολύ αργό Differential Backup

Χρήστος Γεωργακόπουλος — Fri, 27 Oct 2006 16:39:30 GMT

Τρέξε ένα benchmark στους δίσκους, πολλοί usb δίσκοι είναι εξαιρετικά πιο αργοί. Ο χρόνος πάντως μου φαίνεται μεγάλος.

Απ: Πολύ αργό Differential Backup

imanos — Fri, 27 Oct 2006 16:06:45 GMT

Θα μπορούσες να παίρνεις το Backup σε κάποιο σημείο του κανονικού δίσκου αντί του USB και κατόπιν να βάλεις ένα schedule στα Windows που θα κάνει copy και paste το συγκεκριμμένο Backup στον USB δίσκο .

Επίσης αυτό που αναφέρει ο papadi νομίζω ότι είναι πολύ καλό.Προσωπικά αυτό κανω δηλ έχω βάλει στο schedule των Windows να κανει NetStop τον SQL κατόπιν να εκτελείται ένα copy του mdf και ldf σέ ένα δίσκο και τέλος με NetStart επανεκίνηση του SQL.

Απ: Πολύ αργό Differential Backup

KelMan — Fri, 27 Oct 2006 03:37:17 GMT

Δεν μας έχεις δώσει εικόνα του συστήματος. Από μόνα τους τα νούμερα δεν λένε και πολλά... Επίσης, δεν μας είπες, παλιότερα είχες μικρότερο χρόνο στα backups; Έχεις ένα baseline για σύγκριση;

Πάντως, γενικά μιλώντας, μεγάλος όγκος από INSERT συνεπάγεται κατά πάσα πιθανότητα μεγάλο fragmentation. Έχεις τσεκάρει με DBCC SHOWCONTIG τι fragmentation έχει ο πίνακας; Όσο μεγαλύτερο το fragmentation τόσο περισσότερες σελίδες έχεις στα indexes, άρα και όγκο δεδομένων, ακόμα και αν χρησιμοποιείς το 3%-4% των εγγραφών.

Απο εκεί και πέρα, μπορείς να κάνεις διάφορα για να βελτιστοποιήσεις την κατάσταση. Αφ'ενός να οργανώσεις ένα maintenance plan, αφ'ετέρου να προσθέσεις έναν επιπλέον δίσκο και να φτιάξεις filegroup στο οποίο θα βάλεις τον μεγάλο σου πίνακα ώστε να κάνεις backup ανά filegroup.

Απ: Πολύ αργό Differential Backup

Mitsaras — Fri, 27 Oct 2006 03:15:20 GMT

Πήγαινε στα properties του δίσκου από device manager, και άλλαξε το Optimize for

Quick removal σε Optimize for performance.

Απ: Πολύ αργό Differential Backup

Dimitris Papadimitriou — Fri, 27 Oct 2006 02:52:24 GMT

Teo wrote:
Ποιός είναι ο αναμενόμενος χρόνος Full Backup για μια βάση 15GB που δεν έχει traffic τις ώρες του backup? 3Hours δεν είναι πολύ?

Δεν γνωρίζω πολλά για τα υπόλοιπα, αλλά 3 ώρες για 15GB σε USB δίσκο δεν μου φαίνονται και τόσο πολύ. Δοκίμασε να αντιγράψεις ένα αρκετά μεγάλο αρχείο μέσω του windows explorer να δεις πόσο πέρνει και να κάνεις μια εκτίμηση. Π.χ. δοκίμασε να βγάλεις την βάση offline και να αντιγράψεις το mdf ή/και το ldf.

Πολύ αργό Differential Backup

Teo — Thu, 26 Oct 2006 17:44:27 GMT

Έχω μια βάση περίπου 15GB σε SQL 2005.Η βάση αύτή έχει έναν πίνακα ο οποίος καταλαμβάνει το 60-70% του μεγέθους της ο οποίος δέχεται κατά τη διάρκεια της ημέρας μεγάλο όγκο INSERT και SELECT εντολών (περίπου εισάγονται και διαγράφονται εγγραφές της τάξης του 3-4% του μεγέθους του πίνακα). Η βάση είναι σε SIMPLE Recovery Model. Έχω έναν εξωτερικό USB-δίσκο στον οποίο παίρνω backup.

Έφτιαξα 2 SQL Server Integration Services Packages που εκτελούνται βράδυ (η βάση και ο server δεν έχουν activity το βραδυ). Το ένα (μεταξύ των άλλων εργασιών) παίρνει Full Backup την βάση και το αποθηκεύει στον USB δίσκο (κάθε Κυριακή)-->Μέγεθος Backup:13GB-- Διάρκεια του Backup task: 3Hours! Το δεύτερο παίρνει Differential Backup και το το αποθηκεύει στον USB δίσκο (καθημερινά εκτός Κυριακής)-->Μέγεθος Backup:11,5-12GB-- Διάρκεια του Backup task: 2Hours 30minutes!

Το οτι παίρνω Backup σε USB δίσκο και όχι στον εσωτερικό επηρεάζει την ταχύτητα καθοριστικά? Αν ναι, υπάρχουν κάποιες ρυθμίσεις που μπορώ να κάνω?

Για ποιό λόγο το Differential έχει τόσο μεγάλο μέγεθος (προφανώς γι αυτό αργεί....)???

Ήξερα ότι το Differential θα πάρει backup μόνο τις pages που αλλάξανε. Στην βάση μου, ο κύριος πίνακας (=10GB) δεν αλλάζει όλος οπότε θα περίμενα να πάρει backup μόνο τις pages που προστέθηκαν (ή/και διαγράφηκαν)΄? Είμαι λάθος? Μήπως παίρνει backup όλες τις pages του πίνακα ?

Ποιός είναι ο αναμενόμενος χρόνος Full Backup για μια βάση 15GB που δεν έχει traffic τις ώρες του backup? 3Hours δεν είναι πολύ?