March 14, 2002 at 1:16 am
Hi all,
Is it just my server, or am I missing a point here.
SQL Server 7.0 Ent Edition on NT4.0 SP6 running only one 5GB database.
About 30 fat clients connecting to it via the application. It also has about 20 connections (concurrent) coming via our web users.
The sqlserver.exe uses upto 868MB out of the 1GBmemory on the server. The server has "dynamically configure sql server memory" turned on. After a reboot, sqlserver.exe starts with 38MB of mem and slowly starts to grow until it takes up almost 90% of the physical mem that is available on the server.
Please let me have your opinions
regards
Uday
March 14, 2002 at 4:34 am
38 is the average load space SQL takes up, what happens is as queries run it starts caching data and plans so that subsequent runs can take advantage of previous runs (this is why the memory usage expands) and complete faster. What then happens is as the plans and data are not used often and the space in memory is taking over to the fullest possible (leaving room for the OS and other apps to run) as a new query runs the oldest least used data and plan is flushed. If you look at the VM usage you will also find that SQL has taken a large chunk of it as well in addition to the physical memory. Dynamic just allows it to start small and expand as needed (it pretty much always never releases memory back to the OS unless the OS has a specific demand then it should start flushing the memory cache to accomadate).
Hops this helps.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
March 14, 2002 at 5:22 am
Yeah, I'm afraid that SQL Server will negotiate with the OS and grab whatever memory it can. I generally set a max to that though in the server properties. With a 1GB RAM SQL Server, you can expect to see it take all but about 100 MB in some cases. Always check to make sure the app is explicitly closing connections also.
Brian Knight
http://www.sqlservercentral.com/columnists/bknight
Brian Knight
Free SQL Server Training Webinars
March 14, 2002 at 10:16 am
Are you having issues? If not, this is ok. The more memory SQL takes, the more data that is cached and the faster queries run.
I have 2GB in my server, but only about 800MB in databases. I do have a bunch of DTS and other stuff happening, but SQL takes 1.7GB of mem and I have no issues. Performance is great on this box.
Steve Jones
March 18, 2002 at 1:18 pm
try having the users shut down their query analyzers once their queries are run
that is a great help too.
lot of users, do not use the (nolock) options
Paresh Motiwala Manager of Data Team, Big Data Enthusiast, ex DBA
March 18, 2002 at 2:14 pm
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply