November 15, 2016 at 3:07 am
The architect of a large new system posed the following question and I wondered how the debate on here might go (and how that compares to the answer I gave):
Given that the new server - a virtual machine - has the following drives:
C: OS
D: SQL Server binaries
L: Database t-log files
T: TempDb datafiles
U: Database datafiles
Which drives would you use solid state drives on - if you can pick only 2?
I don't know many particulars of the storage yet - it's all in planning. The server will be a Windows 2012 server running SQL 2014. Thoughts please.
November 15, 2016 at 3:30 am
Rather depends on the usage / query pattern for the server.
I would probably not put the OS & SQL binaries on SSD.
If TempDB is going to be heavily used, then that needs to go on SSD.
If the databases are read-heavy, then SQL data on SSD.
If the databases are update-heavy, then data & tlogs on SSD, assuming tempdb isn't a bottleneck.
Thomas Rushton
blog: https://thelonedba.wordpress.com
November 15, 2016 at 6:31 am
I'm with Thomas, it depends.
However, just a general observation, tempdb and logs are pretty frequent I/O bottlenecks. Lots of other stuff can, and will, be, but you get consistent problems from those two. I'd lean in that direction if I had no other knowledge or input. So, assuming zero knowledge, this is where I would go. However, I'd get the knowledge so that I was not making decisions in the dark.
--EDITED: Added a "not" that was missing.
"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
November 15, 2016 at 8:21 am
My personal experience is that it's not going to matter much especially if you have performance challenged code. We went from 16 processors/non-SSD to 32 processors/SSD on all the drives and the best that happened was a 2X increase in performance. That means that 50 minute jobs took "only" 25 minutes and 4 hour jobs took "only" 2. Big win, eh? 2X improvement is paltry compared to what CAN happen.
It didn't help front end code much at all because the data for most of that fit in memory already.
What did help (and continues to help) is fixing the miles of performance challenged code. For example, bit of front end code that runs in 250MS doesn't sound bad but when it executes 100,000 times in a day and consumes 1.2 million reads for each execution in 8 hours (that's 943TB of reads), it needs to be fixed because even SSDs aren't going to help much because the data is inn memory already and doesn't even need to be read from the SSD. Making just a couple of tweaks and adding an appropriate index got it down below 5MS (50X reduction in CPU and Duration) and only 800 reads (still bad but that's a 1,500X reduction). Same goes for batch code.
Do that to the top 10 or 20 worst code offenders in your system and you'll wonder why you need SSDs.
Also, if you have a lot of TempDB usage, it's probably because of the same performance challenged code. Remember also that TempDB usage starts out in memory and spills to disk only if it gets too big.
All that, notwithstand because a lot of people think that hardware is somehow the answer compared to what can be accomplished by fixing code, and given the choice of only two drives, I SSD the log file and data file drives and make sure that TempDB was properly configured according to RAID, number of files, and having Trace Flag 1118 enabled even if it doesn't look like you have contention in TempDB so that you don't have to worry about it as the system grows.
Still, it's kind of stupid to be limited to two. The binaries for SQL Server take relatively little for size and if you need more than 18GB(2GBx8 an 2GB), then you may have a serious problem in the code that really does need to be fixed even if you do move it to SSD.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 17, 2016 at 4:00 am
Thanks a lot for the replies - especially as they pretty much matched what I said off the top of my head at the time (my manager looked aghast at the possibility of prioritising logs/tempdb ahead of the data files). In reality there will probably be more SSD than having so pick only two (there seems to be budget).
Jeff's comments are entirely valid, however this is all vendor provided code we aren't allowed to touch - I'm sure it will be horrendous as usual.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply