One of my previous articles was devoted to carrying out a range of tests to compare speed of data read operations using BPE. But in real life we need not only read but also update data, that’s why I decided to check whether BPE has influence on performing update operations. Before running new tests we should take into account several crucial features. First of all insert, update and delete operations are logged, which means transaction log will be under load and a lot will depend on the disk subsystem. That’s why I made a pair of test measurements and then put the transaction log on a SSD to minimize its impact on the update operations. Another moment is that before data update SQL Server reads the page from Buffer Pool, then makes changes and marks the page as "dirty». Later the page is written to the disk through performing Checkpoint or Lazy Writer processes, and the first of mentioned processes may also affect the test results indirectly, so I turn it off. Thirdly, I write a query to change the data so that new data will be of the same size as the old data, which allows to prevent pages split. Fourth, Buffer Pool Extension can store only "clean" pages, consequently, update operations will "wash out" the data from it. But I will provide more details on this situation later and now let’s have a look at the configuration of the test bench.
