Placing tables in memory

Question

Placing tables in memory

chandio11

Valued Member

Points: 69
More actions
June 12, 2009 at 4:33 pm

#222464

Hi,
We are working on high performance application, which will serve
1000K visitors at the same, my questions are as below
1) i want to put the 4 tables in memory
the estimated size of these four table is 2 GB and available memory at databaser server is 16 GB, can any one help me how can i achieve this task.?
2) All of transaction will be carried out in memory and on specific time the transactions will commited to database.
Please help me out for this scenario or suggest some other way to do this.

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

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

Ale Pelc SSCrazy Points: 2421 More actions · Answer 1

Hi,

1) i want to put the 4 tables in memory
the estimated size of these four table is 2 GB and available memory at databaser server is 16 GB, can any one help me how can i achieve this task.?

I don't think you'll be able to do that. You can load tables in memory if you use table variables, but SQL will decide if the table will be on memory or TempDB. For the sizes you're managing, I'm pretty sure they will be on tempdb...

Cheers,

Alejandro

Alejandro Pelc

Jeff Moden SSC Guru Points: 1004177 More actions · Answer 2

For the record, both Temp Tables and Table Variables start out in Memory and spool to TempDB if they get too big.

I suspect neither a Local Temp Table nor a Table Variable will do in this case because I believe the op wants the data from the table to be "pinned" in memory for all to use and both Local Temp Tables and Table Variables have a session scope.

Maybe making Global Temp Tables will do but if all sessions stop using them and the session that created them drops, so will they.

You used to be able to PIN tables in memory, but no longer. I believe you'll need to rely on the abilities of system cache and write some intelligent, very tight code that can reuse execution plans.

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

Paul White SSC Guru Points: 150467 More actions · Answer 3

You could also put tempdb on a RAM disk.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Jeff Moden SSC Guru Points: 1004177 More actions · Answer 4

Paul White (6/13/2009)
You could also put tempdb on a RAM disk.

Heh... I always forget the simple stuff. 😀

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