Memory contention between two databases?

Question

Post reply

Memory contention between two databases?

bbr

SSChasing Mays

Points: 613
More actions
March 18, 2010 at 6:50 am

#216687

Looking for ideas and opinions on a situation that I have.
I have two decent size databases (~500 GB each) running on the same SQL 2008 instance (64 bit Enterprise). Database A is a TPS system that sees activity every day except Sunday. Database B tends to have more batch type updates. Some of these updates can be very large and run for several hours. These large updates are mostly run when Database A is not in use.
The problem I am seeing is the next morning after a running a Database B process. Database A users will complain about how slow things are. After a few hours of use, Database A performance is right back to "normal".
My thinking is that the Database B process is simply kicking Database A out of memory. And once Database A gets used for a bit, it's back to where it was. Does this seem a reasonable theory?
The solution I am considering is to move each database to its own instance. This way they would have their own memory pool. My first choice is to run both instances on the same server. The server would have enough memory so that the combines SQL max memory would be less then server memory. Would this be a viable approach? Any advantages to having a the instances on separate servers? That would outweigh the additional cost?

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Steve Cullen SSCertifiable Points: 5598 More actions · Answer 1

What does your SQL error log say?

Converting oxygen into carbon dioxide, since 1955.

bbr SSChasing Mays Points: 613 More actions · Answer 2

Haven't looked (I will). Why would you expect anything in the log? Nothing is breaking, it's just slow for a while.

Steve Cullen SSCertifiable Points: 5598 More actions · Answer 3

You have a theory that you are running out of memory. Before you take any action, I would be certain that is the case. I don't see how splitting out to two instances will help a memory problem. You can use the resource governer to regulate what things use, preventing any stealing from Database A.

In any case, you should start with the error log to see if anything is logged during the time when everything is running slow. If you don't find anything in the logs, you should use performance monitor to collect memory related stats over the course of the slowdown and see if that supports your theory. You should check your SQL memory configuration. You should also look at processes that hog resources.

Converting oxygen into carbon dioxide, since 1955.

bbr SSChasing Mays Points: 613 More actions · Answer 4

My theory is not that I am running out of memory. It's that I am seeing the LRU behavior of cache. Stuff from database A gets kicked out because it's older. Separate instances would give each DB its own memory pool.

bbr SSChasing Mays Points: 613 More actions · Answer 5

I checked the SQL error log. The only entries during the 6 hours, that this process was running, are a handful of user login errors (bad PW type stuff).

bbr SSChasing Mays Points: 613 More actions · Answer 6

One Stat. that was pushing me toward this idea was the Buffer Cache Hit Ratio. It is generally very good (>99%). But after running a large process in Database B, it takes a sizable drop, but recovers after several hours of operations in Database A.

What else would be causing this behavior?

Carl Federl One Orange Chip Points: 25384 More actions · Answer 7

My thinking is that the Database B process is simply kicking Database A out of memory. And once Database A gets used for a bit, it's back to where it was. Does this seem a reasonable theory?

Yes, that seems to be a reasonable theory and you can prove the theory with the below queries on the data management views by running before and after the "Database B" process is executed.

It's that I am seeing the LRU behavior of cache

Starting with SQL Server 2005, buffers are managed by a "clock" and not by LRU/MRU. As each page is referenced, the page use counter is incremented. On a period basis, all page use counters are decreased.

Data buffer by database name:

SELECTDB_NAME( dm_os_buffer_descriptors.database_id ) as DB_Name

,COUNT(*)/128 AS Buffer_MB

,COUNT(*) AS Buffer_Cnt

FROMsys.dm_os_buffer_descriptors

group by dm_os_buffer_descriptors.database_id

ORDER BY Buffer_Cnt DESC

Data buffer by database name, object_name and index_id ( top 50 only )

IF OBJECT_ID('tempdb..#BufferObjects') is not null drop table #BufferObjects;

create table #BufferObjects

(DB_Namesysname

,Object_Namesysname

,Index_idinteger

, Buffer_MBinteger

, Buffer_Cntinteger

)

insert into#BufferObjects

execmaster.dbo.sp_msforeachdb @command1 = '

SELECTDB_NAME( dm_os_buffer_descriptors.database_id ) as DB_Name

,OBJECT_NAME ( partitions.object_id , dm_os_buffer_descriptors.database_id ) as Object_Name

,partitions.index_id

,COUNT(*)/128 AS Buffer_MB

,COUNT(*) AS Buffer_Cnt

FROMsys.dm_os_buffer_descriptors

JOINsys.allocation_units

ON allocation_units.allocation_unit_id = dm_os_buffer_descriptors.allocation_unit_id

JOINsys.partitions

ON allocation_units.container_id = partitions.hobt_id

WHEREdm_os_buffer_descriptors.database_id = db_id()

GROUP BY dm_os_buffer_descriptors.database_id

, partitions.object_id

, partitions.index_id

'

select top 50 *

from#BufferObjects

ORDER BY Buffer_Cnt DESC

SQL = Scarcely Qualifies as a Language

bbr SSChasing Mays Points: 613 More actions · Answer 8

Carl,

Thanks for the reply. I'll get a chance to test this on Sunday. Provided that the results support my thoery, will moving each DB to its own instance (on same server) address the problem?

Carl Federl One Orange Chip Points: 25384 More actions · Answer 9

Provided that the results support my thoery, will moving each DB to its own instance (on same server) address the problem?

It depend (i hate that answer).

Let us call the two databases OLTP and batch:

As you will need to allocate the physical memory to each of the two SQL Servers, the amount of memory available to OLTP will be less than currently allocated and you may find that the cache hit ratio will decrease significantly, resulting in more physical IO, and a decrease in performance.

An alternative solution is that after the batch process completes, clear the data buffers and then repopulate with the data/indexes for the OLTP database by running some select statements. Which select statments will be based on what objects (really the indexes) are needed in memory for the OLTP database.

-- Flush dirty pages to disk.

Checkpoint;

-- clear the data cache

DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS ;

-- Force indexes into memory and skip any rows that are locked

select count(*) from oltp_table with (index(#) , READPAST ) ;

-- Force tables into memory that do not have a clustered index

select count(*) from oltp_table with (index(0) , READPAST ) ;

You may find that some tables/indexes consume a large proportion of the cache but are a small percentage of all possible pages for the objects, so one should not attempt to populate the cache with these tables.

SQL = Scarcely Qualifies as a Language

bbr SSChasing Mays Points: 613 More actions · Answer 10

Adding the second instance would also include adding memory. That way both instances would have the same memory the current one has now.

bbr SSChasing Mays Points: 613 More actions · Answer 11

I ran the queries suggested by Carl. They appear to support my thinking. After running the process, Database B occuppies a large percentage of the buffers. I will likely move forward with installing a second SQL instance. I will be adding memory to the server as part of this process.

Thanks for all the input. Any thoughts?