SQL Server CACHES查询结果吗？ [英] Does SQL Server CACHES Query Results?

问题描述

运行查询时，SQL Server是否缓存结果？

When I run a query does the SQL Server caches the results?

因为：
运行以下查询时：

Because: When I run the below query:

SELECT id
FROM Foo
WHERE Foo.Name LIKE '%bar%'

查询第一次运行 40秒。

但是在第二次运行中，只需要几秒钟。

But on the second run it takes only a few seconds.

是因为是因为执行计划被某种程度地缓存了，或者实际上是缓存了数据，以便我可以在第二次运行时更快地检索它？

Is this because the execution plan is somehow cached or actually the data is cached so that I can retrieve it much faster on the 2nd run?

推荐答案

SQL Server不会缓存查询结果，但是会缓存数据页。这些页面中的数据然后用于产生查询结果。

SQL Server does not cache the query results, but it caches the data pages it reads in memory. The data from these pages is then used to produce the query result.

通过设置

SET STATISTICS IO ON

上设置统计信息IO，其中返回以下有关查询执行的信息

Which returns the following information on execution of the query

Table 'ProductCostHistory'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

逻辑读取和物理读取之间的区别是从内存读取的数据。

The difference between logical and physical reads is the data read from memory.

SQL Server还将声明内存用于进行高速缓存，直到达到最大值（配置的最大值或物理最大值），然后刷新最早的页面。

SQL Server will also claim Memory for caching until the maximum (configured, or physical maximum) is reached and then the oldest pages are flushed.

这篇关于SQL Server CACHES查询结果吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！