Execute the following script to display buffer memory usage by database. SQL Server 2005 stores its data and indexes in 8KB data pages. The cached data pages also stored in 8KB pages in buffer memory. A list of all the data pages in memory can be viewed by querying the dynamic management view sys.dm_os_buffer_descriptors.

USE master;

SELECT   [Database] = isnull(db_name(database_id),'Resource DB'),

         BufferedPages = count(* ),

         BufferedKB = count(* ) * 8192 / 1024,

         FreeKB = sum(free_space_in_bytes) / 1024

FROM     sys.dm_os_buffer_descriptors

GROUP BY db_name(database_id)

ORDER BY BufferedPages DESC;

GO

/*  Database            BufferedPages     BufferedKB  FreeKB

Resource DB             3789              30312       8620

AdventureWorks          1322              10576       2053

AdventureWorks2008      888               7104        2494

..... */

------------

-- SQL Server memory check

DBCC memorystatus

/* Memory Manager       KB

VM Reserved             14794876

VM Committed            95268

Locked Pages Allocated  180224

Reserved Memory         1024

Reserved Memory In Use  0

..... */

------------

Related articles:

How to adjust memory usage by using configuration options in SQL Server

Server Memory Options

How to use the DBCC MEMORYSTATUS command to monitor memory usage on SQL Server 2005