Sybase: force loading in cache

You can load an entire index into Sybase’s cache by executing the following:

select count(*) from <tablename> (index <indexname> lru)

index <indexname> forces the optimizer to use the specified index.
lru tells Sybase to use the LRU Buffer Replacement Strategy i.e. keep the data in cache after the execution of the statement.

This will perform an index scan and load the index data in cache.

If you want to perform a table scan instead i.e. load the data pages in cache, you can write the table name instead of the index name. But if you have an unclustered index with the same name as the table, it will be loaded in cache instead to make sure that the table is loaded, you can issue the following statement:

select count(*) from <tablename> (index 0 lru)

Please note that this will not load the text and image columns of the table.

In order to check whether the whole table is in cache, you can do the following:

select DBName+".."+ObjectName, CachedKB*100/TotalSizeKB as percentage from monCachedObject where IndexID = 0 order by percentage desc

This lists all tables in cache and the percentage of the data pages loaded.

One thought on “Sybase: force loading in cache

Leave a Reply

Your email address will not be published.