对于数GB的大索引，初次查询实在太慢

Question

怀疑是SQL SERVER初次要缓存整个索引，导致速度非常慢

从推理上来说，根据索引找一行数据，只需要缓存几个页，应该是几十毫秒就能返回数据

但一个场景下，18GB的索引，初次查询要60+秒，即使加一点预读，也是数MB的量

如何避免？？

---

SQL Server 2016 ~ 2000 性能优化、方案设计 QQ:315054403 田园嘉兴

Answer 1

set statistics io on;

得到的数字也非常小，但就是要数十秒

Table '**tab name**'. Scan count 8, logical reads 34, physical reads 19, read-ahead reads 2, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

---

SQL Server 2016 ~ 2000 性能优化、方案设计 QQ:315054403 田园嘉兴

Answer 2

Looks like optimizer did index scan, did you check execution plan if has better index suggestion?

Answer 3

看来楼上没碰到大索引初次查询慢的场景

执行计划是正确的，statistics io on 显示的值也非常小

---

SQL Server 2016 ~ 2000 性能优化、方案设计 QQ:315054403 田园嘉兴

Answer 4

怀疑是统计信息的锅，不会缓存整个索引的

---

想不想时已是想，不如不想都不想。

Answer 5

偶尔，还是初次肯定会出现？

Answer 6

原因已明确，设置统计信息异步更新解决

---

SQL Server 2016 ~ 2000 性能优化、方案设计 QQ:315054403 田园嘉兴

Answer 7

Hi iDBApp,

很高兴您的问题得到了解决，能够将怡红公子和您的回答标记为答案吗？

Best Regards,

Teige

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].