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.

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.

Difference between bufferpool extension and pagefile in sql server?

Accepted answer

AmeliaGu-MSFT 13,976 Reputation points Microsoft Vendor

2021-07-07T05:48:27.443+00:00

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.
Please sign in to rate this answer.

1 person found this answer helpful.
Chirag Sachdeva 281 Reputation points

2021-07-07T17:20:48.367+00:00

One stupid question: Can we create BPE on HDD like pagefile?

Erland Sommarskog 111.5K Reputation points MVP

2021-07-07T19:50:00.457+00:00

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.

Tom Phillips 17,731 Reputation points

2021-07-07T21:53:02.453+00:00

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.

Chirag Sachdeva 281 Reputation points

2021-07-08T06:13:27.853+00:00

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?

Martin Cairney 2,256 Reputation points

2021-07-08T06:54:52.23+00:00

No - they operate completely differently and in different places.

The BPE is effectively you defining to SQL Server that the amount of memory it has is MORE than just the physical memory on the server but also the BPE which is stored on a very fast SSD (if you don't have a very fast SSD to dedicate to a BPE then just DO NOT use it). SQL Server then thinks it has much more memory and can use the BPE in the same way as the internal Buffer Pool (i.e. where it loads the pages from disk so that it doesn't have to read from disk when you send a query). It effectively becomes an additional cache between the memory and the on-disk database files and so improves your IO.

A Page File is an Operating System feature. Windows manages when memory is written out to the Page File - not SQL Server. If you have properly balanced Max Server Memory with the available physical memory, allowing for memory needed by the OS for drivers etc then your usage of the Page File should be minimal. If SQL Server does not have enough buffer pool space to load another page of data from your database it does NOT mean that memory will spill to the Page File - what will happen is that the pages that have the longest time since they were touched are removed from the buffer pool (meaning that if they were needed again by the next query to come in they would have to be read from disk again) to make space for the pages being read.

Chirag Sachdeva 281 Reputation points

2021-07-08T07:45:13.933+00:00

Million thanks! Martin for explaining this. Its more clear now.

AmeliaGu-MSFT 13,976 Reputation points Microsoft Vendor

2021-07-08T08:38:38.233+00:00

Hi ChiragSachdeva-6150,

Was your issue resolved?
If the replies could help you, please kindly accept the useful reply as answer to help other community members find the helpful reply quickly. If you have any question, please feel free to ask.

Best Regards,
Amelia
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Difference between bufferpool extension and pagefile in sql server?

1 additional answer

Your answer