CPU pinning is pretty useful for virtual machines, i.e. I've used it myself to improve performance on a VFIO setup, by limiting which cores where qemu runs on and thus improving cache locality.
Databases and other high-throughput data infrastructure software use CPU pinning, also HPC. The reasons are similar: higher cache locality, reduced latency, and more predictable scheduling. It is most useful when the process is taking over part or all of the resources of the machine anyway.
Memory and PCIE lanes in larger systems can be attached to particular CPUs, or to sub sections of a single CPU (i.e. AMD Threadrippers / Eypcs in particular) where traversing the the inter CPU / CCX links can cause latency or bandwidth issues.
The software will be pinned to CPU cores close to the RAM or PCIE device they are using.
Only really seen it be an issue in crazy large scale systems, or where you have 4 CPUs, but I haven't spent a huge amount of time on microsecond critical workloads.