I have an asp.net application based on DotNetNuke, and dataprovider is sql server. Just recently, I found out that once I access the web page the server system become very slow,  I cheched the windows task manager, sqlservr.exe  quickly increases its memory to more than 500,000 k.  Even though there is no any activity on the web site (except open the home page), I checked "Process Info" inside Sql Server Enterprise Manager, there are 8 new processes after I open the web page. The Database are all "DotNetNuke", and Status are all "sleeping". The application are all ".Net SqlClient Data Provider", all data access are SqlHelper method

What should I do to free the space? Otherwise, the system cannot run normally.

Thanks

SQL server will not allocate more memory than your system can handle. If it can't allocate the memory it wants for caching it will simply retrieve records from disk instead of from core. This will of course reduce system performance. This behavior is normal and any decent system (P3-1G+ should be able to handle the SQL load of a DNN site. Better machines give better performance, of course.

That said, perhaps the caching behavior of DNN can be changed to affect SQL server memory consumption but I doubt that.

Keep in mind that SQL Server is an *Enterprise* product that is not meant to scale on small machines. DNN is also not a small application, it's huge. It therefore also needs decent hardware to run well.

I knew the reason was not DotNetNuke. I believe I must have done something wrong, because at first, just after I downloaded DotNetNuke, and ran it. There was no memory problem. But after I wrote some modules myself. The memory problem came out even through I have not open my module page yet.

I also developed another .net solution/project based on DotNetNuke. There has never been the memory problem. I am sure I must have done something wrong. But the problem is I don't know how to check where the problems come from and how to fix them. I know almost nothing about how to tune and manage sql server although I have written thousands of stored procedures.

Thanks for your help. Could you give me some further tips?

If you have sys admin access to the SQL server you can set it to only take a limited amount of memory, so that other applications will still have some available.

Using Enterprise Manager right click on the DB server and go to Properties, then use the Memory tab to set a fixed amount (maybe 75% of total resources).

Unless you do this SQL server will always try to gobble up as much memory as it sees available, so I'm not saying it isn't but it might not be a problem with your modules.

SQL server has an internal optimisation engine that records it's workload and tries to intelligently retain resources to best serve itself. The engine is a little pessimistic, so typically retains more Ram than it usually needs, so that it can react to request spikes. If you periodially have some sql running that does more work than your usual day to day (e.g. running nightly OLAP data mining) , you'll find that the Ram hovers around 80-90% of the maximum that that request used to finish.

IIS has a similar mechanism, which is why Microsoft recommend not installing both on the same server, as they both compete for the same Ram. Whilst they're both good to giving up reserved ram upon request, the overhead of doing this can slow things down.

Cathal