Database Reference
In-Depth Information
Figure 1-18.
Data modification: Checkpoint
The insert process works in a similar matter. SQL Server reads the data page where new data row need to be
inserted into the buffer pool, or it allocates a new extent/page if needed. After that, SQL Server synchronously saves
the transaction log record, inserts a row into the page, and asynchronously saves the data page to the disk.
The same process transpires with deletions. As already mentioned, when you delete a row, SQL Server does
not physically remove the row from the page. Rather, it flags deleted rows as ghosted (deleted) in the status bits. This
speeds up deletion and allows SQL Server to undo it quickly if necessary.
The deletion process also sets a flag in the PFS page indicating that there is a ghosted row on the page. SQL Server
removes ghosted rows in background through a task called the
ghost cleanup task
.
There are two key points that you need to remember. First, when SQL Server processes DML queries(select,
insert, update, delete, and merge), it never works with the data without first loading the data pages into the buffer
pool. Second, when you modify the data, SQL Server synchronously writes log records to the transaction log. The
modified data pages are saved to the data files asynchronously in background.
Much Ado About Data Row Size
As you already know, SQL Server is a very I/O intensive application. SQL Server can generate an enormous amount of
I/O activity, especially when it deals with large databases accessed by a large number of concurrent users.
There are many factors that affect the performance of queries, and the number of I/O operations involved is at
the top of the list; that is, the more I/O operations a query needs to perform, the more data pages it needs to read and
slower it gets.
The size of a data row affects how many rows will fit in a data page. Large data rows require more pages to
store the data and, as a result, increase the number of I/O operations during scans. Moreover, objects will use more
memory in the buffer pool.
Let's look at the following example and create two tables, as shown in Listing 1-16. The first table,
dbo.LargeRows
,
uses a
char(2000)
fixed-length data type to store the data. As a result, you can fit only four rows per data page,
regardless of the size of
Col
data. The second table,
dbo.SmallRows
, uses a
varchar(2000)
variable-length data type.
Let's populate both of the tables with the same data.
