Extensible Storage Engine (ESE), also known as JET Blue, is an ISAM (indexed sequential access method) data storage technology from Microsoft. ESE is the core of Microsoft Exchange Server, Active Directory, and Windows Search. It is also used by a number of Windows components including Windows Update client and Help and Support Center. Its purpose is to allow applications to store and retrieve data via indexed and sequential access.
ESE provides transacted data update and retrieval. A crash recovery mechanism is provided so that data consistency is maintained even in the event of a system crash. Transactions in ESE are highly concurrent making ESE suitable for server applications. ESE caches data intelligently to ensure high performance access to data. In addition, ESE is lightweight making it suitable for auxiliary applications.
The ESE Runtime (ESENT.DLL) has shipped in every Windows release since Windows 2000, with native x64 version of the ESE runtime shipping with x64 versions of Windows XP and Windows Server 2003. Microsoft Exchange, up to Exchange 2003 shipped with only the 32-bit edition, as it was the only supported platform. With Exchange 2007, it ships with the 64-bit edition.
Databases A database is both a physical and logical grouping of data. An ESE database looks like a single file to Windows. Internally the database is a collection of 2, 4, 8, 16, or 32 KB pages (16 and 32 KB page options are only available in Windows 7 and Exchange 2010),In this context 1 KB = 1024 B arranged in a balanced B-tree structure. These pages contain meta-data to describe the data contained within the database, data itself, indexes to persist interesting orders of the data, and other information. This information is intermixed within the database file but efforts are made to keep data used together clustered together within the database. An ESE database may contain up to 232 pages, or 16 terabytes of data,In this context 1 TB = 10244 B for 8 kilobyte sized pages.
ESE databases are organized into groups called instances. Most applications use a single instance, but all applications can also use multiple instances. The importance of the instance is that it associates a single recovery log series with one or more databases. Currently, up to 6 user databases may be attached to an ESE instance at any time. Each separate process using ESE may have up to 1024 ESE instances.
A database is portable in that it can be detached from one running ESE instance and later attached to the same or a different running instance. While detached, a database may be copied using standard Windows utilities. The database cannot be copied while it is being actively used since ESE opens database files exclusively. A database may physically reside on any device supported for directly addressable I/O operations by Windows.
Tables A table is a homogeneous collection of records, where each record has the same set of columns. Each table is identified by a table name, whose scope is local to the database in which the table is contained. The amount of disk space allocated to a table within a database is determined by a parameter given when the table is created with the CreateTable operation. Tables grow automatically in response to data creation.
Tables have one or more indexes. There must be at least one clustered index for record data. When no clustered index is defined by the application, an artificial index is used which orders and clusters records by the chronological order of record insertion. Indexes are defined to persist interesting orders of data, and allow both sequential access to records in index order, and direct access to records by index column values. Clustered indexes in ESE must also be primary, meaning that the index key must be unique.
Clustered and non-clustered indexes are represented using B+ trees. If an insert or update operation causes a page to overflow, the page is split: a new page is allocated and is logically chained in between the two previously adjacent pages. Since this new page is not physically adjacent to its logical neighbors, access to it is not as efficient. ESE has an on-line compaction feature that re-compacts data. If a table is expected to be frequently updated, space may be reserved for future insertions by specifying an appropriate page density when creating a table or index. This allows split operations to be avoided or postponed.
Records and columns A record is an associated set of column values. Records are inserted and updated via Update operations and can be deleted via Delete operations. Columns are set and retrieved via SetColumns and RetrieveColumns operations, respectively. The maximum size of a record is 8110 bytes for 8 kilobyte pages with the exception of long value columns. Column types of LongText and LongBinary do not contribute significantly to this size limitation, and records can hold data much larger than a database page size when data is stored in long value columns. When a long value reference is stored in a record, only 9 bytes of in-record data are required. These long values may themselves be up to 2 gigabytes (GB) in size.
Records are typically uniform in that each record has a set of values for the same set of columns. In ESE, it is also possible to define many columns for a table, and yet have any given record contain only a small number of non-NULL column values. In this sense, a table can also be a collection of heterogeneous records.
ESE supports a wide range of columns values, ranging in size from 1-bit to 2 GB. Choosing the correct column type is important because the type of a column determines many of its properties, including its ordering for indexes. The following data types are supported by ESE:
Column types Name Description Bit ternary value (NULL, 0, or 1) Unsigned Byte1-byte unsigned integer Short 2-byte signed integer Unsigned Short 2-byte unsigned integer Long 4-byte signed integer Unsigned Long 4-byte unsigned integer LongLong 8-byte signed integer UnsignedLongLong 8-byte unsigned integer Currency8-byte signed integer IEEE Single 4-byte floating-point number IEEE Double 8-byte floating-point number DateTime8-byte date-time (integral date, fractional time) GUID 16-byte unique identifier BinaryBinary string, length <= 255 bytes TextANSI or Unicode string, length <= 255 bytes Long Binary Large binary string, length < 2 GB Long TextLarge ANSI or Unicode string, length < 2 GB
Fixed, variable and tagged columns Each ESE table can define up to 127 fixed length columns, 128 variable length columns and 64,993 tagged columns. Fixed columns are essentially columns that take up the same amount of space in each record, regardless of their value. Fixed columns take up a 1-bit to represent NULLity of the column value and a fixed amount of space in each record in which that column, or a later defined fixed column, is set. Variable columns are essentially columns that take up a variable amount of space in each record in which they are set, depending upon the size of the particular column value. Variable columns take up 2-bytes to determine NULLity and size, and a variable amount of space in each record in which that column is set. Tagged columns are columns that take no space whatsoever if they are not set in a record. They may be single valued but can also be multi-valued. The same tagged column may have multiple values in a single record. When tagged columns are set in a record, each instance of a tagged column takes approximately 4-bytes of space in addition to the size of the tagged column instance value. When the number of instances of a single tagged column is large, the overhead per tagged column instance is approximately 2-bytes. Tagged columns are ideal for sparse columns because they take no space whatsoever if they are not set. If a multi-valued tagged column is indexed, the index will contain one entry for the record for each value of the tagged column.
For a given table, columns fall into one of two categories: those which either occur exactly once in each of the records, with possibly a few NULL values; and those which occur rarely, or which may have multiple occurrences in a single record. Fixed and variable columns belong to the former category, while tagged columns belong to the latter. The internal representation of the two column categories is different, and it is important to understand the trade offs between the column categories. Fixed and variable columns are typically represented in every record, even when the occurrence has a NULL value. These columns can be quickly addressed via an offset table. Tagged column occurrences are preceded by a column identifier and the column is located by binary searching the set of tagged columns.
Long values
Column types of Long Text and Long Binary are large binary objects. They are stored in separate B+tree from the clustered index keyed by long value id and byte offset. ESE supports append, byte range overwrite, and set size for these columns. Also, ESE has a single instance store feature where multiple records may reference the same large binary object, as though each record had its own copy of the information, i.e. without inter-record locking conflicts. The maximum size of a Long Text or Long Binary column value is 2 GB.
Version, auto-increment and escrow columns
Version columns are automatically incremented by ESE each time a record containing this column is modified via an Update operation. This column cannot be set by the application, but can only be read. Applications of version columns include being used to determine if an in-memory copy of a given record needs to be refreshed. If the value in a table record is greater than the value in a cached copy then the cached copy is known to be out of date. Version columns must be of type Long.
Auto increment columns are automatically set by ESE such that the value contained in the column is unique for every record in the table. These columns, like version columns, cannot be set by the application. Auto increment columns are read only, and are automatically set when a new record is inserted into a table via an Update operation. The value in the column remains constant for the life of the record, and only one auto increment column is allowed per table. Auto increment columns may be of type Long or type Currency.
Escrow columns can be modified via an EscrowUpdate operation. Escrowed updates are numeric delta operations. Escrow columns must be of type Long. Examples of numeric delta operations include adding 2 to a value or subtracting 1 from a value. ESE tracks the change in a value rather than the end value of an update. Multiple sessions may each have outstanding changes made via EscrowUpdate to the same value because ESE can determine the actual end value regardless of which transactions commit and which transactions rollback. This allows multiple users to concurrently update a column by making numeric delta changes. Optionally, database engine can erase records with zero value of the column. A common use for such escrow column is reference counter: many threads increment/decrement the value without locks, and when the counter reaches zero, the record automatically gets deleted.
Indexes
An index is a persisted ordering of records in a table. Indexes are used for both sequential access to rows in the order defined, and for direct record navigation based on indexed column values. The order defined by an index is described in terms of an array of columns, in precedence order. This array of columns is also called the index key. Each column is called an index segment. Each index segment may be either ascending or descending, in terms of its ordering contribution. Any number of indexes may be defined for a table. ESE provides a rich set of indexing features.
Clustered indexes