Chapter 33. SQL Server Internals

by Ray Rankins

IN THIS CHAPTER

• SQL Server Memory Management

• SQL Server Process Management

• SQL Server Disk I/O

• SQL Server Storage Structures

• Database Files and Filegroups

• Database Pages

• Tables

• Indexes

• Data Modification and Performance

If you are migrating to SQL Server 2000 from versions prior to SQL Server 7.0, you will notice that the physical database architecture in SQL Server 2000 is quite different. The new architecture provides increased performance, better scalability, and improved stability. The new architecture also provides a foundation for features that might be introduced in future versions (for example, table partitioning, intertable clustering, and so on).

Why bother learning about the SQL Server internals at all? Although there are fewer settings to tweak and tune and fewer knobs to turn in SQL Server 2000 and 7.0 than in previous versions, you have a better chance of getting the most out of SQL Server if you have a basic knowledge of the internal architecture. This chapter is also meant for those who just like to know this kind of stuff.

This chapter looks at the internal architecture as well as the storage structures in SQL Server and how the storage structures are maintained and managed. This information will help you better understand various issues raised in many of the subsequent chapters.