Minimalistic, non-robust program to test the effects of prefetching and defragmentation on ESENT databases. With sample results for one database.

EseDefrag.cpp

// Compile as cl.exe /EHsc EseDefrag.cpp

#include <Windows.h>
#include <stdio.h>
#include <esent.h>
#include <vector>
#include <unordered_set>
#include <string>
#include <cstdint>
#include <memory>

#pragma comment(lib, "esent.lib")
#pragma comment(lib, "winmm.lib")


_Check_return_
static bool TryMove(JET_SESID sesid, JET_TABLEID tableid, long move)
{
    const JET_ERR err = ::JetMove(sesid, tableid, move, JET_bitNil);
    if (JET_errNoCurrentRecord == err)
    {
        return false;
    }

    if (err != JET_errSuccess)
    {
        throw err;
    }

    return true;
}


_Check_return_
static bool TryMoveFirst(JET_SESID sesid, JET_TABLEID tableid)
{
    return TryMove(sesid, tableid, JET_MoveFirst);
}

_Check_return_
static bool TryMoveNext(JET_SESID sesid, JET_TABLEID tableid)
{
    return TryMove(sesid, tableid, JET_MoveNext);
}


static std::string RetrieveColumnAsString(JET_SESID sesid, JET_TABLEID tableid, JET_COLUMNID columnid)
{
    std::unique_ptr<char[]> pBackupBuf;

    char smallbuf[50];
    unsigned long datasize = sizeof(smallbuf);

    char* buf = smallbuf;

    JET_ERR err = JET_wrnBufferTruncated;
    while (err == JET_wrnBufferTruncated)
    {
        unsigned long actual;
        err = ::JetRetrieveColumn(sesid, tableid, columnid, buf, datasize, &actual, JET_bitRetrieveCopy, nullptr);

        if (err < JET_errSuccess)
            throw err;

        if (err == JET_wrnColumnNull)
            return std::string();

        if (err == JET_wrnBufferTruncated)
        {
            datasize = actual;
            pBackupBuf.reset(new char[datasize]);
            buf = pBackupBuf.get();
        }
        else
        {
            datasize = actual;
        }
    }

    return std::string(buf, datasize);

}

static std::vector<std::string> GetTableNames(JET_SESID sesid, JET_DBID dbid)
{
    std::vector<std::string> tableNames;

    JET_OBJECTLIST objectList;
    objectList.cbStruct = sizeof(objectList);
    JET_ERR err = JetGetObjectInfoA(sesid, dbid, JET_objtypTable, nullptr, nullptr, &objectList, objectList.cbStruct, JET_ObjInfoListNoStats);

    if (err != 0)
    {
        throw err;
    }

    if (objectList.cRecord > 0)
    {
        tableNames.reserve(objectList.cRecord);

        for (bool moveSuccess = TryMoveFirst(sesid, objectList.tableid); moveSuccess == true; moveSuccess = TryMoveNext(sesid, objectList.tableid))
        {
            auto name = RetrieveColumnAsString(sesid, objectList.tableid, objectList.columnidobjectname);
            tableNames.push_back(name);
        }
    }

    JetCloseTable(sesid, objectList.tableid);

    return tableNames;
}

std::unordered_set<std::string> GetIndexNames(JET_SESID sesid, JET_TABLEID tableid)
{
    std::unordered_set<std::string> inxNames;

    JET_INDEXLIST inxList = {};
    inxList.cbStruct = sizeof(inxList);

    JET_ERR err = JetGetTableIndexInfoA(sesid, tableid, "", &inxList, sizeof(inxList), JET_IdxInfoList);
    if (err != JET_errSuccess)
    {
        throw err;
    }

    for (bool moveSuccess = TryMoveFirst(sesid, inxList.tableid); moveSuccess == true; moveSuccess = TryMoveNext(sesid, inxList.tableid))
    {
        inxNames.insert(RetrieveColumnAsString(sesid, inxList.tableid, inxList.columnidindexname));
    }

    JetCloseTable(sesid, inxList.tableid);

    return inxNames;
}

static JET_THREADSTATS GetEseThreadStats()
{
    JET_THREADSTATS stats = {};
    stats.cbStruct = sizeof(stats);
    ::JetGetThreadStats(&stats, stats.cbStruct);

    return stats;
}


static void TimeTableIteration(JET_SESID sesid, JET_TABLEID tableid, const char* tableName, const char* indexName)
{
    const DWORD startTime = timeGetTime();

    const JET_THREADSTATS startStats = GetEseThreadStats();

    ::JetSetCurrentIndex2A(sesid, tableid, indexName, JET_bitMoveFirst);
    unsigned long ulRecs = 0;
    for (bool bMoveSuccess = TryMoveFirst(sesid, tableid); bMoveSuccess; bMoveSuccess = TryMoveNext(sesid, tableid))
    {
        ++ulRecs;
    }
    const DWORD endTime = timeGetTime();
    const JET_THREADSTATS endStats = GetEseThreadStats();

    printf("In table '%s' by index '%s', there are %u records. Elapsed: %.3lf seconds\n", tableName, indexName == nullptr ? "(primary)" : indexName, ulRecs, (endTime - startTime) / 1000.);
    printf("Read %u pages. Referenced %u pages. Preread %u pages.\n",
        endStats.cPageRead - startStats.cPageRead,
        endStats.cPageReferenced - startStats.cPageReferenced,
        endStats.cPagePreread - startStats.cPagePreread);
}

static void TimeDatabaseIteration(bool prefetch, JET_SESID sesid, JET_DBID dbid)
{
    for (const std::string& table : GetTableNames(sesid, dbid))
    {
        JET_TABLEID tableid;

        JET_GRBIT openOpts = JET_bitTableReadOnly | JET_bitTableSequential;

        if (prefetch)
        {
            openOpts |= JET_bitTablePreread;
            openOpts |= JET_bitTableOpportuneRead;
        }

        if (JetOpenTableA(sesid, dbid, table.c_str(), nullptr, 0, openOpts, &tableid) == JET_errSuccess)
        {
            for (const auto& inx : GetIndexNames(sesid, tableid))
            {
                TimeTableIteration(sesid, tableid, table.c_str(), inx.c_str());
                TimeTableIteration(sesid, tableid, table.c_str(), inx.c_str());
                puts("");
            }
            ::JetCloseTable(sesid, tableid);
        }
        else
        {
            fprintf(stderr, "Failed to open table %s\n", table.c_str());
        }
    }

}




static HANDLE g_hDefragCompletedEvent = nullptr;


static JET_ERR JET_API DefragCallBack(_In_ JET_SESID      ,
                                      _In_ JET_DBID       ,
                                      _In_ JET_TABLEID    ,
                                      _In_ JET_CBTYP      ,
                                      _Inout_opt_ void*   ,
                                      _Inout_opt_ void*   ,
                                      _In_opt_ void *     ,
                                      _In_ JET_API_PTR    )
{
    if (g_hDefragCompletedEvent != nullptr)
    {
        SetEvent(g_hDefragCompletedEvent);
    }

    return JET_errSuccess;
}




// One possible error-handling strategy is to jump to an error-handling
// label whenever an ESENT call fails.

#define Call(func)  \
    do { \
        err = (func); \
        if (err < JET_errSuccess) { \
            goto HandleError; \
        } \
        __pragma(warning(suppress:4127)) \
    } while (false)



int main(int argc, char* argv [])
{
    const char* szFileName = nullptr;
    const char* szTxLogsPath = ".\\";

    bool justDoTiming = false;
    bool prefetch = false;

    for (int i = 1; i < argc; i++)
    {
        switch (argv[i][0])
        {
        case '/':
        case '-':
            switch (argv[i][1])
            {
            case 'l':
            case 'L':
                szTxLogsPath = &argv[i][2];
                break;
            case 't':
            case 'T':
                justDoTiming = true;
                break;
            case 'p':
            case 'P':
                prefetch = true;
                break;
            default:
                fprintf(stderr, "Invalid command line flag: %s.", argv[i]);
                return 1;
            }
            break;
        default:
            if (szFileName != nullptr)
            {
                fprintf(stderr, "Database file already specified.\n");
                return 1;
            }
            szFileName = argv[i];
        }

    }

    if (*szTxLogsPath == '\0')
    {
        fprintf(stderr, "Invalid transaction log file path.\n");
        return 2;
    }

    if (szFileName == nullptr)
    {
        fprintf(stderr, "Must specify the database filename.\n");
        return 1;
    }

    JET_ERR err;

    unsigned long dwPageSize = 8192;
    Call(JetGetDatabaseFileInfoA(szFileName, &dwPageSize, sizeof(dwPageSize), JET_DbInfoPageSize));

    Call(JetSetSystemParameterW(nullptr, JET_sesidNil, JET_paramDatabasePageSize, dwPageSize, nullptr));
    JET_INSTANCE instance;


    Call(JetCreateInstanceW(&instance, L"esedefrag"));
    Call(JetSetSystemParameterA(&instance, JET_sesidNil, JET_paramLogFilePath, 0, szTxLogsPath));
    Call(JetSetSystemParameterA(&instance, JET_sesidNil, JET_paramSystemPath, 0, szTxLogsPath));

    const JET_API_PTR uiNewMinCacheSizeMegs = 1024;
    const double dPagesToMBRatio = dwPageSize / static_cast<double>(1024 * 1024);
    Call(::JetSetSystemParameter(nullptr, JET_sesidNil, JET_paramCacheSizeMin, static_cast<JET_API_PTR>(uiNewMinCacheSizeMegs / dPagesToMBRatio), nullptr));


    Call(JetInit(&instance));
    JET_SESID sesid;
    Call(JetBeginSessionW(instance, &sesid, 0, 0));


    Call(JetAttachDatabaseA(sesid, szFileName, JET_bitNil));
    JET_DBID dbid;
    Call(JetOpenDatabaseA(sesid, szFileName, nullptr, &dbid, JET_bitNil));

    if (justDoTiming)
    {
        TimeDatabaseIteration(prefetch, sesid, dbid);
    }
    else
    {
        g_hDefragCompletedEvent = CreateEventW(nullptr, TRUE, FALSE, nullptr);

        if (g_hDefragCompletedEvent != nullptr)
        {
            unsigned long ulPasses = 1;

            Call(::JetSetSystemParameterW(&instance, sesid, JET_paramEnableOnlineDefrag, JET_OnlineDefragAll, nullptr));

            const auto start = timeGetTime();
            Call(JetDefragment2A(sesid, dbid, nullptr, &ulPasses, nullptr, DefragCallBack, JET_bitDefragmentBatchStart));
            ::WaitForSingleObject(g_hDefragCompletedEvent, INFINITE);
            const auto end = timeGetTime();
            printf("Defrag took %.2lf seconds\n", (end - start) / 1000.0);

            ::CloseHandle(g_hDefragCompletedEvent);
        }
        else
        {
            fprintf(stderr, "Failed to allocate defrag completed event\n");
        }
    }

    JetEndSession(sesid, 0);
    JetTerm(instance);

    return 0;

HandleError:
    printf("ESENT error %d\n", err);
    return 1;

}

postdefrag, no prefetch

In table 'main' by index 'data', there are 25 records. Elapsed: 0.031 seconds
Read 5 pages. Referenced 31 pages. Preread 0 pages.
In table 'main' by index 'data', there are 25 records. Elapsed: 0.000 seconds
Read 0 pages. Referenced 31 pages. Preread 0 pages.

In table 'main' by index 'key', there are 25 records. Elapsed: 254.066 seconds
Read 41713 pages. Referenced 41738 pages. Preread 0 pages.
In table 'main' by index 'key', there are 25 records. Elapsed: 1.951 seconds
Read 340 pages. Referenced 41738 pages. Preread 0 pages.

In table 'MSysObjects' by index 'Id', there are 136 records. Elapsed: 0.000 seconds
Read 0 pages. Referenced 141 pages. Preread 0 pages.
In table 'MSysObjects' by index 'Id', there are 136 records. Elapsed: 0.000 seconds
Read 0 pages. Referenced 141 pages. Preread 0 pages.

In table 'MSysObjects' by index 'Name', there are 136 records. Elapsed: 0.000 seconds
Read 0 pages. Referenced 137 pages. Preread 0 pages.
In table 'MSysObjects' by index 'Name', there are 136 records. Elapsed: 0.000 seconds
Read 0 pages. Referenced 137 pages. Preread 0 pages.

In table 'MSysObjects' by index 'RootObjects', there are 20 records. Elapsed: 0.015 seconds
Read 0 pages. Referenced 21 pages. Preread 0 pages.
In table 'MSysObjects' by index 'RootObjects', there are 20 records. Elapsed: 0.000 seconds
Read 0 pages. Referenced 21 pages. Preread 0 pages.

In table 'MSysObjectsShadow' by index 'Id', there are 136 records. Elapsed: 0.000 seconds
Read 2 pages. Referenced 141 pages. Preread 0 pages.
In table 'MSysObjectsShadow' by index 'Id', there are 136 records. Elapsed: 0.000 seconds
Read 0 pages. Referenced 141 pages. Preread 0 pages.

In table 'MSysUnicodeFixupVer2' by index 'secondary', there are 0 records. Elapsed: 0.000 seconds
Read 1 pages. Referenced 1 pages. Preread 0 pages.
In table 'MSysUnicodeFixupVer2' by index 'secondary', there are 0 records. Elapsed: 0.000 seconds
Read 0 pages. Referenced 1 pages. Preread 0 pages.

In table 'MSysUnicodeFixupVer2' by index 'primary', there are 0 records. Elapsed: 0.000 seconds
Read 0 pages. Referenced 1 pages. Preread 0 pages.
In table 'MSysUnicodeFixupVer2' by index 'primary', there are 0 records. Elapsed: 0.000 seconds
Read 0 pages. Referenced 1 pages. Preread 0 pages.

postdefrag, with prefetch

In table 'main' by index 'data', there are 25 records. Elapsed: 0.031 seconds
Read 5 pages. Referenced 31 pages. Preread 0 pages.
In table 'main' by index 'data', there are 25 records. Elapsed: 0.000 seconds
Read 6 pages. Referenced 31 pages. Preread 0 pages.

In table 'main' by index 'key', there are 25 records. Elapsed: 253.161 seconds
Read 41712 pages. Referenced 41738 pages. Preread 1 pages.
In table 'main' by index 'key', there are 25 records. Elapsed: 1.907 seconds
Read 335 pages. Referenced 41738 pages. Preread 1 pages.

In table 'MSysObjects' by index 'Id', there are 136 records. Elapsed: 0.000 seconds
Read 0 pages. Referenced 141 pages. Preread 0 pages.
In table 'MSysObjects' by index 'Id', there are 136 records. Elapsed: 0.000 seconds
Read 0 pages. Referenced 141 pages. Preread 0 pages.

In table 'MSysObjects' by index 'Name', there are 136 records. Elapsed: 0.000 seconds
Read 0 pages. Referenced 137 pages. Preread 0 pages.
In table 'MSysObjects' by index 'Name', there are 136 records. Elapsed: 0.000 seconds
Read 0 pages. Referenced 137 pages. Preread 0 pages.

In table 'MSysObjects' by index 'RootObjects', there are 20 records. Elapsed: 0.000 seconds
Read 0 pages. Referenced 21 pages. Preread 0 pages.
In table 'MSysObjects' by index 'RootObjects', there are 20 records. Elapsed: 0.000 seconds
Read 0 pages. Referenced 21 pages. Preread 0 pages.

In table 'MSysObjectsShadow' by index 'Id', there are 136 records. Elapsed: 0.000 seconds
Read 0 pages. Referenced 141 pages. Preread 2 pages.
In table 'MSysObjectsShadow' by index 'Id', there are 136 records. Elapsed: 0.000 seconds
Read 0 pages. Referenced 141 pages. Preread 0 pages.

In table 'MSysUnicodeFixupVer2' by index 'secondary', there are 0 records. Elapsed: 0.000 seconds
Read 0 pages. Referenced 1 pages. Preread 1 pages.
In table 'MSysUnicodeFixupVer2' by index 'secondary', there are 0 records. Elapsed: 0.000 seconds
Read 0 pages. Referenced 1 pages. Preread 0 pages.

In table 'MSysUnicodeFixupVer2' by index 'primary', there are 0 records. Elapsed: 0.000 seconds
Read 0 pages. Referenced 1 pages. Preread 0 pages.
In table 'MSysUnicodeFixupVer2' by index 'primary', there are 0 records. Elapsed: 0.000 seconds
Read 0 pages. Referenced 1 pages. Preread 0 pages.

predefrag, no prefetch

No prefetch, pre defrag:

In table 'main' by index 'data', there are 25 records. Elapsed: 153.617 seconds
Read 23306 pages. Referenced 23380 pages. Preread 50 pages.
In table 'main' by index 'data', there are 25 records. Elapsed: 1.912 seconds
Read 278 pages. Referenced 23380 pages. Preread 0 pages.

In table 'main' by index 'key', there are 25 records. Elapsed: 252.253 seconds
Read 41713 pages. Referenced 41738 pages. Preread 0 pages.
In table 'main' by index 'key', there are 25 records. Elapsed: 0.025 seconds
Read 0 pages. Referenced 41738 pages. Preread 0 pages.

In table 'MSysObjects' by index 'Id', there are 119 records. Elapsed: 0.000 seconds
Read 0 pages. Referenced 124 pages. Preread 0 pages.
In table 'MSysObjects' by index 'Id', there are 119 records. Elapsed: 0.000 seconds
Read 0 pages. Referenced 124 pages. Preread 0 pages.

In table 'MSysObjects' by index 'Name', there are 119 records. Elapsed: 0.000 seconds
Read 0 pages. Referenced 120 pages. Preread 0 pages.
In table 'MSysObjects' by index 'Name', there are 119 records. Elapsed: 0.000 seconds
Read 0 pages. Referenced 120 pages. Preread 0 pages.

In table 'MSysObjects' by index 'RootObjects', there are 19 records. Elapsed: 0.000 seconds
Read 0 pages. Referenced 20 pages. Preread 0 pages.
In table 'MSysObjects' by index 'RootObjects', there are 19 records. Elapsed: 0.000 seconds
Read 0 pages. Referenced 20 pages. Preread 0 pages.

In table 'MSysObjectsShadow' by index 'Id', there are 119 records. Elapsed: 0.023 seconds
Read 2 pages. Referenced 124 pages. Preread 0 pages.
In table 'MSysObjectsShadow' by index 'Id', there are 119 records. Elapsed: 0.000 seconds
Read 0 pages. Referenced 124 pages. Preread 0 pages.

In table 'MSysUnicodeFixupVer2' by index 'secondary', there are 0 records. Elapsed: 0.000 seconds
Read 1 pages. Referenced 1 pages. Preread 0 pages.
In table 'MSysUnicodeFixupVer2' by index 'secondary', there are 0 records. Elapsed: 0.000 seconds
Read 0 pages. Referenced 1 pages. Preread 0 pages.

In table 'MSysUnicodeFixupVer2' by index 'primary', there are 0 records. Elapsed: 0.000 seconds
Read 0 pages. Referenced 1 pages. Preread 0 pages.
In table 'MSysUnicodeFixupVer2' by index 'primary', there are 0 records. Elapsed: 0.000 seconds
Read 0 pages. Referenced 1 pages. Preread 0 pages.

predefrag, with prefetch

Prefetch, pre defrag

In table 'main' by index 'data', there are 25 records. Elapsed: 153.511 seconds
Read 23306 pages. Referenced 23380 pages. Preread 50 pages.
In table 'main' by index 'data', there are 25 records. Elapsed: 1.739 seconds
Read 248 pages. Referenced 23380 pages. Preread 0 pages.

In table 'main' by index 'key', there are 25 records. Elapsed: 252.182 seconds
Read 41712 pages. Referenced 41738 pages. Preread 1 pages.
In table 'main' by index 'key', there are 25 records. Elapsed: 0.032 seconds
Read 0 pages. Referenced 41738 pages. Preread 0 pages.

In table 'MSysObjects' by index 'Id', there are 119 records. Elapsed: 0.000 seconds
Read 0 pages. Referenced 124 pages. Preread 0 pages.
In table 'MSysObjects' by index 'Id', there are 119 records. Elapsed: 0.000 seconds
Read 0 pages. Referenced 124 pages. Preread 0 pages.

In table 'MSysObjects' by index 'Name', there are 119 records. Elapsed: 0.000 seconds
Read 0 pages. Referenced 120 pages. Preread 0 pages.
In table 'MSysObjects' by index 'Name', there are 119 records. Elapsed: 0.000 seconds
Read 0 pages. Referenced 120 pages. Preread 0 pages.

In table 'MSysObjects' by index 'RootObjects', there are 19 records. Elapsed: 0.000 seconds
Read 0 pages. Referenced 20 pages. Preread 0 pages.
In table 'MSysObjects' by index 'RootObjects', there are 19 records. Elapsed: 0.000 seconds
Read 0 pages. Referenced 20 pages. Preread 0 pages.

In table 'MSysObjectsShadow' by index 'Id', there are 119 records. Elapsed: 0.016 seconds
Read 0 pages. Referenced 124 pages. Preread 2 pages.
In table 'MSysObjectsShadow' by index 'Id', there are 119 records. Elapsed: 0.000 seconds
Read 0 pages. Referenced 124 pages. Preread 0 pages.

In table 'MSysUnicodeFixupVer2' by index 'secondary', there are 0 records. Elapsed: 0.000 seconds
Read 0 pages. Referenced 1 pages. Preread 1 pages.
In table 'MSysUnicodeFixupVer2' by index 'secondary', there are 0 records. Elapsed: 0.000 seconds
Read 0 pages. Referenced 1 pages. Preread 0 pages.

In table 'MSysUnicodeFixupVer2' by index 'primary', there are 0 records. Elapsed: 0.000 seconds
Read 0 pages. Referenced 1 pages. Preread 0 pages.
In table 'MSysUnicodeFixupVer2' by index 'primary', there are 0 records. Elapsed: 0.000 seconds
Read 0 pages. Referenced 1 pages. Preread 0 pages.