|
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 |
|
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. 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!
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
|
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?
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 :)