Speeding up Count(*) on large tables
The indexed view should be among the fastest options, with the lowest maintenance overhead, when implemented optimally.

Modifications are incremental (deltas) as I explain in detail in Indexed View Maintenance in Execution Plans (a full recount is not performed on every base table update); however, you do need to ensure that the delta update parts of the execution plan have efficient access methods (like any query).

It is typically quite simple to identify a missing index from the INSERT/UPDATE/DELETE execution plan. Perhaps you could add an illustrative post-execution (actual) execution plan to your question.

Automatic matching of query text to an indexed view is only available in Enterprise Edition (and equivalents). In other editions, you must use the WITH (NOEXPAND) table hint. There are also good reasons to use NOEXPAND even on Enterprise Edition.

Regarding the demo code: Make sure you specify the hint using WITH (NOEXPAND). The way you have written it, NOEXPAND is parsed as an alias. Note also that only materialized (indexed) views can have a NOEXPAND hint.

If you are unable to add a hint directly, this would be an excellent use of a Plan Guide. A plan guide can also be used to ensure that a query that matches an indexed view (without naming it explicitly) actually uses the indexed view.

Remember that without NOEXPAND on a materialized (indexed) view, SQL Server always expands the view definition at the start of plan compilation. Enterprise Edition may (or may not) match (parts of) a query to an indexed view depending on its assessment of the costs of each option.

Related Q &amp; A:


How can I make sure the SQL Server query optimizer uses the exact tables in the query
Why does the READPAST hint cause indexed views to be ignored?
What factors go into an Indexed View&#39;s Clustered Index being selected?