Hi Folk,
Can anyone put some light on the differences of bufferpool extension and pagefile in sql server?
Thanks
Chirag
Hi Folk,
Can anyone put some light on the differences of bufferpool extension and pagefile in sql server?
Thanks
Chirag
Hi ChiragSachdeva-6150,
Buffer Pool Extension allows the buffer pool to retain more “buffers”, avoiding an extra paging activity. Basically, the Buffer Pool Extension will be another level of cache. Not so efficient as the memory – in the end of the day this is a SSD disk, not a RAM memory – but way better than access the disk to get the pages. The buffer pool extension provides the seamless integration of a nonvolatile random access memory (that is, solid-state drive) extension to the Database Engine buffer pool to significantly improve I/O throughput.
The page file is located on the disk, and when there is not enough physical memory left, operating system can use the page file and move data from the physical memory to it. When it will need that data, it will have to read it from the disk. The problem is that disk is a lot slower than the physical memory. If SQL Server's cache will be stored on the disk instead of the on the physical memory, we will have a huge performance issues.
Please refer to Buffer Pool Extension (BPE) and Page File for SQL Server which might help.
Best Regards
Amelia
If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
You mean a spinning disk? I don't know if SQL Server performs an explicit check, but in any case it would be utter madness. The idea with a cache is to have data quickly available - a spinning disk is the antitheses of that.
Are you having an actual problem or just asking a general question?
You are confusing 2 completely different concepts. Pagefile is a Windows solution for getting more RAM. Buffer Pool is SQL Server's cache for database objects.
pagefile and bufferpool extension both are used as virtual memory when sql memory can't store objects in itself.
Pagefile is created on spinning disk. Bufferpool extension is created on SSD.
Am i getting this right?
If you use bufferpool extension, SQL Server knows what this is and use it wisely.
On the other hand, if SQL Server is paged out to the page file, this is not supposed to happen, and SQL Server will act as if the data had been in memory, and it will not be a happy ending. Also, SQL Server will sooner or later start to trim the buffer cache, since obviously there is memory pressure in the machine.
I have seen mixed comments on whether the bufferpool extension is really worth it, but I have not given it much attention. On the other hand, the pagefile is absolutely not a place where you want SQL Server to be.
I should add that it is possible to prevent SQL Server from being paged out. This will not happen if you assign the service account for SQL Server account the permission "Lock pages in Memory".
14 people are following this question.