Your current problem is definitely a resource issue.
The minimum spec for a Biztalk Server 2006 is 1GB RAM, if you are also running SQL Server 2005 then you will need another 512MB to 1GB RAM.
I would increase the memory to the minimum spec and retest, you may find that it will handle 10 simultaneous message OK but fail at 20. You can then size the server to handle this, throw lots of hardware at it and/or tune Biztalk Server.
e.g. Limit the batch size on the File adapter, look at the high and low water marks, etc
You should also check out the stored procedure – use SQL Server Profiler.
Any inefficiencies here will have an impact on the performance.
Do you know why the stored proc functoid is failing, is it a timeout, locking …