Post reply

SSD Drive

  • January 11, 2017 at 10:44 am

    #339265

    My SQL Server has a local SSD drive and regular SAN storage. Should I put the

    tempdb on the SSD and my databases on the SAN

    or

    tempdb on SAN and databases on SSD?

  • January 11, 2017 at 10:56 am

    #1922012

    How big is your SSD compared to the size of your database?

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • January 11, 2017 at 11:01 am

    #1922015

    SSD drive is 1.5 TB

    Database is 50 GB

    tempdb is 85 GB. Last week it filled up and crashed the server.

    The database has performance issues due to table scans in stored procedures. Manager wants to throw hardware at the issue instead of tuning. (He has been advised otherwise)

  • January 11, 2017 at 1:19 pm

    #1922054

    If the server is Clustered then only TEMPDB can be on the local SSD

    If it isn't then placing the DB on SSD could be an option.

    And regular backups (every 5 min or less to SSD then copy to SAN) to SAN if reason for being on the SAN is DR/Data safety. A backup of a DB that size split into 4 files should only take 1-2 min to finish.

  • January 11, 2017 at 1:26 pm

    #1922055

    I placed the database file on the SSD drive with 5 minute transaction log backups to a san drive.

    A manager from another dept. told my manager that placing the tempdb on the SSD would have been a better decision.

    I'm looking for any research to support either one.

  • January 11, 2017 at 1:59 pm

    #1922064

    It totally depends on the IOPS of each storage, and your performance testing.

    Have you allocated files properly for tempdb, etc?

    ------------------------------------------------------------------------------------------------Standing in the gap between Consultant and ContractorKevin3NFDallasDBAs.com/BlogWhy is my SQL Log File HUGE?!?![/url]The future of the DBA role...[/url]SQL Security Model in Plain English[/url]

  • January 11, 2017 at 2:09 pm

    #1922067

    The tempdb has 20 datafiles and no symptoms of contention.

    The application database has extensive patch latch waits (due to table scans).

  • January 11, 2017 at 2:22 pm

    #1922072

    20 seems high, but your real issue is the scans (as you already know).

    Are the indexes and stats being created, maintained, reviewed and altered as appropriate?

    ------------------------------------------------------------------------------------------------Standing in the gap between Consultant and ContractorKevin3NFDallasDBAs.com/BlogWhy is my SQL Log File HUGE?!?![/url]The future of the DBA role...[/url]SQL Security Model in Plain English[/url]

  • January 11, 2017 at 2:34 pm

    #1922075

    The server has 64 logical processors. 20 is a bit of overkill but not much.

    Stats are updated nightly. Indexes are defragmented nightly.

    Performance issues are truly caused by poorly written stored procs.

    The application database has twice the IO as the TempDB

    So, which is it better to put on the SSD drive? The application database or the TempDB?

  • January 11, 2017 at 2:42 pm

    #1922076

    Just curious but what other databases are on that server? That sounds like a lot of hardware for only a 50GB database.

    Not that that would be related to bad table scans.

  • January 11, 2017 at 3:20 pm

    #1922084

    There are 2 other large archival (reporting) databases on the server that are located on different SAN drives. They have been tuned and are not having performance issues.

  • January 11, 2017 at 8:16 pm

    #1922099

    since the correct final answer is impossible to say without IOPS, brand specs, etc. I'll say

    Tempdb on ssd.

    There.

    ------------------------------------------------------------------------------------------------Standing in the gap between Consultant and ContractorKevin3NFDallasDBAs.com/BlogWhy is my SQL Log File HUGE?!?![/url]The future of the DBA role...[/url]SQL Security Model in Plain English[/url]

  • January 12, 2017 at 6:37 am

    #1922229

    I don't think there is a "right" answer here. It's completely dependent of the behavior of your system. Where are you seeing primarily slow behavior related to disk I/O? What are your wait and queue statistics? Without this information picking one single location for tempdb isn't really possible. Logs might be better put there, but maybe not. Data might be better put there, but maybe not. Without data, you can't really make a decision.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • January 12, 2017 at 6:47 am

    #1922233

    In general, if you have a choice of only one, then you want to put the files with the highest IO latency (or highest IO load) on the faster storage.

    I'm curious as to why you don't just put both onto the SSD and be done with it.
    SSD: 1500 GB
    User Database: 50 GB
    TempDB: 85 GB

    The SSD is running RAID 1 or 10, right?

    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

Viewing 14 posts - 1 through 13 (of 13 total)

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