go backarticles

Articles of SQLschool.gr Team

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

Antonios Chatzipavlis

Η Ερώτηση

Αυτές τις ημέρες κατά την διάρκεια ενός σεμιναρίου που κάνω και αφορά το 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.

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.


Relative Articles

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.