TempDB into Memory

Question

TempDB into Memory

sam rebus

Say Hey Kid

Points: 692
More actions
January 28, 2010 at 10:54 pm

#216213

In older versions of SQL Server, like 6.5 and i think 7, we used to be able to load entire TempDB into memory to increase performance of queries using temp tables. I cannot find a way to do this in SQL 2005 x64 Standard. has this functionality been removed? if there still is a way, please share with me.
thanks

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

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

SQL Editor SSCommitted Points: 1980 More actions · Answer 1

Why U need For Store Tempdb In Memory???

If U want to work with temp table which must be placed in memory for fast access.u can use table variable instead of temp table overhead.

table variable always take place in memory.

sam rebus Say Hey Kid Points: 692 More actions · Answer 2

i have no influence over queries that developers write (and already written) and queries that reporting software uses. i have to try speeding things up using what is available to me.

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 3

SQL manages the buffer pool, you cannot control what's in there or on disk.

SQL will try, as far as possible, to keep temp tables and table variables in memory because it knows that they will be needed very soon. It will only spill them to disk if there's memory pressure, either cause by large temp tables or other pressure in the buffer pool.

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

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 4

vbprogrammer1986 (1/28/2010)
If U want to work with temp table which must be placed in memory for fast access.u can use table variable instead of temp table overhead.
table variable always take place in memory.

Common myth, completely false.

Temp tables and table variables are treated identically with regards to when they are written to disk. Both are allocated space in the TempDB database, both have entries in the system catalog, both will be flushed to disk if there is memory pressure within the buffer pool.

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

sql_lock SSCrazy Eights Points: 9373 More actions · Answer 5

sql_lock

SSCrazy Eights

Points: 9373

January 29, 2010 at 10:05 am

#1110848

A blog to support Gila's post

Steve Jones - SSC Editor SSC Guru Points: 728164 More actions · Answer 6

You could look at an SSD drive for tempdb, which might provide some speed boost as well.

Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com

sam rebus Say Hey Kid Points: 692 More actions · Answer 7

sam rebus

Say Hey Kid

Points: 692

January 29, 2010 at 10:17 am

#1110858

OK, thanks everyone.

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 8

Steve Jones - Editor (1/29/2010)
You could look at an SSD drive for tempdb, which might provide some speed boost as well.

I seem to remember reading something somewhere (twitter maybe?) where someone tried using SSD's but burned them up in a high transaction environment? Not saying you shouldn't cosider it, but be sure to look at the load on tempdb. You may also want to see if you can get tempdb on its own set of disks so it isn't competing with the database files as well.

Steve Jones - SSC Editor SSC Guru Points: 728164 More actions · Answer 9

I have had some people say they burned up SSDs, but I think that was under extreme load. I have had a few people say they have used SSDs in some servers (not sure if this was tempdb or userdb) and been very pleased with the response.

Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 10

sam rebus (1/28/2010)
i have no influence over queries that developers write (and already written) and queries that reporting software uses. i have to try speeding things up using what is available to me.

Man, I feel for you, Sam. Especially since it's usually the queries themselves that are responsible for things being slow.

There are a couple of things you can try like making sure TempDB is on it's own harddrive(s), has 1 file for each CPU (up to 8 I believe is the recommendation), doing similar with LDF's, etc. Of course, none of that will really help if the code is as bad as you think it might be.

Personally, I'd run profiler and capture the 5 worst queries, rewrite one of them to be blazingly fast, and approach the Dev manager with some friendly suggestions.

--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)
Intro to Tally Tables and Functions

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 11

Jeff Moden (1/29/2010)
There are a couple of things you can try like making sure TempDB is on it's own harddrive(s), has 1 file for each CPU (up to 8 I believe is the recommendation), doing similar with LDF's, etc.

By "doing similar with the LDFs" do you mean on separate drives or having multiple files?

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

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 12

GilaMonster (1/30/2010)
Jeff Moden (1/29/2010)
There are a couple of things you can try like making sure TempDB is on it's own harddrive(s), has 1 file for each CPU (up to 8 I believe is the recommendation), doing similar with LDF's, etc.
By "doing similar with the LDFs" do you mean on separate drives or having multiple files?

My apologies... I meant separate drives.

--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)
Intro to Tally Tables and Functions

GSquared SSC Guru Points: 260824 More actions · Answer 13

Lynn Pettis (1/29/2010)
Steve Jones - Editor (1/29/2010)
You could look at an SSD drive for tempdb, which might provide some speed boost as well.
I seem to remember reading something somewhere (twitter maybe?) where someone tried using SSD's but burned them up in a high transaction environment? Not saying you shouldn't cosider it, but be sure to look at the load on tempdb. You may also want to see if you can get tempdb on its own set of disks so it isn't competing with the database files as well.

Older SSDs used to burn up pretty easily. Newer ones are harder to burn up. Each bit/sector is rated for a specific number of writes, based on an average comparable to MTBF for HDDs. I think it's pretty standard these days to have numbers in the 10s of thousands of writes per sector, which is quite a bit of data re-writing. The SSD will spread writes out over the disk to make the load more even.

It's still not recommended that you use SSDs on highly transactional databases, because you can hit the writes limit pretty rapidly in some cases. If you have a few hundred transactions per second, it doesn't take long to get up to 20k writes over a significant part of the drive. If you're looking at hundres of transactions per hour, like many small businesses, you'll be fine.

There are enterprise-quality SSDs that have MUCH higher threshholds. Also, MUCH higher prices. Those are designed for this kind of work, and do it quite well. Large data centers can use them, and the increased cost quickly disappears into the savings on electricity. (The drives use much less, and generate a lot less heat, so the air-conditioning bill goes way down for these centers.)

Because of the rewrites limits, you shouldn't use a disk defrag utility on an SSD. It'll burn up the disk that much faster, and it's not needed (zero benefit). Also, it's not recommended that you have virtual memory (page file) on an SSD, even though the higher speed on them would seem to make that a good use. Too many rewrites if you use it much at all.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 14

Why is there a limit to begin with? Is it just due to the physical nature of the device?

--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)
Intro to Tally Tables and Functions