Speeding up Count(*) on large tables We're using a Vendor App running on SQL Server Enterprise, and it has a rather annoying quirk of executing COUNT statements on the Items table while processing most financial documents (orders, invoices, etc.).

E.g. SELECT COUNT('A')  FROM [dbo].[Items] T0

I'm sure that would normally be fine, but there's over 6 million records, and it takes ~400ms to count them all. This can constitute a substantial portion of the overall processing time.

The table already has an extremely narrow NonClustered Index (tinyint, plus Clustered Key) on it which is what SQL is using when it does the Table Scan, so I don't think we can do any better in that regard.

There's a few solutions I'm aware of, which we'd like to avoid if possible:


Indexed View using COUNT_BIG(*)
Use faster Processors - we're using Cloud Servers, and there's very limited options in this regard.
Deleting Records - not really an option in this case.


Do we have any other options to speed this up?

Here's a Gist showing the setup: https://gist.github.com/elvishfiend/5094f120b14f8ecfb325623edcb5f3eb
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?