SQL Hint to load entire table into RAM before execution?

Question

I'm running some aggregation queries on some very large tables in an OLAP environment. I'm currently bottlenecked by disk IO at 200 MB/s.

I'm doing these queries on a machine with 92 GB of RAM. Is there any SQL hint I can write into my query that basically tells SQL to load the entire table into RAM before execution?

Something like:

select * from myTable with (ramdisk)

I am using MS TSQL.

Crookes

DBCC PINTABLE used to do this but has now been disabled. Next version has Hekaton for in memory tables. You would still presumably be bottle necked loading the data into RAM in the first place so not sure how much it would help (might be a hinderance) to load into RAM first vs just loading into RAM as the query executes. – Delaney

Answer 1

No. The database engine will do this automatically if it has enough space in the page cache.

You can set the amount of memory being used by using SQL Server Management Studio. Right click on the server, choose the memory option, and put a large number in the "Minimum Server Memory" box. If you have 92 Gbytes of RAM, then a number like 85,000 is probably good. You need to leave additional memory for the operating system and other services on the machine.

Assuming the table(s) fit into memory, this should facilitate processing. If they don't fit into memory, then you might have to take another approach.

Thatcher

SQL isn't perfect at predicting what to put in the read cache, so it does seem like if this hint existed it could significantly speed up some cases of queries. – Crookes

And for other classes of queries, loading a 50 GB table into RAM would actually be the bulk of the execution time. Seems like the perfect situation to add a hint capability. – Crookes

@JohnShedletsky . . . I imagine there are tremendous difficulties in a multi-user environment, with mixed load processing, in getting such a hint to work. Having a smart cache is much more versatile as a solution. However, this isn't the forum for such a discussion. – Thatcher