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 5, 2012 8:59 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, October 7, 2013 6:25 AM
Points: 13, Visits: 46
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.
Post #1381354
Posted Monday, November 5, 2012 9:11 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, October 7, 2013 6:25 AM
Points: 13, Visits: 46
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.
Post #1381360
Posted Monday, November 5, 2012 10:04 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: Today @ 3:00 AM
Points: 42,774, Visits: 35,871
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 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 #1381366
Posted Monday, November 5, 2012 10:13 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

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




-----------------------------------------------------------------------------
संकेत कोकणे
Post #1381369
Posted Monday, November 5, 2012 10:34 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: Today @ 3:00 AM
Points: 42,774, Visits: 35,871
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 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 #1381370
Posted Monday, November 5, 2012 10:46 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, August 1, 2013 10:17 AM
Points: 323, Visits: 984
GilaMonster (11/5/2012)
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.


Yes, and that to be done carefully with the help of your well experienced system administrator.

I had done this once with one of my server last year,when we try to restart server (yes you need to restart your server after enabling the /PAE or /3GB option) ,it was not getting up.
One of my system administrator friend saves my life by restarting it with safe mode and then removing /PAE option, server got up .



-----------------------------------------------------------------------------
संकेत कोकणे
Post #1381377
Posted Tuesday, November 6, 2012 12:09 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, October 7, 2013 6:25 AM
Points: 13, Visits: 46
GilaMonster (11/5/2012)
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.


Well... i dont need the historical data too be that fast, so it can reside whereever SQL puts it. But i do need the Database holding Realtime data to be as fast as possible. And the point of their idea was to keep Database2 (Realtime only) in RAM and keep it small enough to fit in there easily, and as it will be very active it will stay in their the whole time. So I figure i could move all the old realtime data into the Historical Database (Database1).

I like the idea, but not sure about the pros/cons?
Post #1381405
Posted Tuesday, November 6, 2012 12:12 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, October 7, 2013 6:25 AM
Points: 13, Visits: 46
sanket kokane (11/5/2012)
GilaMonster (11/5/2012)
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.


Yes, and that to be done carefully with the help of your well experienced system administrator.

I had done this once with one of my server last year,when we try to restart server (yes you need to restart your server after enabling the /PAE or /3GB option) ,it was not getting up.
One of my system administrator friend saves my life by restarting it with safe mode and then removing /PAE option, server got up .



I hear you on that one and not like that option/switch very much.

There are some niche applications that i have programmed with that do not recommend using this option. For me its a gray area.
Post #1381407
Posted Tuesday, November 6, 2012 12:39 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, August 1, 2013 10:17 AM
Points: 323, Visits: 984
in your this case you don't need /PAE option ..as you are using 64 bit machine

[b]
I hear you on that one and not like that option/switch very much.

There are some niche applications that i have programmed with that do not recommend using this option. For me its a gray area.


Can you please explain,why this is not recommended ?


-----------------------------------------------------------------------------
संकेत कोकणे
Post #1381418
Posted Tuesday, November 6, 2012 12:48 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, October 7, 2013 6:25 AM
Points: 13, Visits: 46
sanket kokane (11/6/2012)
in your this case you don't need /PAE option ..as you are using 64 bit machine

[b]
I hear you on that one and not like that option/switch very much.

There are some niche applications that i have programmed with that do not recommend using this option. For me its a gray area.


Can you please explain,why this is not recommended ?


Not sure. The programming guide did not explain why. So its still a gray area
Post #1381423
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse