I managed to dig up some info after searching for about 2 hours since the search tool of this forum often isn't very useful.
Apparently if an object has sprites on two different sheets then each time it switches between those, it requires more drawing overhead which can impact performance. So if we were working with something like modern mobile game graphics, having small spritesheets would be a terrible idea. But in the case of NES res graphics, looks like using the smallest possible setting is the way to go because I only saw a few out of 1905 different object types in my game that were spread across more than one sheet and the memory savings far outweighs the negligible performance increase.