Chris Atkin this session is as per the title problem solved in memory engine. The whole reason, I'm doing this is because in the past, I've done some really seriously.
Uber Geeky Level 500 sessions one of them was on the memory engine and what I found was I found that not many people are actually using it and one of the main reasons why I think this is, is 'cause.
Props struggling for ideas about how to actually use it. So it's sort of talented something that will provide incredible workload acceleration for OLTP workloads on I kind of gas that some people got a mindset.
To be a DBA developer any kind of SQL server, professional who's working. Let's say I'd known online gaming company, or or somebody that's processing gazillion. LTP transactions a day and you need insane LTP.
But I'll do is I'll actually give you some nice that will use cases I'm absolutely confident that each and everyone of you here will find some useful examples. I'm going to come up with so.
OK, so that's main that's the boring standard stuff. I was a bit of a dork when registering for SQL bits and support. My title in the wrong wrong page on the form. So the company. I work for isn't actually called Mr. the company. I work for these people.
And historically that's the sort of thing that's really interested me not just SQL server internals, but actually really going seriously deep into the database engines so.
This is billed as a level 300 session are not going to try and fry. Amel peoples brains. Today I'll touch on some very advanced tools that some you might not have seen but I'll try and keep things at a gentle pace, OK, so the legacy database engine.
And Love very basic level 100 architecture work fantastically well, but what's what's wrong with that, so is anybody heard of post grass.
OK, good anybody, he will admit to being as old as me. And I admit to knowing about Ingress hearing about Ingress OK, so if you're in my mid forties. If you care to admit to being in the ballpark of my age, you might have heard of a database engine called?
If you're interested in database engines. There's a guy called Doctor Michael Stonebraker, written turn off white papers. I think is it UC Berkeley, it at the states at the moment and one of the things that he said was that.
Most database engine. LTP workloads is they spend most of their time messing around with locks and latches as opposed to performing meaningful work.
Second.
OK, so lock clutch and Spinlock 101, so lock is most the time less you're using the rowversion store or you already using the in memory engine whatever you do.
Locking OK latching is certainly different 'cause latching is usually associated with certain memory structures as opposed to being something that happens is a byproduct of the acid nature of the database engine, however.
If you require a latch or lock in a certain mode, OK and somebody else is using the database engine is already got the row table page. In the buffer pool and they've got that latch and lock in a mode, which means that you.
Eat poisoning competitor is incompatible with the mode that you need it in right now, what happens is your session goes to sleep. It goes on to the end of the waiting queue. Spinlock is softly different so.
In the world of computing this several use usual suspects in terms of things, which are really expensive in terms of performance so physical. IO is one a pause is another if you go down much lower one app.
This thing called contact switch and what that means is it means when you've got a thread or process and it's running on a CPU call and it has to relinquish that so somebody else can jump in and use that is Mega Mega expensive and you talking around a thousand plus CPU side.
So SQL server from version 2005 forms at this thing called Syclo S, so you'd actually you're on top of a scheduler and when you're well your session is associated with task when that relinquished.
The scheduler you still get this performance hit it's not quite as bad. But it's still a hit. Nonetheless, so spinlock. It's usually associated with certain internal structures in SQL server. So I'll give you an example every time you run a?
Action in SQL Server, a special spinlock needs to be taken out so that your transaction gets a unique transaction number OK? What happens is is if we have to wait instead of automatically going on to the waiting queue weeper.
Spin we burn up CPU time, OK, so obviously, we don't want to do this forever.
And that is why you have this thing called a back off after we spend for a certain amount of times. We do eventually go on to the waiting queue. The whole point being is that with the traditional database engine that we all know in love with certain workloads this is.
Is where we bottlenecked OK?
This is something that I've used quite a lot in the past. I'm guessing not many people will be familiar with what this is this is this comes from Windows Performance Toolkit and what you can do is.
The not many people would use this in their day job so there's people in the engineering escalation team at Microsoft. We most definitely use. This it's a tool of getting a an overall view of what's running on windows and you can.
Where your time is going across the?
Whatever is running on windows, providing you've got these things here, yeah, these are DLL's OK and we can drill down into these we see the stacks. We need what's called public symbols so this thing here. This is our SQL server instance and one of the changes.
Might well there was introduced in SQL Server 2012 instead of everything being wrapped up in one single great big executable what they did was they split things out into different DLL's so if anybody's been to a any.
On SQL Server 2017, you might be familiar with these concepts of the platform abstraction layer platform abstraction layer. It basically what it is. It's a is what's called a shame so it allows the same.
SQL server EXE to run on windows and all it does is it captures system calls and it translates them into things that Unix understands and you can kind of imagine that even though this screenshot predate SQL Server 2017.
Sing the database engine different DLL's it might been in somebody's head back in the day in Microsoft. If they did. This it would make porting SQL server to to Lenox Hell of a lot easier. So this thing here spider name. This is what most of SQL OS runs out of.
This thing here this is responsible for handling the tabular data stream, but also language processing so.
Another problem with the legacy database. Engineers is that it can burn up a hell of a lot of time processing the T SQL language. And this is what this illustrates here. You've got the sandals language processing whoops and it should give you this thing here.
Ice.
Sample CPU time, OK never problem is contention around the logging infrastructure.
Funny body is done any scalability performance work with SQL server? What you might actually notice is that if you've got a got server, and it's using 2 CPU sockets, and you can play around with the CPU affinity mass to make sure.
Load only runs on one socket if you scale to 2 sockets. You don't get double the performance OK and one of the problems is contention around the login infrastructure, so basic.
Tuning 101, I guess that one of the things that most people in here do whenever perceived performance problem is, you look at white stats. One of this starts that you'll probably be familiar with is right log. He says looking for knowing looks and not yes.
One of the other things, you probably look at as well is.
Uh the virtual file starts IO stalls and what you can actually get is you can actually if you're really seriously kicking the back out of database engine. You can get into a situation whereby the average right log weight is greater than the right store for the actual TR.
Self and the reason for that is.
Is contention around the mainly this spinlock here OK?
So the in memory engine. It's locked free locks free spin lock free and also what you can do is you can actually compile. TC called down to machine code so the way this works is is you.
Memory optimized table, I'll show you some T SQL and you get a DLL for that. You also get a DLL for every natively compiled object and then actually linked in to the database engine.
OK, you have this concepts of these things called offline checkpoint, so it is offline checkpoint process and what that essentially does is that the more better expression that mines the transaction log.
That's that's written to what's called the is an in memory table space so set this up. You have to have a special memory optimized so it's not tablespace filegroup and there's 2 different types of of objects in these things called Delta files and data files.
And if for any reason you have to restart your instance as to recreate all the all the data in the memory. Optimized table from scratch. And that support. That's where it goes too, so key features can everybody hear me OK, yeah, great.
OK so no right head locking all operations are logical operations are locked only logical operations and what you find is is that the?
Um what the in memory engine does is really, really aggressive with batting.
Log records together, OK is under percent lock and unlock free.
OK, as mentioned log recalls are aggressively patched together, it uses row versioning for all concurrency control, so if you look into this wanted recommendations at Microsoft makes a general rule of thumb is that say friends.
One mega storage is a very general high level rule of thumb. You need to double that for the amount of memory that the equivalent memory. Optimized table requires and there's good reason for that and that reason is that you require over.
Nothing needs some memory to live in OK.
Hum.
So you also have this thing this concept of of garbage collection as well, which applies this thing called the rowversion store OK and finally your code can be natively compiled and that's quite important. And if you want the very best performance out of the memory engine. You need to look at you.
We compiled code and I'll talk about exactly why that's the case so engine within an engine so I was playing around with this a lot couple years ago.
And this guy, he was a thing is tide with some luck in SD for Microsoft. This guy reached out to me and he was heavily involved in the development of the database engine. So the memory engine and he referred to it as a container and the way this guy was talking about it.
Database database engine and he he made this really interesting comment, and I absolutely wish I'd taken into task on it, but he said that the Microsoft actually running memory engin somewhere else outside outside of the conventional database engine.
OK so the database engine it's like a?
Have you ever seen those like rainbow cakes. Those cakes where you put the cake open and it's all sorts of different colors with a different lie is data. The database engine is very, very similar. You've got these different layers that make the engine on so starting at the very top you've got.
SQL Lang, which handles language processing statistics collection results, rendering, which is why I mentioned TDs query. Optimization, you got what most people consider to be the database engine runtime you got this.
Carmen dot DLL you've got the in memory end OLTP engine. So when this Microsoft first announced this they referred it to refer to as this thing called Hackettstown and loan behold. You've got this hekaton DLL you got this thing called the expressions.
Yes.
You got the query data store, which is quite interesting. So when 2014 first came out. I did some digging around into the database engineer notices thing called Q DS DLL Wanna mention this on Twitter. This guy is this MVP.
Then he got quite skittish for the simple fact that this is the part of what would be surfaced as the query data store in SQL Server 2016 MVP's and are not one of them. They get told about a certain amount of things ahead of time.
Did the NDI mark software very unhappy so I sorta kinda knew about this thing just through my own investigation and obviously by knowing about this. Obviously people in the MVP community knew about it as well, OK, so the actual storage engine.
So that that's that comes out the same thing as the query execution runtime.
And then at the very bottom. You've got you've got SQL OS.
OK, so the memory engine.
Logical operations.
OK join transactions what is a joint transaction. I'll tell you why I joined transaction is if you want to use a memory optimized table. There's 2 ways. You can do that. You can use a natively compiled stored procedure or?
You can use a conventional T SQL object, OK and what happens is is if you use an in memory table from a say a traditional stored procedure you have a transaction, which spans 2 worlds so as one thought in the.
And then it has another thought in the world of the memory engine that is what's called a joint transaction OK?
If you only ever used natively compiled stored procedures and objects. You don't have a joint transaction. You have something which lives 100% in the world of the memory engine and again for the best performance that is what you want and I'll get into why that why that is.
OK, so.
And also the memory engine users sort things out the legacy engine for the allocation of large pages OK so.
So this came out in version 2014. It was an enterprise only features so it costs a lot more money than things that you could get in a standard edition.
OK, limited T SQL support them. Today's type inflation support you will limited to 256 gig of memory limited support for constraints.
So short story Long story short when this first came out in SQL Server 2014. The only people that were really using it with people that had a need for performance at the bleeding edge, OK for most most P.
All the things I've mentioned there men that it was yeah. That's nice that's interesting get back to me. You know when you've you've sort of shave the rough edges off when you've made it a bit more usable OK, so Fast, forward to SQL Server 2016. I'm not going to.
Go for that, but the big takeaway point about SQL Server 2016 was a lot of the restrictions that you had with 2014 went away. It was a hell of a lot more usable also other big thing.
SQL Server 2016 with service pack one all of a sudden Microsoft decided that they wanted to give you things away in standard edition for 3, which previously you could only get in Enterprise Edition.
OK and I remember talking to a guy it was another speaker at the SQL Saturday in Dublin toll free. I think about 3 years ago and he was saying it was the SQL Server 2016 service pack one was the third biggest day.
In the history of SQL server, so one of the first day was when Microsoft got rid of all the remnants of the Sybase Codebase second biggest day with SQL Server 2005 and.
This 2016 service pack one because all of a sudden you got all these nice call funky new features in standard addition, which you had to pay big, big, big books for in enterprise edition OK so that's a bit of background.
Do some some use cases now OK and I'm hoping that at least one of them will be useful to most people in this room, OK so.
Sequence generation.
What is the fastest and most scalable way to generate a sequence in SQL server?
OK, so let's have a look at the different ways. We can do this right first up the conventional sequence second up an in memory table with an identity column So what you do is you insert into the table and use scope identity to get the most Reese.
From the art from the identity column OK and then you can. Delete the road. It's just been inserted. It's a hell of a clunky solution, but it is one way of doing this.
New approach in memory table, you can increment a numeric column and get the get the latest value from that column OK. So 3 different approaches to get in values getting sequenced values.
OK, so we show you some some code I've used.
OK and I'll looks I'll talk you guys through this OK so.
Well, I've got appear or what school natively compiled stored procedures and there might be some things on there that you've never seen before. You might be thinking? What the hell is that I've never seen that before so let's pick. Some of these things out OK. So this thing here this native compilations that tells it say.
Natively compiled stored procedure, we always have to use schema binding. The other thing that you've probably never seen before. If you've never played around this is this thing here this begin atomic.
With natively compiled stored procedures, you have this concept of what's called atomic blocks and with an atomic block everything in that block it. Either succeeds or it fails OK?
Transaction isolation level that you can specify so in conjunction you always after this with claws and a language OK, so on the left. We were using scope identity on the right where?
Say inserting we actually updating the value in a column and then we're getting the latest value from that column.
OK.
So.
They'll graph here is this is on a box with.
20 CPU cores, I've turned hyperthreading off So what windows actually seizes 20 logical process is and what you can see here is.
Is that they use in the the memory optimized table using the insert? Delete scope identity identity by far that is the fastest way of doing it, OK, so the approach using a sequence with a cash.
Is is this green line OK? This this line here? Is use new memory? Update selector approach? Whereby we update value in column and then we get back the latest value in that column.
So data ingestion show hands type question who does anything with ETL OK.
I'm guessing most people do some image whether you've gotten requirements actually bang data into the database as fast as possible. And so he's looking for nodes knowing looks great OK so.
This too I'm going to, I'm going to cover 2 things I'm going to cover ingesting streams of data into the database whereby you want to get data in SQL server. You actually care about the order ordering and then I'm also going to cover a use case for actually bought loading data.
Into the SQL server database as fast as possible, OK so.
If anybody is played around with anything where you've got lots of different sessions inserting data into the same table with 5 are a clustered index or any kind of index whatsoever. You may have come across the fact that it's incredibly.
Non scalable and what you get is you get this thing here. This thing called the last page problem. You get scalability death by page lighting on the.
The most recent leaf page and index betrayal be trees, OK, So what can you do?
What you can do is you can implement a queue so there's a guy might be people have heard this guys no longer on the SQL server seen you work for the sequel cut team and a nice guy called Thomas Kaiser, one of the most brilliant people I've ever met and he played.
This this idea of using the queue to get data into SQL server database. So you had some ordering and he wants this thing to be really scalable and he he reused this idea from this company. I think also in Ireland.
Elmax forget with the M stands for is London asset exchange is A and they produce. AI think it's a forex trading platform. This thing has to be low latency has to be blindingly fast and really scalable so he took this thing any for how can I get this working in the world of SQL server?
And he at the time he did this with conventional tables because the memory engined exist so this. This was Pre Sequel Server 2014. I took this on iPhone? What could you do with this, if you you are using the memory engine memory optimized tables So what you have is? Yeah.
Evil.
OK, you have this slot column OK and the slot column contains an ID so imagine each one of these where you put a message each one of those is a slot OK, you got your actual message. That's the thing you want to put in time stamp.
The reference count OK, so reference counter one means we contain a message.
Empty OK, some code, there I'll make the slides available. This all works don't expect everybody to have photographic memories and remember that.
Grave he did prove that you know like a magician is nothing up my sleeves are actually coded this up and got this thing working OK. So So what we do is we got 2 procedures here. We've got one for putting messages onto the cube, which is colloquially referred to as pushing messages.
For getting messages off which is popping messages OK, so we get a we get a sequence value, which is a slot where the message is going to go OK. We never actually create Rosina table. The table is always pre populated with rose all we're doing is update.
Right we just fitting these message slots OK.
So what we're doing here is we put in a message on the queue with send reference counts and one which means we've got a message in our slot doing things. The other way round. We taking messages off off OK. We just sit in the.
And she slaughters normal true value of hello world, taking one of the reference count, which means that the Q Slot is empty.
OK.
And what you can see is is.
Here, I'm using?
Delayed durability for this particular task there this thing is blindingly fast, OK blindingly fast compared to using the conventional.
Displaced Dater engine.
Um and also because I presented this to somebody and somebody said to me well. That's fantastic. Chris is great being able to get messages onto AQ but you kind of also need to get them off as well. So I did was I did the same thing again and II. Did this, where I have things running at the same time, I was putting messages on and I will take.
And again, you can see that the in memory engine is still.
By fall incredibly the fastest and the most scalable way of doing this OK, so boatload in this is the other use case.
So given it will tell you store II worked at this particular little software house they had.
Up.
A customer who was a food retailer and had to take these files off off this mainframe and for whatever reason, these files came off at certain times and even sort casting. It was cast in stone, the time that when these files were produced from the mainframe and we could get hold of these things and load them into SQL server.
So now shall not change. This this schedule at which the mainframe produce these files so I used all the integration services tricks in in the book and in the end what ultimately killed me was the actual SP.
Could stage data?
Son.
This way that you can do this.
Um.
So so if you create a?
Just create table really silly piece of T SQL. There hasn't really gotten meaning over the fact. It creates a table. This thing here with that many roads OK exported to a CSV file and if anybody that's played around with integration services.
One of the things that you do is if you got anything. This performance critical is you performance profile. You source an easiest and fastest way to do that is just have a simple dataflow. That's got 2. Things is got your data source and it's got a row count destination that tells you how fast.
And the whole point is so say for example, I do this, I can get rose out the sources say. I know thousand roses, second, but my requirement is to do that 2002 thousand roses, secundino very earlier on that I've got a
Problem rather than just go away in invent weird and wonderful data flows and packages. I know that my performance issue is, is inherent in the actual source.
OK, so.
2 tables got memory optimized table OK and one of the things that you can do memory optimized tables. You can completely do away with durability. And those people who might be thinking? What this guy is he absolutely lost his mind that you know, I'm going to have something in.
And it's going to be at the database engine falls over. That's it. I've lost my data. But if you think about this, if you have any kind of ETL process. And if for any reason, it fails. All you do is you just re run it and it picks up where it left off.
This thing here doing away with durability.
Uhm this is something you can absolutely get away with.
What we've got here is?
He says get his pointed to work is.
Every table has to have a clustered an index into 2 types is arranging decks and as a hash index table the hash index for the?
The point of this.
It says.
It says clustered out she says, Nonclustered it has to have an index that is the point. Sorry about that and people get into this and it's not realize that there's 2 types of indexes range indexes and his hash indexes and this sort of thing why, why do I need? Why do I need to why? Can I not just have one of these.
This is the software developing this what they found was that when using these these hash indexes for point operations things like point lockups. So if you imagine anything where you've got inequality sign in the where clause that was so much faster than anything that you strange.
By range mean anything that's got a greater than or less than in in in the way a clause OK. So Microsoft realize that these 2 things were for point lockups hash indexes was so much faster than than than range indexes that they they made a judgment call and they 4 ha, you know it's.
Having these 2 different types of index.
OK so this thing here traditional disk based table.
OK, so, So what I've done here is to test this thing I've got my CSV file. I use the balance data distributor to feed this into a bunch of road count destinations case. I've got just one here, but I did was a scale this thing out.
OK.
Like so.
And what I found was so this thing here. This is the This is the baseline. This is just taking a CSV file and banging into a row count OK. This thing here is using the disk based table. We bought logging listing here this is this is.
Load in the data table with no durability and using the balance data distributor and for destinations. It's almost as fast is just trucking the data away so there's a?
A Dutch guy we got one from here with the front not him. Guy called Hank Vandervalk and he did a whole presentation on this on on scalable data loading using the memory engin OK. So so this thing here. This is what I probably considered to be the one use case, which guess most.
So you doing, any type of batch book processing. ETL you want to be able to state data as fast as possible fastest way to do that is to use a memory. Optimized table with with no durability, durability of schema only.
OK, so something else.
Low level transactions, so one of the so the best of my knowledge. This restriction has been lifted. In 2017 not need to double check whether this is still the case for 2019 but historically you haven't been able to create triggers on memory optimized table.
So I'm generally not a massive fan of triggers I guess they have their uses. I generally try and avoid them. Like the plague. Some people might like to use them to actually audit what's going into a table.
OK.
So.
So this is what you get if you well this is what you did get if you try to create a trigger on in memory or memory. Optimized tables SQL Server 2016, you get this nice.
OK so you could look at.
Problem is, you end up waiver really messy complex solution.
Up.
Best solution is to use temporal tables.
You have a
Yeah, yeah, basically you have this thing here, you have this history table, which stores versions of the rows, which are going into your main table. One of the things that Microsoft recommends is they recommend it to keep this thing down to A.
I see you actually create a clustered column store index on that table so we
Obviously, depending on your data that showed compressed down to a nice size. So some things on here, which she might not have seen before so this thing here.
These sister times this end time this line here. Also, the system version in these roll things that you need to actually create your temporal table.
OK so how does it work OK so?
You have this memory optimized staging table, which taxes like a middleman or middle woman that excused is your go between under the covers. You don't see this thing that's used to actually stays data before it goes into your history tape.
OK.
And a synchronous dataflow task and the idea is to keep this.
This of hidden memory optimized table. You can't see down to a manageable size OK. So obviously you got a limited amount of Memory. You don't want this thing to to grow some inordinate stupid size.
OK so stage, NIST is triggered when the memory consumption of this hidden staging table re reaches around 8% of the memory of your temporal, temporal tables so the temporal, temporal, temporal table. This is the thing. This is this is your main table instead of a conventional table, so that you can get some.
You re create this fingers a temporal table and as a history table associated with it.
And that's all the stage in task, which runs under covers that South frequently this thing can can run OK, so I tested this.
I used the memory table, OK, I tested this, I used a clustered column store on the history table.
I use the
In memory table in memory, temporal table delayed durability. And I just use a conventional memory memory table with that should say.
July durability also and I mean, these things are quite messy to quite noisy. But what you can see is even with using the temporal table that scales quite well compared to not using one at all, one of the things that I.
These are absolutely not straight lines and generally speaking when I was playing around the legacy database. Engine generally kind of get is I get the straightest line.
So so this thing here this is a machine. It's got 2. CPU sockets 10. CPU cores on each and what find is if I was processing any workload as soon as I went past 10 threads. The throughput would dip and then it would gradually creep up again.
Something I never got to the bottom of I always got these like really jagged graphs.
OK so natively compiled code OK, so I mentioned to you, that this is absolutely what you want to use for the very best performance with the memory engine and what you can see here is this is using a conventional TC corporate.
This is using the natively compiled siege and you can see that.
When we start using natively compiled code it is an order of magnitude faster.
OK so that was for little go back.
Um.
This was for using our little message give just pulling messages on.
OK, this is for putting them on and take them off is still much, much faster.
And the reason why are natively compiled procedures are faster? Is is because when we use conventional T SQL code against in memory in memory tables this.
Thing called joint transaction this begins to killers So what happens is.
is?
When we're using the conventional database engine.
You've got this spinlock.
This access manager thing OK and then when the transaction moves because remember I said earlier on the joint transaction lives in 2 different worlds. It said it said it's gotta Jacqueline Hyde personality is.
Whichever way you want to phrase it jackal personalities that it needs to get transaction ID from the world. The legacy database. Engine is Mr. Hyde personalities is that when it makes the jump from the world of the legacy database engine to the world of the memory engine, it needs.
Is this OK and that's where you get this spinlock Here if you use natively compiled code?
The database, the database engin doesn't touch. I've these things and that is why imparts you get much better performance. It's partly because.
Your code is is machine code, but it's partly because you'll completely eliminating these 2 things from the equation.
OK, so recap so I gave you a fast and scalable means a sequence generation.
Um.
I gave you a means of ingesting data. When you care about the ordering of that date when you care about not just getting into the database, but actually getting it from a table and you actually care about the order in which that data actually arrived in the table.
I gave you fastest means of actually staging data in SQL server, which is to use a memory. Optimized table with Joe ability of schema only.
Another gave you a means of actually auditing data within memory. Optimized table, which is to create what's called a memory optimized temporal table?
OK and also I mentioned I mention it right at the very beginning. But I gave you a nice graph, which hopes reinforce the point. If you want the very best performance from the in memory engine that you service need to look at using natively compiled code.
OK so that's my contact details. It's not appear ever mentioned it so far. It's the one perhaps one of the most important slides in the deck. That's not then it should be something which is a big. Thank you in a big shout out to the people are giving up there.
This event and a fantastic job that they do year after year, so I kind of guessed that deserves a bit of a round of applause if you.
So yeah, thanks for coming to my session and I hope this was something in there, the at least one of you managed to to take away and if you're not going to use it in anger at your in your day job. You know it is perhaps giving you some ideas some.
Thanks a lot.