• How are you tuning your BPE and server Max Memory? A BPE can turn poor performance to acceptable if you are starved of memory, but it is not a substitute for having enough memory.

    Make sure that Max Memory is set correctly. A reasonable starting point is 80% of server memory, then tune to get the best value for your environment. Make sure you only refer to the physical memory on your server and do not include the page file size. Let your server run for a few hours dealing with real workload before starting your tuning, otherwise you may base your decisions on the wrong figures.

    Even at the 80% level you should be getting an idea about how much memory SQL Server would ideally need by comparing Target Server Memory (as shown by Performance Monitor) against Total Server Memory. If the target memory is significantly higher than total memory, then you are going to suffer performance issues until you put more memory on to the server.

    If pages are going into the BPE, how is this affecting overall IO for your server? You can test this by measuring performance with the BPE on and with it off. Hopefully you will see an improvement with the BPE turned on. However, it can take some time for the BPE to become fully effective. On our BI server we reckon it takes the best part of 2 days from SQL startup before the BPE is being used to its maximum extent.

    If you do find your BPE is taking a lot of your server IO then you should consider adding more RAM.

    As an example, our move to SQL2014 in January coincided with some new BI extracts that significantly increased the IO demand on the server. We started out by specifying a 400GB BPE on a SSD, and found a big improvement but still had performance worse than our users wanted. The BPE was peaking at 75MB/Sec IO with a normal load of about 40MB/Sec, while our spinning disks were peaking at 10MB/Sec with normal load in the KB/Sec range. The BPE never got beyond 75% full and took about 90% of all IO. We then added another 64GB memory and got performance that kept our users satisfied. With the extra memory, our BPE was now peaking at under 10MB/Sec with normal load in the KB/Sec range, and our spinning disks now very rarely go out of KB/Sec IO. Our conclusion was that a BPE on SSD can be a useful tool in tuning a server, but that nothing beats physical memory when it comes to getting the best performance.

    Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

    When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara