tempdb files on SSD

  • On sql 2008 R2 which has 40 CPU's and one SSD drive I want to do the following, please throw your concerns here, appreciate it.

    1. creating multiple tempdb data files (may be 10) on a single SSD drive.

    2. pre allocating tempdb size to 1TB with restricted growth, each file 100gb.

    3. If 1st file is filled does it uses the second file and so on untill it max out to 1TB or 10 files any query would not fail, correct?

  • I have very successfully moved Log files to a SSD system (*) and received huge (2-5) times improvements in speed. I'd have thought moving the Log files would be more benefit than moving the TempDB.

    SSD system, was just a couple of 128GB SSD's in RAID 1, quite cheap and the performance gain was fantastic.

    Also looking at what you looking at doing, 10 tempdb files on one SSD, up to 1 TB, must be behind, is there a 1TB SSD on the market?

    And do you really want to put any DB on a single drive, I'd want to RAID it somehow.

  • Tara-1044200 (1/17/2013)


    3. If 1st file is filled does it uses the second file and so on untill it max out to 1TB or 10 files any query would not fail, correct?

    That is how log files fill, not data files.

    The data files can all be in use at the same time to fulfill different queries or the same query depending on the needs of the query(ies) being run.

    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

  • Tara-1044200 (1/17/2013)


    On sql 2008 R2 which has 40 CPU's and one SSD drive I want to do the following, please throw your concerns here, appreciate it.

    1. creating multiple tempdb data files (may be 10) on a single SSD drive.

    2. pre allocating tempdb size to 1TB with restricted growth, each file 100gb.

    3. If 1st file is filled does it uses the second file and so on untill it max out to 1TB or 10 files any query would not fail, correct?

    Like most everything else pertaining to SQL Server, the "it depends" answer hold very true for TempDB. IMHO I wouldn't go beyond 1 data files per logical CPU (not to be confused with cores). I would also recommend you pre-size your datafiles to avoid any growth - period (exception being for the log file)...some general rules of thumb are to size total tempdb data files size to about 80-90% of the total drive space, making the single log file approximately double the size of a single data file. So if you you have 4 CPUs you could have:

    Tempdb1.mdf @ 150GB

    Tempdb2.mdf @ 150GB

    Tempdb3.mdf @ 150GB

    Tempdb4.mdf @ 150GB

    Tempdb_log.ldf @ 300GB

    (just an example of course)

    Tempdb utilizes its data files in a round-robin manner and should always be of equal size to avoid SQL Server picking the largest data file to use first (SQL will continue to use the larger data file first, until the amount of free space is the same across all the other files...which will alleviate the point of having multiple files in the first place...which can lead to other issues - best to avoid this upfront)

    Depending on your SSD drive model, SSD has about 10-20 times the amount of IOPS than regular SAS or SATA drives and are AWESOME/expensive hardware to have! As was previously mentioned, you may want to consider a RAID solution for your tempdb.

    ______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

  • Here's a good article on tempdb configuration

    http://www.simple-talk.com/sql/database-administration/optimizing-tempdb-configuration-with-sql-server-2012-extended-events/

    There is a recommendations section you should check. Along with the recommendations, read the section on tracking tempdb contention. This should help with sizing and proper number of files for your environment.

    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

  • You realise that if the one SSD fails you're SQL Services will fall over. While all you'll need to do is replace the drive bring it online with the same drive letter and path for the files and SQL should start up ok. It's a situation that can be easily avoided with another SSD in raid 1.

  • I don't think you can even buy 1TB SSD???

    ______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

  • Tara-1044200 (1/17/2013)


    3. If 1st file is filled does it uses the second file and so on untill it max out to 1TB or 10 files any query would not fail, correct?

    NO , it oftens get used in parallell fashoin (multiple disk is being used) thats the reason many intermediate processes like sorting , aggregation , index rebuild/reorgainze temp table or table variable storage become FASTER while we have multiple disks

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • Bhuvnesh (1/21/2013)


    Tara-1044200 (1/17/2013)


    3. If 1st file is filled does it uses the second file and so on untill it max out to 1TB or 10 files any query would not fail, correct?

    NO , it oftens get used in parallell fashoin (multiple disk is being used) thats the reason many intermediate processes like sorting , aggregation , index rebuild/reorgainze temp table or table variable storage become FASTER while we have multiple disks

    As the transaction log grows, the first log file fills, then the second, and so on, by using a fill-and-go strategy instead of a proportional fill strategy which happens in data file. Therefore, when a log file is added, it cannot be used by the transaction log until the other files have been filled first.

  • I see 40 CPU's (2 nodes) when i check properties of sql server under "Processors". I think creating 40 tempdbdata files may be too much.

    I do have 5 TB of SSD, i was told by our network admin and may they are combination of multiple drives in a LUN i assume.

  • Tara-1044200 (1/22/2013)


    I see 40 CPU's (2 nodes) when i check properties of sql server under "Processors". I think creating 40 tempdbdata files may be too much.

    I do have 5 TB of SSD, i was told by our network admin and may they are combination of multiple drives in a LUN i assume.

    Check the links I recommended. BP currently is to start at 8 files (with more than 8 procs) and then grow from there if there is tempdb contention.

    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

  • but i am planning to put them on a single SSD drive which i asusme would be 8 files each of 100gb. so i think any file would be used out of 8 during a transaction untill it reaches 800gb right?

  • MyDoggieJessie (1/17/2013)


    I don't think you can even buy 1TB SSD???

    http://www.newegg.com/Product/Product.aspx?Item=N82E16820227724

    A 2TB SSD was announced last week as well.

  • Tara-1044200 (1/22/2013)


    but i am planning to put them on a single SSD drive which i asusme would be 8 files each of 100gb. so i think any file would be used out of 8 during a transaction untill it reaches 800gb right?

    A single drive is a bad idea just in case of failure. At the very least, spread out across 2.

    The way files are filled is that they are used in a proportional manner, based on free space. So a query could potentially put objects in multiple tempdb files. That's why recommendations are that you size all the files the same, to get even spreading.

  • when you say proportional manner do i have to restrict each file to certain size and then when total size uses more than max of all files then the query would fail, correct.

    also if 1 drive fails which has 4 tempdb files out of 8 does the sql server continue to run with the rest of the 4 files located on the second drive?

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

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