Welcome to Wetpixel. Like you, I have usually used the second slot for backup. With the D500, in this configuration write speed is limited by the slower card i.e. the SD slot. For most underwater shooting, the large buffer and the limitation of strobe recycle time on high speed shooting bursts means that the SD card write time isn't a real limitation. However, there could be occasions when shooting longer bursts of fast action, using natural light or possibly low strobe power mean that you would like to clear the buffer as quickly as possible. Usually these situations would be predictable and it's easy to switch second slot function on the fly. If needed you could then back up to the SD card in retrospect. I don't think there's a right answer to how to configure the slots on a D500 - it depends on your shooting preferences. Life just got a bit more complicated compared with the D7200.
Of course the advantage of an XQD card isn't just write speed. Read speed is also potentially higher so uploading images should be faster as long as you don't have a bottleneck somewhere else in your system.