How to buffer more data for longer time

Question

Post reply

How to buffer more data for longer time

Bill Talada

SSChampion

Points: 11954
More actions
January 8, 2015 at 9:18 am

#313494

I have a customer with awful disk throughput but lots of memory on the host. I understand data pages are aged out of cache after five minutes. His database is only a bit larger than memory. If I run dbcc dropcleanbuffers (to mimic 5 minutes of inactivity) and run a proc, it takes a minute to fetch a couple gigs of data at 25MB/sec. Running the proc a second time takes 2 seconds to compute because all data is in cache.
I've set sql server to a higher minimum memory setting but doubt it will help. I've heard of "preheating data cache" but don't think I can do it in this case. Would setting recovery time really high help keep dirty pages around at least? Any suggestions?

Viewing 5 posts - 1 through 4 (of 4 total)

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

tcronin 95651 SSCertifiable Points: 6735 More actions · Answer 1

tcronin 95651

SSCertifiable

Points: 6735

January 9, 2015 at 3:26 pm

#1769211

what is the PLE like?

sqlsurfing SSCarpal Tunnel Points: 4883 More actions · Answer 2

Has 'locked pages' in memory configured for this server?

If there are non sql or non buffer pool activity that require more memory it might be clearing it earlier too?

--------------------------------------------------
...0.05 points per day since registration... slowly crawl up to 1 pt per day hopefully 😀

Jeff Moden SSC Guru Points: 1003888 More actions · Answer 3

Bill Talada (1/8/2015)
I have a customer with awful disk throughput but lots of memory on the host. I understand data pages are aged out of cache after five minutes. His database is only a bit larger than memory. If I run dbcc dropcleanbuffers (to mimic 5 minutes of inactivity) and run a proc, it takes a minute to fetch a couple gigs of data at 25MB/sec. Running the proc a second time takes 2 seconds to compute because all data is in cache.
I've set sql server to a higher minimum memory setting but doubt it will help. I've heard of "preheating data cache" but don't think I can do it in this case. Would setting recovery time really high help keep dirty pages around at least? Any suggestions?

I've not known there to be a hard limit on how long something stays in cache, Bill. In theory, if (for example) 50% of the database is "active" and it has been loaded into memory, then it should stay loaded until something drives it out of memory.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Bill Talada SSChampion Points: 11954 More actions · Answer 4

Thank you all for the helpful pointers. I'm shocked! It turns out they are running Windows Server 2003 32 bit and no one ever configured AWE, PAE, Locked pages, etc. Windows never gave more than 2 GB to sql server 2005 so it ignored my min server memory setting. The Page Life Expectancy was always under 200 sec. They never used 12 of the 16 GB on the server.

I was misinformed about the 5 minute aging. Turns out that is an old rule of thumb of at least how long a page should last.