What is the buffer cache usage for each database in a SQL Server instance?

Η Ερώτηση

Αυτές τις ημέρες κατά την διάρκεια ενός σεμιναρίου που κάνω και αφορά το administration του SQL Server ένα συνάδελφος είχε μια ερώτηση και αυτή ήταν

«Πώς μπορώ να δω το χώρο που καταναλώνουν από την buffer cache οι βάσεις που έχω σε ένα SQL Server instance;»

Μια εύκολη στην απάντηση ερώτηση αλλά πάντα μου αρέσει να ζητώ από αυτόν που ρωτάει το λόγο για τον οποίο θέλει αυτό που ζητάει καθώς θέλω να αποκλείσω την πιθανότητα να ζητάει άλλα και να θέλει άλλα. Σε αυτή την περίπτωση ο συνάδελφος ήταν εξαιρετικά συνεπής και με συγκροτημένη σκέψη. Η απάντηση του ήταν η εξής:

«Το ζητάω αυτό καθώς έχω ένα SQL Server instance το οποίο έχει αρκετές databases και θέλω να δω ποιες είναι αυτές που έχουν μεγάλες απαιτήσεις μνήμης ώστε να τις μεταφέρω σε κάποιο άλλο SQL Server instance ή να βάλω κάθε μία σε δικό της αν δω ότι είναι απαιτητικές πολύ, ώστε να βελτιωθεί η απόδοση όλων.»

Εξαιρετική απάντηση!. Βέβαια αυτή η απάντηση είχε έρθει αφού πρώτα είχε γίνει εκτενείς αναφορά για την σχέση του SQL Server με την μνήμη και την buffer cache που αυτός έχει και τον τρόπο με τον οποίο ανεβοκατεβαίνουν οι σελίδες της κάθε database σε αυτή.

Η ερώτηση απαντήθηκε αλλά επειδή δεν έγραψε το script του υποσχέθηκα ότι θα του το κάνω post ώστε να το έχει αυτός και όλοι όσοι θα ήθελαν να δουν κάτι παρόμοιο.

Η Απάντηση

Το script που δίνει την απάντηση στο ερώτημα αυτό είναι το παρακάτω αλλά σας το δίνω σε δύο εκδόσεις καθώς στον SQL Serve 2012 έχει αλλάξει η δομή της sys.dm_os_sys_info σε σχέση με τις προηγούμενες εκδόσεις.

SQL Server 2008 & 2008 R2

WITH CachedPages

AS       (SELECT   DB_NAME(database_id) AS database_name,

                   COUNT(*) AS cached_pages,

                   (COUNT(*) * 8192.0 / 1024.0) / 1024.0 AS cached_size_mb

          FROM     sys.dm_os_buffer_descriptors CROSS JOIN sys.dm_os_sys_info

          WHERE    database_id > 4 -- exclude system databases

                   AND database_id <> 32767 -- exclude ResourceDB

          GROUP BY DB_NAME(database_id)),

BufferCacheTotal

AS       (SELECT ((bpool_committed * 8192.0) / 1024.0) / 1024.0 AS buffer_cache_size_mb

          FROM   sys.dm_os_sys_info)

SELECT   database_name,

         cached_pages,

         cached_size_mb,

         (100 * cached_size_mb) / buffer_cache_size_mb AS buffer_cache_pct_usage

FROM     CachedPages AS P CROSS APPLY BufferCacheTotal AS B

ORDER BY cached_size_mb DESC;

SQL Server 2012

WITH CachedPages

AS       (SELECT   DB_NAME(database_id) AS database_name,

                   COUNT(*) AS cached_pages,

                   (COUNT(*) * 8192.0 / 1024.0) / 1024.0 AS cached_size_mb

          FROM     sys.dm_os_buffer_descriptors CROSS JOIN sys.dm_os_sys_info

          WHERE    database_id > 4 -- exclude system databases

                   AND database_id <> 32767 -- exclude ResourceDB

          GROUP BY DB_NAME(database_id))

SELECT   database_name,

         cached_pages,

         cached_size_mb,

         (100 * cached_size_mb) / (B.committed_kb / 1024.0) AS buffer_cache_pct_usage

FROM     CachedPages AS P CROSS APPLY sys.dm_os_sys_info AS B

ORDER BY cached_size_mb DESC;

Η Επεξήγηση της απάντησης

Στον SQL Server υπάρχει η sys.dm_os_buffer_descriptors η οποία επιστρέφει αναλυτικά ανά σελίδα αυτές που είναι την στιγμή που ζητάς να δεις τα δεδομένα στην μνήμη. Αν απλά κάνεις ένα COUNT aggregation σε αυτή με grouping με βάση το database_id πεδίο που υπάρχει έχεις αυτό που ζητάς (cached_pages στο τελικό αποτέλεσμα). Αλλά επειδή πιθανόν να θέλουμε να το δούμε σε MB θα πρέπει να κάνεις ένα απλό υπολογισμό καθώς γνωρίζουμε ότι κάθε σελίδα στο SQL Server είναι 8Kb (cached_size_mb στο τελικό αποτέλεσμα). Και επειδή πιθανότατα να θέλεις να δεις και το ποσοστό που καταλαμβάνουν τα MB αυτά σε σχέση με το δεσμευμένο την δεδομένη χρονική στιγμή ποσό για την buffer cache απλά ρωτάς την sys.dm_os_sys_info. Σε αυτή και για τις εκδόσεις πριν το SQL Server 2012 υπάρχει το πεδίο bpool_committed το οποίο μου επιστρέφει το χωρό αυτό, αλλά είναι σε σελίδες των 8Kb και με ένα απλό υπολογισμό μπορούμε να το μετατρέψουμε σε MB. Στον SQL Server 2012 επειδή όπως είπα έχει αλλάξει η sys.dm_os_sys_info αντί για το πεδίο που αναφέρθηκε υπάρχει πλέον το committed_kb το οποίο επιστρέφει το δεσμευμένο χώρο αλλά σε Kb όπου με τον απαραίτητο μηχανισμό μετατροπής γίνεται σε MB και υπολογίζεται το ποσοστό που ζητάμε (buffer_cache_pct_usage στο τελικό αποτέλεσμα).

Απλά μια τελευταία επεξήγηση που θα ήθελα να δώσω είναι στο αποτέλεσμα δεν συμπεριλαμβάνονται οι system databases. Όποιος θα ήθελε να βλέπει και αυτές το μόνο που έχει να κάνει είναι να βγάλει το  WHERE database_id > 4  AND database_id <> 32767

Επίλογος

Τελικά για ακόμα μια φορά βλέπουμε ότι όλα μπορούμε να τα βρούμε στον SQL Server.