SSD Drive

  • 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?

  • 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

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

  • 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.

  • 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.

  • 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]

  • The tempdb has 20 datafiles and no symptoms of contention.

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

  • 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]

  • 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?

  • 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.

  • 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.

  • 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]

  • 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
    The Scary DBA
    Author of: SQL Server 2022 Query Performance Tuning, 6th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • 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