Having a SQL 2008 R2 Database in Memory

Question

Post reply

Having a SQL 2008 R2 Database in Memory

rmudway

SSC-Addicted

Points: 481
More actions
November 5, 2012 at 5:43 am

#284153

Hi All,
I have looked around and not found enough to answer my questions. And under some pressure to review this topic. So im wondering if someone could help me understand a few blanks...
What i found out is that it seems that i have no control over putting my Database into RAM, and that SQL decides this on its own. My Database is 4 GBs and i have 8GB of RAM. As the Database is holding realtime data thus having a lot of activity, could I assume that SQL has already promoted the Database into RAM already?
If so, how do i know if its already in RAM?
Thanks,
Rich.

Viewing 15 posts - 1 through 15 (of 27 total)

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

sanket kokane Hall of Fame Points: 3465 More actions · Answer 1

this might help you

^{SELECT DB_NAME(database_id) AS [Database Name],}

COUNT(*) * 8/1024.0 AS [Cached Size (MB)]

FROM sys.dm_os_buffer_descriptors

WHERE database_id > 4 -- system databases

AND database_id <> 32767 -- ResourceDB

GROUP BY DB_NAME(database_id)

ORDER BY [Cached Size (MB)] DESC OPTION (RECOMPILE);

Query by Glenn Berry

-----------------------------------------------------------------------------
संकेत कोकणे

sanket kokane Hall of Fame Points: 3465 More actions · Answer 2

Sorry,this is with proper code format 🙂

SELECT DB_NAME(database_id) AS [Database Name],

COUNT(*) * 8/1024.0 AS [Cached Size (MB)]

FROM sys.dm_os_buffer_descriptors

WHERE database_id > 4 -- system databases

AND database_id <> 32767 -- ResourceDB

GROUP BY DB_NAME(database_id)

ORDER BY [Cached Size (MB)] DESC OPTION (RECOMPILE);

-----------------------------------------------------------------------------
संकेत कोकणे

rmudway SSC-Addicted Points: 481 More actions · Answer 3

Hi sanket!!

Thanks for your response, that was a nice little piece of info. I found that my test machine had the whole DB in RAM, but the Main SQL Server only had half of its Database in RAM.

What can i do to speed up the Database if it is already in Mem, its holding realtime info and i see that it can get 100Msg/sec, any suggestions?

I have inherited this database and need to try and get it to a good place 😀

Thanks.

Rich.

sanket kokane Hall of Fame Points: 3465 More actions · Answer 4

Is your production machine 32- bit ?

if yes ,you need to add /PAE option in your boot.ini file .

http://support.microsoft.com/kb/283037

But I will recommend before doing this discuss with your system admin .

Because making change in boot.ini file is risk .

-----------------------------------------------------------------------------
संकेत कोकणे

sanket kokane Hall of Fame Points: 3465 More actions · Answer 5

Also consider this as your very last options.

To speed up your DB check,first check if there any scope for Query Tuning.

-----------------------------------------------------------------------------
संकेत कोकणे

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 6

sanket kokane (11/5/2012)
Also consider this as your very last options.
To speed up your DB check,first check if there any scope for Query Tuning.

I wouldn't consider this the last option. Of course, query tuning is important, I won't deny that.

In the session "Building the fastest SQL Servers", Brent Ozar - which we can consider a performance guru - had this piece of advice for OLTP systems: "add enough RAM so that your DB fits into memory".

Original video can be watched here:

http://channel9.msdn.com/Events/TechEd/NorthAmerica/2012/DBI328

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

CapnHector SSCoach Points: 16143 More actions · Answer 7

rmudway (11/5/2012)
Hi sanket!!
Thanks for your response, that was a nice little piece of info. I found that my test machine had the whole DB in RAM, but the Main SQL Server only had half of its Database in RAM.
What can i do to speed up the Database if it is already in Mem, its holding realtime info and i see that it can get 100Msg/sec, any suggestions?
I have inherited this database and need to try and get it to a good place 😀
Thanks.
Rich.

if you have the OS setup to use all 8 gig of ram (either 64bit os or something else) Sql Server will read pages into memory from disk as it needs them. SQL Server will only flush the cache if the server comes under memory pressure and needs to reclaim some from sql server. otherwise the information just sits in the buffer cache. along with how much of your database is in memory also look at the page life expectancy. In our production environment we have around 100 gigs of data and 32 gig of ram on our server, however we only use a small portion as there is allot of historical logging tables that are not accessed very often. because of that our "Active" portion of our database fist very comfortably in memory and our page life expectancy sits at several hours. (an overnight maintenance on indexes uses all of that ram and then our page life expectancy just rises through out the day.)

all that is to say that just because the entire DB is not in memory does not mean something is wrong. If SQL Server never needs half the data (or has not needed half the data since the last time the buffer cache was flushed) its perfectly normal behavior to only have half the DB in memory. As long as you have the memory there so that if SQL Server needs more memory its there and ready so its only a read from disk and not a flush of memory then a read from disk your good to go.

as far as what to do once it all is in memory that would be performance tuning which is an entirely deep and broad subject.

For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]

rmudway SSC-Addicted Points: 481 More actions · Answer 8

Hi sanket,

The Machine is 64-bit, so i have not done this setting.

The OS is Win 2008 R2 Ent and my Database is running on SQL 2008 R2 STD.

Thanks.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

Performance tuning should be done first, not last. Well performing queries are generally more efficient in memory usage.

Database being entirely in memory doesn't automatically mean the queries will be fast.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

rmudway SSC-Addicted Points: 481 More actions · Answer 10

Koen Verbeeck (11/5/2012)
sanket kokane (11/5/2012)
Also consider this as your very last options.
To speed up your DB check,first check if there any scope for Query Tuning.
I wouldn't consider this the last option. Of course, query tuning is important, I won't deny that.
In the session "Building the fastest SQL Servers", Brent Ozar - which we can consider a performance guru - had this piece of advice for OLTP systems: "add enough RAM so that your DB fits into memory".
Original video can be watched here:
http://channel9.msdn.com/Events/TechEd/NorthAmerica/2012/DBI328

Hi Koen,

Thanks for the link, i will go and check this out.

Apart from performance tuning i think the Server may be suffering from not being setup properly such as the hardware. It is all on a single hard disk including the OS.

rmudway SSC-Addicted Points: 481 More actions · Answer 11

I also had some advice from someone who said that for me to get over a performance issue with my Database, i could use a 2nd Database which would have Realtime data only. Leaving the historical information in the 1st Database which could grow.

He went on to say that this 2nd Database would remain small and SQL would likely keep this in RAM, as the activity would be constant.

Are there be any issues that i should be thinking about, if i go for this approach? Such as delay between accessing databases, configuration, etc?

Thanks.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 12

Doesn't sound right. If the historical data isn't queried, it won;t be in memory, doesn't matter if in one DB or two.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sanket kokane Hall of Fame Points: 3465 More actions · Answer 13

Koen Verbeeck (11/5/2012)
sanket kokane (11/5/2012)
Also consider this as your very last options.
To speed up your DB check,first check if there any scope for Query Tuning.
I wouldn't consider this the last option. Of course, query tuning is important, I won't deny that.
In the session "Building the fastest SQL Servers", Brent Ozar - which we can consider a performance guru - had this piece of advice for OLTP systems: "add enough RAM so that your DB fits into memory".
Original video can be watched here:
http://channel9.msdn.com/Events/TechEd/NorthAmerica/2012/DBI328

What I was mean to say is consider enabling /PAE as your last option.

and first check if there any scope for Query Tuning.

Of course I have bad experience with /PAE .

-----------------------------------------------------------------------------
संकेत कोकणे

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 14

sanket kokane (11/5/2012)
What I was mean to say is consider enabling /PAE as your last option.

If running SQL on 32-bit OS with more than 4GB of memory, /PAE is a requirement if that memory it to be used by SQL.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass