Ridders, I am going to take a wild-eyed guess on this one. Some of it is speculative and I freely admit I am drawing on general operating system experience. Regarding the MaxLocksPerFile parameter:
1. Is there a sensible upper limit for this value? In other words if this was set to say 1,000,000 would this cause other performance issues?
I'd be lying if I said I knew my next comments for an absolute fact, but...
The MaxLocksPerFile deals with a system feature called the Lock Manager. A lock is a data structure within WINDOWS (not necessarily just Access) that allows a program to create a software lock on an object and then control who can access the locked item (and, more importantly, HOW they are allowed to access it.)
When you lock a file, you establish information in the system data structure for that file so that other users attempting to use the file will have to ask you (and any other lock holder) for permission to use the file. Then, if you hold a lock and are waiting for it to happen, a user asking for access to the file triggers an event in your process. When you take the event, you know what process was asking and what they were asking to do. (And the time, of course.)
You can lock something but allow others to read it, for example. You can lock a file exclusively so nobody else can read or write in it. Or you can lock something simply because you wanted to track who was "touching" the object (sometimes called an "interest" lock.)
This is supported by data structures in the Windows pool and the lock management is honored by the device driver, which is after all the thing that you call to open a file. The following article shows you how to get to the list of open files, ALL of which are locked against deletion by other processes.
https://superuser.com/questions/117902/find-out-which-process-is-locking-a-file-or-folder-in-windows
In general, the locking structure is taken from one of the memory pools.
https://msdn.microsoft.com/en-us/library/windows/desktop/aa965226(v=vs.85).aspx
These pools dynamically change size as needed, but if it is the non-paged pool (and I think all file locks ARE in the NPP), then physical memory will constrain how big the NPP can grow. So performance-wise, setting MaxLocksPerFile to a very high number allows the pool to get very large. But NPP cannot be paged (by definition) so letting it get big will take away from physical memory. AND because the file system is a Windows function (as opposed to an Access function), the NPP comes from the SYSTEM memory, not from a process area.
Actually, that's not entirely true, because if you opened a LOT of files, you need a per-process list of files that will be automagically closed if your process exits without doing its own closure. So you will have a bunch of file-handle pointers somewhere in your local memory to support something called process rundown.
https://msdn.microsoft.com/en-us/library/windows/desktop/aa364225(v=vs.85).aspx
In the last paragraph of the above link, there is a comment that clarifies that a file access structure is partly private, partly shared by other Windows users/processes.
Therefore, I must say that the number of locks per file, because it uses a lot of physical memory, CAN reach a point where you will affect performance by reducing the number of free pages available for dynamic memory management and virtual memory activities.
The thing that barfs first is hard to predict. It MIGHT be the page file (virtual memory file) that is adjusted from the System's Advanced Performance dialog. It MIGHT be that you run out of physical memory because of loading down the NPP or the system's lower limit on free pages.
https://blogs.msdn.microsoft.com/ti...-windows-memory-management-revealed-part-two/
The above article explains Windows paging dynamics including the various page lists. What it doesn't show quite as well is that the NPP area consumes Free Pages over time, thus reducing the pages available for other work. The size of the file handle structures and file internal lock structures isn't that big, but a million of ANYTHING is a lot to manage, and you said your question was about 2.5 million records. I believe that you implicitly create at least a million file internal lock structures when you do that.
2. Is this one situation where 64-bit Access is better? In other words can it handle system resources any better than 32-bit?
Because the structures for file handles probably changed for 64-bit windows due to addresses getting bigger, you might be able to have more of them, but the truth is that the same argument still applies. The more of these things you have allocated, the more physical memory you will use. The only difference is that if you have a system with not merely the Very Large Memory model but the Ultra Large Memory model, Win64 will have more memory to allocate to you before it barfs.
VLM is what lets you get to 8 GB or 16 GB of RAM. ULM is for time-sharing systems where it is not impossible to have 192 GB of RAM. Note also that many virtual hosting systems (VMWare, for example) can limit each individual virtual host to the 32-bit addressing models, where the Large Memory Model (LMM) limits each virtual environment to 4 GB of RAM. In THOSE environments, the MaxLocksPerFile parameter can be quite limiting.
