My simplistic testing suggests that a statement like

UNLOAD FROM TABLE MyVeryBigTable TO 'nul';

results in all the rows being read - and therefore ending up in the cache, but no disk activity. Is this correct or is there some big catch I'm not spotting?

Background: There have been a number of questions over the years to do with cache warming like this one, but basically everyone one is searching for the "holy grail" of the database that runs as fast after you've restarted it as it did before you stopped it. The old advice was - "Well, don't stop it then!" but this is becoming more of a problem with the ever more frequent Windows updates.

SAP have added some functionality that can help, such as the default cache warming process that reloads the pages loaded when the database was last started, and the new v17 option that allows you to manually chose a point in time to record what is in the cache and have that reloaded. Neither of these quite hits the spot - the new feature being dependent on users choosing a good time to do it and the data not changing much.

A lot of of customers try to address this problem by running big reports etc after a restart - but have to do it manually or set up something to schedule it. Memory is now pretty cheap and for most customers we tend to have pretty well as much RAM as database - so a more lavish approach seems possible.

The idea would be to have an event triggered by database start up that just executes the above unload for all the important tables in the database, omitting audit trails, blob data etc that don't impact normal query performance. The first plan was to force an unload to file, or possibly a variable, that was then discarded, but there are dangers of running out of disk space or exceeding the 2GB limit on variables. The nul device approach would seem to be safer and also more efficient.


UPDATE

We ended up using the UNLOAD SELECT method. We also put in a check on ISSERVER so that the process isn't invoked when the database is being started manually or by batch file for things like applying logs etc.

CREATE EVENT "adminuser"."CacheWarm" TYPE "DatabaseStart"
HANDLER
BEGIN 
IF PROPERTY  ( 'IsService' ) = 'Yes' THEN 
    MESSAGE string('Cache Warming Started at ',current timestamp) TO CONSOLE;
    UNLOAD select * from AnImportantTable TO 'nul';
    .... lots more tables
    MESSAGE string('Cache Warming Completed at ',current timestamp) TO CONSOLE;
END IF;
END

asked 20 Jun, 10:03

Justin%20Willey's gravatar image

Justin Willey
6.8k110142212
accept rate: 20%

edited 06 Jul, 10:07

2

UNLOAD TABLE places an exclusive lock on the whole table or materialized view.

Might that be a show stopper?

(20 Jun, 10:07) Volker Barth
Replies hidden

Mmmm... good point. It's in a database start up event so to some extent we aren't too bothered about immediate access, but we maybe could avoid it with

UNLOAD select * from  MyVeryBigTable TO 'nul';

the docs don't talk about locking in that case.

(20 Jun, 10:17) Justin Willey
2

Looking at the lock display in Sybase Central: UNLOAD TABLE puts a Shared Table Lock and a Shared Schema lock on the table in question. UNLOAD select * just applies a Shared Schema lock.

(20 Jun, 10:27) Justin Willey
1

A very clever post deserves a very clever meme!

(20 Jun, 16:40) Breck Carter

Durrr.. just got it. Flash-to-bang time getting quite extended these days!

(22 Jun, 08:07) Justin Willey

Hm, I'm still in the brain warming phase... - memory swapped out:(

(22 Jun, 08:50) Volker Barth

Justin, if the "question update" is your (currently best) answer, I'll suggest to provide it as such:)

FWIW, when testing v17's "cache warming to a steady state" feature: Have you tried to "save the cache" just before the database is stopped deliberately (however to achieve that)? I'd think that should work well but have not yet tested for myself...

(06 Jul, 10:30) Volker Barth
Replies hidden
1

BTW: Would the new v17 builtin procedure sp_read_db_pages(), such as

sp_read_db_pages(dbspace_id, null, 1, 0);

do the same for you (for all tables on the specified dbspace)?

(06 Jul, 10:38) Volker Barth
Replies hidden

Yes - it looks like that would have the same (or maybe better as it's designed for the purpose!) effect.

(06 Jul, 11:11) Justin Willey

I've rather been hoping for an "offical" SAP response along the lines of - "yes that's OK", or "what a $%^&&^ stupid idea, it won't work because..." The v17 feature looks good for the future, but I do need something that will work with v16.

(06 Jul, 12:04) Justin Willey
1

I've rather been hoping for "an "offical" SAP response...

Hm, is it just my impression that for certain areas, answers by SAP engineers have been less frequent than before? And one would think, hey, that's a v17 feature we have implemented, and someone wants to use that, so we're eager to answer all questions about that...

I'm probably sounding rather naive:)

(06 Jul, 12:20) Volker Barth
showing 3 of 11 show all flat view
Be the first one to answer this question!
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×203
×39
×21

question asked: 20 Jun, 10:03

question was seen: 131 times

last updated: 06 Jul, 12:20