The best use for a dual CPU workstation in the late 90s was probably 3D rendering. Most rendering engines at that point had been optimized for multiprocessing and could make efficient use of two or more chips. I made an animated short movie using 3D Studio MAX on a dual 600MHz PIII setup (Asus P2B-DS motherboard, WinNT 4) and it was really satisfying to see CPU utilization above 90% in Task Manager during rendering. Maya, Lightwave, and Softimage could all leverage multiple CPUs to varying degrees.
As for workstation accelerators, the major players have already been mentioned - Intergraph Intense3D, 3Dlabs Wildcat, Accelgraphics, Elsa, etc. My workstation had a Gloria II that ran great and included special Heidi drivers that were optimized for MAX. There were also some high-end boards from Evans & Sutherland - Lightning and Tornado, I believe. Some good P3-era workstation boards:
Elsa Gloria II/III
3Dlabs Wildcat 4000/5000 series
Oxygen VX1/GVX1/GVX210
Diamond FireGL 3000/4000
Accelgraphics Eclipse
Just bear in mind that these cards typically cost several thousand dollars and were special-purpose boards meant to accelerate OpenGL in high-end applications. They were generally slow at 2D (though visual quality was good) and they were almost useless for gaming. Most of them didn't even support DirectX and their texture fill rates weren't anything to write home about. High-resolution windowed wireframe/smooth-shaded 3D was their territory, not the full-screen low-res textured graphics that were used in games.
The Gloria II was the exception, since it was based on the GeForce/Quadro line. It was one of the first versatile pro-level boards and it sort of spelled the end of the $2,000+ specialty graphics accelerator era. As mentioned above, within a few years everybody was basically using Quadros.
I have a few dual-CPU P3-era workstations that use the aforementioned hardware, but as you can probably imagine they spend most of their time powered-off since they are of such limited use.
Silicon Classics on: YouTube | Twitter | Google+