1. OCAU Merchandise now available! Check out our 20th Anniversary Mugs, Classic Logo Shirts and much more! Discussion here.
    Dismiss Notice

Using software RAID and NVME SSD for SQL server.

Discussion in 'Business & Enterprise Computing' started by Quantum Flux, Apr 9, 2019.

  1. Quantum Flux

    Quantum Flux Member

    Joined:
    Aug 1, 2005
    Messages:
    925
    Location:
    Canberra
    I’ve got a MS SQL 2012 server that’s underperforming using a RAID1 2x DP SAS HDD. Database is around 10GB. Server is HP DL360 G9. Sweet FA budget to deal with it <$800 ex.

    The safe bet I think is a couple of HPE 2.5” SATA SSDs which I can just RAID1 using HW RAID.

    But I was also looking at Intel’s NVME SSDs, such as the 900P (SSDPED1D280GASX). These can’t be hardware raided so the only option is a windows RAID 1. I’m a real layperson when it come to DBA. Is this a bad idea? Anyone else done similar?
     
  2. OP
    OP
    Quantum Flux

    Quantum Flux Member

    Joined:
    Aug 1, 2005
    Messages:
    925
    Location:
    Canberra
    Actually the server is running Win 2016 Svr.

    Can I use a storage spaces volume with MS SQL 2012SP4?
     
  3. PabloEscobar

    PabloEscobar Member

    Joined:
    Jan 28, 2008
    Messages:
    13,921
    What does "underperforming" mean in this case?
    Where are your bottlenecks?

    a 10GB database would end up mostly loaded into memory, Throwing more hardware at it, won't resolve bad query design.
     
    Daemon, 2SHY and NSanity like this.
  4. fad

    fad Member

    Joined:
    Jun 26, 2001
    Messages:
    2,379
    Location:
    City, Canberra, Australia
    Post up some taskmgr screenshots of the disk, cpu and mem.

    You may need to turn it on and then off.

    Diskperf -y

    You could also run Sql profiler and see what query is causing the problem
     
  5. ir0nhide

    ir0nhide Member

    Joined:
    Oct 24, 2003
    Messages:
    4,377
    Location:
    Adelaide
    With a database that small, sounds like a SQL/Application issue rather than hardware.
     
  6. chip

    chip Member

    Joined:
    Dec 24, 2001
    Messages:
    3,850
    Location:
    Pooraka Maccas drivethrough
    Basic sysadmin style troubleshooting of ms sql server can be done with performance monitor, the MSSQL specific objects are documented here: https://docs.microsoft.com/en-us/sq...nce/identify-bottlenecks?view=sql-server-2014

    It can't hurt to run diskspd (a tool from MS) that will help give you an idea of what your IOPS your disk subsystem is capable of: https://gallery.technet.microsoft.com/diskspd-a-robust-storage-6cd2f223 , and SQLiosim to simulate actual mssql style write patterns: https://support.microsoft.com/EN-US/help/231619

    Based on the lack of info so far, jumping straight to buying Optane storage doesn't sound like a good move to me.
     
    Last edited: Apr 10, 2019
  7. DivHunter

    DivHunter Member

    Joined:
    May 31, 2005
    Messages:
    3,178
    Location:
    Melbourne
    It's only 10GB, get the DB onto a system with SSDs and test it out. MSSQL has a free developer version license you can select at install, it is Enterprise level so it does not behave exactly the same way as Standard.

    https://www.brentozar.com/responder/

    The first responder kit has some handy tools to find suspect queries, lazy devs may not like you after using it.
     
  8. bcann

    bcann Member

    Joined:
    Feb 26, 2006
    Messages:
    5,883
    Location:
    NSW
    just out of interest have you rebuilt the index redone the statistics?

    Although it likely is just some shittily done query.
     
  9. samus

    samus Member

    Joined:
    Jun 3, 2002
    Messages:
    1,264
    Location:
    Baulkham Hills, Sydney.
    Hey, came here to echo what other people have said. a 10GB database will cache tables in memory, unless you have set SQL to only use 4GB or whatever, which is very unlikely in a "default" install. By all means throw some memory at it but in my experience, it's probably bad application design.
     
    MrSnuffy and NSanity like this.
  10. cvidler

    cvidler Member

    Joined:
    Jun 29, 2001
    Messages:
    13,338
    Location:
    Canberra
    bets of every query being

    "select * from table"
    with a million joins each pulling every column.
     
  11. ir0nhide

    ir0nhide Member

    Joined:
    Oct 24, 2003
    Messages:
    4,377
    Location:
    Adelaide
    Crickets......
     
  12. chip

    chip Member

    Joined:
    Dec 24, 2001
    Messages:
    3,850
    Location:
    Pooraka Maccas drivethrough
    OP's waiting for a query to finish
     
    tensop likes this.

Share This Page

Advertisement: