The effects of an SSD on SQL Server Performance

I.                    Introduction

A little while ago, my wonderful wife Renee got a Solid State Drive (SSD) for me.  Being a DBA, one of my first thoughts was to wonder what kind of effect this would have on database performance, since I use my home computer for a lot of testing and my writing projects.  So, I installed the drive in my machine (a slightly more complicated process than I expected that wound up involve type III paracord…) and reinstalled my necessary software.

Then, being a father of a three as well as a database professional and a grad student, I proceeded to put that idea aside for some time.  But I wanted numbers and a direct performance comparison, and thought they would be of interest to the broader database community.  So, once I found some time, I began running some tests for direct comparisons.

II.                  A Brief Background on SSDs

Traditional harddrives store the data on a small array of platters using magnetic charges.  In order to read the platters or write to them, the movable read/write heads must be properly positioned over the platters.  Most solid state drives are based on flash memory and often involve no moving parts whatsoever.

These different techniques to store data create several broad differences between HDDs and SSDs:

  1. SSDs tend to have dramatically lower latencies since they can access the entire drive all the time without having to align the head with the part of the drive to be read.
  2. SSDs tend to be much more resistant to being moved around while doing read/write operations.
  3. HDDs tend to be substantially larger and have a much better size/price ratio.

I carefully hedged by using “tend” rather than a more definitive word.  Both SSD and HDD technologies are still evolving and how large the differences are, or even if they hold true, can vary when comparing different SSDs with different HDDs and with the different circumstances they are used under.  But those three broad trends will hold true the vast majority of the time.

SSDs, because of the way they write to the drive, can be subject to write-cycle exhaustion.  This happens as parts of the drive are overwritten too many times and eventually cannot be written to any more.  To dramatically oversimplify this topic:

  1. According to Tom’s Hardware, it is rarely something a consumer making standard use of their drive needs to worry about.
  2. Ars Technica points out that many SSDs targeted at the enterprise use SLC instead of the consumer grade MLC flash, and SLC is much less susceptible to write-cycle exhaustion, but more expensive.
  3. The risk of write-cycle exhaustion can be significant under certain circumstances though, such as using a consumer grade MLC drive in a write intensive enterprise-style environment

There are also hybrid drives which combine relatively large standard harddrives with a smaller cache of flash memory which is used to reduce latency.

As for my personal experience, the change in overall performance of my system when I went from using a HDD to using an SSD as my primary system drive with the HDD for bulk storage was dramatic.  While I have not measured it precisely, the system boot time is now substantially shorter, as are load times for all programs.  The shortened load time is particularly noticeable in Spyder, a python IDE I use that used to take nearly a minute to fully load and now takes just a few seconds.  The computer as a whole is subjectively more responsive and “snappy”.  I think that for many modern computers, moving to a SSD is perhaps one of the best ways to improve the overall performance of the system.

III.                A Word on Methodology

Here, I am essentially looking at performance difference as they pertain to databases between the two drives in my home computer.  While I think this generalizes relatively well to give an idea of the database performance implications of SSD’s generally, it is important to emphasize that in the end my sample size is one standard harddrive and one SSD.  More than that, these are both consumer grade drives sitting in desktop computer.  In short, while I think this comparison is instructive, it is not a rigorous comparison of enterprise class drives that would be used in corporate servers, much less a look at large scale SANs which can readily incorporate multiple types of storage medium in complex tiers.

I selected two slightly different tests to time.  First, I took the Python script I wrote to compare the performance of SQL Server and SQLite and ran it with just the target drive changed.  Then I ran some of the long running scripts from Jeff Moden’s excellent Performance Tuning: Concatenation Functions and Some Tuning Myths article.

In this setup, I am using the same installation of Windows and SQL Server 2012, both installed on the SSD, and only the location of the database is really changing.  The relevant system specifications for reference are:

System Alienware (Modified)
Processor Intel Core i7 3.4GHz
Ram 16GB
OS Windows 7 Home
SQL SQL Server 2012 Expres

IV.                The Tests

a.       Rerunning the SQL Server and SQLite Tests

A little while ago I wanted to do a direct comparison between SQL Server and SQLite in a single user, desktop environment.  Since it already had a system with speed measurements in place, I reran all the tests twice, once with the destinations for the database pointing to the HDD and once with them pointing to the SSD.  The full details of the tests are in the previous article, but to briefly summarize, the test program creates a SQL Server and a SQLite table and then repeatedly inserts into the tables, sending a commit after each statement.  Then it repeatedly inserts into the tables, sending a commit only at the end, and then attempts to read from both of the tables.  The results are:

0.917 secs for 5000 inserts SQL Server (commit each)10.386 secs for 5000 inserts SQLite (commit each)0.435 secs for 5000 inserts SQL Server (commit once)0.020 secs for 5000 inserts SQLite (commit once)1.556 secs for 5000 reads SQL Server

1.960 secs for 5000 reads SQLite

2.120 secs for 5000 inserts SQL Server (commit each)538.364 secs for 5000 inserts SQLite (commit each)0.402 secs for 5000 inserts SQL Server (commit once)0.459 secs for 5000 inserts SQLite (commit once)1.518 secs for 5000 reads SQL Server

1.890 secs for 5000 reads SQLite

As expected, with a commit after each insert, which forces the server to write after every single insert, the SSD performs dramatically better.  It surprised me somewhat that the HDD came out better on the SQL Server inserts with one commit and even slightly faster on the SQL server reads.  Although I am not certain, I suspect this is because of the sophisticated caching that both the harddrive and SQL Server itself do.  So, I cleared the cache before executing the reads and got:

SSD (Cache cleared) HDD (Cache cleared)
1.564 secs for 5000 reads SQL Server 1.655 secs for 5000 reads SQL Server

This increased the time for reads on both drives slightly and shifted the advantage to the SSD where it was expected.

b.      Jeff Moden’s Concatenation Tests

Next, I turned back to Jeff Moden’s concatenation article.  This article came to mind because it helped give me more insight into performance tuning when I was much earlier in my SQL career and because it included a nice long running “Hog” Test that in his article took over 2 minutes and 50 seconds on the hardware he was using at the time.  So, I ran his setup code, and then created the function in the article, each twice, once on a database sitting on the SSD and once on a database sitting on the HDD.  Then I cleared the cache[1] and ran

SELECT DISTINCT SomeID, dbo.fnConcatTest(SomeID) AS CSVString
FROM dbo.TestData

Against each of them to get:

SSD (Cache cleared) HDD (Cache Cleared)
7 Secs 22 Secs

Clearly the SSD helped substantially.  This should not in any way to be taken to mean that improving the hardware can replace tuning the code though.  The “STUFF” version he provided at the end of his article had sub-second response times on the HDD, which is a nice reminder that frequently you can get better improvements by optimizing the code than by improving the hardware.


V.                  Conclusion

As the numbers show, the SSD generally performs better.  Though how much better varied by the circumstances, with SQL Server’s sophisticated caching system letting the HDD perform or even top the SSD under certain limited circumstances.

In most consumer and workstation systems currently in use, I suspect switching from a HDD to a SSD is one of the most effective upgrades currently available.  Moving purely to SSDs is probably prohibitively expensive for most large database applications, however they can serve as an effective part of a SAN to hold the most active data or to hold selected parts of the database which particularly require low latency with the bulk data held on HDDs in a simpler storage configuration.

VI.                More References

  1. The SSD Revolution – A series of feature articles on SSDs by ArsTechnica.
  2. In-depth on how SSDs really work– By Lee Hutchinson.  This is part of ArsTechnica’s SSD Revolution series, but goes into great depth on SSDs work and what those inner workings imply.  This is well worth reading.
  3. Is Your SSD More Reliable Than a Hard Drive by Andrew Ku

[1] It was significant to clear the cache here, without doing so the SSD and HDD times were identical.


New articles posted.

My article Plotting SQL Server Data for Data Visualization is up on MSSQLTips now and discusses using PyQt and Matplotlib to plot data from SQL Server.

My article Single User Performance of SQLite v SQL Server was posted yesterday on SQLServerCentral.  This one does a comparison of the performance of the python implementation of SQLite and SQL Server under single user scenarios.   It also touches on some interesting performance aspects of primary keys that I came across while writing the article.

Some of the commenters have pointed out that this is a somewhat “apples-to-oranges” comparison, and they have a fair point.  Still, I occasionally run into cases where both are valid choices so I thought it worth writing about.  One comment mentioned that SQL SE would be a more valid comparison, so I may do a brief follow up when I get a chance.

Writing About SQL Server

  1. I.              Introduction

I recently submitted my entry for a writing competition as part of my continuing education.  While I was preparing for the contest, in which we had one week to create the best entry we could on a topic they provided, I starting doing considerable research on the topic of writing well.  I thought it might be beneficial to organize what I found and provide it to the community.

I will be focusing primarily on SQL Server, because that is what I know, but most of these concepts are fairly general and should be useful in most forms of writing.  In looking at writing in the context of SQL Server, I will consider style and techniques for doing the actual writing.  I also look at how to find things to write about and ways to improve writing skills.

  1. II.            Style
    1. a.    Remember your audience and reason for writing

“Remember your audience” is repeated constantly, almost to the point of being cliché, when looking through discussions on writing.  But it really seems to be one of the most significant factors.  The target audience and reason for writing can affect the style, word choice, and what is said.  I write differently if I am sending a quick e-mail about the groceries to my wife than I do when sending a status report to my boss and both are different from how I would write an academic paper meant for submission in a class.

There are of course an enormous number of variations of target audiences and reasons for writing, but here it is worth looking at the normal cases when writing about SQL Server, specifically the writing of technical articles.  Technical articles tend to be targeted at being informative rather than persuasive, which means they can focus on objectivity rather than advocating a position.  It also means that generally opinions are not helpful, though there is some room for discussing taste and personal preferences, especially in informal writing.

Technical writing about SQL runs the gamut from being highly formal to fairly informal.  The differences hinges of course on how things are presented.  In formal writing, grammar tends to be more important and there may be conventions to that specific type of formal writing.  For instance, some organizations suggest that in formal writing contractions should be avoided and acronyms should be spelled out the first time they are used (even if the target audience should know them).  Some suggest that the use of any form of the words “I” and “you” should be avoided as they can add the appearance of subjectivity.  For instance, before I went back to school, I used to read Scientific American regularly.  Most of the authors in the articles went to pains to avoid using “I” or “we” and instead said things like “The author”.  There are a wide variety of types of formal writing with different style guides such as the Chicago Manual of Style that is used in the competition I was preparing for.

More informal writing on the other hand, might eschew many of those conventions and use contractions or “I” readily.  One of the main benefits of less formal writing is that it permits a more conversational style, which many people find easier and more pleasant to read.

  1. b.    Use passive voice only when there is a good reason.

I remember one of my middle school teachers used to rail against the passive voice.  She would always say we should never use the passive voice and deduct points for every time we used it in an essay.  It used to drive me crazy.  I intuitively felt (though I’m not sure I actually knew the word intuitively then) that there were some times when it just sounded better.  More than that, the passive voice persisted in English and other languages, which seemed to indicate there was a reason for it.

Though I generally think she was an excellent teacher, I have to respectfully disagree about this.  There are times when the passive voice is the right choice.  But we disagree only in degree.  The active voice is normally the better choice, even if the passive voice does have its place.  The active voice is easier to read and generally it is more expressive.  The passive voice also tends to result in longer sentences.  The passive voice tends to be horribly overused.  For a trite example, “Suzy kicked the ball” is shorter and simpler than “The ball was kicked by Suzy”.

I have adopted the approach of going back during editing to find sentences that use passive voice.  Then I ask myself why I used it there.  If I have a good reason, I leave it in passive voice.  Otherwise, I rephrase it in active voice.  This of course leads to the question of what constitutes a good reason.

One reason is that you may not know who performed the action.  Or you might know, but might not want to say for some reason.  For instance even if you know Jack, the new programmer, was the one that deleted the backups, you might just want to tell the manager, “The backups were deleted” instead of “Jack deleted the backups”.  This puts the focus on the backups and the current situation and avoids laying blame.

Along those lines, even if you intend to indicate the actor in the same sentence, the passive voice lets you emphasize what was acted on.  “The production server was ruined by the rainwater.”  Yes, we know that it was the water doing the action.  Yes, “The rainwater ruined the production server” is shorter.  But what is important is that the production server is dead, how that happened is secondary.  The passive voice lets us make it secondary grammatically as well.

Finally, the passive voice can be used to give commands or make suggestions without phrasing them as commands.  For instance, “It is generally better to avoid passive voice” can come across much less harshly than a prohibition of “Never use the passive voice”.

  1. c.    Grammar and spelling matter, but not as much as content and flow.

C.S. Lewis wrote in a letter “’Good English’ is whatever educated people talk; so that what is good in one place or time would not be so in another.”  It is easy to spend inordinate amounts of time focusing on spelling and grammar without improving the actual writing itself.  For some words, spelling varies by region.  In England, “colour” is considered correct while in the U.S. “color” is used.

Worse, an absolute devotion to certain rules of grammar can lead to awkward sentences.  I was told throughout school that a sentence should never end in a preposition.  Wikipedia’s list of Common English usage misconceptions says that this was not always necessary.  Either way, striving too hard to avoid ending in a preposition can result in odd sentences that are hard to read.

But spelling and grammar do matter.  Truly sloppy spelling or grammar simply comes across poorly and gives a poor impression.  They can distract the reader, and in some cases actually change the meaning of the sentence.

As with almost everything in writing, audience and purpose matter.  The more formal the writing is supposed to be, the harder I try to ensure the grammar and spelling are correct.  For the right audience, I even try to avoid preposition at the end of sentences. For some situations, like a writing competition, the dictates of a specific style guide may be enforced.

  1. d.    Prefer short, simple words and shorter sentences.

This again is common advice.  Paul Graham advised writers to “use simple, germanic words”.  George Orwell said, “Never use a long word where a short one will do.”  Short, common words, and short, direct sentences, are simply easier to read and understand.  Use long, unusual words and long, complicated sentence structures only when they are necessary to convey the idea.

  1. e.    Consider carefully the use of jargon

If you can avoid jargon, or terms of art, you generally want to.  George Orwell said “Never use a foreign phrase, a scientific word or a jargon word if you can think of an everyday English equivalent” in his Politics and the English Language.  But he was speaking specifically of political speech, which is generally meant for a mass audience, and even he limited it to times when a non-jargon word was available.  In writing about a technical topic like SQL Server, there often is no simpler word, or using one word of jargon would permit you to avoid a lengthy phrase.

How hard you should work to avoid jargon and what techniques you use to minimize it thus depend a on your target audience.  For instance, if I am writing a note to a group of senior DBAs, I will use “3NF” with abandon and expect them all to know what it means.  I express in three characters a concept that takes several sentences to convey clearly and whose implications fill numerous articles.  But I would only do that if I knew I was writing for a group of database specialists.  If I were writing to developers that were not specialists in SQL, I would probably write out “Third Normal Form” and might even provide a brief description to at least hint at what it was.  If I were writing to include a group without a technical background, I would try to avoid the term entirely if I could, and would definitely include a brief description if I felt it impossible to completely avoid it.

  1. f.     Minimize the use of adverbs.

Mark Twain is often quoted as saying “If you see an adverb, kill it”.  Yet, adverbs can be helpful.  They can help clarify a situation or describe it more graphically, and obviously they can provide emphasis.  I have used a few already in this paragraph, and even Mark Twain’s The Adventures of Huckleberry Finn makes frequent use of them.  But they are often overused, and text is frequently improved by removing them.

Like the passive voice, I have started looking for adverbs when I edit.  When I find one, I don’t immediately kill it, but I do ask if it is serving a purpose.  Even when it is serving a purpose, I consider whether I might do better with a stronger or more descriptive verb or adjective.  Only if it is serving a purpose and can’t be easily supplanted by something better would I leave an adverb in peace.

I give particular attention to the word “very”.  It adds emphasis, but rarely in a way that is truly useful.  Normally, I find that “very” is adding nothing of substance and purge it.  Sometimes, I find that the emphasis is necessary, but then I can normally use a stronger adjective to better effect.  For example, “Tarantulas can be very big.”  The “very” adds little.  If I just want to convey an impression of the size, “Tarantulas can be huge.” is both stronger and shorter.  But if I want to actually convey details then “Tarantulas can have bodies up to ten centimeters long.” is more precise.

  1. g.    Provide the readers a roadmap.

It generally helps to give readers an idea of what you are about to talk about prior to diving in to technical details.  This helps the text appear organized and well thought out and helps the reader to know what to expect.  It also helps to find something quickly when you are referring back to a document or looking for a specific answer in a longer document.

There are a number of techniques that can help provide a road map.  One of the more obvious ones that is particularly helpful in technical documents is to provide headers and sub-headers.  Within the writing itself, it often helps to introduce a topic briefly before going into in great detail.  Especially in technical writing, this can involve simply stating outright what you are going to discuss in that document or topic and why it is important.  It is also helpful to clearly and cleanly indicate when you are changing topics rather than continuing and elaborating on the last one.

  1. III.           Technique
    1. a.    Have a plan, but don’t be bound by it.

The best way to ensure that a text is well organized is to organize it from the beginning.  A plan can bring both structure and purpose to any writing project and writing without a plan is likely to be difficult to read and full of unrelated digressions.  It is difficult to make anything with a complicated structure without a plan ahead of time.

But a text written with utter devotion to the original plan is likely to be inflexible and brittle.  The process of writing helps you think, and it is possible that you will come up with new ideas or discover new facts while writing.  Sometimes those will even be surprising, as I was surprised by some things I found while writing the comparison between SQL Server and SQLite.  You might even find that the original plan needs to be substantially revised either because of something new you found or simply because you find that the structure you planned to use is not working well.  A plan is extremely helpful, as long as that plan is flexible.

  1. b.    Edit and Rewrite

A common maxim in writing fiction is “Write less, rewrite more.”  Similarly, Paul Graham advises getting out a first draft as quickly as possible and then rewriting and indicates he often spends nearly twice as long editing and rewriting as he did in making his initial draft.  While that much editing might not be necessary for a technical article, which tends to be more straightforward and a little less concerned with tone than either fiction or Paul Graham’s essays, editing is of vital importance.  I have always been able to improve anything I have written by going back over it carefully and making small tweaks, and then repeating the process.

When editing, I tend to make several passes, and there are a few things I focus on.  The most important thing for me is to make sure I am actually saying what I intend to say.  I look for places where my sentences are ambiguous or my structure is unclear.  I also try to look for places that don’t flow, or don’t sound right.  Reading the paper aloud can help with this, though I skip equations or blocks of code when doing that. Naturally, I also look for any actual mistakes I made or anything I said that I might need to double check or add a reference for.  Then I look for specific details, like unnecessary adverbs or over-use of passive voice.

On anything that is actually of real importance, I try to make at least two editing passes, once for large scale issues and the second focusing on the small scale issues.  More than two editing passes can certainly be appropriate, especially if I made major changes during one of the prior passes.  It also helps, when deadlines permit, to let some time pass between initial writing and one of the editing passes.  This helps you look at it with fresh eyes.

  1. c.    Get Help

When possible, I try to have another person read over what I wrote, for technical accuracy, and grammar and style.  For instance, I had my lovely wife read over an early draft of this article.  When I am writing something technical, I am always inclined to ask what was confusing or unclear as that indicates a section that probably needs more editing.  Of course, in situations like a writing contest, it might be against the rules to have someone else assist.  But outside of that narrow scenario, having someone read over your writing can be useful.

  1. IV.          What to Write

Finally, I think it is worth looking at what to write.  When I write about SQL Server or most other technical topics, I am normally writing the article I wish I had been able to find a couple weeks before.  Most of my topics come from situations where I faced a technical challenge for the first time, but could not find a good reference on how to deal with it.  So, I would normally piece together a solution through a combination of references that dealt with something close to what I was trying to do, poring over the manuals, and banging my head against the problem until I found something that worked, even if it wasn’t elegant.  Then, if I thought it might be something others would also face, I might write it up.

Although that type of article comes from a problem I already solved, the writing process has always taught me something I didn’t know.  At a minimum, writing about it cements the knowledge in my mind and helps me clarify my own understanding by forcing me to explain it.  Sometimes, I am actively surprised by things I find while fact-checking or doing additional testing for an article before submitting it.  Before I wrote the article about SQLite and SQL Server performance I did not realize that under some circumstances a clustered index can improve insert performance by avoiding IAM/PFS lookups.

Sometimes I decide to write because I think it is an excellent way to learn about a subject that I know little about and I find my topic through curiosity.  I decided to write about Undocumented Extended and Stored Procedures precisely because I knew little about them at the time and the writing process gave me a method to explore them and organize my research.

Occasionally, I will write something because someone asked a question, either directly to me or on a general forum, which got me thinking about the topic.  I wrote a general article on database performance because there was a small flurry of generic questions about performance on and I found myself writing similar answers repeatedly.

Generally, there are a variety of ways to find things to write about.  I find it most productive to focus on topics which will be appealing to decent niche of the SQL Server using audience, but most importantly topics that interest me.

  1. V.           Improving

As with many endeavors in life, the best way to improve is to practice, with a feedback mechanism.  When practicing something like writing, you need to know what you could do better; otherwise you might simply make the same mistakes next time without really improving.  The simplest way is just to wait a while and read over what you wrote with a critical eye and think about what you could have done better.  That is like reviewing your own games of Go or Chess to improve your playing.  It will definitely help, but only so much.

It is even more beneficial to get outside feedback.  One way is to publish and see what responses you get.  But of course, most of the responses will be targeted at your content rather than your style.  To improve on style, it helps to have another person review it specifically for style and give you detailed feedback.  Of course, it is nice if that other person is an experienced editor or writer themselves, but anyone vaguely in your target audience can tell you what sounded good and what didn’t.  They can point out what flowed well and parts where it was confusing or hard to read.

Reading can also help improve your writing.  Hopefully, reading about writing is useful.  But more specifically, it is good to read things that have a style you like, while actually focusing on the style.  See what they did that you liked so you can emulate that technique in your own writing.  I have frequently read good essays twice, once for the content and a second time to see how they wrote it.

  1. VI.          Conclusion

As with most things I write, I learned a fair bit in the process of writing this.  I think one of the most significant things I have found is the value of editing and rewriting.  Hopefully it will also be useful to others.

Additional References:

C.S. Lewis on Writing by C.S. Lewis

The Age of the Essay by Paul Graham

Writing, Briefly by Paul Graham

Writing and Speaking by Paul Graham

So You Want My Job: Freelance Writer  By Brett and Kate McKay (interview with Edward Mitchell)

The write stuff by Marvin Olasky

Writing, part deux by Marvin Olasky