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.
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.
Advertisement
Answer
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.
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 contains one row for each data page currently in cache