>Michel, it's quite unusual to need that many indexes. 4 million rows is not a small table but it's not astronomically large either.

Maybe but the client requests to be able to do fancy advanced searches on all the fields. So, if one does not have, at least, a single index, this would crawl. Or, even if SQL Server would try to use another related index as it sees fit, it will never be as fast as the field being indexed as is. Then, of course, we are talking about a single condition, which would be a perfect match on the field being indexed. There are those times when the user enters 6 criterias so we rely on the most closest compound indexes to fit that needs as to cover all the possibility in regards to what we ever, we would need about 13 trillion indexes.

>How much memory is on your server, and what kind of processor/server architecture do you have?

Processors are 2.6 Ghz, with 42 GB available to SQL Server on a 22 GB sitting on the OS. We can't have more than that allowed to SQL Server as net needs 16 GB for the clustering. This is configured at configured as an Active/Passive Microsoft Failover Cluster. SQL Server 2012 is installed on the cluster, which manages the failover process from one node to the other in the event of an issue with the active node. The two servers are configured identically so that one system can handle the load as well as the other upon failure of a node.

I always requested more RAM but I cannot obtain it.

This is presently set to 1 CPU and 8 cores. They tried 2 CPU and 4 cores but that did not do very well.

>Are you using SQL 2014 Enterprise? If so, the in-memory optimized table capability (known as Hekaton) "might" be able to help you out, though it would likely require some changes to stored procedures.

This is SQL Server 2012 on Windows Server 2008 R2 Enterprise SP1.

We will not be able to see when we will switch to SQL Server 2012. Net confirmed this is how it will be.