Issues with large solutions in Ms Dynamics CRM 2011.

We have a Ms Dynamics CRM 2011 in box Test environment, i.e. CRM and SQL on the same box, don't ask why, and our main solution is a good 10+ MB zipped up, which means that sometimes it takes a few attempts to get the solution imported.

As of late, my lack of commitment to scientific inquiry and betterment of the world continues to show as I really haven't tested this thoroughly enough, but here it goes anyway.

The problem seemed to be that the W3WP process was using almost all the available memory on the server, which resulted in timeouts when running various SQL queries, at least that's what the trace log said, it's hard to trust a log that seems surprised that the are no errors, but I digress. 

The solution was to set upper and lower limits of memory on SQL server, to be fair I think the problem was the lower limit, but it makes sense to limit memory usage at the high end as well, lest SQL server thinks all the memory it's for itself.

EXEC sys.sp_configure N'show advanced options', N'1'  RECONFIGURE WITH OVERRIDE

GO

EXEC sys.sp_configure N'min server memory (MB)', N'512'

GO

EXEC sys.sp_configure N'max server memory (MB)', N'2048'

GO

RECONFIGURE WITH OVERRIDE

GO

EXEC sys.sp_configure N'show advanced options', N'0'  RECONFIGURE WITH OVERRIDE

GO

For the record the server had 4 GB of RAM, which could well be the source of the issue in the first place, i.e. this might not happen in a server with 8 GB of RAM.

We've not had any of these issues on our OAT environment, which features separated CRM and SQL boxes, each with 8 GB of RAM, so hopefully setting limits to the memory used by SQL server was the solution to the problem.