SQL Server Performance Forum – Threads Archive

Memory Configuration and SQL Agent

Hi SQL Folks, I am working on a server that has plenty of memory (16-gb). In the SQL 2000 Enterprise Manager all the memory is dynamically assigned to SQL. This db has several SQL Agent jobs that get execute frequently. As a best practice should I configure SQL to only take 85% or so of the memory and leave plenty free for the OS and SQL Agent process (sqlagent.exe)? Thanks!

In my oppinion leave memory dinamically to SQL.
Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

SQLAGent deals with the scheduled jobs and the resource for this process will be utilised from SQLServr process. As suggested leave the settings dynamic and take advantage of AWE. If the jobs are running slow then check the referred stored procedure execution plan and maintain good indexes. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

If you have over 8 GB of RAM, there are a couple considerations. 1. Use the /3GB and /PAE switches in the boot.ini file.
2. Make sure awe enabled is set to 1 with sp_configure. I would personally recommend on a dedicated SQL Server box with that much RAM to use a set memory size leaving 1.5GB free, which disagrees a little bit from what the others are saying. There is no reason to leave memory for SQL Agent. It is managed in the SQL Server processing space and SQL Server does a fine job managing the memory for it. It uses the same memory space assigned to the overall SQL Server. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

Thanks everyone for the help! The Boot.ini file is currently configured without the /3GB or /PAE switches and awe enabled is set to 0. I found this article on this site: http://www.sql-server-performance.com/awe_memory.asp It refers to Windows 2000…this is a Windows Server 2003 Enterprise server. Is it just an outdated article and is the same for W2K3? Thanks,
~Fish

I was wrong on my posting. I apologize. The article is correct. I am correcting my post as well. I would still recommend a set memory size on a dedicated SQL Server box, especially with that much RAM. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

]]>