How to optimize: having COUNT(*)>1
I have now moved from dev to production.
the table FactBackOrder has 4,183,289 rows.

We're in sql server 2012.
Microsoft SQL Server 2012 (SP1) - 11.0.3412.0 (X64) 
    Mar  2 2014 01:25:09 
    Copyright (c) Microsoft Corporation
    Enterprise Edition (64-bit) on Windows NT 6.3  (Build 9600: )

But I don't want to use Columnstore indexes - these tables are updated very often.
Maybe in sql 2014?           

the original query plan of the query I wanted to improve the performance of is:



the only way I found out to improve the orginal query was to add an index to the table.

create index idx_FactBackOrder_OrderNo_OrderItemSeqNo 
on Facts.FactBackOrder (OrderNo,OrderItemSeqNo) with (online=on)


When I then run the query, I notice it takes longer than the original without index.
the new query plan, after creating the index is as follows:



I immediately drop the newly created index:

drop index idx_FactBackOrder_OrderNo_OrderItemSeqNo on Facts.FactBackOrder


I then create an indexed view:

--drop view [dbo].[VfactBackOder]

CREATE view [dbo].[VfactBackOder]
WITH SCHEMABINDING
AS
select fo.OrderNo, 
       fo.OrderItemSeqNo, 
       COUNT_BIG(*) as the_number
from Facts.FactBackOrder fo
group by fo.OrderNo, fo.OrderItemSeqNo
GO


create the clustered index for the indexed view:

CREATE UNIQUE CLUSTERED INDEX [IDX_VfactBackOder] ON [dbo].[VfactBackOder]
(
    OrderNo, OrderItemSeqNo
)
go


The when I run the query using the newly created indexed view instead of the original query referencing the underlying tables:

select fo.OrderNo, 
       fo.OrderItemSeqNo,
       fo.the_number
from VfactBackOder fo
where fo.the_number &gt; 1


I get a non satisfactory result. 
then I add another index to the view:

CREATE NONCLUSTERED INDEX [&lt;Name of Missing Index, sysname,&gt;]
ON [dbo].[VfactBackOder] ([the_number])
INCLUDE ([OrderNo],[OrderItemSeqNo])
go


Now the query flies like a rocket.
the new query plan is:


And looking at the space used by the indexed view and the underlying table:

sp_spaceused 'Facts.FactBackOrder'
go
sp_spaceused 'VfactBackOder'
go



query is performing well enough, customers happy here.
thanks for all the comments.