My impression is that its invention was for the sole purpose of eradicating the idea that Windows is insecure and prone to viruses, which explains why it can be overzealous and CPU hungry.
I would only enable it for family members who don't know what they are doing. For some reason, I haven't needed any form of active virus scanning in something like 15 years. If it turns out I've been infected this entire time, the criminals sure are taking their time stealing my money, etc.
At this point the only other antivirus I bother keeping an install of on my personal system is Malwarebytes free in case things really go tits up and I need to run it and rkill from safe mode.