0

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.

MadHatter
  • 79,770
  • 20
  • 184
  • 232
Pete Mottershead
  • 1
  • What was the Idera Available Memory reading prior to the OS patch? – jl. Nov 01 '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 01 '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 01 '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 01 '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 01 '10 at 16:15
  • I agree. system is performing top notch - NO performance issues whatsoever. What this is boiling down to is - many people say SQL Server grabs all the memory that min = max specifies at start up - but BOL does not say that. Reporting 4 gig makes sense to me. The Orion stats show using all 24 g prior to the Oct 3 reboot. That's! the part that does not make sense to me. thx. – Pete Mottershead Nov 01 '10 at 16:20

1 Answers1

1

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.

Community
  • 1
BradC
  • 2,220
  • 4
  • 27
  • 36