Microsoft Jet 3,5 Performance Overview and Optimization Techniques. Non-configurable Performance Settings, страница 12

lngCacheRead = DBEngine.ISAMStats(2)

lngCacheReadAheadCache = DBEngine.ISAMStats(3)

lngLocksPlaced = DBEngine.ISAMStats(4)

lngLocksReleased = DBEngine.ISAMStats(5)

Debug.Print "Disk reads " & lngDiskRead

Debug.Print "Disk writes " & lngDiskWrite

Debug.Print "Cache reads " & lngCacheRead

Debug.Print "Cache reads from RA cache " & lngCacheReadAheadCache

Debug.Print "Locks placed " & lngLocksPlaced

Debug.Print "Locks released " & lngLocksReleased

End Sub

Notes

·  The number of disk reads and writes include all reads and writes in all circumstances, including background read-ahead in separate threads. One read or write doesn’t necessarily equal one page: One read or write could represent many pages that were read or written simultaneously. An example of this is commands wrapped in a transaction. This is why it is important to issue a null transaction to ensure accurate statistics. A null transaction is defined as issuing the CommitTrans and BeginTrans methods with no commands in between the two statements.

·  There are two types of cached reads returned. The CacheRead statistic reflects pages that are read from previously modified pages that still remain in the cache and have not been modified in the physical database by other users. The read-ahead cache statistic shows reads that occurred when Jet anticipates that a sequential read activity is about to occur. This is done to reduce reads to disk.

·  The number of locks placed and released may not be balanced. A single call to release a lock may result in the release of many locks.

Using these statistics in conjunction with the SetOption method can allow the developer to instantly see results that may produce timing differences on a LAN that may not be apparent from running on a local machine. This is why many of the graphs above show I/O instead of timings as it more accurately represents what is occurring.

The ShowPlan Function

The Microsoft Jet query engine implements a cost-based query optimizer. When a query is compiled, the query engine creates a query plan. This plan is used internally to find the quickest way to execute a query. Using the ShowPlan key in the registry will cause Jet to create a text file containing the query execution plans.

The ShowPlan function was available in Jet 3.0 by adding the following key to the registry:

\\HKEY_LOCAL_MACHINE\SOFTWARE\MICROSOFT\JET\3.0\Engines\Debug

Under this key, add a string data type called JETSHOWPLAN (make sure to use all capital letters). To turn on ShowPlan, set the value to ON. To turn it off, set the value to OFF.

When ShowPlan is turned on, Jet appends text to a file called SHOWPLAN.OUT every time a query is compiled. You must modify or compact the database in order to have a stored query show its query plan. It is also important to note that SHOWPLAN.OUT  appends new data for every new query plan. Leaving ShowPlan on could result in an extremely large SHOWPLAN.OUT file.

Jet 3.5 includes enhancements and bug fixes to ShowPlan. For example, you can now determine the inputs to the query. This is very useful in determining the uniqueness of an index; thus determining how useful that index is in retrieving the overall result set and what affect it may have on concurrency. Since Jet 3.5 utilizes a different registry key structure, the physical location of ShowPlan has changed. To activate ShowPlan for Jet 3.5 you must use this key location:

\\HKEY_LOCAL_MACHINE\SOFTWARE\MICROSOFT\JET\3.5\Engines\Debug

Below is a sample output generated by running the Invoices query.  This query comes with the Northwind database in Microsoft Access 97.