Home Forums SQL Server 2014 Administration - SQL Server 2014 Buffer Pool Extension RE: Buffer Pool Extension

  • July 23, 2017 at 5:56 pm

    #1952287

    SteveOC - Sunday, July 23, 2017 4:39 AM

    Hello.

    I have been puzzling over this for some time and still can't grasp what the benefit of BPE is over 'normal' IO from disk to BP,

    If all data is already on SSDs (which in this case I am told it is), and BPE is just an area of disk on the same SSDs, what is the benefit of BPE over just doing IO?

    I can envisage that a paging algorithm could be more efficient than IO (perhaps), but am unsure if it makes enough of a difference to be worthwhile (or even noticeable, so have requested some space in DEV to try this out, but I am wondering if this is intended for use in environments where all data still resides on rotational disk with limited SSDs available.

    Has anybody implemented BPE in a memory constrained Live environment (running STD Edition), and did it really make enough difference to forestall going to Enterprise?

    TIA

    Steve O.

    There's SSD and there's SSD. They are definitely NOT all created equally, especially in the manner in which they are connected to the server. A card such as FusionIO (nee SanDisk) sitting on a 16X PCI bus will give you VASTLY improved performance over any form of network-attached IO.

    Having said that there are some substantial limitations/gotchas/provisos/caveats to BPE, so test thoroughly before rolling into production.

    BTW, you mention "memory constrained". Does your production server has 128GB provisioned? You wouldn't IMAGINE the number of clients I have that are crushing their IO subsystem (especially tempdb) because they are not maxed out on RAM. Just plain silly.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service