Database-type applications usually require huge pages and do not want to fail over to small pages, they want to use absolutely as much system memory as possible, and it is usually feasible to modify the application in order to get best performance.
Another class of applications has the opposite qualities. This can be thought of as the "desktop" workload. Huge pages are nice but not required, the system usually has some memory free, and few people think it's worth the time to tune these applications in order to get a few more percent of performance.
Something slightly mixed but with a similar solution as above is an HPC workload. These applications want to use as much memory as possible, but won't be rewritten to use large pages explicitly. It's okay to use small pages. One of the interesting things about these workloads is that they are usually the only "important" application on the machine, and they ramp up to full memory usage quickly. So you will see alternation between most of memory used and most of memory free.