Introduction
Several people asked me the same question during the time I worked on this book. “Why have you decided to write yet another book on SQL Server Internals? There are plenty of books on this subject out there, including an excellent one by Kalen Delaney et al., the latest version being entitled, Microsoft SQL Server 2012 Internals, Developer Reference series (Microsoft Press 2013).
To be absolutely honest, I asked myself the same question while I toyed with the idea of writing that book. In the end, I defined two goals:
- I wanted to write a book that explains how SQL Server works while keeping the content as practical as possible.
- I wanted the book to be useful to both database administrators and developers.
There is a joke in SQL Server community: “How do you distinguish between junior- and senior-level database professionals? Just ask them any question about SQL Server. The junior-level person gives you the straight answer. The senior-level person, on the other hand, always answers, “It depends.”
As strange as it sounds, that is correct. SQL Server is a very complex product with a large number of components that depend on each other. You can rarely give a straight yes or no answer to any question. Every decision comes with its own set of strengths and weaknesses and leads to consequences that affect other parts of the system.
This book talks about on what “it depends”. My goal is to give you enough information about how SQL Server works and to show you various examples of how specific database designs and code patterns affect SQL Server behavior. I tried to avoid generic suggestions based on best practices. Even though those suggestions are great and work in a large number of cases, there are always exceptions. I hope that, after you read this book, you will be able to recognize those exceptions and make decisions that benefit your particular systems.
My second goal is based on the strong belief that the line between database administration and development is very thin. It is impossible to be a successful database developer without knowledge of SQL Server Internals. Similarly, it is impossible to be a successful database administrator without the ability to design efficient database schema and to write good T-SQL code. That knowledge also helps both developers and administrators to better understand and collaborate with each other, which is especially important nowadays in the age of agile development and multi-terabyte databases.
I have worn both hats in my life. I started my career in IT as an application developer, slowly moving to backend and database development over the years. At some point, I found that it was impossible to write good T-SQL code unless I understood how SQL Server executes it. That discovery forced me to learn SQL Server Internals, and it led to a new life where I design, develop, and tune various database solutions. I do not write client applications anymore; however, I perfectly understand the challenges that application developers face when they deal with SQL Server. I have “been there and done that.”
I still remember how hard it was to find good learning materials. There were plenty of good books; however, all of them had a clear separation in their content. They expected the reader either to be developer or database administrator — never both. I tried to avoid that separation in this book. Obviously, some of the chapters are more DBA-oriented, while others lean more towards developers. Nevertheless, I hope that anyone who is working with SQL Server will find the content useful.
Nevertheless, do not consider this book a SQL Server tutorial. I expect you to have previous experience working with relational databases — preferably with SQL Server. You need to know RDBMS concepts, be familiar with different types of database objects, and be able to understand SQL code if you want to get the most out of this book.
Finally, I would like to thank you for choosing this book and for your trust in me. I hope that you will enjoy reading it as much as I enjoyed writing it.
How This Book Is Structured
The book is logically separated into eight different parts. Even though all of these parts are relatively independent of each other, I would encourage you to start with Part 1, “Tables and Indexes” anyway. This part explains how SQL Server stores and works with data, which is the key point in understanding SQL Server Internals. The other parts of the book rely on this understanding.
The Parts of the book are as follows:
- Part 1: Tables and Indexes covers how SQL Server works with data. It explains the internal structure of database tables; discusses how and when SQL Server uses indexes, and provides you with the basic guidelines about how to design and maintain them.
- Part 2: Other Things That Matter provides an overview of different T-SQL objects, and it outlines their strengths and weaknesses along with use-cases when they should or should not be used. Finally, this part discusses data partitioning, and provides general system design considerations for systems that utilize SQL Server as a database backend.
- Part 3: Locking, Blocking, and Concurrency talks about the SQL Server concurrency model. It explains the root-causes of various blocking issues in SQL Server, and it shows you how to troubleshoot and address them in your systems. Finally, this part provides you with a set of guidelines on how to design transaction strategies in a way that improves concurrency in systems.
- Part 4: Query Life Cycle discusses the optimization and execution of queries in SQL Server. Moreover, it explains how SQL Server caches execution plans and it demonstrates several plan-caching–related issues commonly encountered in systems.
- Part 5: Practical Troubleshooting provides an overview of the SQL Server Execution Model, and it explains how you can quickly diagnose systems and pinpoint the root-causes of the problems.
- Part 6: Inside the Transaction Log explains how SQL Server works with the transaction log, and it gives you a set of guidelines on how to design Backup and High Availability strategies in systems.
- Part 7: In-Memory OLTP Engine (Hekaton) talks about the new in-memory OLTP engine introduced in SQL Server 2014. It explains how Hekaton works internally and how you can work with memory-optimized data in your systems.
- Part 8: Columnstore Indexes provides an overview of columnstore indexes, which can dramatically improve the performance of Data Warehouse solutions. It covers nonclustered columnstore indexes, which were introduced in SQL Server 2012, along with clustered columnstore indexes, introduced in SQL Server 2014.
As you may have already noticed, this book covers multiple SQL Server versions including the recently released SQL Server 2014. I have noted version-specific features whenever necessary; however, most of the content is applicable to any SQL Server version, starting with SQL Server 2005.
It is also worth noting that most of the figures and examples in this book were created in the Enterprise Edition of SQL Server 2012 with parallelism disabled on the server level in order to simplify the resulting execution plans. In some cases, you may get slightly different results when you run scripts in your environment using different versions of SQL Server.
Downloading the Code
You can download the code used in this book from the Source Code section of the Apress web site (www.apress.com) or from the Publications section of my blog (http://aboutsqlserver.com). The source code consists of SQL Server Management Studio solutions, which include a set of the projects (one per chapter). Moreover, it includes several .Net C# projects, which provide the client application code used in the examples in Chapters 12, 13, 14, and 16.
Contacting the Author
You can visit my blog at: http://aboutsqlserver.com or email me at: [email protected].