- where running a server 4GB of RAM
- set to use a min 2.5GB and max of 3.9GB
- AWE is not enable
-- the problem is that microsoft sql server 2000 is only using 1.6GB and is paging a lot
Also the server is dynamically managing memory the RAM at 2.5 GB to 3.9 GB|||ensure your /PAE and /3gb switches are correct. in SQL 2000 you could use DBCC PinTable to "pin" an entire table's worth of records into memory, yet this has been deprecated in 2005 for good reason the database engine is efficient enough to decide when pages should enter/remain/and exit buffer pool.
Derek
|||With 4GB of RAM, and without /3GB the largest buffer pool will grow to is ~1.6GB.
Paging is caused by external memory pressure - meaning SQL Server shares the box and other things need memory too. You should use perfmon to look at the Memory\available MB counter and ensure it's something reasonable, say somewhere larger than 20MB. Next thing is to look at Process\Private bytes counter for all processes on the machine. If SQL Server is the app you care most about then shutdown as many large memory consumers as you can.
Please don't use dbcc pintable. That will just guarantee that the same data get's paged out over and over again, and shrink the amount of buffer pool available for hotter pages. SQL 2000 did a fine job of determining what data pages belonged in memory, the decision to remove dbcc pintable rested more on the fact that it does more harm than good.
|||The /3GB switch is used to tell SQL Server to take advantage of 3GB out of the base 4GB of RAM that Windows 2000 supports natively. If you don't specify this option, then SQL Server will only take advantage of 2GB of the first 4GB of RAM in the server, essentially wasting 1GB of RAM.
Use AWE or the /3gb switch to get the memory support you desire via your min/max buffer pool settings.
Good articles I always references for sql memory config issues:
http://www.sql-server-performance.com/awe_memory.asp
Wait a second here.. I just promoted a competitor's site lol. these guys know their stuff regardless
Derek
|||If this is SQL Server 2000 Standard Edition, it won't matter. You can only address 2Gb of RAM with that edition.|||Michael is correct regarding sql 200 std. I forgot that the old std does not support AWE...GLAD 2005 DOES!