Our instructor is Maciej Pilecki (@DrHouseofSQL). I’m familiar with Maciej because of MagicPASS, the SSUG I call home. He did a presentation to us on the memory cache back in June. It was amazing and I had promised to write a blog on that, and have not gotten that one published yet. However this one will go first and what a day it has been. Maciej is an MCM (Microsoft Certified Master), and a SQL Server MVP. He has presented at conferences like the PASS Summit, Tech Ed North America, and many many more.
We spent the first half of the day discussing the SQL OS and the way that SQL Server uses CPU’s. The second half of the day was all about Memory, and we’ve got some big changes coming in Denali. So of particular interest is not just how we work now, but how we are going to be working in Memory in the future.
WHAT IS THE SQL OS
SQL Server has it’s own Operating System. WHAT!?! Yep you heard me.
“But Balls”, you say, “You install SQL Server on Windows right?”
Yes Dear Reader you do! However Windows and the way that it handles processing is very different than how we would like processing to occur within SQL Server. Windows uses Pre-emptive Scheduling. Each Thread, work that is being done on your CPU/Processor, is given a quantum of time. Windows as an Operating System must manage that time and make sure everybody get’s a turn.
This is similar to having multiple sugar infused children, all of them run up to you wanting to tell you something. You let one go, and depending on how much they are jumping over one another you may need to stop one before they are done, and have another one start to tell their story. You’ll get back to the first one, but your job is to make sure everybody get’s a turn. Windows will stop your Threads as they are working before they are finished, they will let them have another turn, but they need to make sure all the kids get a chance to talk.
SQL OS is a Cooperative Scheduling OS, it is a bunch of orderly Kiddo’s lining up to take turns telling you a story. And if one needs to Wait and think they go to the back of the line and let the next one speak. They Cooperate to make sure that things happen as orderly as possible, but most importantly if you are telling your story, your Thread is working, no one stops it until it is ready to stop.
“But Balls”, you say, “When did this change occur, this is huge?”
Yes Dear Reader it is huge! But take heart we have all been using the SQL OS since version 7.0. It has been maturing over the years and we see a lot of benefits of that today in 2008 R2 and we will see even more in Denali. We spent the rest of the morning talking about how CPU’s work within the SQL OS, and I learned more that I have time to write. This was an incredible day of training, and I can’t wait to apply some of what I learned.
If you are reading my blog more than likely you’ve heard of these guys, they’re kinda a big deal around here. We all sit down at a table as we are eating we are joined by Mike Hilwig (@mikehilwig | Blog). I met Mike at SQL Saturday 79 in South Florida, and he is awesome. It was really nice lunch with great company, as Stan Lee would say ‘Nuff Said!
MEMORY, RESOURCE GOVERNOR, AND EXTENDED EVENTS
We spent the second half of the day learning about how Memory and Buffers work and are managed within the SQL OS, how Resource Governor works internally, how you can bind SQL to multiple ports and affinity each port to a specific set of CPU’s, what Maciej called a poor man’s Resource Governor, looked at Extended Events, and some of the new features coming up in Denali.
And now on to day 3!