January 4, 2008 at 12:34 pm
I'll ask the question up front: As long as the server isn't idle and your data file exceeds the size of the RAM allocation, shouldn't SQL Server use most of the RAM, most of the time?
I recently obtained Idera's newest SQL Diagnostic Manager and started grooming the alerts. There are no current reported performance issues, but I wanted to try and make sense of the alerts and decide what to do about it.
I've got a 4 physical core, dual core, HT CPUs on my server, server running SQL 2000 Enterprise SP4 (+AWE Hotfix), on Win 2003 SP2. (All 32 bit) AWE Enabled, 3 GB and PAE also enabled.
The tool reported very high memory usage. (100%)
92% used by Sql server, the rest by the OS. Buffer cache hit is 99%, Proc Cache hit is above 90% and very little paging going on.
This server is configured so that SQL Server cannot take more than 15 GB of the 16 GB of RAM. The server has about 30 active user connections and a couple databases on it. The main db they are connected to is about 35 GB and regularly re-indexed.
I've been reading a lot about the memory performance counters and am not finding much information that makes sense to me.
In our world, the data files will always exceed the size of the memory allocation. If the memory is available, should SQL Server use most of it most of the time it isn't idle?
Chris.
Chris.
January 4, 2008 at 1:23 pm
Under SQL Server 2000 32 bit with AWE enable, memory is not managed dynamically, so under your specifications of max server memory of 15Gb, at start-up SQL Server will obtain 15GB of memory and will never release the memory. Workload and database size do not affect the amount of memory SQL Server is allowed to allocate.
"The tool reported very high memory usage. 92% used by Sql server"
That is about right as 90% of 16GB is about 14.7 GB.
SQL = Scarcely Qualifies as a Language
January 4, 2008 at 1:30 pm
I remember reading that now that you mention it. That explains something else I saw (on the tool) on the identical test server when I changed the allocation to a lesser amount. Thanks!
Chris.
Chris.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply