Sql-server – SQL Server 2005 and 2008 Memory Configuration Recommendations

It was most likely caused by a query wanting to read more pages into the buffer pool, and the buffer pool grabbing more memory to accomodate that. This is how SQL Server is supposed to work. If the box experiences memory pressure, it will ask SQL Server to give up some memory, which it will do. The customer shouldn't be concerned.

You can use the DMV sys.dm_os_buffer_descriptors to see how much of the buffer pool memory is being used by which database. This snippet will tell you how many clean and dirty (modified since last checkpoint or read from disk) pages from each database are in the buffer pool. You can modify further.

SELECT
   (CASE WHEN ([is_modified] = 1) THEN 'Dirty' ELSE 'Clean' END) AS 'Page State',
   (CASE WHEN ([database_id] = 32767) THEN 'Resource Database' ELSE DB_NAME (database_id) END) AS 'Database Name',
   COUNT (*) AS 'Page Count'
FROM sys.dm_os_buffer_descriptors
   GROUP BY [database_id], [is_modified]
   ORDER BY [database_id], [is_modified];
GO

I explain this a little more in this blog post Inside the Storage Engine: What's in the buffer pool?

You could also checkout KB 907877 (How to use the DBCC MEMORYSTATUS command to monitor memory usage on SQL Server 2005) which will give you an idea of the breakdown of the rest of SQL Server's memory usage (but not per-database).

Hope this helps!

we prefer to set the max and min memory settings on our database servers, reserving some memory for the OS of course. For a 16Gb box we would probably also set the max and the min to be 12Gb, but we have dedicated database servers and that is not always the case for everyone.

i believe it is best practice to set your memory as you have suggested, right out of the box after you do your install and initial configurations.