Speeding up Count(*) on large tables

Question

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

Answer 1

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 & 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's Clustered Index being selected?

Answer 2

If you're stuck on SQL Server 2012 you could try creating an index on just the clustered index key. It might be a little smaller than an index on a TINYINT column. You could also try adding page compression to your index. That could make your query faster but it depends on the data in the table.

If you're able to upgrade to SQL Server 2016 then you can create a nonclustered columnstore index on the table. That will make COUNT(*) queries extremely fast with a lower overhead on DML operations. Here's a quick demo:

DROP TABLE IF EXISTS #Items;

CREATE TABLE #Items (
    CLUST_KEY BIGINT NOT NULL,
    SMALL_COLUMN TINYINT NOT NULL,
    FILLER VARCHAR(50) NOT NULL,
    PRIMARY KEY (CLUST_KEY)
);

INSERT INTO #Items WITH (TABLOCK)
SELECT
    ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    , 1
    , REPLICATE('Z', 50)
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;

CREATE INDEX NCI ON #Items (SMALL_COLUMN);

SET STATISTICS TIME ON;

-- CPU time = 312 ms,  elapsed time = 320 ms.
SELECT COUNT(*)
FROM #Items
OPTION (MAXDOP 1);


CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI ON #Items (SMALL_COLUMN);

-- CPU time = 0 ms,  elapsed time = 1 ms.
SELECT COUNT(*)
FROM #Items
OPTION (MAXDOP 1);

With the NCCI I'm able to count six million rows in under 20 ms.

Answer 3

1. you can add id column which you update it manually such that is always in sequence come rain,summer or winter.

2. If you have single table and there is no where condition or join then

    SELECT o.NAME
   ,o.schema_id
   ,ddps.row_count
   FROM sys.indexes AS i
   INNER JOIN sys.objects AS o ON i.OBJECT_ID = o.OBJECT_ID
   INNER JOIN sys.dm_db_partition_stats AS ddps ON i.OBJECT_ID = ddps.OBJECT_ID
     AND i.index_id = ddps.index_id
   WHERE i.index_id < 2
     AND o.is_ms_shipped = 0
     AND o.NAME = 'even'
     AND o.schema_id = 1
   

I hear it do not depend upon updated statistics.I am not sure.

3. sp_spaceused employee It depend on updated statistics.

4. you can create something like job which run once a time and store the latest id and count

   create table ItemCount as Latestid int not null, LatestCount int not null

Itemcount table always contain only 1 rows and no need of any index

insert into ItemCount (Latestid,LatestCount)
select top 1  itemid
,(select count(*) from [dbo].[Items])LatestCount
from [dbo].[Items]
order by itemid DESC

-- Here count logic is your own

so whenver your query require count,you can do this,

declare @LatestID INT
declare @LatestCount int

select @LatestID=LatestID,@LatestCount=LatestCount 
from ItemCount ic 

declare @FreshCount int
declare @NewCount int
SELECT @FreshCount=COUNT(1)  FROM [dbo].[Items] it
where it.itemid>=@LatestID 

set @NewCount=@FreshCount+@LatestCount

here [dbo].[Items] 's itemid column should be indexed

This will also suit if you have join and filter condition in your count (*) query