4

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.

Tom Gullen
  • 61,249
  • 84
  • 283
  • 456

4 Answers4

3

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

Ben Tennen
  • 445
  • 2
  • 6
1

No, but there are a ton of caching solutions out there such as Memcached and Ehcache.

bad_coder
  • 11,289
  • 20
  • 44
  • 72
Jody
  • 8,021
  • 4
  • 26
  • 29
1

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

This is a main reason to avoid table scans where possible. If the table being scanned is big enough, your cache gets flooded with potentially useless data.

A lot of people have a "who cares how long the query takes, it is running it batch mode" attitude, but they fail to see the impact on other processes, such as the one you mentioned.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
datagod
  • 1,031
  • 1
  • 13
  • 21
0

Not to miss the obvious, you could also create a wholly separate reporting table and update it hourly. While there'd be a cost in populating and administering it, you could limit the fields to what's needed and optimize the indices for reads.