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.
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;
This query returns a value for cache_size_mb and the database name for every database in the instance of SQL Server. On my test server, the results look like this:
The first thing that I evaluate when I run this query on a "new to me" instance of SQL Server is if my most important database is using the most RAM. The more RAM the database has allocated from the available RAM (assuming it is a restricted commodity), the faster the database will perform. If some nonessential databases are using RAM, I consider moving them.
Another technique is to take offline all databases that are not in use and only turn them on when they are in use. In essence, I am trying to get my main databases as much RAM as they can use.
All the databases on the server share the available RAM, and SQL Server manages what databases get RAM. There is no way to configure one database to use more or less RAM than another database on the same server. There is no way to say that one database has a higher priority than another database. All the databases are treated equally.
And that fact separates the SQL Server superheroes from the sidekicks.
A common problem that I see when I look at SQL Server configurations is that DBAs run the test databases on the same server as the production databases. They think this is not a problem because thousands of users are making thousands of queries against the production databases and only a handful of people are calling the test databases. They assume that the test databases will consume resources that are proportional to the users and queries that are calling them. This is true when you look at CPU resources, but when you consider the shared RAM situation, it is not true.
For example, a tester who is using a test database runs a query that selects all the rows on a table, and those rows are paged from the hard disk into RAM. This causes the data in the production database to be paged out of RAM and onto the hard disk. A split second later, the thousands of users who are making thousands of requests make queries, and the production data is reloaded from the hard disk and back into RAM. In this case, the tester is severely affecting the performance of the production queries even though the ratio of work is 1 to 1,000. You can witness this case by running the query above and by watching the shift of the database sizes.
RAM is a precise resource that affects performance and shouldn't be shared across databases that have different priorities.
Wayne Berry (6230289B-5BEE-409e-932A-2F01FA407A92) is a computer programmer and serial entrepreneur with a passion for dynamic Web sites that run on SQL Server. When he is not blogging for MSDN, you can find him blogging for Project 31-A. http://www.31a2ba2a-b718-11dc-8314-0800200c9a66.com