The trick here is that if SQL Server is on the same machine as your website (more common that it should be, and far from best practice), then SQL Server and IIS will continually grapple for memory. However, unlike IIS, SQL Server doesn't like letting go of memory once it's got hold of it. Application pools in IIS can be configured to recycle/release memory periodically. Setting a fairly aggressive value here will help you keep your application pool sizes down, but won't deal with the SQL Server issue.
The solution is to cap the amount of memory SQL Server can claim by running a script such as the one posted by RickNZ above, which will cap the memory used by SQL Server at 2048MB (2GB) - i.e. half the memory on the server.
As mentioned by other respondents: MS SQL Server will (if left to own devices) automatically claim RAM to service its various caching requirements (data buffers, procedure cache, lazy writer queues, etc) up to around 95% of the available memory available to the O/S before it stops gradually inching its use forward. It's not a memory leak per se - SQL Server was designed to run by default as the primary application service on a server. Shared server scenarios are definitely a "By Configuration" situation. Every data query that pulls back a new set of pages from disk may drive caching of:
- Data Pages
- Index Pages
- Query Plans (possibly multiple per stored procedure if you have non-homogeneous data distributions for key data in your tables/indexes, and also plans for raw SQL queries)
If you want to minimize the cache footprint rather than tune the memory, you need to get your head around topics such as Parameter Sniffing, Query Plan Cacheing and using code objects such as functions and views to minimize the amount of data returned to your queries.
Hope that helps.
Ozziemedes