Add more memory to Server?

Question

I have a Dell 2900 Server with 16 GB of memory running SQL Server 2005.

All 3 of the databases I have configured on the Server all have the maximum memory allocated to each Database. The primary database runs my production environment of our BAAN application, the other two databases run a development environment.

Besides reducing the amount of memory used in the other 2 Databases, is there a command I can issue to show the memory used by each database?

Answer 1

Try the query below:

SELECT (count(*)*0.0078125) AS 'Pages in Buffer pool'
    ,CASE database_id
        WHEN 32767 THEN 'ResourceDb'
        ELSE db_name(database_id)
        END AS Database_name
FROM sys.dm_os_buffer_descriptors
GROUP BY db_name(database_id) ,database_id
ORDER BY 1 DESC

This will show you the amount of space each DB has occupied in the buffer pool. Note that there are other factors in SQL that also use up the memory apart from the buffer pool.

Answer 2

I don't think there's a way to limit or show the amount of memory used by individual DBs in SQL Server. If you are having memory issues, I think what you're probably better off doing is to install a second instance of SQL on that server, use it to host the dev environments and limit it to a certain amount of memory (like a GB or two) in the server options.