MSSQL Server - read whole databases at startup to memory

UNIX for Advanced & Expert Users
1

Hello all,

i installed here an MSSQL Server 2022 on Ubuntu Server 22.04LTS. Has over 25% more power. Very good.
Now I have the problem that after a restart only the parts of the database that are queried are imported into the RAM. (i know that's normal)
But, is there a way to have the entire database (very small at 10GB) written to RAM after a reboot? In other words, simply query all the contents?

I found also that: How to emulate Persistent Memory But when the machine is restarted, the data is all gone. Also clear.

Very thanks
best regards
darktux

2

Schedule the following on startup?

SELECT * FROM 
  (SELECT TABLE_NAME 
    FROM [<DATABASE_NAME>].INFORMATION_SCHEMA.TABLES 
    WHERE TABLE_TYPE = 'BASE TABLE'
  )```
3

@Skrynesaver , doesn't that (just) return a list of BASE TABLE names for each DB, would that be sufficient to load data in memory ?

4

@darktux , why would you want to do that ? , what's the game plan here ? , have you consulted the MSSQL server documentation wrt memory usage etc ?

5

@Skrynesaver thank you but this will only list the tables...

@munkeHoller
The is running with Autoupdate und Autoreboot, so after reboot everything must be read in again. This means that each "first access" takes at least 3 times as long.
The goal is to move the entire database into RAM after starting the VM.

2 Likes
6

Sorry, too early, you'll have to step over that list selecting * from each with a cursor...

DECLARE table_cursor CURSOR FOR
  SELECT TABLE_NAME 
    FROM [<DATABASE_NAME>].INFORMATION_SCHEMA.TABLES 
    WHERE TABLE_TYPE = 'BASE TABLE'

OPEN table_cursor 
FETCH NEXT FROM table_cursor into @table_name
WHILE @@FETCH_STATUS = 0  
BEGIN  
  SELECT * FROM @table_name
  FETCH NEXT FROM table_cursor into @table_name
END
CLOSE table_cursor
DEALLOCATE table_cursor
7

@Skrynesaver , np.

Maybe parallelize and split that across N clients - each client focused on a single DB ... , though if everything on a single disk/single-network connection .....

Whilst that would undoubtedly touch all rows, not sure that'd guarantee the rows are kept in memory ... (would need to see the innards of the back end engine and observation ....). It would howver in most cases improve initial access speeds - but again, that'd is totally dependant upon the initial query request.
Additionally, user requests would most likely have 2ndry impact - some clever user requesting a cartesian join on the N largest tables for example.

Doing any of this without extensive understanding of use cases in 'the real' is less than ideal wrt 'performance'.

finally

@darktux your closing comments '....But when the machine is restarted, the data is all gone. Also clear....' , well RAM is not preserved between system recycles. If you want that you would need to save the memory contents to disk then load into memory at startup, but that is an overly simplified 'explanation' of what would actually be needed wrt to a MSSQL server instance and way outside the remit here. Talking to MS about this in detail and following their advice should be your initial course of action. Having data in memory is only part of the story. Ensuring all the tables are properly indexed, hints etc that the optimiser employs are up to date, that client applications are also accessing in a reasonable manner to minimise delays on the db etc.

8

First: I don't know.

...

Sixth: Would causing it to do a backup / dump induce it to read everything into memory?

9

The whole thing is probably much more complex. This has now become a development customer project. I'll keep you up to date.