go backarticles

Articles of SQLschool.gr Team

Estimate Database Compression Stored Procedure (sp_estimate_db_compression)

Antonios Chatzipavlis

Τις τελευταίες ημέρες για ένα περίεργο λόγο συνάντησα αρκετές φορές την ανάγκη να πρέπει να εκτιμηθεί η δυνατότητα να γίνει compress μια database. Για το λόγο αυτό αποφάσισα να φτιάξω μια stored procedure που να κάνει όλα όσα ήθελα να γίνονται με την εκτέλεση της. Μετά από μερικές μέρες δουλειάς και αρκετές αλλαγές πάνω σε αυτή το τελικό αποτέλεσμα μπορείτε να το βρείτε εδώ.

Όμως πριν αρχίσετε να την χρησιμοποιείται νομίζω ότι είναι σκόπιμο να διαβάσετε το documentation αυτής και τις δυνατότητες που παρέχει.

Πριν όμως το κάνω αυτό θα πρέπει να επισημάνω ότι το database compression είναι ένα feature το οποίο εμφανίστηκε στον SQL Server 2008 Enterprise Edition άρα η stored procedure αυτή μπορεί να δουλέψει από αυτή την έκδοση και πάνω, δηλαδή 2008, 2008R2 και 2012.

Επίσης ο χρήστης που θα την εκτελέσει θα πρέπει να έχει δικαιώματα sysadmin.

Syntax

sp_estimate_db_compression

                                                   [data]

                                                 , [mode]

                                                 , [scripting]

                                                 , [scripting_threshold]

Arguments

data’

Με την παράμετρο αυτή ορίζουμε την συμπεριφορά που θέλουμε ως προς τα συλλεγόμενα δεδομένα. Είναι nvarchar(20) και η default τιμή της είναι NONE.

Οι τιμές που αυτή μπορεί να πάρει είναι

ΝΟΝΕ

Είναι η default τιμή. Εάν ο πίνακας στον οποίο αποθηκεύονται τα δεδομένα ([dbo].[dbCompressionEstimationData]) είναι άδειος τότε γίνεται συλλογή δεδομένων.

Αλλιώς χρησιμοποιούνται τα προηγουμένως συλλεγμένα δεδομένα.

ΙΝΙΤ

Με την τιμή αυτή στην παράμετρο σβήνονται τα υφιστάμενα δεδομένα του πίνακα ([dbo].[dbCompressionEstimationData]) και γίνεται ξανά συλλογή δεδομένων.

DROP

Με αυτή την τιμή γίνεται ολική διαγραφή του πίνακα ([dbo].[dbCompressionEstimationData]) και δεν συνεχίζει η εκτέλεση της stored procedure.

mode’

Η παράμετρος αυτή ορίζει το είδος της πληροφορίας που θέλουμε να πάρουμε. Είναι nvarchar(20) και η default τιμή της είναι DEFAULT. Οι τιμές που αυτή δέχεται είναι

DEFAULT

Eίναι η default τιμή. Επιστρέφει συνολικά την εκτίμηση του compression για όλη την database σε όλους τους δυνατούς τρόπους (row, page, none compression) μαζί με τα ποσοστό κέρδους σε χώρο για τον καθένα.

SCHEMA

Επιστρέφει συνολικά την εκτίμηση του compression για τα schemas της database σε όλους τους δυνατούς τρόπους (row, page, none compression) μαζί με τα ποσοστό κέρδους σε χώρο για τον καθένα.

TABLE

Επιστρέφει συνολικά την εκτίμηση του compression για tables της database σε όλους τους δυνατούς τρόπους (row, page, none compression) μαζί με τα ποσοστό κέρδους σε χώρο για τον καθένα.

INDEX

Επιστρέφει συνολικά την εκτίμηση του compression για τους index όλων των πινάκων της database σε όλους τους δυνατούς τρόπους (row, page, none compression) μαζί με τα ποσοστό κέρδους σε χώρο για τον καθένα. Θα πρέπει να αναφερθεί ότι συμπεριλαμβάνονται και οι περιπτώσεις που ο πίνακας είναι HEAP.

PARTITION

Επιστρέφει συνολικά την εκτίμηση του compression για τους index όλων των πινάκων της database αλλά ανά partition σε όλους τους δυνατούς τρόπους (row, page, none compression) μαζί με τα ποσοστό κέρδους σε χώρο για τον καθένα. Θα πρέπει να αναφερθεί ότι συμπεριλαμβάνονται και οι περιπτώσεις που ο πίνακας είναι HEAP. Επίσης στο συγκεκριμένο display view γίνεται και η εκτίμηση για το ιδανικό compression με βάση το ποσοστό κέρδους σε χώρο του αντικειμένου.

scripting’

Με την παράμετρο αυτή δίνεται η δυνατότητα να παραχθούν τα scripts για την εφαρμογή κάποιου είδους compression. Οι τιμές που αυτή δέχεται είναι:

DEFAULT

Είναι η default τιμή και δεν παράγει κανενός είδους scripting.

ALL

Παράγει script για όλα τα objects και για όλες τις μεθόδους compression. Δεν ελέγχει την βέλτιστη μεθοδολογία.

ROW

Παράγει script για όλα τα objects και για row compression. Δεν ελέγχει την βέλτιστη μεθοδολογία.

PAGE

Παράγει script για όλα τα objects και για page compression. Δεν ελέγχει την βέλτιστη μεθοδολογία.

NONE

Παράγει script για όλα τα objects και για none compression. Δεν ελέγχει την βέλτιστη μεθοδολογία.

RECOMMENDED

Παράγει script για όλα τα objects και για την βέλτιστη μέθοδο compression ανά object.

THRESHOLD

Παράγει script για την βέλτιστη μέθοδο compression, αλλά μόνο για αυτά τα objects που το κέρδος είναι πάνω από το όρισμα της scripting_threshold παραμέτρου.

scripting_threshold’

Δηλώνει το ποσοστό κέρδους σε χώρο πάνω από το οποίο θέλουμε να κάνουμε compression. Χρησιμοποιείται σε συνδυασμό με την παράμετρο scripting όταν αυτή έχει την τιμή THRESHOLD και το συνδυαστικό τους αποτέλεσμα είναι να δημιουργηθούν τα scripts για τα objects που έχουν κέρδος πάνω από αυτό που έχει ορισθεί σε αυτή την παράμετρο για το καλύτερο σε εξοικονόμηση χώρο compression method.

H default τιμή της παραμέτρου αυτής είναι 25%.

Παρατηρήσεις

Η stored procedure αυτή δημιουργεί ένα πίνακα στην database τον [dbo].[dbCompressionEstimationData] και ο λόγος που αυτό έγινε είναι καθαρά για performance για τις επόμενες εκτελέσεις της. Θα πρέπει όμως να τονίσω ότι τα δεδομένα που κατά την πρώτη εκτέλεση μαζεύονται σε αυτόν είναι αυτά που υπάρχουν κατά την χρονική στιγμή που αυτή εκτελείται. Αν οι επόμενες εκτελέσεις γίνουν άμεσα οι πληροφορίες που θα ανακτηθούν θα είναι σωστές, αν όμως έχει περάσει κάποιο χρονικό διάστημα από την αρχική εκτέλεση και η ποσότητα των δεδομένων έχει αλλάξει σημαντικά θα πρέπει να γίνει ξανά συλλογή των δεδομένων. Αυτό μπορεί να γίνει αν στην παράμετρο @data δώσω την τιμή ΙΝΙΤ.

Στην περίπτωση που δεν υπάρχει όφελος από κάποιο row ή page compression αλλά το none έχει βρει ότι λόγο κάποιου fragmentation μπορεί να εξοικονομηθεί χώρος επιστρέφει στην πρόταση NONE (DEFRAG). Σε κάθε όμως περίπτωση θα πρέπει να δείτε το FillFactor καθώς κατά την εκτίμηση δεν λαμβάνεται υπόψη αυτό καθώς συμπεριλαμβάνεται στο μέγεθος του αντικειμένου.

Όλα τα παραγόμενα scripts μπορείτε να τα κάνετε copy paste από το Message tab του Query windows που έχετε κάνει την εκτέλεση της stored procedure

Παράδειγμα εκτέλεσης

exec sp_estimate_db_compression    
                  @data =N'NONE'                
                , @mode =N'DEFAULT'                
                , @scripting =N'DEFAULT'        
                , @scripting_threshold =25.0    

Antonios Chatzipavlis

Antonios Chatzipavlis

Antonios is a Data Solutions Consultant and Trainer. He has been working in IT since 1988. In his career, he has worked as senior developer, IT Manager, Solutions Architect and IT Consultant. Since 1995 he has been devoted on new technologies and software development tools, mainly by Microsoft, either by training company staff and colleagues or assisting them in design, development and implementation as a consultant or chief developer. He has focused in Databases and Data Science since 1995. He specialized in Microsoft SQL Server since version 6.0 in areas like SQL Server Internals, Database Design and Development, Business Intelligence and in 2010 he has started working with Azure Data Platform, NoSQL databases, Big Data Technologies and Machine Learning. He is an active member of many IT communities in Greece, answering colleagues' questions and writing articles in his web site. He is the owner of SQLschool.gr which is a community portal with a lot of information about Microsoft SQL Server. He has been a Microsoft Certified Trainer (MCT) since 2000. Microsoft honored him as MVP on Data Platform due to his activities in SQL Server since 2010. He holds a large number of Microsoft Certifications and Microsoft SQL Server Certifications since version 6.5.


Leave your comment

COMMENT

FULL NAME

EMAIL ADDRESS

We use Gravatar

WEB SITE



captcha


 

Newsletter

If you want to receive updates from us subscribe below with your email.
Follow us in
PASS chapter logo

The Official PASS Local Group for Greece

About us Contact us Terms of Use Privacy Sing in Register
sql school greece logo
© 2010-2020 All rights reserved

This site uses cookies for operational and analytics purposes only. By continuing to browse this site, you agree to their use.