Memory
is one of the important resources for any database management system and for
any IT service in general and controlling this critical resource is one of any
DBA major challenges.
Most
of database management systems has a parameter or more to define the its share
from the total amount of server installed memory, this maintain a hard limit to
the DBMS that should not exceed and the rest should be used for the operating
system itself “in case that there is no other software installed like web
services for example and this–by default–the best practice for a production
environment.”
This
parameter is very useful for defining a hard limit for the memory the database
case use and assign the rest for the OS operations in order to maintain the
server performance but it is useless for measuring the amount of memory
utilized, this is because the simple fact that this is a static value and in
order to measure the memory and its peaks we need another figure to be
considered which it the buffer pool utilization.
The
buffer pool is the area inside SQL Server memory that used for hosting data and
indexes pages for both reading and writing operations and represent about 99%
of the total memory of SQL Server, by measuring the buffer pool consumption we
can then measure the actual memory utilization for SQL Server instance.
The
below snapshot shows the difference between the SQL Server memory utilization
“as a whole” and the buffer pool utilization.
While
the red line represent the memory utilization the blue one represents the
buffer pool utilization, as we can see the total memory utilization is always
the same because it is a static value, while the buffer pool utilization
changes by the reading/writing operations activities on the system and that is
why we find the utilization during some points reached the total assigned
memory and sometimes else it decreased below.
The
next step will be how to collect the memory and buffer pool utilization figures
and this will be explained deeply in the next post.