June 9, 2011 at 10:13 am
We have a sql server that hosts a couple of heavy traffic applications.
The RAM now is 4 gb. It is currently use 3.1 gb for sqlservr.exe.
I would like to change from the default memory settings of SQL server to allocate a fixed ammount for sql server.
What size should I allocate to SQL server vs OS?
This morning we experience a quite sluggish time, I see in SQL logs, there are quite a few statements before that is:
MemoryBROKer_For_Cache, allocation =24533, Rate=0, Targetallocation =298431, future allocations =0, last notification=GROW
MemoryBROKer_For_Steal................,
Memorybroker_for _reserver allocations=3155, Rate=0, traget allocations=419985, future allocation =95233, last notification=Grow
What does that mean, is it a memory problem?
THanks
June 9, 2011 at 10:26 am
My immediate recommendation is to get the other applications off your SQL Server. Otherwise they will cause even more problems in the future.
As far as setting SQL to a specific Max amount of memory, we can't give you a recommendation. It depends, unfortunately, upon your setup. The best you can do is mimic your production load on a sandbox server (or a dev/test server) and fiddle with the settings in that environment. Once you have a good idea of what you want, then change it in production.
Are you using AWE to allocate memory? If not, you might want to investigate that.
I don't recognize the two lines about Memory Broker. Have you googled them yet?
EDIT: Found a link for Memory Brokers. http://msdn.microsoft.com/en-us/library/bb522548.aspx
June 9, 2011 at 10:33 am
Also note, if you are going to change your max memory setting, you should change your min memory setting as well.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 9, 2011 at 10:48 am
I should say it more clearer.
The box is only for SQL server, it hosts databases for diffrent applications.
So shall I allocate 1 gb to OS, and 3 to SQL server?
June 9, 2011 at 10:52 am
If you only have 4gb RAM available, then yes I would venture to go that route. The OS typically needs no more than that 1GB but mileage varies. If you are going to fiddle with memory settings, make sure you set min and max on SQL Server (e.g. min=1GB and max=3GB).
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 9, 2011 at 10:56 am
Thank you.
I will try that.
I always wonder: does microsoft recommend using default memory settings OR is it good to allocate memory mnually like this?
Thanks
June 9, 2011 at 10:57 am
Again, it depends, but I believe the blanket recommendation is to let SQL Server figure out memory allocation for itself unless you are having problems.
Too many people don't understand what they are doing when they fiddle with memory settings and usually make a bad situation worse, or a good situation bad.
June 9, 2011 at 11:09 am
If all depends, this will not help.
It's impossible for us now to get another sandbox for us to play with, no budget for that, the server hosts 4 vendor applciations related with network monitoring, ITSM, and quite a few sharepoint sites. This also hard to replicate on another server.
June 9, 2011 at 11:16 am
sqlfriends (6/9/2011)
It's impossible for us now to get another sandbox for us to play with, no budget for that, the server hosts 4 vendor applciations related with network monitoring, ITSM, and quite a few sharepoint sites. This also hard to replicate on another server.
And right there you are telling me that this is not a SQL Server only box. Those applications should be on another box (middle tier) and pointing to the databases on the SQL Server only box. The applications really should be removed from your server as they are taking up a lot of CPU and memory that SQL Server will need access to.
It doesn't matter if SQL Server has their databases in it, the fact that the apps themselves are running on your SQL Server box makes it a multi-function box and a serious risk to your production environment's availability.
This is definitely a case you need to make to your boss. And if you don't have a sandbox server to test on, you also need to be aware of the risk of making this change in production. Be ready for the worst and hope for the best.
June 9, 2011 at 11:19 am
Sorry, I meant it hosts databases for those applications and databases for sharepoints.
No application other than SQL server is installed on this server. All our SQL servers are designated box only for SQL servers.
June 9, 2011 at 11:24 am
Then consider your issue might be that you have too much database and not enough memory. Altering the memory allocation is unlikely to fix performance issues if the server is feeling memory pressure as it is. You're taking away memory that SQL Server may need.
Are you absolutely sure the issue is memory? Have you run a server side trace and PerfMon to verify that your bottlenecks are in the memory department?
If they are, how will decreasing the amount of memory available to SQL Server help you? Because that's what you're planning on doing.
June 9, 2011 at 11:46 am
Brandie Tarvin (6/9/2011)
Then consider your issue might be that you have too much database and not enough memory. Altering the memory allocation is unlikely to fix performance issues if the server is feeling memory pressure as it is. You're taking away memory that SQL Server may need.Are you absolutely sure the issue is memory? Have you run a server side trace and PerfMon to verify that your bottlenecks are in the memory department?
If they are, how will decreasing the amount of memory available to SQL Server help you? Because that's what you're planning on doing.
I would agree with that.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 10, 2011 at 2:53 pm
Brandie Tarvin (6/9/2011)
Again, it depends, but I believe the blanket recommendation is to let SQL Server figure out memory allocation for itself unless you are having problems.Too many people don't understand what they are doing when they fiddle with memory settings and usually make a bad situation worse, or a good situation bad.
You have to be careful with this blanket statement. On x86 hardware - that was valid, but on x64 hardware it really isn't valid anymore. If you are on x64 with only 4GB of memory and SQL Server needs more memory - it could take it all and starve the OS.
Now, the above statement is dependent on whether or not you have set locked pages in memory right. If not, then SQL Server can take all the memory - but when the OS asks for it back SQL Server will try to give it back. This may not happen fast enough though. With the locked pages in memory right set, SQL Server will not give the memory back and you are going to starve the OS (and any other processes).
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 10, 2011 at 3:31 pm
Thank you.
I'm a little confused again. All our servers are 64 bits. And sometimes I heard from the forum microsoft recommend not changing the default settings for memory, and let SQL server dynamically adjust it. And sometimes I heard for 64 bits, in most cases we need to change the default memory settings to a max size...
So everytime I cannot remember when I'm going to do so, I don't know which one is the right way to go?
I hope there are some microsoft article about it.
And in the last post, how can I set lock memory right?
Thanks
June 11, 2011 at 11:05 am
I really don't know where these articles are that recommend letting SQL Server manage the memory. Anyways, here is what I have found to work on x64 hardware:
With lock pages in memory right (gpedit.msc | local policies ...): Always set a max memory, min memory is optional depending on what else is on the server.
Without lock pages in memory right: let SQL manage for smaller systems, larger systems (more than 4GB of memory) always set max memory.
Basically, since the only time I have found that I can rely on SQL Server managing it is for small systems with low overhead - I just set it always.
For a dedicated system, don't set min memory - no need. For a system where you are going to run other processes/applications - make sure you set lock pages in memory, max memory - and possibly min memory (usually not needed because memory is locked by SQL Server).
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 15 posts - 1 through 15 (of 31 total)
You must be logged in to reply to this topic. Login to reply