The New SQL Server 2005 File System

SQL Server 2005 includes a number of features that improve performance, reliability, availability, capacity, and manageability of database files and filegroups over its predecessors. They are as follows:

• Page checksum and page-level restore combination

• Read-only filegroups on compressed drives

• Instant file initialization

• Database snapshots

• Row-level versioning

• Partitioning

Page Checksum Error 824 and Page-Level Restore

Error code 824 is detected if a checksum error occurs. This is produced by the page checksum feature, which helps increase data protection by giving you an early heads up on I/O errors that go unnoticed by the OS or any underlying hardware. Both data and the log files benefit from the feature when PAGE_VERIFY_CHECKSUM is enabled. (See the ALTER DATABASE statement later in this chapter.) The checksum can also be calculated and verified during BACKUP and RESTORE commands, which increases the reliability of database backups.

Why Do You Care about This?

The larger the database, the higher the chance of data loss going unnoticed. SQL Server will report the error, the operation that caused the error, the page in the database, and the file offset. If you see the error, there may be additional messages in the SQL Server error log or the system event, and they may provide more information about the error. If you get the error, then run DBCC CHECKDB immediately, before database integrity is risked (see Appendix).

If you unable to fix the problem with DBCC, then you may need to restore the affected pages by using online page-level restore (see Chapter 7) that is available in Enterprise Edition. Otherwise, a full restore with and application of transaction logs to save pending transactions may be in order.

Read-Only Filegroups on Compressed Drives

The read-only filegroup option is not new, and we will discuss it shortly However, what is new is that you can now put these files on compressed drives. These will help you in your filegroup design by allowing flexibility in capacity planning and I/O.

Why Do You Care about This?

SQL Server 2005’s support for read-only filegroups on compressed drives helps to improve capacity utilization, manageability, and I/O performance. Any secondary filegroup of a user database that is marked as read-only can be placed on compressed NTFS volumes. If the entire database is set to read-only, all filegroups can be placed on compressed volumes.

Instant File Initialization

SQL Server 2005 includes support for instant file initialization, a feature of the Windows Server 2003 and later operating systems. File pages are typically initialized by a process of writing zeros into the pages before the file gets used, a process that can negatively affect performance when databases are set to grow automatically. Instant file initialization can lessen the impact on server performance with large database files that are growing in size.

SQL Server uses instant initialization for data files and not for transaction logs. It can be used automatically by SQL Server as long as the SQL Server service account is given the Windows SE MANAGE VOLUME NAME privilege. Instant file initialization can occur both at database creation and for tempdb when a server is started up. It can also be applied when modifications are made to a database using ALTER DATABASE ... MODIFY FILE statements (discussed later in this chapter).

Why Do You Care about This?

The zeroing process can have a negative affect on performance during modifications that trigger an autogrow. So when SQL Server 2005 is given the ability to use the instant file initialization feature, the zeroing out of data pages is skipped and the time to initialize a file for the creation of a very large database (VLDB) and tempdb is reduced.

Now if you decide to run SQL Server under the Network Service account (see Chapter 5), this permission defaults to OFF and you will need to enable it by the correct application of rights.

Database Snapshots

Database snapshots are a feature of the Enterprise Edition and are discussed in Chapter 7. However, if you plan to implement this feature, you should be aware of the effect it has on I/O performance and space requirements.

Why Do You Care about This?

Obviously storage space is taken by snapshots, but they are still better than full copies of the database because they use the sparse file technology provided only by NTFS, which is used to maintain efficiency.

There is one snapshot file for each data file in the source database. A page is copied to a snapshot file when it is updated in the source database. This mechanism is referred to as copy-on-write. The combinations of the pages in the snapshot and those that have not changed in the source database give a consistent view of the database at the time the snapshot was taken. Remember that if all pages in the source database are updated, the snapshot file can grow to a size similar to that of the source database file.

I/O performance can be negatively impacted when making a first-time update to a page on the source database because this leads to an extra write of the source page to the snapshot database. However, any subsequent changes to the copied page do not incur this extra write. Therefore, if a source database experiences only localized updates to certain pages, a snapshot database will have little effect on the I/O performance and storage efficiency. However, if there are many database snapshots of the same source database, a single modification to the source database can cause a ripple of several writes to each appropriate database snapshot. See Chapter 7 for more information on the snapshots.

Row-Level Versioning

As mentioned in Chapter 2, SQL Server 2005 includes a new technology called row-level versioning, which provides improved concurrent access, translating into higher throughput and performance. Row-level versioning is integrated with the following SQL Server 2005 features, discussed in Chapters 12 and 17:

• Snapshot isolation

• Multiple active result sets (MARS)

• Online index operations

Why Do You Care about This?

Besides the advantages just discussed, row-level versioning requires storage space on tempdb that affects the I/O performance of tempdb during reads and updates. This is caused by the additional I/O taken to maintain and retrieve old versions. Here’s the issue in a nutshell: When a row in a table or index is updated, the old row gets copied to the version table that resides in tempdb. The row is stamped with the transaction sequence number of the transaction that performed the update, and the new record maintains a pointer to the old record in tempdb. However, the old record might still have a pointer to a yet older record, which in turn might point to an even older record. The result is a version chain that begins to impact transaction performance. See Chapters 13 and 17 for further guidance.

Data Partitioning

Data partitioning, mentioned in Chapter 2, is a feature that is available in SQL Server 2005 Enterprise Edition. As you may be aware, partitioning horizontally divides tables or indexes or both into smaller segments, or partitions. These can then be maintained and accessed independently from one other. This is a huge improvement over SQL Server 2000’s partitioned views.

Why Do You Care about This?

Partitions must be assigned to filegroups, and thus the maintenance and performance gains of filegroups can be taken advantage of with a well-thought-out partition scheme. Designing for partitioning will impact your database and storage design, especially when setting up clusters and other high-availability solutions. See Chapter 9 for more information.