How do I clean data cache buffers? 1

[micha123]

In MS SQL SERVER there is the command:
DBCC DROPCLEANBUFFERS
which cleans all caches dynamically.

Do I have such a command (docummented or not) for SYBASE ASE?

Thanks.

 

[micha123]

Hey, guys.... someone...?
Tell me that it is not possible, but don't leave me in silence!
(Thanks)

"A long life is when each day is full and every hour wide!"
(A Jewish Mentor)
M.

 

[dbalearner]

What do you need to do so M? Flushing the cache is rarely required in ASE? Please explain the underlying problem so we may be able to help. Are you running out of procedure cache or you have a bad plan in memory?

 

[micha123]

Ok.
I need to test various dynamic configurations scenarios for my ADAPTIVE SERVER in a production environment.
The problem is that I can't reboot the ASE.
What I want to do is, before each test, clean my data cache, so I would be able to compare each case with the others fairly.
I know I can do that by "playing" with the Buffer pools size, but I thought maybe there is a more gentle way to do that, such as: DBCC DROPCLEANBUFFERS offered by SYBASE competitor - MSSQL...
Thanks in advance.

"A long life is when each day is full and every hour wide!"
(A Jewish Mentor)
M.

 

[dbalearner]

M,

As you suggested if you are doing systematic testing then you should go back at every cycle to where you started. In that case rebooting ASE (not just for cache) is expected. However, if that is not possible you can do one of the following:

a) You create a very large table, a table larger than the cache, and force a cache pool size and prefetch/replacement policy and then flush with this table.

For example)

SELECT COUNT(*) FROM TABLE (INDEX 0 PREFETCH 2 LRU)

b) Bind to a different cache and back.

If you can get isolation, you could also create a very small cache and bind the database to that cache (The process of binding an object/database to a different cache flushes the pages out of its current cache). Afterwards, bind the database/objects back to the original cache.

c) You can change the cache pool sizes. Just create a 16K cache and move away almost everything from 2K pool and put it back. In new versions oif ASE (12.5), this is dynamic.

sp_poolconfig 'default data cache', '1000M', '16K'
go

Hope this helps

 

[micha123]

Hi, dbalearner,

Thank you very much for your reply.
I'm already doing what you suggested in c),
I thought SYBASE guys prepared an undocummented dbcc command to clean caches or something.
What do you mean by "rebooting ASE (not just for cache)".
What else do I have to think of, except cleaning tempdb?
(and well - I can't clean the procedure cache either, right?)

"A long life is when each day is full and every hour wide!"
(A Jewish Mentor)
M.

 

[dbalearner]

M,

To flush our procedure cache you can do the following:

1) reduce it to minimum size
2) run a dummy proc to remove small number of residual entries (a large, but trivial proc e.g. lots of select count(*) duplicated in the source text and run x1000 should do it)
3)return to original size

To clean up tempdb just use the following dynamic SQL to create a list say for tables and drop them:

use tempdb
go
set nocount on
declare @a varchar(16384)
select @a = "select 'DROP TABLE', name, convert(char(1),0x0A),'go' from tempdb..sysobjects o where o.type = 'U' order by o.name"
exec (@a)
go
exit
-- Then use isql to run the result of this script against the database. Do the same for procedures and views.

If you write a shell script you can easily automate this easily.

Good luck

 

[dbalearner]

M,

It is my understanding that you can flush all procedures out of cache, for a database, by using dbcc markprocs. This will cause all procedures in the database to be recompiled.
dbcc markprocs (database name)
go

 

[dbalearner]

M,

It is my understanding that you can flush all procedures out of cache, for a database, by using dbcc markprocs. This will cause all procedures in the database to be recompiled.



dbcc markprocs (database name)
go

 

[micha123]

Thanks, dbalearner!
Gave you a star for your help.
Just remember that procedure cache size is static, so I will use the dbcc command you suggested and I will check its behavior.

M.

"A long life is when each day is full and every hour wide!"
(A Jewish Mentor)
M.

 

[dbalearner]

Thanks. Which version of ASE are using? It should be dynamic in almost all versions. Either a % of total mamory < 12.5.0.3 or absolute value in MB in 12.5.0.3 or higher but you should be able to change it either way.

Good luck

 

[micha123]

Hey, dbalearner,
I'm sorry.... you're right - it's dynamic.
M.

"A long life is when each day is full and every hour wide!"
(A Jewish Mentor)
M.