What happens when async load is called again on the same TSoftObjectPtr?
Async load first checks if the object is loaded in memory before trying to load it. So it will exist in memory only once. You do not need to check if the object is already loaded, the soft pointer takes care of that for you, read below.
Should you use Game Instance to hold TSoftObjectPtrs?
You could, but its not a necesity. Any actor can hold a TSoftObjectPtr since it is basically a string that points to the asset. It does not matter how many Soft Pointers you have and where, even if pointing to the same object, said object will be loaded only once, irregardles of which actor is requesting it to be loaded.
As long as there is a single HARD reference to an object it will remain loaded. So if you Async Load a soft object pointer but you do not reference with a regular pointer somewhere it will get garbage collected. TSoftObjectPtrs will not prevent garbage collection, so keeping an array of these anywhere will not keep objects loaded. Instead, every time you load an object be sure to reference it as a regular pointer or you will lose it (which might be a good thing actually! if you are not using it you certainly do not need it).
Please do not forget to mark this answer as the correct one if it is what you where looking for so others having the same doubt can easily find it.
Hope this info helps you. Make it a great day!