8.2. Data Performance
The performance of data simply means how well the data will perform under a load. Obviously, you want to have as much performance as possible. This section will cover pages, partitioning, indexing, and other topics that have a great impact on data performance.
8.2.1. Pages
Most everything covered so far culminates here with a discussion about pages. SQL Server 2005 utilizes eight different page types; here we'll only discuss data pages. Intimately understanding data pages is a key for optimizing performance. The reason it's key is that the page is the fundamental unit of storage within SQL Server. Disk I/O occurs at the page level. SQL Server's locking mechanism defaults to the page level.
8.2.1.1. Data Pages
Data pages will be described first. These pages contain all the data, with the possible exception of the large value data types, described earlier, which may be stored on Text/Image pages (described later). Data pages are 8KB (8,192 bytes) in size. The data page is divided into three main sections. The first section is a 96-byte header, which contains information about the page. The next section contains the data rows themselves. The final section is a row offset array (see Figure 8-18).
Figure 8-18. Figure 8-18
8.2.1.1.1. Page Headers
Table 8-4 describes the content of the page header.
8.2.1.1.2. Data Rows
The table's actual data rows are next ...
Get Professional SQL Server® 2005 Performance Tuning now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
