- [Instructor] When the SQL Server team set out to design and build a specialized database engine specifically for in memory workload, their vision was of a new relational database engine that would be 100 times faster than the existing SQL Server engine, hence the use of the codename for this project, called Hekaton, which is from the Greek word meaning 100. This feature uses a couple of key structures that allow it to go fast. As we mentioned, by maintaining a row version history instead of using latches, memory allocation for insert and update operations is eliminated.
Secondly, with transient data, we have the ability to use non durable tables, which can bypass the transactional log for faster rides. Obviously, if you have data that needs to be persistent, you're going to want to use durable tables, but this is really good for operations like staging data, and finally when we'd use natively compiled stored procedures, our queries no longer have to be compiled before they're executed against the engine. Let's see a demo of this in action and see the kind of performance benefits we can get.
So, here, we're going to start out using the WideWorldImporters database. If you haven't restored this database, you'll want to go ahead and do that now. We're going to do a couple of things, first. We're going to go through here and drop things that we may have created earlier. So, the nice thing about this set of demos is that you can run them over and over again, and then we're going to create a schema here called OnDisk, and this is going to be our sample for how this works on disk, and not using a memory optimized table. So, starting on line 20, going all the way to line 32, we're just going to create a basic table called VehicleLocations.
Next, starting on line 34, we're going to create a stored procedure called OnDisk.InsertVehicleLocation, where as you can see on line 45, we're going to insert into OnDisk.VehicleLocations a set of values that we're getting, and then we're going to create a similar set of procedures and tables for InMemory. So, starting on line 55, we're going to create a schema called InMemory, and on line 58, we're going to create the same table, InMemory.
The difference, here, is we see that on line 66, we're specifying with MEMORY_OPTIMIZED on, and here we're saying durability equals SCHEMA_AND_DATA. We have the option, here, of doing schema only. That's where our data is not persisted. You'll note, if you're not using the WideWorldImporters database and you were doing this, you would want to create a new database with an InMemory file group, but if you're using WideWorldImporters, you don't have to do that. So, the next thing we're going to do, here, is create a procedure called InMemory.InsertVehicleLocation, starting on line 70, and we see on line 75, we have that with NATIVE_COMPILATION and SCHEMABINDING option.
SCHEMABINDING is required for natively compiled stored procedures, but what that means is this is going to be stored as a DLL. If you look on line 83 and 85, we're doing the same logic in the stored procedure that we were doing in our OnDisk stored procedure. So, we have now switched to example 2-1.2, where we're going to run this OnDisk workload. So, starting on line two, going through line 10, we're going to declare a number of variables that we're going to set within our code, as you can see on line 17 through 20.
This is just going to be dummy data, and we're going to go through and do this 500,000 times and on line 28, we have a bit of code that's going to show us how long this takes and as we can see, that took around 26.6 seconds to do that 500,000 row insert into a disk based table. Now, we're going to switch over to example 2-1.3, and we see we're going to do an insert similar loop except we're going to call our 500,000 vehicle location that's in memory, here, so, we'll go ahead and pass these variables in, and go ahead and execute this code that we see starting on line 24, and we'll get the same report here, this time, and as you can see, it took 1.6 seconds, compared to 26 seconds, so actually faster than 100 times that we would have been expecting.
Very, very impressive. We dropped in 500,000 rows in about 1.6 seconds, and that includes the overhead of firing up the session and returning that query to the screen.
- Designing for high-write and high-read workloads
- Designing an archiving model
- Leveraging SQL features such as portioning and compression
- Building a tenancy model
- Using Azure design patterns for scaling