By default, SQL Server 2005 dynamically manages its memory consumption, which is of course the recommended configuration for SQL Server. When doing so, SQL Server monitors system memory availability and grabs more memory when it needs it as long as enough memory exists to prevent paging. Although this benefits SQL Server, it can hamper the performance of applications that must coexist on the same computer with SQL Server.
When running Team Foundation Server in a single-server deployment, you should consider controlling SQL Server’s memory appetite. You can do this by specifying a lower value for the SQL Server memory option, max server memory. Determining the optimal value (by Monitoring Memory Usage) would require some fine tuning, but I’m going to arbitrarily try using some values between 256 MB – 512 MB, or 25% of physical memory (whichever is higher).
When running Team Foundation Server in a dual-server deployment, you’ve already decided to place SQL Server on its own computer and should probably leave this setting alone (unless we end up using Full-Text Search).
For more information, see Server Memory Options and Optimizing Server Performance Using Memory Configuration Options in SQL Books Online. Another great resource is this SQL Server technical article, Troubleshooting Performance Problems in SQL Server 2005 (see the section on Memory Bottlenecks).