缓存SQL查询 [英] Caching SQL queries

问题描述

如果我在SQL Server的探查器中查找，它会出现很多重复的查询，例如：

If I look in my profiler for SQL-server, it comes up with a lot of duplicate queries such as:

exec sp_executesql N'SELECT *
FROM [dbo].[tblSpecifications] AS [t0]
WHERE [t0].[clientID] = @p0
ORDER BY [t0].[Title]', N'@p0 int', @p0 = 21

不需要很多这些查询可以显示实时数据，也就是说，如果有人插入了与该查询匹配的新记录，则在插入后一个小时内没有显示该记录都没关系。

A lot of these queries are not needed to display real time data, that is, if someone inserted a new record that was matched in that query it wouldn't matter if it didn't display for up to an hour after insertion.

您可以输出asp.net页面的缓存，但是我想知道dbms（尤其是SQL-server）上是否有类似的功能，它将查询结果保存在缓存中，并在设置后更新缓存一段时间，例如1小时，目的是提高记录的检索速度。

You can output cache the asp.net pages, but I was wondering if there was similar functionality on the dbms (SQL-server in particular), which saves a query results in a cache and renews that cache after a set period of time, say 1 hour, with the aim of improving retrieval speeds of records.

推荐答案

在SQL Server 2000及更低版本中，您可以使用DBCC PINTABLE（数据库ID，表ID），但最好允许SQL Server进行管理发挥您的记忆力

In SQL Server 2000 and prior, you can use DBCC PINTABLE (databaseid, tableid), but its best to allow SQL Server to manage your memory

如果您有一个昂贵的汇总查询需要缓存，请创建索引视图以实现结果。

If you have an expensive aggregate query that you would like "cached", create an indexed view to materialize the results.

否则，数据库页保留在内存中的时间由最近最少使用的策略确定。高速缓存中每个数据页的标题存储有关最近两次访问该页面的详细信息。后台进程扫描缓存，如果自上次扫描以来尚未访问该页面，则减少使用计数。当SQL Server需要释放缓存时，将首先刷新使用计数最低的页面。 （专业SQL Server 2008内部和故障排除）

Otherwise, the amount of time a database page remains in memory is determined by the least recently used policy. The header of each data page in cache stores details about the last two times it was accessed. A background process scans the cache, and decrements a usecount if the page has not been accessed since the last scan. When SQL Server needs to free cache, pages with the lowest usecount are flushed first. (Professional SQL Server 2008 Internals and Troubleshooting)

sys.dm_os_buffer_descriptors对于当前缓存中的每个数据页包含一行

sys.dm_os_buffer_descriptors contains one row for each data page currently in cache

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