Low memory utilization by Data Cache

[Former Member]

Hi,

I have BW 7.30 installed on MS-SQL Server 2008 (there is virtual machine).

Phisical memory = 20.480 MB

Current memory = 7.168 MB

SQL Memory Setting = Fixed

...

Data Cache = 448,1 MB / Hit Ratio = 48,476%

Proc Cache = 807,4 MB / Hit Ratio = 98,274%

...

Free Pages = 5.576 MB

Can you tell me, why does Data Cache and Proc Cache not utilize all assigned memory ?

And hit ratio is so low?

Regards

Maciej

[former_member184473 · Accepted solution]

Hello Maciej,

Are you using locked pages (note [1134345|http://service.sap.com/sap/support/notes/1134345])?

If you observe many "page in" in your database server (ST06 -> Detail analysis menu -> Previous 24 hours: Memory) this shows you're using locked pages.

Regards,

Eduardo Rezende

[xymanuel · Accepted solution]

Hello Maciej,

you can easy check this, if you look in your SQL Server Log.

The approx. 15th entry after startup your SQL Server should be:

"Using locked pages for buffer pool."

If you see something like, "Cannot use locked pages in memory" you have the problem.

The SQL Buffer take some time to warm up. Even if you set fixed memory min/max to the same value,

the SQL Server buffer may not be filled up with pages, if the DB Server is not under load.

Do you have load on the system?

Its normal, that short time after start up, the cache hit ratio is very bad, becaus its empty

Kind regards

Manuel

Edited by: Manuel Herr on Feb 20, 2012 3:39 PM

[Former Member]

Hello

I have entry "Using locked pages for buffer pool."

How long time does buffer need to warm up? It runs for more than week.

The question is why does buffer not utilize all assigned memory?

As result hit ratio is low.

Regards

Maciej

[former_member184473]

Hello Maciej,

Which SQL Server version are you using (select @@version)?

Regards,

Eduardo

[Former Member]

Hi

My version is:

Microsoft SQL Server 2008 R2 (RTM) - 10.50.1702.0 (X64) May 7 2010 15:21:11 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

Regards

Maciej

[former_member184473]

Hello Maciej,

SQL Server 2008 R2 SP1 is already available.

Can you update your database server and check if you face the same issue?

Regards,

Eduardo