Tags: SQL Server Internals
I’ve been writing and teaching about SQL Server internals for over 25 years. While there is usually a lot of interest in my books and classes, there are always people who ask, frequently after about the first day of my 5-day class, “What’s the point? Why is this information useful to know?”
What is “Internals”?
First of all, we need to answer the question “What is Internals?” For SQL Server, the short answer is “The way SQL Server works internally!” Internals education looks to answer some of these questions:
• How does SQL Server manage memory, processors, i/o and other physical resources?
• How is data physically stored, written, retrieved and cached?
• How are queries optimized and processed?
• How are query plans cached and managed?
• What is the transaction log used for and how it is managed by SQL Server?
• What does SQL Server do to protect data during concurrent access by multiple processes?
The diagram shown below is one I frequently use to show the various internal components of SQL Server. The engine is divided into four layers.
“”
- Language Processing deals with the client/server interface and validates that the batch you’re submitting to SQL Server is legal Transact-SQL.
- The Query Processor is made up of the optimizer and the execution engine which processes and runs your queries.
- The Storage Engine manages the relationship between the queries and the data.
- The SQLOS deals with components that interact directly with the Operating System. In fact, you can think of the SQLOS as a “mini” operating system inside SQL Server that deals with scheduling of requests, memory management, i/o to and from the buffer pool, etc.
In my SQL Server Internals classes, I focus heavily on the middle two layers. We cover the storage engine in great detail, and also look at the query processor. For query processing, the focus is on execution plans and how to tune them. So although my training, and the topic of “internals” in general, is not synonymous with “performance tuning”, there definitely is an overlap. The more you know about the way things work, the easier it will be to determine when and WHY things are not working as well as they could.
One of my favorite stories from a student happened about 5 years ago. Joe was actually one of the people who was very concerned on the first day that the information didn’t seem particular ‘useful’. But he was committed, and there for the week, so he paid attention and tried to learn as much as he could. He actually knew a whole lot already about query tuning, and was an excellent DBA, from what I could tell. At the end of the week he admitted that although he knew a lot of the information already, he hadn’t really understood how all the pieces fit together and why certain indexes or query choices were better than others. I got an email from him a couple of weeks later. He told me he had been trying to persuade his team that a certain indexing scheme would improve performance, but no one would believe him. After the class, he made exactly the same suggestion in the weekly meeting, and this time, it was accepted. He didn’t offer any more ‘proof’, he just stated his opinion. The suggestion was implemented, reducing the processing time of a couple of critical queries by over 90%. Joe told me that he attributed his colleagues acceptance of his recommendation as being solely due to his increased confidence that he knew what he was talking about!
Is “internals” information undocumented?
Some people think “internals” means “undocumented”, and again, there is an overlap. Some information, like the exact format of SQL Server log records, is undocumented. The exact algorithm SQL Server uses to determine what percentage of data in an index to sample when updating the statistics has never been published. Some undocumented information is undocumented because it is proprietary. But some is undocumented just because Microsoft hasn’t gotten around to documenting it yet. In addition, certain metadata and dynamic management objects are undocumented, because documenting them would require maintaining them and guaranteeing that they won’t change, and Microsoft doesn’t want to do that for certain objects.
My training does include many undocumented features, and I’ll tell you what is undocumented, why it is undocumented (if I know) and tell you why it’s useful to know.
Why is internals information useful?
I’ve partly answered this already, so let me just summarize here:
- Knowing how SQL Server is ‘supposed to’ work can help you track down and fix performance issues.
- Knowing what is going on under the covers can help you understand and troubleshoot errors.
- Understanding the way SQL Server works can give you greater confidence in all your database related work.
Community input:
I asked this same question on Twitter last week, and I’d like to end by sharing some of the responses I received.
WHY is it useful to learn about SQL Server Internals?
- “Because I know the devs aren’t going to do what I say so I need to show them why I said it.
- “Because building steady future-proof solutions could be done so much better with knowledge about how the product works internally”
- “Designing the right way in the beginning from an understanding of how things work best makes life infinitely easier.”
- “Knowing how stats and indexes are considered in query plans is a must. That’s where internals fill the gaps and tell us the magic.”
- “Where to begin; not to blow smoke. Knowing how and why the database engine does what it does is pivotal to tuning queries.
- “To be able to explain to sysadmins why SQL Server uses so much memory.”
- “If you understand the way things work it provides you insight into how things may ‘break’ “
- “Learning about internals is much less expensive than hiring a knowledgeable consultant.”
Hopefully, you’re convinced now! Through DB Best Technologies, I am now presenting my entire 5-day SQL Server Internals training as a series of webinars. I have presented the first 5 hours already. You can find the announcements of upcoming webinars here: /events/ and recordings of past webinars here: /videos/
