SQL query caching

Question

If I look in my SQL server profiler, it has a lot of repeated queries, for example:

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

Many of these queries are not needed to display data in real time, that is, if someone inserted a new record that was mapped in this query, it would not matter if it had not been displayed for an hour after the insert.

You can display the cache on asp.net pages, but I was wondering if there are similar functions in dbms (in particular, the SQL server), which stores the query results in the cache and updates this cache after a set period of time, say, 1 hour, in order to improve the search speed of records.

Answer 1

In SQL Server 2000 and earlier, you can use DBCC PINTABLE (databaseid, tableid), but it is best to let SQL Server manage your memory

If you have an expensive aggregate query that you want to cache, create an indexed view to materialize the results.

Otherwise, the amount of database page remaining in memory is determined by the least used policy. The header of each data page in the cache stores information about the last two times that it accessed. The background process scans the cache and reduces the usecount if the page has not been accessible since the last scan. When SQL Server needs to free the cache, the pages with the lowest usecount are first flushed. (Professional internal SQL Server 2008 and troubleshooting)

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

Answer 2

No, but there are tons of caching solutions like: http://memcached.org/ http://ehcache.org/

Answer 3

The query results are not cached, but the data pages themselves remain in the cache until they are pushed out by other read operations. The next time your request is sent, these pages will be read from memory instead of disk.

This is the main reason, if possible, to avoid scanning tables. If the table being scanned is large enough, your cache will be flooded with potentially useless data.

Many people have "who cares about how long the request takes, whether it works in batch mode", but they don’t see the effect on other processes, such as you mentioned.

Answer 4

In order not to miss the obvious, you can also create a completely separate report table and update it hourly. Although the costs of filling it out and administering it may be limited, you can limit the required fields and optimize the indexes for reading.