July 23, 2017 at 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.
July 23, 2017 at 5:56 pm
SteveOC - Sunday, July 23, 2017 4:39 AMHello.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
July 24, 2017 at 5:14 am
Thanks for the response Kevin.
My take was that the SSDs are what they are - whether they are locally or network attached in our environment it would be the
same SSDs used for all IO and for BPE so is there any point?
My guess is that these SSDs are in a SAN, and I don't know if local drives is an option or if the difference in performance
justifies looking at this.
Coming from a background where there is no 'local' disk, and all disk was copper or ficon attached (or even in a different state),
I don't automatically consider where the disk is located as it was never an issue.
The maxed out memory is a bit of a sticking point since I share your view on 'silly' - the quick answer is 'almost'.
I am also looking at reclaiming a few GB by setting the Optimise for Ad-Hoc to free up the space used by single use plans.
Regards
Steve O.
July 24, 2017 at 9:32 am
SteveOC - Monday, July 24, 2017 5:14 AMThanks for the response Kevin.
My take was that the SSDs are what they are - whether they are locally or network attached in our environment it would be the
same SSDs used for all IO and for BPE so is there any point?
My guess is that these SSDs are in a SAN, and I don't know if local drives is an option or if the difference in performance
justifies looking at this.
Coming from a background where there is no 'local' disk, and all disk was copper or ficon attached (or even in a different state),
I don't automatically consider where the disk is located as it was never an issue.The maxed out memory is a bit of a sticking point since I share your view on 'silly' - the quick answer is 'almost'.
I am also looking at reclaiming a few GB by setting the Optimise for Ad-Hoc to free up the space used by single use plans.Regards
Steve O.
As I said, there is absolutely a diffierence between PCI storage and network based storage (even 10Gbe iSCSI).
I will amend my "maxed out" statement to ensure that you and others that read this in the future know that you should have MORE RAM than the max SQL Server can be licensed for in the case of Standard or lower editions!! If you only have 128GB in the server, SQL Server is NOT getting 128GB. My statement meant, but was not clear, that SQL Server should be able to use every single bit of that 128GB of memory. You need to allocate additional to the server for ALL of the other things you may have running on it (including all Windows OS pieces-parts).
In an attempt to elevate the importance of this I will state that I have actually TURNED DOWN CONSULTING REQUESTS when I found out the memory configuration potential clients were attempting to run their SQL Server with!!!! I am EXCEPTIONALLY good at what I do, but I simply cannot compare to the microsecond-time-scale benefits that would come from increasing RAM when trying to, say, run a 1TB data warehouse on an 8GB virtual machine. 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply