Summary

In this chapter, you've been provided with an inside look at SQL Server's internal processes including how memory and I/O are managed. You also were provided with an in-depth look at the SQL Server storage structures. You've seen that databases are created on a set of database files, and those files can be configured to grow automatically, if desired. The smallest unit of I/O and the basic storage construct in SQL Server is a page, which is 8KB in size. These pages are allocated in blocks of 8 called extents. Extents can be shared among tables, resulting in less wasted storage for small tables. Using filegroups, you can more finely control your file placement (or set of files) and specify in which filegroup a table or index should be stored.

You should also now have a better understanding of the table, row, and clustered and nonclustered index structures and how they are maintained by SQL Server during data modifications. You also were introduced to some helpful tools (such as DBCC PAGE) to examine the internals of SQL Server to help you further understand its inner workings.

An understanding of SQL Server internals is not a requirement for administering or developing applications for SQL Server, but if you really want to understand why SQL Server performs the way it does, or if you need to really get in and troubleshoot performance problems, a solid understanding of the internals of SQL Server will prove invaluable. Much of this information will help you better understand the concepts presented in the remaining performance-related chapters of this book.