SQL 2008 Target Server Memory value goes up and down for no reason.
MS-SQL Server Enterprise Edition x64, running on Windows Server 2008 with 24GB and it is dedicated db server.
The Target Server Memory counter is around 22 GB most of the time, however last week I noticed that the counter value gradually went down to 12GB in 2 days period, then suddenly started to going down to 1GB approximately every 10 minutes. Eventually it recovered back to 12GB but then went down again that last all day long.
Also the Total Server Memory counter was never above 1.3 GB during that period.
This happened for apparently no reason there was not stress on the server. I fixed the problem by restarting SQL Windows service. I had similar scenario 3 months ago.
I checked some articles that talk about “Cache Bytes” and “System Cache Resident Bytes” but both values were around 115 MB.
I cannot figure out the root of the problem, any idea of where to start tracking this issue.