On a Windows Server 2003 server (RTM, SP1, SP2, R2) you may find that alot of memory is used straight out of the box.
I have found SQLSERVR processes to be the culprit at both of these sites, with some instances using up to 500Mb to 2Gb
Server 2003 uses instances of MSDE for a few of its core tasks:
None of these instances require so much memory! I have no problem with a SQL / MSDE instance using memory when it is for a LOB application, but for maintenance, reporting, and logging?!
To limit the amount of memory each instance can use:
1. Determine which instances you wish to limit. I do this by finding the process ID using the built in Task Manager, then determining what instance is running under that process ID with Sysinternals Process Explorer.
2. Launch a command prompt
3. Start the SQL prompt, connecting to the desired instance (e.g. SBSMONITORING)
osql -E -S SERVERNAME
4. Execute the following commands to enable setting of advance options:
USE master
EXEC sp_configure 'show advanced options',1
RECONFIGURE WITH OVERRIDE
GO
5. Execute the following commands to set the maximum memory in MB. Replace 100 with your desired setting (I use 1024MB):
USE master
EXEC sp_configure 'max server memory (MB)',1024
RECONFIGURE WITH OVERRIDE
GO
6. Execute the following commands to disable advanced settings, for safety’s sake:
USE master
EXEC sp_configure 'show advanced options',0
RECONFIGURE WITH OVERRIDE
GO
7. quit
Within about 30 seconds the instance should have dropped it’s memory usage to within about 150mb of your desired setting.
Changing this actually changes the size of the dataset that is cached in to memory, there is still other overhead that means your active memory usage may still be around 1.2Gb
I have found SQLSERVR processes to be the culprit at both of these sites, with some instances using up to 500Mb to 2Gb
Server 2003 uses instances of MSDE for a few of its core tasks:
- WSUS (For centrally managing and reporting on the Windows Update process to Small Business Clients)
- MSFW (For the ISA Server 2000 / 2004 instance shipped with SBS Premium)
- SBSMONITORING (For the inbuilt performance and usage reporting)
None of these instances require so much memory! I have no problem with a SQL / MSDE instance using memory when it is for a LOB application, but for maintenance, reporting, and logging?!
To limit the amount of memory each instance can use:
1. Determine which instances you wish to limit. I do this by finding the process ID using the built in Task Manager, then determining what instance is running under that process ID with Sysinternals Process Explorer.
2. Launch a command prompt
3. Start the SQL prompt, connecting to the desired instance (e.g. SBSMONITORING)
osql -E -S SERVERNAME
4. Execute the following commands to enable setting of advance options:
USE master
EXEC sp_configure 'show advanced options',1
RECONFIGURE WITH OVERRIDE
GO
5. Execute the following commands to set the maximum memory in MB. Replace 100 with your desired setting (I use 1024MB):
USE master
EXEC sp_configure 'max server memory (MB)',1024
RECONFIGURE WITH OVERRIDE
GO
6. Execute the following commands to disable advanced settings, for safety’s sake:
USE master
EXEC sp_configure 'show advanced options',0
RECONFIGURE WITH OVERRIDE
GO
7. quit
Within about 30 seconds the instance should have dropped it’s memory usage to within about 150mb of your desired setting.
Changing this actually changes the size of the dataset that is cached in to memory, there is still other overhead that means your active memory usage may still be around 1.2Gb
Tags
internet