The CMOS battery is not there to provide power to the computer when it is in operation, it’s there to maintain a small amount of power to the CMOS when the computer is powered off and unplugged. The primary function of this is to keep the clock running even when the computer is turned off. Without the CMOS battery, every time you turned on the computer you would need to reset the clock.
On older systems the CMOS battery also provided the small amount of charge required to maintain the nonvolatile BIOS memory, which remembered BIOS settings between reboots. On modern systems this information is typically stored in flash memory and does not require a charge to be maintained.
The CMOS battery, or RTC battery, provides power for the real-time clock so that your computer can continue to keep time when it is unplugged.
The name “CMOS battery” is a misnomer on modern computers. Older computers used the battery to maintain BIOS settings, which were stored in a CMOS SRAM chip that would lose its contents if disconnected from power. This setup does have the advantage of making it easy to clear the BIOS settings, either by removing the battery or by inserting or removing a jumper. Newer systems store firmware (BIOS or UEFI) settings in flash memory or EEPROM, which does not require power to maintain data. See: Wikipedia article on nonvolatile BIOS memory and How can a CMOS battery store data?
This battery was, and still is, used to maintain the real-time clock. As this is the sole purpose of the battery today, it is sometimes called the RTC battery. This ensures that your computer can continue to keep time even when unplugged. The battery is usually a readily-available CR2032 button cell, and is typically usable for 2 to 10 years. If your computer loses track of time when unplugged for more than a few hours, it is time to replace the battery. See: How long does CMOS battery (3V) on a motherboard last?
CMOS battery on Pico ITX motherboard – Image source
Think of it like your car battery. When you unplug the battery, your radio loses all of its presets and the clock resets. Orginally, the CMOS battery held a similar function, maintaining the memory that held the BIOS settings and keeping the Real-Time clock running when AC power was unavailable.
However, with modern computers the CMOS battery plays a lesser role as most BIOS firmware is smart enough to automatically detect the correct settings and those settings are stored such that they don’t need power to persist. The CMOS battery is still required to maintain the RTC.
More information is available in Nonvolatile BIOS memory.