May 2, 2004 at 7:59 pm
Hi,
There is no other backend application is running. Once i run the store procedures the PF Usage memory will increase from 140 MB to 431 MB. I used the command DBCC FREEPROCCACHE to free all cache for all the databases in the SQL server. It doesn't help. The PF Usage memory still remain there (will increase if executed others procedures) but not decrease. As a result, the available physical memory will become lower and lower. It will hurt the performance. If I restart the SQL server it can get back to the original stage (I means 140 MB). I can't do this due to the SQL server is in the production. How am I going to solve this issue? Please advice. Thanks.
lss
May 3, 2004 at 10:30 pm
SQL Server by design uses as much memory as it can get subject to other demands on the server. If there are no other processes requiring memory, there is no real need to release it.
This has the benefit that SQL Server can cache data which can be used by other queries which means that there is a significantly reduced requirement to do any disk IO. If the data you need is already in memory, it is available to you much faster i.e. performance is significantly better.
As it happens, DBCC FREEPROCCACHE is not likely to release much memory any way. All this will do is clear the procedure cache which is most likely relatively small in comparison to the data cache. If you really want to get dat pages out of memory, use DBCC DROPCLEANBUFFERS. However, I strongly recommend that you don't do this. All you will end up doing is slowing SQL Server down.
Using the page file is not necessarilly a bad thing. Having a lot of page faults is though. This would indicate that you don't have enough RAM. I have seen the odd occassion when SQL Server uses too much memory resulting not quite enought memory for Windows (that was SQL 7/NT4). If you experience this, you could consider limiting the maximum amount RAM that SQL is allowed to use.
Have a look at topics "Server Memory Options", "Monitoring Memory Usage" and "I/O Architecture" in SQL Books On Line for a little more information.
Cheers
Stephen
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply