The real part
Mild dehydration — in the region of one to two percent of body mass — has been shown in controlled studies to affect attention, short-term memory and subjective alertness, and to increase the perceived effort of mental tasks. The effects are consistent enough to take seriously and modest enough that nobody should be promising transformation.
Notably, the effect on how hard a task feels is often larger than the effect on how well it is performed. A dehydrated person frequently completes the work and finds it unpleasant.
The invented part
“Eight glasses a day.” No credible source for this has ever been produced. Requirements vary with body size, climate and activity, and a good deal of intake arrives in food, tea and coffee.
“Coffee dehydrates you.” At habitual intakes it does not produce net fluid loss. The diuretic effect is real and smaller than the volume of water the drink contains.
“Thirst means you are already dehydrated.” Thirst is a functioning signal that arrives in good time for healthy adults. It becomes less reliable with age, which is the genuine qualification worth keeping.
The one real qualification
Thirst perception does blunt with age, and some medications — diuretics in particular — change the picture meaningfully. For an older adult on a diuretic, waiting for thirst is a less reliable strategy than it would have been at forty, and that is worth a conversation with whoever prescribed it.
What to do with this
Drink when thirsty. Keep water within reach so that the barrier is low. Pay more attention in hot weather, after exercise and if you are on a diuretic. Then stop thinking about it, because the returns beyond that point are close to zero.
If afternoon fog is the complaint, water is worth ruling out precisely because it is so cheap to rule out — and then move on to sleep, to lunch, and to when you schedule the demanding work.