December 7, 2015 at 8:54 am
I'm working on an established product in which nearly everything is "done in the database." Inevitably, this has led to the occasional performance problem and I was just investigating a few options recently, seeing how / if an SSD could help. I got some rather surprising results and was wondering if anybody could suggest what was going on.
What I did was restore the very same backup a number of times (to the same machine, on which nothing else was going on), and then perform a fairly significant amount of processing. (I don't want to go in to their details, as it's not this I want to investigate.) In all cases, tempdb was on the SSD.
Test 1. Data and log files (one each) located on an HDD.
Test 2. Data and log files (one each) located on an SSD.
As expected, test 2 completed in about 15% of the time test 1 took. But realistically, we couldn't put our whole database on an SSD. So I repeated test 1 but put some tables (and their indexes, defaults, etc.) in to a new, separate file group which I located on the SSD.
I was expecting this to give a result somewhere between those of test 1 and test 2 but was surprised / disappointed to find that they ran roughly 20% slower than the HDD test. i.e. moving some tables to an SSD made the database slower.
Does anybody know what might have been going on?
December 8, 2015 at 1:06 pm
If you start SQL Server with a cold buffer and empty cache, the plan_handles should match, the execution counts should match should match, the logical reads should roughly match, and statistics time should roughly match. Where they don't match by 15% or more is likely to pinpoint your concern.
A 15% reduction in a drag race can make the difference between winning and loosing. But in a road race what other drivers are doing can be more impactful than 15% ??. That includes Windows API calls, kernel/privileged time, and your system's drivers. Be sure to also compare perfmon.exe's disk and sqlservr process metrics.
If these races are not already IO bound on current HDDs, but instead are CPU bound (per statistics time, but with a warm buffer and with cache utilization), those SSDs may not be the droids you are looking for, even if a cold buffer test indicates SSDs could reduce a duration by 15. The difference could simply be due to buffered versus unbuffered pages.
December 8, 2015 at 1:19 pm
Two disk channels is twice the pipes! If you're writing mostly to an SSD, you're idle on the second pipe.
I only run logs on SSDs since nothing gets changed until sql server can log it first (synchronous)!
Furthermore, data gets lazy written eventually (async)...and makes use of contiguous sector writes when it does; this diminishes the advantage of SSDs which are outstanding at random sector reads or writes.
To answer your main question about mixed drives performing worse than an SSD...I'm wondering if you've got an awesome cache on your HDD.
I usually "preheat" data cache to give better real world results. Just select from your main tables, and by certain index criteria to get data and indexes all loaded.
December 9, 2015 at 2:56 am
Ahhh!
So you're suggesting that my "some tables" were actually running faster when on the SSD compared to the HDD?
And if I left the "some tables" on the HDD but moved everything else to the SSD, I'd get results even better than my test 2 (everything on SSD).
My existing results would certainly seem to support this hypothesis.
I'll give it a go and post the results (when I have time ... so don't hold your breath!)
Thanks for your replies.
December 9, 2015 at 3:05 am
To get a benefit from an SSD, you need to identify what parts of the workload are IO-bound, what files are under IO pressure and move those files. As usual, trying stuff at random doesn't work well.
http://sqlperformance.com/2014/11/io-subsystem/knee-jerk-performance-tuning-just-add-ssd
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply