Jump to content

Αδικαιολόγητο μέγεθος βάσης δεδομένων


julax
 Share

Recommended Posts

2 η ώρα θα είμαι γραφείο μέχρι της 2:30 θα έχω κάνει relpy.

Βρε φωτιά που μας άναψε ο julax. Απλά έβγαλε στην επιφάνεια ένα παρόμοιο πρόβλημα που αντιμετωπίζω και σε δικές μας db's. Εμείς έχουμε replication και τεράστια transation logs. Επίσης το παρατήρησα και στη βάση του SCServiceManager.

Link to comment
Share on other sites

  • Replies 67
  • Created
  • Last Reply

Top Posters In This Topic

Να ρωτήσω κάτι ? η εντολή CREATE DATABASE T1 ( f1 INT IDENTITY, f2 CHAR(8000) ) είναι σωστή? το λέω γιατί λες να φτιάξω πίνακα.... έτρεξα το script αλλαγμένο σε CREATE table T1 ( f1 INT IDENTITY, f2 CHAR(8000) ).

 

Link to comment
Share on other sites

Το έγραψε απο κεκτιμένη ταχύτητα μάλλον. Πολύ σωστά έκανες

9.     Εκτέλεσε ξανά την sp_spaceused πάνω σε αυτή την βάση και δες το πόσοστο του unallocated space, θα δεις ότι έχεις πλέον ελεύθερα τα 20ΜΒ που ήταν δεσμευμένα από τον Τ2.

Σωστά<?xml:namespace prefix = o />

10.  Εκτέλεσε την εντολή SELECT avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats(DB_ID('test'),OBJECT_ID('T1'),1,NULL,NULL) η οποία θα σου δείξει το ποσοστό που ο clustered index είναι fragmented και θα πρέπει να δεις ότι είναι κάτω από 1%

Σωστά 0,433682688832671

11.  Τώρα κάνε shink database DBCC SHRINKDATABASE(test)

test // db size =23.69 MB / unallocate space =0.23 MB

12.  Και ξαναεκτέλεσε την SELECT avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats(DB_ID('test'),OBJECT_ID('T1'),1,NULL,NULL), θα δεις ότι υπάρχει μια διαφορά αλλά είναι μικρή και βέβαιαείναι πάλι κάτω από 1% σωστά;

95,8077340079509

 
Link to comment
Share on other sites

Στο βήμα 4 τον πίνακα τον γέμισα με το script
DECLARE @count int
SELECT @count =2560
WHILE @count > 0
BEGIN
SELECT @count=@count-1
INSERT INTO T1(f2) VALUES('APOSTOLOS APOSTOLOS APOSTOLOS')
END

 

Εαν γυρίσεις σε SQLCMD mode το query  δεν χρειάζεται να κάνεις loop

Link to comment
Share on other sites

ΟΚ  Απόστολε ...

Τώρα κάνε το εξής (επαναλαμβάνω όλα τα βήματα για καθαρό λόγους ευκολίας στην ανάγνωση):

1.     Φτιάξε μια νέα βάση - CREATE DATABASE test2

2.     CREATE ΤΑΒLE T2 ( f1 INT IDENTITY, f2 CHAR(8000) )

3.     INSERT INTO T2(f2) VALUES('APOSTOLOS APOSTOLOS APOSTOLOS') GO 2560

4.     CREATE ΤABLE T1 ( f1 INT IDENTITY, f2 CHAR(8000) )

5.     CREATE CLUSTERED INDEX CI_T1_F1 ON T1 (f1)

6.     INSERT INTO T1(f2) VALUES('APOSTOLOS APOSTOLOS APOSTOLOS') GO 2560

7.     Εκτέλεσε την sp_helpdb test2 και θα δεις ότι έχεις μια βάση που θα είναι μαζί με το log περίπου 43ΜΒ.

8.     Εκτέλεσε την sp_spaceused πάνω σε αυτή την βάση και δες το πόσοστο του unallocated space, σημείωσε το.

9.     Σβήσε τον πίνακα Τ2 - DROP TABLE T2

10.  Εκτέλεσε ξανά την sp_spaceused πάνω σε αυτή την βάση και δες το πόσοστο του unallocated space, θα δεις ότι έχεις πλέον ελεύθερα τα 20ΜΒ που ήταν δεσμευμένα από τον Τ2.

11.  Εκτέλεσε την εντολή SELECT avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats(DB_ID('test2'),OBJECT_ID('T1'),1,NULL,NULL) η οποία θα σου δείξει το ποσοστό που ο clustered index είναι fragmented και θα πρέπει να δεις ότι είναι κάτω από 1%

12.  Τώρα κάνε shink database DBCC SHRINKDATABASE(test2)

13.  Και ξαναεκτέλεσε την SELECT avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats(DB_ID('test2'),OBJECT_ID('T1'),1,NULL,NULL), θα δεις ότι υπάρχει μια διαφορά αλλά μεγάλη πολύ μεγάλη αυτή την φορά σωστά;

Επιβεβαίωσε μας ότι έτσι όπως τα λέω σε παρακαλώ και ρωτάω εγώ τώρα γιατί έγινε αυτό και τι σημαίνει αυτό;

Link to comment
Share on other sites

Λοιπόν ας δούμε τι κάναμε.

Στην πρώτη περίπτωση ο clustered index και τα δεδομένα υπήρχαν, φτιάξαμε ένα δεύτερο πίνακα το γεμίσαμε, τον στείλαμε κουβά και όλα οκ με τον index.

Στην δεύτερη περίπτωση είχαμε τους πίνακες χωρίς δεδομένα, τους γεμίσαμε, στείλαμε τον ένα πίνακα κουβά και μετά το shrink ανατινάχτηκε(fragmented 99%) ο index του άλλου πίνακα.

Χμμμμμμμ.

Μια γρήγορη παρατήρηση είναι ότι έχει bug ο Sql Server γιατί indexes που ανήκουν σε άσχετο table επηρεάζονται απο διαγραφές άλλων πινάκων μετά το shrink.

Μήπως αυτό γίνεται λόγο ότι μοιράζονται το ίδιο datafile indexes και δεδομένα? Έχουμε δηλαδή το φαινόμενο του fragmentation που γίνεται και στο FAT? Αν ναι τότε η μόνη λύση είναι αυτό που πρότεινες για ξεχωριστό datafile. Μήπως όμως πρόκεται για προβληματική συμπεριφορά?

Link to comment
Share on other sites

Σε ευχαριστώ για την διόρθωση, πάμε τώρα στο προκείμενο.

Στο 1ο πείραμα που σε έβαλα να κάνεις υπήρχε ένα τρικ. Πρώτα φτιάχθηκε ο Τ1 και μετά ο Τ2 ο οποίος όπως κατάλαβες παίζει το ρόλο του να μεγαλώσει την βάση και μετα σβήνοντας τον να δημιουργεί ο κένος χώρος ώστε να έχει νόημα το shrink. Όμως επειδή έγινε μετά τον δεύτερο θεωρητικά οι σελίδες του είναι προς το τέλος του αρχείου. Άρα διαγράφωντας τον στην ουσία στο τέλος είχαμε το κενό. Όπως είπα και πριν η DBCC SHRINKDATABASE, DBCC SHRINKFILE αλλά και το AUTO_SHRINK στην βάση είναι per file operation και χρησιμοποιούν την GAM page ώστε να βρούν την τελευταία γεμάτη σελίδα (το λέω αρκετά χοντροκομμένα ώστε να είναι εύκολο στην κατανόηση διότι είχα παράνονα όπως το περιέγραψα πριν). Αυτό σημαίνει ότι ήταν αρκετά εύκολη υπόθεση το όλο θέμα και δεν χρειάστηκε να κάνει πολλές επαναλαμβανόμενες αναζητήσεις μέσα στη GAM και να αλλάζει θέσεις σελίδων (εφόσον χρειαζεται να γίνει).  Θα το έλεγα ιδανικό σενάριο.

Στο 2ο πείραμα όπως τα πράγματα ήταν διαφορετικά. Σε έβαλα πρώτα να φτιάξει και να γεμίσεις τον Τ2 μετά να φτιάξεις και να γεμίσεις τον Τ1 που είχε και τον index. Αυτό σημαίνει ότι εσκεμένα πλέον όταν σβήσαμε τον Τ2 το κενό ήταν στην αρχή του αρχείου. Με τον τρόπο που δουλεύει το shrink με τη λογική της GAM είδε και τα index pages τα οποία έπρεπε να "μαγειρευτούν" (ας μου επιτραπεί η λέξη για λόγους απλότητας και πάλι) με αποτέλεσμα ο index να την ακούσει στερεοφωνικά. Δηλάδη να γίνει fragmented που σημαίνει και περισσότερες σελίδες μιας και δεν είχαμε αλλάγη στα δεδομένα μας. Για το παράδειγμα μας μειώθηκε κατά ελάχιστο η βάση αλλα σαν αποτέλεσμα είχαμε ένα παντελώς άχρηστο index. Κάτι ανάλογο έπαθε και φίλος μας για αυτό και είχε μεγαλύτερο μέγεθος βάσης από την κανονική του όπου πρέπει να επισημάνω ότι είχε την βάση του σε AUTO_SHRINK που είναι ακόμα χειρότερο μιας και δε σου δίνεται ποτέ η δυνατότητα να κάνει ταυτόχρονα και reorganize τον index. Εσύ σωστά το υποπτεύθηκες όπως και εγώ για αυτό και του ζήτησα να περιμένει και να μην κάνει τίποτα. Απλά τον έβαλα να κάνει αυτά που του ζήτησα διότι ήθελα μέσα από τις πληροφορίες αυτές να έχω την πλήρη εικόνα, αλλά και για να τεκμιριώσω τα λεγόμενα μου.

Η λύση που του πρότεινα να φτιάξει ένα άλλο filegroup και να μεταφέρει εκεί τον πίνακα και τους index αυτού είναι όπως μάλλον έχεις ήδη καταλάβει για να κάνω εξομιώση το 1ο πείραμα. Φυσικά αυτό απαιτεί σχεδιασμό ή να το πω καλύτερα επαναπροσδιορισμό της δομής της βάσης.

Το resume της όλης ιστοριάς είναι, για να μην μακρηγορώ, ότι καλό θα είναι να μη χρησιμοποιούμε ποτέ μα ποτέ (όποιο και να είναι το κόστος) τις DBCC SHRINKDATABASE, SHRINKFILE και το AUTO_SHRINK option σε μια βάση (ειδικά αυτό μακριά και αγαπημένοι), διότι όπως είδες μας κάνουν τους indexes σχεδόν 100% fragmented.

Τώρα αν αυτό είναι bug ή όχι πραγματικά δεν ξερω πως να το χαρακτηρίσω σε κάθε περίπτωση είναι θέμα...

Φιλικά 

 

Link to comment
Share on other sites

Εσύ είσαι διπλά τυχερός. Θα γλιτώσεις και χώρο αλλά θα κερδίσεις και performance.

θα κάνεις reorganize τους index και στο καπάκι shrink. Αυτό θα σου γλιτώσει χώρο αλλά θα καταλήξεις πάλι σε fragmented indexes. Οπότε ξανά reorganize.

Στη demo βάση που κάναμε για το test πήρα τα εξής αποτελέσματα. Aπό το αρχικό 44ΜΒ που είχαμε έπεσε στο 22,13 με 0,17ΜΒ ελεύθερο χώρο και fragmented index στο 99%. Reorganize και 0,24ΜΒ ελεύθερο χώρο και ~,4 fragmented. Σε αυτό το μέγεθος της βάσης δεν είναι και τόσο μεγάλη η διαφορά. Στην δικιά σου που είναι 35GB κάτι θα γλιτώσεις παραπάνω.

Link to comment
Share on other sites

ελπίζω να σας άρεσε...

και για άλλη μια φορά θα πω χαίρομαι που ασχολούμαι μαζί του (για SQL Server μιλάω) 14 χρόνια τώρα γεμίζει εύκολα τα βράδια μου με τέτοια θέματα...

Link to comment
Share on other sites

 Share


×
×
  • Create New...