SQL Server 2017: World’s First Diskless Database

SQL Server 2017: World’s First Diskless Database


Hi I’m Bob Ward for Microsoft a principal architect in the database systems group I’m here today to talk about another reason why SQL server 2017 is built for performance Now at Microsoft we believe SQL server is the fastest database on the planet from our world records in analytic and OLTP performance we have demonstrated wtih our benchmarks to the amazing demonstrations we have done at conferences like Microsoft ignite showing how we can scan billions of rows in a table in seconds now one of the new benchmarks we just released is using an amazing new technology from HPE and SUSE Linux and we call this the world’s first enterprise-grade diskless database Let’s talk a little bit more how this technology works as I said we called this enteprise-grade diskless database and the reason is is that HPE’s new scalable persistent technology uses the powerful combination of memory modules combined with SSD drives to give SQL server the ability without any code changes to the core engine to read data from database files in a matter of seconds yet achieve the amazing characteristics of persistent databases and the other thing that is amazing about this technology is that it allows an incredible reduction in hardware costs and reductions in rack space let’s go see a demonstration of how this technology works now what I’ve observed using this technology is I can get up to five times more faster query performance as we said at up to 50% of the cost of comparable storage What I have in front of me is SQL Sever Management Studio connected directly to the SUSE Linux Enteprise Server that has SQL Server 2017 installed and again this is a server using HPE’s new persistent scalable memory Now on this server are two databases each 1TB, one of them called ssd1tb storing a 1TB SQL server database with files on an array of pretty fast SSD drives. and an identical database that stored against and using HP scalable persistent memory And this database is using a workload derived from TPC-H. So it is a very analytic query type workload So let’s go take a look at the performance of two identical queries running against each database. The query in front of me here it is an analytic query it’s an aggregation of revenue discount data and in fact it is very typical of something that might be generated with a tool like power BI now on the screen here I’m going to first run the DBCC dropcleanbuffers command and that is just an the internal command we use and you can use in your own testing to force SQL server to read its data from disk I don’t wnat to use buffer pool cache in this case because I’m demonstrating how fast SQL server can read data from disk using these technologies so we will run that command first and I’m also using the set statistics time on command because I want to measure the exact performance of what SQL server records in the engine to run this query so let’s run that as well. So let’s kick off this query here against the array of the SSD drives. Now behind the scenes this data is stored using a column store index compression technology but even that type of in-memory data has to be read from disk initially when first queried or even on your system if you have less memory than the total size of your columnstore index data we will have to pull it in and read those column store index segments as needed when running a query so we will let this query finish and you’ll see here the result has come back and in the messages you’ll see below the actual time it took it to run, about 16, a little over 16 seconds to run the query which is really fast that’s pretty good to scan this much data in fact this is an actually representing about 6.5 billion rows of data to scan so 16 seconds is pretty good Let’s take a look at the same identical query but this time the database is backed by HPEs scalable persistent memory so I’ll run the same commands to force SQL server to read data from disk and also run the command to ensure I’ve got timings. Let’s kick this query off and we will see its performance and you are going to run this and see before I can even finish talking here the result has come back and the message below you can see here it is a little over 3 seconds so there it is five times query performance so there is the demonstration very simply this technology high speed analytic query performance directly against storage five times as or more fast and again at 50% of storage cost using HPEs scalable persistent memory technology combined and powered with SQL server 2017 and SUSE Enterprise Linux Now if you would like to learn more take a look at our main SQL server blog where we talk about performance of SQL server 2017 you can also go to youtube to learn more about HPE scalable persistent memory technology and we encourage you to download SQL server 2017 and see how really fast it is in your environment. install SQL server on linux and give it a spin in your shop and if you’d like to learn more about SUSE Enterprise LInux and SQL Server go to their one stop shop webpage where you can learn everything you need to know about how to purchase, install, download and run SQL Server on SUSE Enterprise LInux. Thank you for your time today.

Danny Hutson

Leave a Reply

Your email address will not be published. Required fields are marked *