Κάνοντας Performance Analysis στον Production DB Server (SQL Server 2005-Βάση κάποιοων δεκάδων GB) μέρτησα μεταξύ των άλλων και το SQL Server Buffer Cache Hit Ratio. Ο επίσημος ορισμός του Buffer Cache Hit Ratio με βάση το msdn είναι ο ακόλουθος:

"Percentage of pages found in the buffer cache without having to read from disk. The ratio is the total number of cache hits divided by the total number of cache lookups since an instance of SQL Server was started. After a long period of time, the ratio moves very little. Because reading from the cache is much less expensive than reading from disk, you want this ratio to be high. Generally, you can increase the buffer cache hit ratio by increasing the amount of memory available to SQL Server.
"

Παρόλο που βρήκα Buffer Cache Hit Ratio για το σύστημα μου πάνω από 95% εντούτοις αναρωτιέμαι, με βάση τον ορισμό, πότε ο SQL Server κάνει cache lookups ?

Πώς αποφασίζει ποιά data pages θα βάλει στην cache?

Στο σύστημα έχω πίνακες οι οποίοι αθροιστικά έχουν μέγεθος πάνω από GB και στους οποίους κάνω πολύ συχνά select. Μετρώντας και counters της μνήμης (3GB Server RAM) βρήκα ότι υπάρχει πάρα πολύ διαθέσιμη μνήμη και περισσεύει.

Ήξερα ότι ο SQL Server όταν ψάχνει μια data page την ψάχνει πρώτα στην cache και μέτά πάει στο δίσκο.
Αφού όμως Buffer Cache Hit Ratio=cache hits/cache lookups και οι heavy-traffic πίνακες μου είναι πάρα πολύ μεγάλοι σε μέγεθος, πώς μπορώ να έχω τόσο μεγάλη τιμή για τον counter? Μήπως αυτοί δεν κρατιούντια στην cache?

Εφόσον ο counter αυτός κρατιέται γύρω στο 95% τότε το caching δουλεύει μια χαρά.

Τώρα, το caching αυτό γίνεται από έναν μηχανισμό που λέγεται read-ahead ο οποίος βάζει στην cache αυτά που πρόκειται να ζητήσεις. Δεν βάζει τα πάντα ώστε να χρειάζεσαι μνήμη ίσα με το μέγεθος της βάσης, απλά μαντεύει τι θα ζητήσεις βάσει του query που στέλνεις. Εκεί είναι και η όλη "μαγκιά" του μηχανισμού (ο οποίος τρέχει σε διαφορετικό thread). Να μαντεύει σωστά.

Μπορείς να δεις αυτή τη συμπεριφορά όταν τρέξεις την εντολή SET STATISTICS ΙΟ μέσα από τον query analyzer και κατόπιν δώσεις τα queries που σε ενδιαφέρουν. Θα παρατηρήσεις ότι το output θα είναι κάτι του στυλ

Table xxxx. Scan count A, logical reads B, physical reads C, read-ahead reads D.

Αρχικά, διάβασε στα Books On Line για το τι κάνει η SET STATISTICS ΙΟ. Κατόπιν παίξε λίγο γράφοντας μερικά queries να δεις πως συμπεριφέρεται ο SQL Server. Γράψε το ίδιο query δύο φορές να δεις πάλι τι γίνεται. Ανα πάσα στιγμή, μπορείς να κάνεις reset την cache με τις εντολές DBCC FREEPROCCACHE και DBCC DROPCLEANBUFFERS (προσοχή γιατί στο production σύστημα, ειδικά αν έχεις μεγάλους πίνακες με πολλά data, οι χρήστες θα δουν καθυστερίσεις στην εκτέλεση των queries τους).

---

Vir prudens non contra ventum mingit