SQL Server – How to Simulate More RAM

Another option to optimizer hints if you cannot even touch the query is to use Plan Guides. However, since your query statement is dynamic with many unparameterized tokens, I doubt it's possible.

Since you know your data, I think you're better off just staying the path of query hints. Statistics have been known to be lies, lies and damn lies. It can only provide an overall shape of the data, but for specific conditions (e.g. fund_src_name_id values, state_id values, combination of the two etc) it can just produce the wrong plan.

There is rarely any need, point or benefit trying to micro optimise star schema queries with non-clustered indexes laden with included columns. Fact tables are built to be scanned.

The indexes you've created in your examples are subset copies of the parent table, which are being scanned (no seeks). The minor performance improvements come from scanning marginally fewer pages than the parent table. Given that star schemas are built to support ad-hoc query patterns it is not viable to create the indexes to support every possible enquiry.

• Create your fact table clustered index on the date key. The majority of (typical) fact table queries include a time element and clustering on the date key enables range scanning of fact table rows.
• Add non-clustered indexes on the foreign keys of your fact tables to assist with highly selective queries. Foreign keys to dimension tables can be created with NOCHECK to prevent any impact on ETL.
• Cluster your dimension tables on their surrogate keys.
• Create a non-clustered index on the natural key of each dimension table.
• Stop.

The optimiser detects star schema query patterns and has strategies to deal with them efficiently, utilising scans and hash joins in Standard Edition or bitmap filtering in Enterprise. Follow the indexing strategy outlined above and let the optimiser deal with the rest.