SSD drives and SQL server

I am configuring SQL Server 2014 on a brand new server.

I have 238 GB available on C which is an SSD drive ans 1.81TB on D both raid 1.

The Server has 64GB  with 8 cores.

This server is replacing an older server (8gb)  which has no performance issues with only one small client using it for production.

I am planning to do the following:

(1) Put SQLserver and Analysis services application on D.

(2) Log files and backups on D.

(3) Databases and Temp DB on the SSD.   (the DB is only 4 gb)

The question is Will that work or can anyone recommend a better configuration?

Here is some background information:

This SQL server application is   resource hungry as it emulates a cube in real time so requests from the client to produce a one page report can involve thousands of reads and heavy use of the TempDB.

This one page  report we use for performance checking (performance is linear and predictable) takes 60 seconds on a virtual server, 30 seconds on our old dedicated server with  one user active. We assume the virtual server performance is due to throttling long running transactions.

The goal is to see how fast  we  can make this report execute  using only hardware and configuration  improvements. The rest of the system performance is excellent.




July 31st, 2015 8:48am

 

  1. Performance increases if TEMPDB is placed on RAID 1+0 (dependent on TEMPDB usage and in your case yes).

  2. For the TEMPDB database, create 1 data file per CPU so for you it will be 8 files

  3.  Always place log files on RAID 1+0 (or RAID 1) disks as this gives better Write performance
  4. Isolate system file/data files/log files
  5. For data file RAID 5 is better 

https://technet.microsoft.com/library/Cc966534?f=255&MSPPError=-2147217396

 

 

Free Windows Admin Tool Kit Click here and download it now
July 31st, 2015 10:20am

As written in the below link:

If you want to use SSDs, don't use single SSD drive implementations and plan on replacing the SSDs during the life cycle of your server. Some of the possible SQL Server implementations for SSDs include:

  •                               Moving indexes to SSD. Typically, indexes aren't very large and they have a lot of random read activities, making them ideal candidates to move onto SSD drives.
  •                               Moving data files to SSD. Data files usually have more read operations than write operations, so in most cases, they're well suited for SSDs.
  •                               Moving log files to SSD. Log files are write-intensive. Therefore, if you use SSDs for log files, use enterprise-grade SSDs and a RAID 1 or RAID 10 mirrored implementation.
  •                               Moving tempdb to SSD. Tempdb tends to have very high levels of random writes, which can lead to SSD burnout. Therefore, if you use SSDs for tempdb, use enterprise-grade SSDs, use a RAID 1 or RAID 10 mirrored implementation, and plan for the SSDs' replacement cycle. In addition, you might look into PCIe DRAM implementation for tempdb. DRAM storage provides higher levels of write performance and doesn't experience SSD burnout. However, DRAM storage can be expensive.

http://sqlmag.com/storage/sql-server-storage-best-practices

July 31st, 2015 12:21pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics