Table caching

Question

SQL Server

If there are ways to force specific tables to be cached in memory of mssql server 2008 forcibly? Or give high priority to caching a particular table?

For example, there is a table with a list of documents, 20 thousand entries, and it is one of the main ones in the system. Its full selection with a loaded server is 6 seconds, which is unacceptably long. It is called through the procedure where there is suser_name (), which makes automatic caching incapable.

Answer 1

I haven’t worked with mssql for a very long time, if you can’t climb inside ready-made functions 9a this is the only sure way to optimize), then you have two ways:
1. place the table in a separate tablespace on a ram disk (not recommended, because in case of a server emergency the database will have to be repaired, but this is guaranteed to place the data only in RAM)
2. create a table in memory (more precisely in tempdb) stackoverflow.com/questions/27835/does-ms-sql-support-in-memory-tables and link through the view
Both methods require additional scripts, to fill these tables when the server is restarted, and most importantly, to save changes, nothing better than copying changes to a normal table by a trigger comes to mind - very effective, a little more difficult - to raise another database and set up replication (but the price of the issue is an increase in the cost of a license for server software)
ps By the way, two or more databases spaced apart by servers or even geographically can allow schemes when the entire database is located in RAM.

Answer 2

It is difficult to imagine a task where all 20 thousand records in a non-aggregated form are needed on the client. If on topic, then for the table, as well as for each of the indexes of this table, you can create separate filegroups that can be placed on separate hard drives and get almost parallel reading / writing.