Hi, In our test server (with 4GB RAM) memory usage of sqlservr.exe is keep on increasing upto 2 GB's size without releasing the memory. I need to restart the service everytime to reset the memory to its normal settings. However in our production server (with 16 GB) memory usage is working properly. What could be the reason for this? How to resolve this permanently?
Yes it is with latest Service Pack.. I'm using Sql server 2008 standard edition in my test server.Microsoft SQL Server 2008 (SP1) - 10.0.2531.0
It is normal for SQL Server to slowly request memory from OS. Prior to SQL 2005, SQL Server never released the memory back to OS when there is memory pressuse. SQL 2005 and above do a much bette rjob of releasing memory back to the OS. You can also cap the memory for SQL Server if you need some memory for other processes.
There are around 50 application jobs running in th system, where most of the jobs will run once in a day. Jobs are completing quickly. There are no performance related issues in the individual jobs as they are scheduled in different time span and at off peak usage. Is there any limitation in number of jobs to be configured in sql server?
I'm not understanding what the problem is. SQL is designed to use memory and to avoid disk IO. You should look at setting the MAX amount of RAM that SQL can consume. Secondly, you want to avoid paging the memory to disk. This can be caused by not limiting the amount of memory SQL can use and the OS not having enough. When this occurs, you will have a hit on performance as SQL will need to go to disk more often.