Moderator: Branched to a new thread rather than resurrecting a thread from 2014.
You use Promiscuos mode when you need in a specific portgroup to see all the traffic in all the VMs that are connected to it. It can be used for example to analize the traffic with some tools that are running inside a vm (that vm will see all the traffic in the protgroup)
About the SFP you need to see first compatibility with your server and also with the compatibility guide for esxi.
If you want to monitor the traffic, with vDS you have Netflow (you need ent+ license) Configure the NetFlow Settings of a vSphere Distributed SwitchTriple VCIX (CMA-NV-DCV) | vExpert | MCSE | CCNA
As NicolasAlauzet says, you use Promiscuous Mode to monitor the traffic in that virtual switch with something like a sniffer virtual machine. There is no a specific SFP adaptar to promiscuous mode, you have to check if its compatible with your hardware.