Are You Making This Common RAM Mistake?

RAM is the secret sauce in SQL Server performance: the more RAM, the faster the performance. In this post, I'll discuss how RAM is allocated for the data cache in SQL Server.

SQL Server fulfills a request for data only from RAM, never streaming off the hard drive. If a request requires data that is not in RAM, SQL Server pages the data from the disk and puts it into RAM.

If you have more RAM than databases, the entire database will cache into RAM eventually, and adding more RAM will not increase performance. Yes, it is possible to have more than enough RAM. However, most of us run databases that are much bigger than the RAM that is available.

RAM and Databases

Do you want to know how much RAM each database is using for the data cache? You can determine this by using the sys.dm_os_buffer_descriptors dynamic view. Each cached data page has one buffer descriptor (i.e., one row in the view). Buffer descriptors uniquely identify each data page that is currently cached in an instance of SQL Server. The sys.dm_os_buffer_descriptors dynamic view returns a row for every cached page for all user and system databases.

Here is a query that will help you visualize the size of the data cache for every database:

SELECT count(1) * 8/1024 AS cache_size_mb
   ,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 cache_size_mb DESC;

Read more: Siemens Teamcenter on SQL Server

Posted via email from jasper22's posterous