tempdb local drive ssd versus attached storage

Question

Post reply

tempdb local drive ssd versus attached storage

tcronin 95651

SSCertifiable

Points: 6735
More actions
March 3, 2016 at 9:11 am

#305453

New data warehouse server (has sql source and OLAP instance) on new active passive cluster attached to SSD. Started with 300 gig tempdb, may need to expand that. Looking into whether or not using a local SSD as tempdb may be a more viable answer from possibly a performance perspective and the fact that it would actually be cheaper to go the local route. I realize this is all dependent on hardware you are using (we will be using Dell), anyone already gone down this road, or any opinions appreciated.

Viewing 10 posts - 1 through 10 (of 10 total)

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

Jon.Morisi SSChampion Points: 12846 More actions · Answer 1

I've seen others go that route with clusters, local SSD tempdb storage. It's a relatively new feature for that reason.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 2

I love putting a heavily used tempdb on local SSD, especially if it is like FusionIO and bolted into a PCI slot.

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

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

tcronin 95651 (3/3/2016)
Started with 300 gig tempdb, may need to expand that.

I can't really tell because I know nothing about your databases but that seems a bit much. I've got several half TB databases on one of my systems and it has both a front end playing against it and a shedload of ETL. My TempDB consists of 8 2GB files and they've never grown.

Again admitting that I don't know your system/data/usage but my first-blush belief is that something is very wrong with the database design, table design, and/or code if you actually need a 300GB TempDB.

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

tcronin 95651 SSCertifiable Points: 6735 More actions · Answer 4

THis is a data warehouse with monster ETL's I have seen great things about the Fusion product. The fact is it is cheaper than adding the SSD to the SAN

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 5

tcronin 95651 (3/4/2016)
THis is a data warehouse with monster ETL's I have seen great things about the Fusion product. The fact is it is cheaper than adding the SSD to the SAN

It's better too!

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

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 6

tcronin 95651 (3/4/2016)
THis is a data warehouse with monster ETL's I have seen great things about the Fusion product. The fact is it is cheaper than adding the SSD to the SAN

I'm curious... Can you define "monster ETL's" please? How many typical rows and columns and any graphics or "extended" documents?

We typically load for about 100 customers (banks) per day, each having between 100,000 and 200,000 loans a day along with nearly double that in collaterals, roughly the same number of insurance lines, payments and a couple of other things. Loans, collaterals, and insurance lines are about 100 columns wide and all of it goes through a rigorous validation process.

Shifting gears, I agree about the Fusion product being a good one but have you tested to see if it actually speeds things up for your ETL process? And have you taken a look at the things that are the heavy hitters on TempDB? While things like the Fusion products can speed things up, changing code to use high performance techniques can usually and easily out stripe any hardware addition with tens, hundreds and, sometimes, even thousands of times in performance improvement.

For example, we have a particular view that suffers a lot of usage. It was taking between 1.5 and 4 minutes depending on the criteria used against it. I made one simple tweak in two places (just changed a table alias in two places in the SELECT list columns) and, the view now returns in a very predictable 50ms. If we take the previous low time of 1.5 minutes and compare, that's a 1,799X improvement. In numbers meant to impress, that's a 179,900% improvement. Compared on the high side, that's a 4,799 (479,900%) improvement. It sounds even better out loud... "Improved performance by nearly a half-million percent". 😀 Heh... and I still didn't have to increase the size of TempDB. 🙂

You can't buy hardware that will make that type of improvement. Even MPP is usually advertised to provide 30X (3,000%) improvement and you need to change code anyway to make it work on MPP systems.

Getting back to my question... what are you referring to by "monster ETL" and why is it that it needs 300GB of TempDB?

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

tcronin 95651 SSCertifiable Points: 6735 More actions · Answer 7

Loads to the a data warehouse from source databases. I also have a 3rd party db I support with awful design (no control over it) so tempdb gets pounded.

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 8

tcronin 95651 (3/4/2016)
Loads to the a data warehouse from source databases. I also have a 3rd party db I support with awful design (no control over it) so tempdb gets pounded.

That doesn't tell me much about the volume of the task. Row counts, column counts, and number of times per day would. 😉

While the source data tables and the data warehouse may be monstrous in size, the principle of keeping a DW up to date usually boils down to just two basic methods... UPSERTs (think "merge") and REPLACEMENTs (replace whole tables with new data). Even with such monster tables, they don't usually require "monster ETL" (and that's without touching on the semantic as to whether such a thing is actually "ETL") and, even when they do, they don't usually require 300GB of RAM for TempDB when properly implemented.

Trying to help here and that's why I want to know about the row counts, column counts, and number of times per day the DW is UPSERTed or REPLACEd or both. 🙂

And, I agree... there's usually not much you can do about 3rd party POS unless you make a road trip with your favorite "come to Jesus" bat 😛 but sometimes you can trick around even that limitation.

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

tcronin 95651 SSCertifiable Points: 6735 More actions · Answer 9

Unfortunately in both of these cases my input is little, I just have to get it to work