Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Having a SQL 2008 R2 Database in Memory Expand / Collapse
Author
Message
Posted Monday, November 05, 2012 5:43 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, October 07, 2013 6:25 AM
Points: 13, Visits: 46
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
Posted Monday, November 05, 2012 5:53 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, August 01, 2013 10:17 AM
Points: 323, Visits: 984
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
Posted Monday, November 05, 2012 6:06 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, August 01, 2013 10:17 AM
Points: 323, Visits: 984
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);



-----------------------------------------------------------------------------
संकेत कोकणे
Post #1381039
Posted Monday, November 05, 2012 6:20 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, October 07, 2013 6:25 AM
Points: 13, Visits: 46
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.

Post #1381042
Posted Monday, November 05, 2012 6:29 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, August 01, 2013 10:17 AM
Points: 323, Visits: 984
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
Posted Monday, November 05, 2012 6:44 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, August 01, 2013 10:17 AM
Points: 323, Visits: 984
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
Posted Monday, November 05, 2012 6:58 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:19 AM
Points: 12,205, Visits: 9,162
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
Posted Monday, November 05, 2012 12:12 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, March 27, 2014 5:29 AM
Points: 945, Visits: 1,760
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 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
Posted Monday, November 05, 2012 8:01 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, October 07, 2013 6:25 AM
Points: 13, Visits: 46
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
Posted Monday, November 05, 2012 8:31 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 11:47 AM
Points: 41,525, Visits: 34,442
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 »

Add to briefcase 123»»»

Permissions Expand / Collapse