Anecdotally, my Pentium 3 machine (forgot what GPU it had, sadly) back in 2004 was struggling with most modern (for the time period) games that had even a hint of graphical fidelity. Max Payne 2, on the other hand, looked mindblowing and ran like butter.
One interesting VGA hardware effect in this context which I have not seen in any demo, is to change the character width from 8 to 9 pixels _during_ a horizontal scanline. The trick is to use RDTSC to estimate the pixel position, with which you can create a nice wobble effect in text mode. Obviously, this requires a Pentium or higher, and at the time this became possible, text mode was rather outdated.
And don’t forget scream tracker 3 ! ( which is actually how I discovered the demoscene)
The GPU powered demos are impressive in a different way.