sqlschool.gr logo

articles

Articles of SQLschool.gr Team

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

Antonios Chatzipavlis
Thursday 24 May 2012

Η Ερώτηση

Αυτές τις ημέρες κατά την διάρκεια ενός σεμιναρίου που κάνω και αφορά το 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 Chatzipavlis is a highly experienced Data Solutions Consultant and Trainer. He has been working in the IT industry since 1988, holding various roles such as senior developer, IT Manager, Data & AI Solutions Architect and Consultant.

Since 1995, Antonios has focused on modern technologies and software development tools, primarily by Microsoft. He has specialized in Data & AI since 2000, with expertise in Microsoft Data Platform (SQL Server, Azure SQL Databases, Azure Synapse Analytics, Microsoft Fabric, Power BI, AI) and Databricks.

Antonios is also a Microsoft Certified Trainer (MCT) for over 25 years, has been recognized as a Microsoft Most Valuable Professional (MVP) in Data Platform since 2010 and he is in the Data Expert 40 Powerlist 2024 by Boussias. He is the co-founder and visionary behind XLYTiCA, a company dedicated to Data & AI solutions.

Episode

Task Flows in Microsoft Fabric

image

More Episodes...

Tip

What's New in SQL Server 2022 - Episodes

More Tips...

Become a member

If you want to receive updates from us become a member to our community.

Connect

Explore

Learn


sqlschool.gr © 2010-2025 All rights reserved

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