Called Microsoft

Question

Called Microsoft

SQL ADMIN 79

SSCommitted

Points: 1993
More actions
May 7, 2008 at 2:58 pm

#207122

I called Microsoft and was on phone with them for 6 hours yesterday. They were not able to give me an explanantion. They turned around and said that, this is the way, SQL 2005 is designed to work.
I still do not agree with Microsoft because, one of my friend who works for another IT firm do have SQL 2005 and he says that SQL 2005 should allocate all the available memory. If maximum 14 gigs of memory is configured on SQL SERVER, SQL should utlize every bit of it.
If any one has corrected the issue, please let me know, i shall be eager to know the solution.
Prakash B

Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 734552 More actions · Answer 1

Maybe you should explain the issue a bit more from your side. It's not quite clear what you called about and what the symptoms are.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Also, what edition of SQL are you running (standard, enterprise, ...), 32 bit or 64 bit? What service pack?

What memory switches are present in boot.ini

What's you min memory setting? What's your max?

If maximum 14 gigs of memory is configured on SQL SERVER, SQL should utlize every bit of it.

Not necessarily. SQL will allocate memory = min server memory. After that, if it needs more it will allocate more, up to the max server memory setting. (assuming a 64 bit architecture here)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQL ADMIN 79 SSCommitted Points: 1993 More actions · Answer 3

Version of SQL is : 2005 EE + SP2 32 bit

Min memory is 2 Gigs, Max Mem is 14 Gigs.

Memory utilization of SQL Server in the perfomance counter is 28 MB

Prakash B

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Which performance counter?

What's the problem? That you believe it should be using 14 GB?

Is the /pae switch in the boot.ini file?

Is AWE enabled in SQL?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQL ADMIN 79 SSCommitted Points: 1993 More actions · Answer 5

yes both AWE and /PAE are enable.

Counters:

In SQL Performance Counter:

SQLServer:MemoryManager

Target Server Memory(KB) is not equal to tOTAL sERVER mEMORY(kb)

Prakash B

Ed Hammar-581110 SSC Journeyman Points: 82 More actions · Answer 6

The OS plays a role in determining how much memory SQL Server will use. My experience has shown that SQL Server 2005 will use as much memory as it needs but it won't necessarily allocate the memory you've told it to use at startup unless there is some load on the server.

mahesh.shinde SSC Enthusiast Points: 148 More actions · Answer 7

Hi,

It is recommended to grant the SQL service account the OS privilege "Lock pages in memory". By doing this you are preventing the OS from swapping SQL's memory pages to disk and allowing SQL itself to reduce it's working set when the OS requires it to do so. This should give you a performance benefit under tight memory situations. One of the odd results of using "lock pages in memory" is that we will use the AWE api to map the virtual address space into and out of physical memory (in fact the "lock pages in memory" privilege is a requirement for using AWE with 32bit). As a result the "AWE allocated" value from dbcc memorystatus will show a value, indicating that AWE is enabled even though it is disabled in sp_configure.

You will have reboot your server after granting access to sql service id.

Thanks and Regards,

Mahesh R. Shinde

MCITP - SQL 2005

Erich Brinker SSCrazy Points: 2973 More actions · Answer 8

Have you set the maximum memory on SQL Server? You would want to set the maximum SQL Server can have to at least 1 Gig lower than your server memory so you don't starve your operating system.

SQL ADMIN 79 SSCommitted Points: 1993 More actions · Answer 9

Thanks for the all the above suggestions. They have been done. But still SQL is not utilizing more than 28 MB of memory.

Min memory is set to 2 Gigs

Max server memory is set to 14 Gigs.

Prakash B

Erich Brinker SSCrazy Points: 2973 More actions · Answer 10

What are you looking at to tell you the amount of memory SQL Server is using? If I recall 1 of the perf counters doesn't report the value accurately. If you open task manager in the server what does it show the page file usage at?

schep021 Hall of Fame Points: 3538 More actions · Answer 11

You can check to see if awe is really enabled by executing sp_configure and looking at the "awe enabled" row. Look at both the config_value and run_value. What are they? If run_value is 0, then awe is really not enabled yet.

SQL ADMIN 79 SSCommitted Points: 1993 More actions · Answer 12

SQL ADMIN 79

SSCommitted

Points: 1993

May 29, 2008 at 8:14 am

#821774

AWE is enabled

Prakash B

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 13

GilaMonster (5/7/2008)
Also, what edition of SQL are you running (standard, enterprise, ...), 32 bit or 64 bit? What service pack?
What memory switches are present in boot.ini
What's you min memory setting? What's your max?
If maximum 14 gigs of memory is configured on SQL SERVER, SQL should utlize every bit of it.
Not necessarily. SQL will allocate memory = min server memory. After that, if it needs more it will allocate more, up to the max server memory setting. (assuming a 64 bit architecture here)

Actually - I had a different understanding. You usually have a better grip on this than I, so perhaps you can set me straight.

I thought the min memory setting had nothing to do with startup value for RAM. SQL Server starts up grabbing whatever memory it needs to start (which could be either bigger or smaller than min memory). It only acts as a low-water mark once memory usage pushes past min memory setting; so - the memory doesn't have to reach or go over the min memory setting, but it won't go back below the min memory setting once it exceeds it until the server is restarted.

So - once the server starts up - the mem usage would progressively increase until it reaches the min memory setting, at which point it might fluctuate up or down between the min and the max, until the service is restarted.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

sgmunson SSC Guru Points: 110639 More actions · Answer 14

Umm... I may not be recalling this correctly, but I haven't seen anyone ask what Server OpSys is in use, and I did see the indication that SQL Server is 32-bit, which would suggest the OpSys is W2K3 32-bit, and I was thinking that 32-bit Windows operating systems can only allocate a maximum of 2 GIG to any given process, with a maximum total RAM of 4 GIG. Do I have that right, or is that info out of date?

Steve

(aka smunson)

:):):)

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)