Hi, Customer is running SQLA 12.0.1.3942 on Windows 2008 Server (64 bit). System has 12 GB RAM and 16 CPU cores. The database service (running \Bin64\dbsrv12.exe) is configured with "-c256M -ch 3072M". The database is extremely busy (we are analyzing why) and has been running for at least a couple of weeks, but the memory use (as seen in Task Manager) is steady at 2,051,716 KB, even with 5 GB free RAM on the system. Any explanation why the RAM use is not increasing up to 3GB as we would expect? Thanks, Bob Leviton

asked 14 May '14, 15:12

Bob%20Leviton's gravatar image

Bob Leviton
1967716
accept rate: 0%

Do you start this database by specifying it on the server start line, or some other method? Do you have the -gp switch set? What's the page size of the database?

(15 May '14, 10:11) Mikel Rychliski
Replies hidden

IMO a dedicated server on a dedicated computer has absolutely no need for dynamic cache sizing... determine how much memory can be allocated to the server and set that value via -ca 0 and -c. Then, when the server needs the cache, it's there, no need to wait ... for ... it ... to ... work ... through ... its ... growth algorithm :)

(17 May '14, 09:11) Breck Carter

There can be many reasons. It might be that there is no reason to use more than 2GB of cache (eg, if the sum of the sizes of the database & temp files is < 2GB). Also, Task manager displays the "working set" size of the process, not the amount of memory that it has allocated & is using. Try executing SELECT property( 'CurrentCacheSize' ) to get the current size of the cache.

In newer versions of Task Manager, you can add other columns such as "Commit Size" that will tell you how much memory has been allocated with backing store.

You can also monitor "Process/Private Bytes" or "Process/Virtual Bytes" in perfmon to view the total amount of memory & address space (respectively) that have been allocated by the process.

permanent link

answered 14 May '14, 15:56

John%20Smirnios's gravatar image

John Smirnios
11.9k396165
accept rate: 37%

edited 14 May '14, 16:13

Thanks, John. Actually the database file size is 42 GB, but I will check the CurrentCacheSize and Commit Size.

(14 May '14, 16:16) Bob Leviton

Yes, when I viewed Commit Size in Task Manager I saw the full 3GB. I was used to seeing the full cache size in the other memory column(s). In fact, the next day the other columns were also showing 3GB. Thanks!

(16 May '14, 09:00) Bob Leviton

As I understand it the cache is only filled with data as it is accessed in the database so I would presume that the data being accessed is only around 2GB. As such the cache won't go higher until you access some data in the database that is not already in cache.

It does seem odd that it hovers around almost exactly 2GB though!

permanent link

answered 15 May '14, 08:40

RADicalSYS's gravatar image

RADicalSYS
33191530
accept rate: 9%

As such the cache won't go higher until you access some data in the database that is not already in cache

Well, cache warming - enabled by default - will also fill the cache with pages requested during the last database start.

(15 May '14, 09:01) Volker Barth
Your answer
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:

×26
×19

question asked: 14 May '14, 15:12

question was seen: 8,548 times

last updated: 17 May '14, 09:11