Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 5 down vote favorite
1

I'm new to performance problems, but I saw some discussion that SSD disks solve performance problems caused by index fragmentation. Has anyone experienced this? I'm thinking of changing my hardware and I'm wandering if that would help

share|improve this question
Can you zoom back a little? What evidence do you have that fragmentation is an issue for you? Depending on that, SSDs may not be the best fix. – Brent Ozar Dec 26 '12 at 17:59

2 Answers

up vote 11 down vote accepted

Using solid-state drives certainly does reduce the performance impact of fragmentation, primarily because random I/O is so much faster than traditional storage.

Nevertheless, the reduction in page density (and extra logging) caused by page splitting is still undesirable and may still impact performance.

Generally speaking, well-maintained indexes with an appropriate FILLFACTOR are good regardless of physical storage considerations.

See Paul Randal's excellent article on TechNet for details.

share|improve this answer
4  
+1 And fragmentation still means you are probably using more memory to hold the data than is actually required, and whether you use SSD or not, the impact to memory is the same. So I agree that it reduces the impact, but I want to state explicitly that this does not mean - contrary to an opinion I've seen quite a bit - that once you move to SSD you can stop worrying about fragmentation altogether. – Aaron Bertrand Dec 20 '12 at 13:22
Correct. Also, tiny reads on SSDs are still far from free. They are still 2-3 orders of magnitude slower than a read from buffer pool. – usr Feb 7 at 18:07
up vote 1 down vote

Using SSDs can help with performance. If you've tuned your queries, added more memory and still have an overwhelmed I/O subsystem it's time to think about SSDs. Just adding a solid-state disk to your system will not boost the performance, you have to play smart. Analyze the workload and which part of I/O subsystem is overloaded to determine which data and log files to put on SSDs

More from Paul Randal: Benchmarking: Introducing SSDs . Check out all his posts about benchmarking with tests and graphs comparing SATA vs SSD performance

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.