Summary
When you use adTempus with SQL Server or SQL Server Express, the amount of memory used by SQL Server (sqlservr.exe) can be quite large.
More Information
By default, SQL Server uses essentially as much physical memory as is available on the computer: as long as the computer still has enough physical memory to prevent paging, SQL Server will continue to increase its memory buffers. SQL Server monitors the memory requirements of other applications, and gives back memory to the operating system as other applications require it.
Essentially, SQL Server takes memory that would otherwise be "wasted" because no other application is using it. Thus it is normal for your server to have very little free physical memory. As other applications consume more memory, you will see the SQL Server memory usage decrease.
For more information on SQL Server's memory usage and memory architecture, see support.microsoft.com/default.aspx?scid=kb;en-us;321363 and msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_sa_4rc5.asp.
It is possible to reduce the maximum memory used by SQL Server. Complete information on this is available at msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_config_9zfy.asp; the procedure is described briefly below.
Limiting Memory Usage
IMPORTANT NOTE: Please understand that limiting memory usage is a "cosmetic" change only, because the high memory usage by SQL Server does not affect system performance: SQL Server gives back memory if other applications need it. The only effect this limit will have on your system is that your system will show more free physical memory. Free physical memory is not a measure of system performance or tuning. Please review the Microsoft documentation cited above if you have concerns on this subject.
If you are using SQL Server
If you are using SQL Server, or you have access to a copy of SQL Server Enterprise Manager that can connect to your MSDE installation, you can easily set the maximum memory usage using the settings in the Enterprise Manager. See msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_config_001l.asp for instructions.
If you are using SQL Server Express
If you are using SQL Server Express and/or don't have access to SQL Server Enterprise Manager, you can make the changes using the osql utility:
- Open a command prompt
- Run the following command:
osql -S .\adtempus -E - At the "1>" prompt, enter the following commands (pressing Enter after each line):
USE master
go
EXEC sp_configure 'show advanced option', '1'
go
RECONFIGURE
go
EXEC sp_configure 'max server memory', '512'
go
RECONFIGURE WITH OVERRIDE
go
quit - Stop the adTempus service and the MSSQL$ADTEMPUS service.
- Restart the MSSQL$ADTEMPUS and the adTempus service.
The commands above set the maximum memory usage for SQL Server to 512 MB. We recommend that the limit not be set below this value.
Status
This is normal behavior for SQL Server/SQL Server Express. See the More Information section for an explanation and for information on limiting the memory usage.
