Drop in Mem Usage when engine window minimized

[justin_willey]

UPDATE

A further symptom that is reported after the working set of memory is reduced and before it returns to the original level, is that a frequent number of [Sybase]{ODBC Driver][SQL Anywhere]All threads are blocked errors are encountered. Once the memory usage has returned to the level allocated to cache in the start-up parameters these messages are not encountered again - until the next database re-start or minimization of the engine window.

---

v10.0.1.3931 but also reproducible in v12.0.1 Win XP & Server 2003 This is an issue reported by a client that I can reproduce locally.

When an open database engine window is minimized, the memory usage (as reported by task manager) drops from whatever value is was at to about 1MB and then rebuilds. During this time, performance seems to be effected, behaving in a similar way to when the database has been restarted and has not built up its cache.

Engine window open:

dbsrv10.exe - Mem Usage: 513,104K VMSize: 528,836K

a few seconds after the engine window was minimized:

dbsrv10.exe - Mem Usage: 5,976K VMSize: 528,836K

The VM size is unaffected, reflecting the cache settings of the service:

-n laptop10
-c 500M
-ca 0
-gp 4096
-x tcpip
-z
-md full

I know that Task Manager's memory usage reporting is by no means the whole story, but it seems very odd that minimizing the engine window should have such an effect (restoring the window does not seem to have any effect) and there does seem to be a real impact on users.

Does any one have thoughts?

[VolkerBarth]

I have no idea - but would suggest to test with SysInternals's Process Explorer and to check for working set sizes and the like...

[former_SQLA_member1694874]

v10.0.1.4075 does exactly the same thing, although I can't test if the performance is effected.

[justin_willey]

Type: Open - Minimized
Private Bytes: 528852 - 528852
Virtual Size: 1627724 - 1627724
Working Set: 513524 - 2452
WS Private: 509676 - 1944
WS Shareable: 3848 - 508
WS Shared: 644 - 280

sorry about the awful formatting - I can't get the hang of the markdown!

[VolkerBarth]

From John's elaborate answer to this FAQ (highlighted by me:):

If you watch the working set (using perfmon, NOT task manager), you might notice that Windows will trim the server's working set size periodically. It will do so aggressively whenever you minimize the server's window for example. This behaviour is not problematic because those pages show up as "free" (ie the sum of "avail+working_set" stays the same) but, more importantly, they are not truly freed...

Funny thing: I've found this answer when searching for what memory statistics to look at - primarily I simply do know (like you, apparently) that Task Manager is not the tool of choice:)

[justin_willey]

If I read that link correctly, then John saying that this is expected behaviour, and not to worry about it, as the data in still in memory (assuming something else doesn't grab it) and the only penalty is a soft fault when first accessed.

It does seem to be having an effect in our case (at least in 10.0.1) as we appear to get a fall off in performance until the working set goes back up, it's had to be precise about this though.

One demonstrable effect is that during the "regrowing" phase, there is a time of about 5-10 minutes during which the server becomes unresponsive (user are not disconnected however). This happens after a server restart and after an engine window minimization.

[johnsmirnios]

From the "Memory / PageFaults/sec" counter description in perfmon: "Most processors can handle large numbers of soft faults without significant consequence". To regrow your working set from 5MB to 512MB, you will need to service about 132000 soft faults which doesn't sound like a lot to me. Certainly not 5-10 minutes worth. You might be able to monitor soft faults system-wide at least via "Memory / TransitionFaults/sec" though the documentation isn't clear and it includes a few other fault types. You could also compare "Process / PageFaults/sec" for the database server versus "Cache / Pages/sec" system-wide. The former includes all hard and soft faults for the the database server and the latter just the hard faults for the whole system. I don't see any more direct way of monitoring soft faults for a particular process.

[justin_willey]

I don't think the soft faults could be the cause of the hiatus since it happens after a reboot as well as after the working set being trimmed.

It doesn't seem to kick in until the database gets busy - eg if you re-start the server during the evening, then the problem happens about half an hour after most users start in the morning. If you have to restart during the working day, it's about half an hour after the restart. During the hiatus, the MemUsage (measured in Task Manager) grows steadily but there is virtually no processor usage - normally about 40%.

[VolkerBarth]

BTW, as 10.0.1 uses cache warming by default - do you know whether your database makes use (or could make use) of cached pages from the last start?

[VolkerBarth]

Is running the engine as a service an alternative?

At least that might help you to get rid of the "minimizing the window" problem AFAIK...

[justin_willey]

It's on the default setting of ON. I had wondered about trying to turn it off.

[justin_willey]

The example in question is in fact running as a service (but with the interact with desktop enabled). Now of course I can switch this off - which solves the immediate problem.

I raised the issue however, as what happens when the engine window is minimized is exactly the same as the behaviour when the database server starts - making me wonder if there really is something real happening.

[VolkerBarth]

Well, I had formerly thought it would mean "cache reloading" (i.e. reload the cache contents from before the database was stopped) and then learnt that it just reloads the pages already cached by the last database start...

For older versions, I used to have particular queries such as "select avg(pk) from MyCentralTables)..." on startup to pre-load the cache with what I thought of "frequently used pages".

Possibly you can use similar queries to pre-load relevant pages? (From your postings, I would suspect the current pre-loaded cache is not that useful...)

[justin_willey]

I think you are right. Really you want in cache is what was there when the database stopped last!!

[VolkerBarth]

Cf. this thread called "Cache Hibernation" from the "product futures" NG...