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.
Well, during Windows XP days if you connect to a LAN with compromised devices (in some countries it was popular to just hook up the entire neighborhood to a series of switches or poorly managed office network) before you install every single update possible - too late, your machine is part of the botnet.
Also, some environments require antivirus running for certification even if the machine in question is a linux server with read-only volumes.