we are running sql server 2008 (not R2). 64 bit machine/operating system - 32 gig memory. The hardware/system guys are using Orion, I use Idera to monitory system vitals. Orion refers to "Physical Memory Size" Idera uses the term "Available Memory". Right now they both show about 4 gig. Summing the memory usage of all proceses comes to around 3.5 gig so the sytem looks OK.

The sys guys are concerned because Orion always shows 32 gig 'physical memory' until this last op sys patch. After reboot that variable started displaying the 4 gig. They think something is wrong. (no reports of performance loss, no issues with any vital stats).

The min and max are set to 24 gig. SQL Server uses only what it needs. however if min is set to max, once the min is reached mem allocation will never drop below min. (Right?). However, it looks to me like our system never even gets close to min. BOL (and others) talk about a 'DataBase Server Load shortly after startup' to get to that min to grab that space for SQL Server so no other process can have it. (That's what we want its a dedicated server to that instance of sql server).

THE QUESTION: What is a database server load and how do I implement it?

Thanks, Pete Mottershead Senior DBA Central States Indemnity Omaha, NE.

  • What was the Idera Available Memory reading prior to the OS patch? – jl. Nov 1 '10 at 15:48
  • You're using the X64 SQL 2008, correct? Believe that the referenced "database server load shortly after startup" only applicable when AWE comes into play. – jl. Nov 1 '10 at 16:05
  • Excellent question. I don't know, I just came on board about a month ago and have no history. But I can tell you this: talked to Idera: that 4 gig value comes from: Windows 2003 "Win32_PerfRawData_PerfOS_Memory class, 'Available bytes' column. Since it agrees with the value Orion displays I'm guessing Orion uses that same column. Also, I checked on the net for issues regarding our last patch and there is no mention of anything changing regarding available bytes in that class. – Pete Mottershead Nov 1 '10 at 16:05
  • I am wondering if the patch simply caused a change to what is being reported, rather than a real change to the avaiable memory. I would have expected that would see query issues if what is being reported is indeed accurate. – jl. Nov 1 '10 at 16:09
  • Almost every reference I saw on the net made mention of AWE. However, in Delaneys book SQL Server 2008 Internals she discusses min = max and this 'db server load' thing separate and apart from the AWE section. BOL 'Dynamic Memory Management' seems to suggest it applies to both 32 bit AWE and 64 bit. (really hard to decipher). – Pete Mottershead Nov 1 '10 at 16:15

Don't use OS-level counters to determine SQL memory usage, use:

SQLServer:Memory Manager: Total Server Memory (KB)

(tells you the amount of committed memory from the buffer pool)

Process(sqlservr): Working Set

(reports the set of memory pages (in bytes) touched recently by the threads in the SQL Server process)

See http://sqlserverpedia.com/wiki/Memory_-_Performance_Counters for some more.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.