Why Learn About SQL Server Internals?

I remember once, right after my first book “Inside SQL Server 7” (Microsoft Press) came out, someone I met at a conference asked, “How did you learn all these great things about SQL Server?” Because it would have taken more time than I had right then to give him a full answer, I turned the tables and asked him “Are you asking because you want to know how you could learn all these things?” and when he said “Yes,” I answered, “Just read my book!”

As flippant as that answer was, there isn’t really any other simple answer.  I’ve been working with SQL Server for a long time, and I’ve picked up details about its inner working in all kinds of ways. I doubt anyone could follow the same path these days. For example, my first job with SQL Server was working as a support engineer for Sybase. The company was really small back then, and when we got a case we couldn’t solve, we called upstairs to the development group and asked for help. Several times, the CTO (Bob Epstein) came down to my desk and talked to the customer right there on speaker phone, so I could hear the entire conversation. I learned quite a bit that way.

I’ve always loved learning the way the product actually works; the more details, the better. So I just naturally assumed that there would be many other people out there who would also like to learn details. I started writing newsletters and articles soon after I switched from Sybase to Microsoft SQL Server. My conferences sessions on “SQL Server Secrets” always get large audiences, and I still frequently see questions on the public help forums where people are asking for the “official” list of undocumented trace flags and DBCC commands, which, of course, is sort of an oxymoron. (Just for the record, there’s no such list.) 

But there’s also another type of technical professional, who might wonder WHY we need to know these gory internal details. How will a trace flag that writes to the error log every time a checkpoint occurs help me maintain my system? How will a DBCC command that prints out the actual contents of a data page or index page help me write better applications? Every once in a while, a student in one of my five-day SQL Server Internals classes will ask me such questions. This type of question usually makes me wonder why they even enrolled in the class if they didn’t want to learn about internals, but the class is actually called SQL Server Internals and Tuning, so I just assume they thought it was really about tuning, and they were hoping I would just tell them how to enable the “go fast” switch. Note that “tuning” is the second topic word and “internals” is the first. Even more often, I get people asking this question in the middle of a shorter seminar or conference presentation, when I start talking about DBCC PAGE or the format of SQL Server allocation structures.

Most likely, everyone who wants to learn about internals has their own reasons for wanting to do so. And if you’re not interested, I probably can’t convince you to become interested. But I do find that the more you know about the way SQL Server really works, the more confidence you have when starting to troubleshoot performance or behavior problems with your SQL Server databases or applications, or when trying to convince your coworkers to take your ideas seriously.

That doesn’t mean that there aren’t valid reasons for wanting to know how to use some of the undocumented commands.  For example, if you’re curious about how exactly page split splits your page (is it always a 50/50 split?) or what happens when you split a page and the new row is more than 50 percent of the size of a page, you can use DBCC PAGE to actually see what happens to the page contents after a split. (You can also read my blog post “Geek City: Splitting a page into multiple pages” to find out exactly how to use the DBCC PAGE command, as well as the undocumented DBCC IND command to analyze page splits.)  Of course, someone could then ask “WHY do we really need to know exactly how a page split occurs?”

The fact is that there are questions about SQL Server’s behavior that you might be able to answer for yourself by exploring the internal structures and components. If you aren’t interested in those questions, or their answers, you might not care at all about understanding SQL Server internals. However, for those of you who feel that the more you know, the better you’ll be able to tune and manage your SQL Server and its applications, keep in mind that a secret is only a secret until you know it.