I am in Atlanta this week for Tech-Ed…right after I watch the launch of the Space Shuttle on Monday morning. Despite my commitments to both of those events I cannot ignore my SQL University duties, so here is this week’s post. We are going to talk about the internals of SQL Server, a topic that could never be covered properly with a simple blog post. I will, however, do my best to help you get started with a basic understanding of the topic.
OK, let’s get started…SQL Server is filled internally with sugar and spice and everything nice, and Oracle is filled with frogs and snails and puppy dog tails. Lesson over. What? You want more details than that?
*HEAVY SIGH*
Fine.
tempdb
First thing you need to understand is that MS SQL SERVER IS NOT A BLACK BOX!
As a DBA you need to be aware of a few things. First and foremost would be that your role is to administer a piece of software that runs on top of an operating system that sits on top of some hardware. Next would be your awareness that you are not working with a black box, there is no magic involved here. The system does as it is instructed to do.
Speaking of that system, here is a little something you should know more about, the SQLOS:
Check out that diagram of the SQLOS and note that there are a LOT of moving parts. Most new DBAs have NO IDEA as to how complex things are under the hood. These are the internals you need to be aware of and how to make them work for you. [This is where I like to insert a joke about how in Soviet Russia *you* worked for the internals, but I won’t do that here.]
With so many moving parts it can be very difficult (especially for new or accidental DBAs) to understand everything. And that is why I like to just focus on the major resource bottlenecks: disk I/O, memory, CPU, and network. Focus on those four things and you are many steps ahead of most others.
msdb
After you have developed an awareness of the SQLOS and internals in general you need to shift your thinking a bit. Now you want to start thinking about how you can optimize your shop with regards to how the SQLOS internals are operating. Think of this as being the difference between being able to change the oil in your car to being able to give it a full tune-up. Once you learn the internals then you have the chance to start doing your own tune-ups.
For example, one part of the SQLOS is the scheduler. This is how queries get executed. Most queries will likely touch at least these three states in their lifetime: running, runnable, and suspended (or waiting). The idea behind the waits and queues whitepaper is that if you know what your queries are waiting for then you can focus on fixing that resource bottleneck in order to make your system perform better.
But take a closer look at the SQLOS diagram above. You should be able to see the connections to each of the four resource bottlenecks (disk I/O, memory, CPU, and network) in that diagram. Now think about how your shop, or individual servers, are configured. Got everything on one disk? Chances are you are bound by I/O every now and then (my favorite example of this is when a user tells you to not do any backups because it kills performance).
What about your NICs? Are they as good as can be? And how about the amount of memory available as well as the underlying O/S? There are *many* factors to consider when it comes to the SQLOS running as smoothly as possible, and you need to not only be aware of them but to be able to take action.
model
For most database professionals the opportunity to have servers built to a set of ideal specifications is lost. You are tossed into the fray and asked to tame an environment that is most likely feral. One server has one really big drive with everything on it, another server has two drives mirrored, and a third server has a single NIC card offering up a whopping 10 MB of traffic.
What this means is that at the model level you are often left to fight fires. This is necessary because of all the damage and neglect that has come before you as a result of a general lack of knowledge (either by you, or by others). You need to fight these fires before you will be given the opportunity to build your dream home (servers that are a perfect flavor of vanilla).
So you take your knowledge of SQL Server internals and you apply it to perform query tuning because that is the only real chance for you to demonstrate your value to your end users. Face it, the only time they want you to do something is when performance is not as expected, or needs to be better. That means when the time comes you need to be able to step up and offer some solutions as to how to resolve the bottleneck. And your suggestions will be based upon your knowledge of the SQLOS.
master
I believe that someone who is a master of the internals of SQL Server is someone that knows how to avoid or minimize problems by not allowing them to appear in the first place. You are able to have servers rolled out that are built to a set of specifications that have it optimized for SQL Server performance before SQL is even installed. You are then able to install and configure SQL with a standard set of configuration options, ensuring that your server instances are as alike as possible. Doing so allows for you to easily and quickly troubleshoot problems when they arise.
While you can find examples of standard builds and configurations of servers and instances online the simple fact is that many of those won’t do you any good. Every shop is unique and you need to apply that unique knowledge in order to build servers and instances that are right for you. You cannot just apply some random configuration and expect that it will be perfect solution for you. Having an understanding of the internals as well as the knowledge of your particular business is what will allow for you to be a master and build out your shop in a way to avoid problems before they have a chance to start.
resourcedb
When it comes to the internals of SQL Server I would point to these four individuals as a good starting point.
