JetShowPlan: A Primer

I wrote briefly about JetShowPlan in my article on Tuning Access Query Performance.  As I wrote in that article, SQL is a declarative language.  When you write a query, you are telling the database engine what you want.  The database engine decides how best to accomplish that for you.  This is generally good, because optimizing set-based operations is hard and letting the database engine do it for you allows you to leverage the knowledge of those who devote their lives to that specific problem.

The downside is that the how becomes a black box.  You feed some SQL into the black box and out comes a result set with a bunch of data.  The database engine is extremely reliable at providing you with exactly the data you requested.  The issue is that the performance of the data retrieval can be all over the place.  To be clear, the poor performance is almost never the database engine's fault.  Usually, the problem is that we are missing an index or filtering on the result of a VBA function or joining on two linked tables that are stored in physically separate locations.

When this problem arises, we need a way to troubleshoot it.  Enter JetShowPlan.  Think of this as a special screwdriver that allows us to take apart the black box and peer inside to see how the database engine is implementing the SQL commands we feed it.  With this knowledge, we can tweak the SQL, add an index, or otherwise address the source of our performance bottleneck.  

Let's get started.

Registry Key

JetShowPlan works by writing the query plan (i.e., the contents of the black box) to a text file whenever the ACE/Jet database engine executes any query.  This text file fills up fast.  Creating the text file requires resources that further detract from query performance.  Thus, we only want to enable this feature when we are actively troubleshooting an issue.  

As this is a tool for advanced users, there is no setting in the Access user interface to enable this mode.  The only way to turn it on or off is by setting a value in the registry.  The registry value fits the following pattern (the text inside curly braces serves as a placeholder):

[HKEY_LOCAL_MACHINE\SOFTWARE{\Wow6432Node}\Microsoft\Office\{xx}.0\Access Connectivity Engine\Engines\Debug]
"JETSHOWPLAN"="ON"

Version and bitness considerations

The registry value pattern I showed above uses some placeholder text to account for the differences among Access environments.  The version number text \{xx}.0\ should be replaced with the version number that corresponds to the version of Access installed on your machine:

• 12.0: Access 2007
• 13.0: skipped to avoid triggering triskaidekaphobes
• 14.0: Access 2010
• 15.0: Access 2013
• 16.0: Access 2016 & 2019

The \Wow6432Node ('Wow' stands for "Windows 32-bit on Windows 64-bit") is required only if you are running a 32-bit version of Microsoft Access on a 64-bit version of Windows.  If Access and Windows are both 32-bit or both 64-bit, then that "folder" (or "key" in registry jargon) is unnecessary.

In VBA form:

    If Is32BitAccess Xor Is32BitWindows Then
        IncludeWow6432Key = True
    Else
        IncludeWow6432Key = False
    End If

For example, a 32-bit install of Access 2010 running on 64-bit Windows would require the following registry entry:

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Debug]
"JETSHOWPLAN"="ON"

Likewise, a 64-bit install of Access 2019 on 64-bit Windows would require:

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\16.0\Access Connectivity Engine\Engines\Debug]
"JETSHOWPLAN"="ON"

I should also note that the first time you create this entry, you will likely need to add the "Debug" key (folder) and the JETSHOWPLAN value name and data.  

Here are the steps to do this:

1. Run regedit as an administrator
2. Navigate to the "\Engines" key following the above notes
3. Right-click "\Engines" and choose New -> Key
4. Rename the key from "New Key #1" to "Debug"

Then, you will need to add the "JETSHOWPLAN" string value with the Data "ON" to enable appending to the showplan.out file or "OFF" to stop appending to the file.

1. Right-click the "\Debug" key and choose New -> String Value
2. Rename the value from "New Value #1" to "JETSHOWPLAN"
3. Right-click the "JETSHOWPLAN" value name and choose Modify...
4. Set the Value data to ON then click the [OK] button

The next time you start a new instance of Access, it will begin appending data to the Showplan.out file.  Any instances of Access already running when you make the above changes will not be affected.  The same goes for when you turn the setting OFF.  The changes don't take effect until you start a new msaccess.exe instance.  It is not necessary to close existing instances of Access; it is possible to have one open instance of Access that is actively writing to showplan.out while a different instance of Access is not.

Autohotkey script

I'm not going to lie; jumping into regedit every time I want to turn JetShowPlan ON or OFF is annoying.  If I had to do that, I would hardly bother.  But I don't have to do that!  I created a hotkey in Autohotkey that toggles JetShowPlan ON and OFF.

^#q:: ; Ctl + Win + Q  (feel free to use your own key combination)
    ;--== Toggle JETSHOWPLAN ==--
    
    ;----- BEGIN CONFIGURATION (make all changes here) -------------
    ShowPlanRegView = 64   ; set to 32 for 32-bit Access
    ShowPlanKey = SOFTWARE\Microsoft\Office\16.0\Access Connectivity Engine\Engines\Debug  ; change 16.0 to match Access version
    ;----- END CONFIGURATION ---------------------------------------
    
    SetRegView %ShowPlanRegView%
    RegRead ShowPlanSetting, HKEY_LOCAL_MACHINE\%ShowPlanKey%, JETSHOWPLAN
    If ( ShowPlanSetting = "OFF" ) {
            RegWrite REG_SZ, HKEY_LOCAL_MACHINE\%ShowPlanKey%, JETSHOWPLAN, ON
            If ErrorLevel
                MsgBox Error enabling JetShowPlan. Check permissions on:`n`nHKLM\%ShowPlanKey%`n`nfor user '%A_UserName%'
            Else
                MsgBox JetShowPlan set to ON
    } Else {
            RegWrite REG_SZ, HKEY_LOCAL_MACHINE\%ShowPlanKey%, JETSHOWPLAN, OFF
            If ErrorLevel
                MsgBox Error disabling JetShowPlan. Check permissions on:`n`nHKLM\%ShowPlanKey%`n`nfor user '%A_UserName%'
            Else
                MsgBox JetShowPlan set to OFF
    }
    SetRegView Default
    Return

Now when I want to tune my queries, I press [Ctl] + [Win] + [Q] and I see a message box that says "JetShowPlan set to ON".  When I'm done, I close Access, press [Ctl] + [Win] + [Q] and I see "JetShowPlan set to OFF".

Adjusting permissions

I have two different Windows user accounts: one with standard permissions that I use for everyday work and another with administrator permissions for installing software, etc.  This is a common security best practice.  

The problem is the JetShowPlan registry key is in the HKLM hive.  By default, only administrators can make changes to the values in that hive.  This is annoying because when I try to run my Autohotkey script I get the following error message:

Not to worry, though.  As the message above suggests, we can fix this.  The best part is we can make it convenient without reducing our security posture.  Here's the trick.  

1. Open regedit as an administrator
2. Navigate to the \Debug key
3. Right-click on the \Debug key and choose Permissions...
4. Click the [Add...] button
5. Enter the user name from the message box above ('Mike'), click [Check Names], then click [OK]
6. Allow [√] "Full Control" for the user
7. Click [OK] to save the changes

Now when I press [Ctl] + [Win] + [Q] it toggles JetShowPlan ON and OFF, updating the registry automatically.

Finding Showplan.out

Access will not alert you to where the Jet/ACE database engine is appending query plan information when JetShowPlan is enabled.  I've spent more time than I care to admit searching for a rogue copy of showplan.out.  This section will save you from sharing that fate.

Default location

The first place to look is in the current user's Documents folder.  For example, my Windows user name is "Mike", so the first place I would expect to find the file is: C:\Users\Mike\Documents\showplan.out.

Using CurDir()

Technically speaking, the showplan.out file is created in the current working directory.  That's usually the current user's Documents folder, but not always.  The foolproof way to find the file's location is to use the CurDir() function.

You can copy, paste, and execute the following line of code in the VBA IDE immediate window to open the showplan.out file (assuming you've enabled JetShowPlan in the registry):

Shell "notepad """ & CurDir & "\showplan.out""", vbNormalFocus

Changing the output location via ChDir()

If for some reason you wanted to specify a different location for the showplan.out file, you can do that using the ChDir() function.  That function changes the current working directory.  And, as I mentioned earlier, the current directory is where the showplan.out file resides.  As soon as you change the current working directory, JetShowPlan starts writing to the new folder; there is no need to close and reopen Access.

Why might you want to do this?  Let's say you wanted to compare three different approaches to retrieving the same data.  You write three different queries to see how the changes you make impact the query plan.  Since showplan.out is so verbose, it would be nice to have each query plan in its own file.  This will make the query plans easier to compare.  Here's how I could do that.  The first step is to make sure each of these folders exists.  Then, execute the following lines of code:

ChDir "C:\Users\Mike\Documents\Showplan\A"
DoCmd.OpenQuery "CollectTax1"
ChDir "C:\Users\Mike\Documents\Showplan\B"
DoCmd.OpenQuery "CollectTax2"
ChDir "C:\Users\Mike\Documents\Showplan\C"
DoCmd.OpenQuery "CollectTax3"
ChDir "C:\Users\Mike\Documents"

Use Everything you've got (or download it if you don't have it yet)

While CurDir() will give you a definitive location for the most recent changes to the showplan.out file, it can't tell you what the previous working directories were.  And, if you've closed the instance of Access that created the showplan.out file, there's no guarantee that the next instance of Access you open will have the same current directory.

I recently came across a handy little utility called "Everything."  It's a tiny executable that indexes your entire hard drive in just a few seconds.  Once the indexing is complete, you can instantly search for files or folders anywhere on your drive.

You can download Everything from here or via Chocolatey: choco install everything.  Open Everything, search for showplan.out, and in less than a second you will see every instance of showplan.out on your computer along with the last modified date.  I wish I had this tool years ago.

Making sense of Showplan.out

The first time you open a showplan.out file, expect to be baffled.  There's a lot of text and much of it is noise.  Here's an excerpt from a file generated when I opened the Northwind sample database:

The queries that begin with a tilde (~) represent raw SQL that is saved in the Property Sheet of a form or report and not saved as a permanent QueryDef object.  The main points of interest are the numbered steps for each query: 01), 02), 03), etc.  You want to follow these steps looking for good signs and bad signs that might suggest where there are problems.

To my knowledge, there is no official documentation for the formatting and contents of the showplan.out file.  That's OK, though, because we're not going to get caught up in the minutiae.  Our main goal is to identify glaringly obvious problems and address those.  The 80/20 rule applies here.  Most of the performance gains will come from one or two simple tweaks to our queries.

Good signs

This is all about indexes.  We want the query plan to be using indexes, especially in the beginning steps of a multi-step query.  Two different key words indicate that indexes are being used: index and rushmore.  Rushmore is the codename for the query optimization technology originally developed by Fox Software in the early 1980's.  Microsoft purchased the company in 1992 and incorporated the technology into the Jet database engine.

Queries that use Rushmore technology to process indexes run faster than those that use indexes in a more traditional fashion.  Rushmore technology can only be used with Access tables (both local and linked), along with linked FoxPro and dBASE tables.  Notably, Rushmore cannot be used with linked SQL Server tables.  To boost the performance of linked SQL Server tables, you're often better off writing pass-through queries, but that is beyond the scope of this article.

Bad signs

There are a few bad signs to watch for in the showplan.out file.  The simple presence of these signs does not necessarily mean there is a problem.  That said, if you are troubleshooting a query with poor performance, you can think of these words as warning flags for potential problems: X-Prod, scanning, temp, temporary.

The X-Prod keyword appears when you have a query with a Cartesian join (also known as a cross join or cross product).  This usually happens by mistake when you forget to join two tables in the Query-by-Example (QBE) editor.  The result is that every record in table 1 gets matched up with every record in table 2.  The total number of records is the product of the two table counts.  So, if table 1 has 7 records and table 2 has 9 records, the cross join of the two tables returns 63 records.  You can imagine the problem if both tables have thousands of records or more.

01) Inner Join table 'Table1' to table 'Table2'
      using X-Prod join

The next keyword to watch for is scanning.  If the database engine cannot use an index to filter results, it falls back on scanning.  This means that it has to examine each row individually to see if it satisfies the query criteria.  When you see this word in a showplan.out file, it often means you need to add an index to the column being scanned.  But not always!  For columns with low cardinality (only a few unique values, such as a status column), there is usually little upside to adding an index.  Once added, the index must be maintained.  This slows down inserts and takes up disk space.  Also, if the query performance is acceptable on production data, then adding an index to the scanned column is a premature optimization (which you should avoid).

Finally, there are the temp and temporary keywords.  These indicate that the database engine has had to perform some sort of operation on a temporary basis.  When we create and save a querydef, that object is saved with certain metadata to optimize repeated execution.  Obviously, such metadata is lost when temporary indexes or joins go out of scope.  These keywords can usually be ignored, but they may be able to point you in the right direction if you get stumped on a poor performing query with no other more obvious flaws.

To recap in over-simplified terms:

      GOOD   >  >  >  >  >  BAD:
Rushmore > indexes > temp/temporary > scanning > X-Prod

Notepad++ custom language

If you've been reading my other work, you know that I have strong feelings about increasing the Signal to Noise ratio in programming (and life in general).  To this end, I created a "User-defined language" file in Notepad++ to add syntax highlighting to showplan.out files.  Now, when I open showplan.out files, they look like the screenshot below.  The "GOOD" keywords are colored in blue text and the "BAD" keywords are colored in red text.  This is an example of Making Wrong Code Look Wrong.

To set this up, follow these steps:

1. Open Notepad++
2. Language -> User Defined Language -> Define your language...
3. Click [Create New...]
4. Enter Name: showplan.out
5. Click [OK]
6. Go to the _|Folder & Default|_ tab
7. Under "Folding in code 2 style" enter Inputs for Open and End inputs for Close
8. Go to the _|Keywords List|_ tab
9. Click [Styler] under 1st group and set Foreground color to red
10. Enter the following "BAD" keywords in 1st group:
    temp temporary scanning X-Prod
11. Click [Styler] under 2nd group and set Foreground color to blue
12. Enter the following "GOOD" keywords in 2nd group:
    rushmore index
13. Enter Ext.: out

Final thoughts

Unlike SQL Server, the Jet/ACE database engine does not allow you to directly modify query execution plans.  That means we can look inside the black box with JetShowPlan, but we can't rewire it to do what we want.  Instead, we have to focus on what we can control: the exact SQL we feed it and the indexes and relationships among the tables involved.

Using JetShowPlan has both short and long term benefits.  In the short term, the feature lets you fix the bottlenecks in your Access applications.  Over the long term, you gain insight into the inner workings of Access which helps you avoid the bottlenecks in the first place.

Image by 272447 from Pixabay

UPDATE [2022-03-13]: Changed steps in Notepad++ User Defined Language to add code folding for the "Inputs to query" sections of the showplan.out file.