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 秒.

但在第二次运行时，只需要几秒钟.

这是因为以某种方式缓存了执行计划，还是实际上缓存了数据，以便我可以在第二次运行时更快地检索它?

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.

您可以通过设置轻松查看数据是从内存中读取的还是从磁盘中读取的

You can easily see if the data was read from memory or from disk by setting

SET STATISTICS IO ON

返回以下有关执行查询的信息

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 least recently used pages are flushed.

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