今天在回答别人问题得时候发现了这样一个问题
经过以下 语句测试后 ，发现确实如此
set statistics io on select COUNT(*) from dbo.tb_Info with(index(PK_tb_Info)) select COUNT(*) from dbo.tb_Info with(index(IX_TB_INFO)) set statistics io oFF(1 row(s) affected)
Table 'tb_Info'. Scancount 1,logical reads 3269,physical reads 0,read-ahead reads 0,lob logicalreads 0,lob physical reads 0,lob read-ahead reads 0.
(1 row(s) affected)
Table 'tb_Info'. Scancount 1,logical reads 68,lob logical reads0,lob read-ahead reads 0.
然后我通过对这两个索引详细信息进行查询
SELECT I.name,P.index_level,P.page_count,P.avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats
( db_id(),object_id('ConnectionPool.tb_Info'),default,'DETAILED') P
JOIN sys.indexes I
ON I.object_id = P.object_id AND I.index_id = P.index_id
WHERE I.name = 'PK_tb_Info';
Name index_level page_count avg_page_space_used
PK_tb_Info 0 3258 96.8682357301705
PK_tb_Info 1 9 58.117123795404
PK_tb_Info 2 1 1.42080553496417
SELECT I.name,'DETAILED') P
JOIN sys.indexes I
ON I.object_id = P.object_id AND I.index_id = P.index_id
WHERE I.name = 'IX_TB_INFO';
Name index_level page_count avg_page_space_used
IX_TB_INFO 0 66 99.9414133926365
IX_TB_INFO 1 1 10.575735112429
logical reads 3269=3258+9+1+1(计算)
logical reads 68=66+1+1(计算)
我们可以看到在count查询语句，索引都会去读取叶层级，再进行输出
而聚合索引因为叶子节点就是实际数据所以需要读取次数远大于非聚合索引。
而logical reads=索引各层级页面之和+1
故，索引的完全扫描会导致逻辑读次数=索引页数
这也就是导致在使用count(*)时非聚合索引性能大于聚合索引性能得原因
而其他大多数情况中，不会出现该情况
