Having a SQL 2008 R2 Database in Memory

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Having a SQL 2008 R2 Database in Memory

28 posts, Page 1 of 3

1 2 3 »»»

Having a SQL 2008 R2 Database in Memory

Rate Topic

Display Mode

Topic Options

Author

Message

rmudway

Posted Monday, November 05, 2012 5:43 AM

Grasshopper

Group: General Forum Members
Last Login: Monday, May 13, 2013 8:40 PM
Points: 13, Visits: 44

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.

Post #1381027

sanket kokane

Posted Monday, November 05, 2012 5:53 AM

Old Hand

Group: General Forum Members
Last Login: Yesterday @ 10:50 PM
Points: 323, Visits: 962

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

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

Post #1381033

sanket kokane

Posted Monday, November 05, 2012 6:06 AM

Old Hand

Group: General Forum Members
Last Login: Yesterday @ 10:50 PM
Points: 323, Visits: 962

Sorry,this is with proper code format Smile

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);

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

Post #1381039

rmudway

Posted Monday, November 05, 2012 6:20 AM

Grasshopper

Group: General Forum Members
Last Login: Monday, May 13, 2013 8:40 PM
Points: 13, Visits: 44

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 BigGrin

Thanks.

Rich.

Post #1381042

sanket kokane

Posted Monday, November 05, 2012 6:29 AM

Old Hand

Group: General Forum Members
Last Login: Yesterday @ 10:50 PM
Points: 323, Visits: 962

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 .

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

Post #1381047

sanket kokane

Posted Monday, November 05, 2012 6:44 AM

Old Hand

Group: General Forum Members
Last Login: Yesterday @ 10:50 PM
Points: 323, Visits: 962

Also consider this as your very last options.

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

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

Post #1381053

Koen Verbeeck

Posted Monday, November 05, 2012 6:58 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 9:06 AM
Points: 9,367, Visits: 6,465

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

How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence

Post #1381060

CapnHector

Posted Monday, November 05, 2012 12:12 PM

SSC Eights!

Group: General Forum Members
Last Login: Friday, May 17, 2013 10:07 AM
Points: 935, Visits: 1,709

rmudway (11/5/2012)

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 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

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

Jeremy Oursler

Post #1381231

rmudway

Posted Monday, November 05, 2012 8:01 PM

Grasshopper

Group: General Forum Members
Last Login: Monday, May 13, 2013 8:40 PM
Points: 13, Visits: 44

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.

Post #1381346

GilaMonster

Posted Monday, November 05, 2012 8:31 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 5:49 PM
Points: 37,671, Visits: 29,925

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 2008, MVP
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

Post #1381350

« Prev Topic | Next Topic »

28 posts, Page 1 of 3

1 2 3 »»»

Permissions