TempDB into Memory

Question

TempDB into Memory

Viewing 9 posts - 16 through 23 (of 23 total)

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Nature of the device. Lower end SSDs are designed more for the W.O.R.M (write once read many) type of environment. Enterprise class SSDs should have less of a problem with write limits (for 12k each they should at least).

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

RBarryYoung SSC Guru Points: 143329 More actions · Answer 2

Jeff Moden (2/2/2010)
Why is there a limit to begin with? Is it just due to the physical nature of the device?

Yes, basically. Each sector will only deliver a certain average number of writes (somewhere between 1000 and 1,000,000, I can't remember now) before eventually failing. They use lots of re-vectoring tricks to even it out, but there are real limits.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1004412 More actions · Answer 3

Heh... so "SSD" actually must mean "Slow Sector Destruction". 😛

I wonder if they like pork chops...

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

I wonder how long it will take somebody to figure out how to burn the bat signal into the disk :w00t:

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 5

I wonder if you partition SSDs if that screws up the write-leveling infrastructure. i.e. does the write leveling not really honor partition boundaries. If it doesn't and the people to reported burning up SSDs with heavy tempdb testing could have been hit by this. For example if you have a 100GB SSD and you create 10 10GB partitions for putting files on and the write leveling must honor that, then putting a big file on one of those 10GB partitions and pounding the hell out of it with updates would indeed cause the drive to fail pretty quickly. So would having just one partition but not leaving empty space for write leveling too.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

GSquared SSC Guru Points: 260824 More actions · Answer 6

Jeff Moden (2/2/2010)
Why is there a limit to begin with? Is it just due to the physical nature of the device?

Yep. And it keeps improving as they improve the technology.

- 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

GSquared SSC Guru Points: 260824 More actions · Answer 7

Jeff Moden (2/2/2010)
Heh... so "SSD" actually must mean "Slow Sector Destruction". 😛
I wonder if they like pork chops...

HDDs can still end up with bad sectors too. And RAM can fail. They're just more mature technologies so they fail less often. But SSDs are rapidly catching up with HDDs. MTBF on an HDD is only a few years in heavy systems, and spindle failures are still common enough that RAID is still necessary if you value your data.

SSDs have the advantage that you can drop them from a third-floor window onto the sidewalk and they'll probably still work. Try it with an HDD and see how well that goes. Most of those can barely take being dropped from table height.

The thing with an HDD with bad sectors is you usually lose the data on it. Thus again you need RAID. SSDs with bad sectors usually just mean the capacity of the drive slowly goes down over time. You don't usually lose any data, just your 35 Gig drive slowly becomes a 30 Gig drive, and then 25 Gig, etc.

So yeah, fire up the pork chops, but make sure you apportion them correctly between HDD manufacturers and SSD manufacturers.

- 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

Steve Jones - SSC Editor SSC Guru Points: 736494 More actions · Answer 8

That's an interesting thought on the partitioning. I would think it would be an issue. If you go to SSDs, I think you want to use the entire drive as a single partition, or as part of a RAID array.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 9

Steve Jones - Editor (2/3/2010)
That's an interesting thought on the partitioning. I would think it would be an issue. If you go to SSDs, I think you want to use the entire drive as a single partition, or as part of a RAID array.

Yeah - I was like "WOW, I wonder about this" when I first thought of that. I suppose a top-notch write-leveling system would be able to use all free space on the drive while still enforcing partition sizes/alignments, but there must be a small overhead even if they do it.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service