How to load entire database into memory?

Question

I want to load my entire database into memory, but how can I do this? I have about 256 GB memory and my database is about 200 GB, so I can easily handle in memory.

When I do select count(*) from table1 sqlserver automatically load table into memory and after this I'm able to use table very fast, but I want to know how can I load my entire database into memory?

If I do select count(*) from on each table I'm able to work faster but is there any other way to load entire database to memory? I want to load the entire database by a single command, not select count(*) from table by table.

• Shanky: Do you have enough memory to load you complete database into memory. Will this not affect other databases residing. Have you thought about this. Simple run select * on all tables off that database and all pages would be in memory.But I would say this is not a good idea this for sure will cause memory pressure on production system
• Shanky: AFAIK there is no command to load complete database into memory and IMHO this would not be a correct thing to do. Allow SQL Server to manage it
• paparazzo: Even if you could you would have to wait to load the whole database in memory and that would take longer than the hit on the queries not yet in memory. Maybe get a faster disk.
• Sean Gallardy: If you really want it always in memory all the time - you're already using SQL 2014, use in memory tables. Otherwise, there is nothing you can do.
• Kin Shah: If you want database in memory, look for Hekaton (In-Memory OLTP) and analyze your tables that will benefit from it. There are limitations, but its much better than doing a select * blindly across the whole database !

Answer 1

I want to load my entire database into memory, but how can I do this?

Before figuring out "how" to do something, it is often best to be clear on "why" that something should be done. So, why exactly do you want to load your entire database into memory? Memory is a finite resource, so it needs to be used efficiently / wisely.

I have about 256 GB memory and my database is about 200 GB, so I can easily handle in memory

Ok, so you appear to have 56 GB of RAM beyond the size of the database. But here is what is not being considered outside of the data itself:

• Unless your company goes out of business, databases grow, even if you archive off older data. 56 GB will soon not be enough, right?
• Memory is needed by the OS (Windows plus the related services such as Scheduled Tasks, etc)
• Memory is needed by 3rd party software (monitoring, anti-virus maybe, etc)
• Memory is needed to run the database engine process (the MSSQLSERVER or InstanceName NT Service)
• Memory is needed to run the SQL Server Agent process
• Each Connection / Session requires some amount of memory
• Memory is needed for queries (look at the "memory grant" of a query plan):
  • aggregate operations
  • computations
  • sorting
  • etc
• When objects call other objects, SQL Server maintains a call stack of each parent context so that control can revert back to that parent context when the current context completes. This call stack is managed in memory.
• Query Plan cache
• DMV stats / info
• Result sets (or at least the pointers to the position within each Result Set that each process is currently at)
• etc
• etc

Now, regarding that 200 GB of data, you should consider this: Do you use all 200 GB of that data? I highly doubt it. Loading data pages into memory and not referencing them is inefficient and wasteful. You are far better off loading pages on demand because the disk I/O used to get them and the memory taken up by them will be worth both of those resources since you aren't wasting them on pages that nobody wanted anyway.

I want to load entire database by single command not SELECT * table by table.

No, there is no single command to do this.

Also, the SELECT COUNT(*) method doesn't even accomplish this goal. All it does is load, via a Scan, the smallest object it can find, which might be a NonClustered Index, or it might actually be the base table itself. But it doesn't load:

• any indexes into memory (if scanning the Table) or the Table itself into memory (if scanning an Index), nor any objects that weren't scanned for the COUNT(*) operation. So you would still need to find a way to force an Index Scan for all Non-Clustered Indexes and/or a Table/Clustered Index Scan if a NonClustered Index was used for the COUNT(*) operation. But that would be even more wasteful because not only are they not always being used, but loading them each time would work against your ability to use the index DMVs to determine which indexes are, in fact, not being used!
• any off-row LOB data pages (and probably not Overflow pages). You would need to select any LOB columns separately, and any variable-length columns that have Overflow data.

• jean: IMHO it's best to let the hardware resources management to the Engine/SO. Op can try to create a in-memory drive and move here some filegroups but even if that is possible it's very risk (blackouts or anyone tripling in the power cable will "evaporate" your data, be sure to run a replication if it can be done).
• Solomon Rutzky: @MartinSmith Thanks for mentioning that. I tested it and found that SQL Server does indeed behave as you described. I have updated my answer to clarify that point, which strengthens my assessment that the SELECT COUNT(*) method doesn't accomplish the goal.

Answer 2

Simply you can convert your RAM as "Ram Drive" using 3rd party software. The one I was used as a test purpose is called- "RamDisk" you can search on google. Yes you will get almost 5/10x more speed on read write but only thing is you will lost data if any power cut happens. So use backup always if you are storing any important data on it. Best of luck