April 14, 2014 at 9:14 am
We have a Sql Server running Server 2003 Std X64 SP2, On this we have a Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (Intel X86).
This Server has 12 gigs of ram in it right now, Sql is using about 3.8 of that and I want it to take more as I have 7 gigs doing nothing. I have turned on AWE and set my limit from 0-6Gigs. Still I top out of 3.8 gigs.
I have turned on lock pages in memory but I am not sure if it is actually doing anything. I just used a Domain admin account as a Test maybe I need a different account.
Am I missing something or is this sort of how it is.
April 14, 2014 at 9:28 am
SQL only takes memory as it needs it. Right now the server only needs 3.8 GB of memory as it needs more it will take more. If you need it to take at least 7GB every time it starts up and "never" releases it then you can set you "min server memory" property to 7 GB. Have you setup your "max server memory" too. I would recommend you give the OS at least 2GB and start from there.
I would recommend you download this ebook (or buy the hard copy if you want). Read the whole book, but you can skip ahead to the memory section to better explain what is going on and what options to set. Be careful with "lock pages in memory" if you don't understand what is going on behind the scenes.
April 14, 2014 at 9:34 am
Its been at 3.8 since we turned on AWE so I doubt that any Microsoft product could do that :). It looks like a hard stop. Its hit the 4 Gig mark and that's all it will take.
It only fluctuates about 500 megs down but will always hard stop at 3.8, We are seeing some slow down in DB function so that's why we want it to have more.
April 14, 2014 at 9:51 am
I have turned on AWE and set my limit from 0-6Gigs. Still I top out of 3.8 gigs.
If this is a 64 bit machine, what did you turn on AWE?
How did you "set your limit"? Did you use sp_configure?
sp_configure 'max server memory' XXXXX
RECONFIGURE
How are you determining that it's using 3.8 GB? Task Manager?
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
April 14, 2014 at 9:54 am
Michael L John (4/14/2014)
I have turned on AWE and set my limit from 0-6Gigs. Still I top out of 3.8 gigs.
If this is a 64 bit machine, what did you turn on AWE?
How did you "set your limit"? Did you use sp_configure?
sp_configure 'max server memory' XXXXX
RECONFIGURE
How are you determining that it's using 3.8 GB? Task Manager?
We turned it on via sql manager right click on the server and selecting properties. the Min to 0 Mb and the Max to 6144MB. We have restarted both
Yes I am checking Task manager.
April 14, 2014 at 9:56 am
First of all you don't need AWE on 64-bit server http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-530-awe-must-be-enabled-on-64-bit-servers/. Second if you want it to take 7GB of memory then set the "min server memory" and it will take that amount of memory.
Are you currently having memory pressure on your server? If not, why is it so important that you see the amount of memory get up to 7GB (i.e. your boss is questioning the amount of memory that the server really needs, etc).
Please download the book and read it (at least the memory chapter).
April 14, 2014 at 10:06 am
Much like other things on the internet there are many different points of of view
April 14, 2014 at 10:14 am
But I thought that you stated that you have a 64-bit SQL Server on a 64-bit OS? Argenis's blog doesn't apply to your situation unless I am wrong and you are running 32-bit SQL Server on a 64-bit server.
April 14, 2014 at 10:17 am
Keith:
Sorry As I reread this it is a bit fuzzy
We have a Sql Server running Server 2003 Std X64 SP2, On this we have a Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (Intel X86).
Yes we have 32bit SQL running on 64Bit windows Due to other limitations it has to be this way.
April 14, 2014 at 10:17 am
A key piece of information is missing. From the article you referenced, is this a 32 bit version running on 64 bit Windows?
Ok, I will give you that. You may or may not see a difference in performance, however.
Can you run this query?
sp_configure 'show advanced options', 1
RECONFIGURE
And then run this one, and post the results?
sp_configure
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
April 14, 2014 at 10:27 am
first command I am getting this
Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.
Msg 5808, Level 16, State 1, Line 2
Ad hoc update to system catalogs is not supported.
See attached for second command.
Sorry Sql is new to me like most It has fallen on my lap among other daily tasks so forgive me If I miss something.
Just waiting for approval to buy that book Keith suggested as it looks like it would be great.
April 14, 2014 at 10:31 am
Please run these separate queries.
sp_configure 'min server memory (MB)'
GO
sp_configure 'max server memory (MB)'
GO
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
April 14, 2014 at 10:37 am
Msg 15123, Level 16, State 1, Procedure sp_configure, Line 51
The configuration option 'min server memory (MB)' does not exist, or it may be an advanced option.
Same on the max
I assume these commands it doesn't matter what DB is select as they are server based?
April 14, 2014 at 11:11 am
Try this one: exec sp_configure 'show advanced options', 1
go
reconfigure
exec sp_configure 'min server memory (MB)'
GO
exec sp_configure 'max server memory (MB)'
GO
April 14, 2014 at 11:16 am
I Tried running each one separate and all of them together and they give the same errors see attached. Maybe I have something wrong.
Viewing 15 posts - 1 through 15 (of 34 total)
You must be logged in to reply to this topic. Login to reply