SQL Server limitation..
One thing I recently learned is that SQL Server is designed to take as much memory (eventually) as available to it which is 2GB max for sql2000 std edition.
I ran some test and shared results with one of our clients and they noticed that all the test we ran the DB server memory stayed at 2GB start to finish with very minor differences (looking at global OS counter memory commited etc).
My question is that are we hitting the SQL memory usuage limitation which is suppose to be 2GB, or how do we find out if all the memory SQL has aquired is actually is in use. When I look at sql memory counters ()
Memory Grants Pending: The current # of process waiting for a workspace memory grant.
Target Server Memory (KB): The total amount of memory that SQL Server can consume. (shows 1.6GB)
Total Server Memory (KB): The total amount of memory that SQL server is using.(reaches to 1.6GB in 5 minutes)
Memory Commited bytes (reaches 2 GB in 5 minutes)
Do we have a problem?
Environment: We are using SQL2000 Std Edition. OS MS Windows Server 2003 Standard Edition with 4 GB Ram.
Re: SQL Server limitation..
SQLServer can be configured to run with fixed amount of mem - min and max. By default, it is set to grow dynamically.
For mem, watch this SQLSrv counter:
Memory\Available Pct. & MB