June 22, 2010 at 9:59 am
Hello,
I've a SQL2005 SP3 CU9 64 bit installed on a Windows 2003 R2 64 bit Maschine. The Servers has 16 GB. I granted the Lock pages in Memory right to the SQLAccount.
I set min server memory = max Server memory = 13500MB
sp_configure 'min server memory', 13500 Reconfigure go sp_configure 'max server memory', 13500 Reconfigure go
But the Taskmanager and the Performance Counte only shows 140 MB an I've less than 640SQLServer:Buffer Manager\Free pages
Any Ideas?
June 22, 2010 at 10:22 am
SQL does not automatically assign min memory. That's only the figure that, once allocated, it will not drop below.
It is not a good idea to set min and max memory to the same figure. You're preventing SQL from releasing memory if the OS should need. That'll result in either SQL getting paged out (if lock pages is not set) or the OS starving (if lock pages is set)
Task manager does not show accurate memory usage for SQL often. Rather use perfmon and look at the total server memory counter.
How much memory is available on the server? (use perfmon)
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
June 22, 2010 at 12:03 pm
It was figured like that before, after I checked the PAL Reports I raised it up.
We got "Less than 640 Free Pages" on the SQL Buffermanager (about 130) and "Page life expectancy is less then 5 minutes".
All these where indicators for to less memory but I had 16 GB in the Server only running a SQL Server. The SQL Servere used about 150MB in maximum and there where about 14,5 GB available Memory (counted with the Performance Counter).
Alf
June 22, 2010 at 12:36 pm
Oldman@ds9 (6/22/2010)
The SQL Servere used about 150MB in maximum ...
What did you use to determine that?
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
June 22, 2010 at 12:43 pm
June 22, 2010 at 12:45 pm
Sorry, wrong answer......Total Server Memory and Task Manager....both nearly the same
June 22, 2010 at 2:58 pm
I agree with with Gail - do not set the min and max the same.
On my old server - which I was running both SSAS and SQL on - I set the SQL to 256 min and 3500 max.
I only needed the 3500 during nightly loads. During the day, SQL only consumed a couple hundred megs most of the time.
Search the site for x64 bit memory settings - you should be able to find a couple of good articles to get you started.
Greg E
June 22, 2010 at 5:03 pm
GilaMonster (6/22/2010)
It is not a good idea to set min and max memory to the same figure. You're preventing SQL from releasing memory if the OS should need. That'll result in either SQL getting paged out
Been there, done that... a few years ago Got lots of “A significant part of sql server process memory has been paged out” in the SQL Server error log. Lesson learned.
OP, trust me, you do not want SQL memory to be paged out to swap area.
June 23, 2010 at 1:39 am
OldMan,
I can second Gails and others comments, you should NOT set the min and max to the same. In your case you should set the max to 13500 (and to be honest thats sounds a little high) and the min should be left as default (think that about 100MB). Remember that Windows will start paging out when its used about 80% memory, so you are not leaving you OS much to play with. By having your min and max set to the same, there is a chance that in an OS preasure situation, the OS will starve and hang.
As far as what you see in Task Manager is concerned, thats not always conclusive. It may report that SQLSvr is using say 3GB but in reality this could be 6 or 7 due to CLR, Proc Cache and other buffer pools.
This thread that I have been working on has some useful scripts, read through do the tests and see how you go.
HTH 😉
Adam Zacks-------------------------------------------Be Nice, Or Leave
June 23, 2010 at 1:41 am
So, today I changed the min server memory and restartet the Server.
sp_configure 'min server memory', 6500
Reconfigure
go
sp_configure 'max server memory', 13500
Reconfigure
go
But it seams not ok:
SQL Buffermanager -> Free Pages = 120
SQL Memory Manager -> Total Memory (KB) = 28.416
SQL Memory Manager -> Target MeMory KB = 13.824.000
Memory -> Avalaible MB = 14.914
So 14,5 GB of Memory are waisted......
Any Ideas
June 23, 2010 at 1:46 am
You have to be patient. As SQL works and needs more, it will take more!
We have a cluster with 128GB and SQL max set to 96GB! However when the server is restarted the intial allocated pools and chaches will be tiny. The page life expectancy will be small as a result.
However as the CLR's run and the Proc Cache fills up, the SQL memory allocation will grow which will push the life expectancy and cache hit rates up!
Remember, Rome wasnt built in a day!
Adam Zacks-------------------------------------------Be Nice, Or Leave
June 23, 2010 at 3:12 am
Adam,
he're the results of ypur statement.
physical_memory_in_bytes
------------------------
17167736832
virtual_memory_in_bytes
-----------------------
8796092891136
Number of 8KB buffers in buffer pool
------------------------------------
12160
Number of 8KB buffers needed by the buffer pool
-----------------------------------------------
1728000
Status of Dynamic Memory
-----------------------------------------------
Extra memory needed from OS for Buffer Pool
Number of 8KB Buffers in Buffer Pool that are directly accessible in the processes VAS.
---------------------------------------------------------------------------------------
1728000
It looks like i've to extend the buffer pool? How Can I do this?
Alf
June 23, 2010 at 3:32 am
Just wait. Be patient.
How large is your database? Transaction level? If you have a low usage small db, then SQL wont use the available memory until it needs it. Do you use CLR are SSIS or have a high dependancies on USP's?
Adam Zacks-------------------------------------------Be Nice, Or Leave
June 23, 2010 at 3:42 am
I'll waiting 😎
The Database ist about 60 GB.
I don't user this....
June 23, 2010 at 7:28 am
You might want to do some reading on SQL Server and memory.
The BOL documentation that comes with SQL Server would be a good place to start.
Making sure you know what these setting do - like not assuming that when the service is restarted it will always consume the min memory - is important to being able to set things right.
One of the big things with x64 bit is avoiding the OS and SQL to fight for memory. There are posts and articles on this site that can give some general recommendations of where to start.
Hopefully you have a test environment you can play with, change some setttings, and put some load on it.
Greg E
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply