In this series I am going to cover the feature Buffer pool extension in MS SQL Server. Now before we can discuss this feature it might be a good idea to define what a buffer pool is and why it needs extensions in the first place. The buffer pool is that part of the RAM in which the data is stored. The idea here is that RAM is divided into many parts when used by SQL Server. One such part is the buffer pool used to store data , other parts include RAM space allocated for CLR , XML , Full text Indexes , DBCC utilities etc. But the buffer pool is by far the largest of the groups since this is the par the SQL needs the most.
A common misconception among Windows Admins is that it’s a bad thing when SQL uses 80% of the RAM, this is counter intuitive to what Windows admins see with other applications which use just enough RAM to do what is needed. SQL however works differently and therefore tries to use as much RAM as possible since the more data in the RAM the faster the query execution and the fewer I/O to the slower disk subsystem.
So where do you configure how much RAM is used by Buffer pool?
The min and max memory setting under the memory tab of SQL Server properties actually configures the size of only the buffer pool. This is commonly misunderstood to be the total SQL Server memory which it is not.
In this series I am going to configure my Buffer poo to use a total of 2GB RAM, which is sufficient to demonstrate the behaviour of SQL server when it runs out of RAM. To be clear I am running this on a 64 bit system and PAE/AWE do not apply.
So I set the MIN and MAX memory setting of my SQL instance to 2048 MB i.e. 2GB
Next I create a dummy data table with a varchar column for 4000 bytes and populate it data up to 2GB.
More in the next part of series.
Please Consider Subscribing
