I have SSIS and SQL Server on the same machine and the SQL Server proces (SQLSERVR.EXE) is taking a lot of memory. How can I limit the memory usage of SQL Server so that there is more left for SSIS?
Solution
SQL Server is a bit greedy when it comes to memory usage. It will take what's there for optimal SQL Server performance, but sharing(releasing) it isn't it's best quality. Luckily you can limit SQL Server. The screenshots are from 2012, but it works the same with 2005 and 2008.
1) Open SSMS
Open SQL Server Management Studio (SSMS) and connect to your server. Right click the server and choose Properties. A new window will open with Server Properties.
 |
| SQL Server Properties |
2) Memory
Select the Memory page and check the value of the "Maximum server memory (in MB)" field. It has a has a gigantic limit of 2,147,483,647MB.
 |
| 2,147,483,647MB |
3) Limit Memory
Now you can limit this amount. My machine has 8GB, so I have limited SQL Server to 3GB: 3 * 1024 = 3072MB. Click OK and you will see that SQL Server will gradually release memory until it reaches your limit (or restart the SQL Server Service).
 |
| Limited to 3GB |
Note: be a little careful changing settings on production servers. Misuse may cause damage...
No comments:
Post a Comment
Please use the SSIS MSDN forum for general SSIS questions that are not about this post. I'm a regular reader of that forum and will gladly answer those questions over there.
All comments are moderated manually to prevent spam.