SQL Server Performance Tuning with SSD drives

Home » Advice for New Developers » SQL and Databases » SQL Server Performance Tuning with SSD drives
Photograph of a SSD Drive Array

Recently when explaining to a student the basics of database design and the need for primary keys, foreign keys and indexes generally, I fired up SQL/Server 2012 and typed in a few simple examples on my laptop.

A very brief summary of this is that you need primary keys to uniquely identify rows in a table, and you use foreign keys to link to primary keys in other tables. Additionally an index must be specified on each foreign key (if you use it as an access method rather than just referential integrity). You can also specify additional indexes unique or otherwise as you see fit, taking into account your access methods (paths) and also being careful not to get too carried away with indexing due to insert performance and space considerations.

So I set up a simple example with a couple of tables, customers and orders. I then used Red Gates SQL Data Generator 2 to generate a million rows in the tables because the first time I tried my laptop was taking less than a millisecond to return the data and the times were coming out as zero.

So to repeat the tests with more data: I then started off with a table scan, tried to access via a unique id (without a primary key defined) and compared the timings, so far so good.

When I tried to access all customers whose names begin with “A”, I got 70,000 returned in a quarter of a second or so. It was doing a table scan. When I added an index to customer name, the times increased by about 25%. The only explanation I have is that because my laptop uses dual Samsung 256GB SSD’s, data access is so quick anyway, using an index results in more IO and hence slower access.

I need to do some more work here to understand whats happening here. Expect future blog posts on this subject.

 

About Phil

I have been working as a software developer since 1983. This blog could have been called "From Fortran 77, C and Cobol to C# in 20 (not so) easy years", but it doesn't sound quite right somehow. Besides I'm talking about what's happened since 2003, not before!

Leave a Reply

Your email address will not be published.


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">




Top Posts & Pages

Categories

Recent Posts

Recent Comments

Archives

Blogroll

  • Mike Cohn's Blog
  • Scott Hanselman's Blog
- mike@mountaingoatsoftware.com

I wrote 25 blog posts during 2018. In case you missed some of them, here are the most popular. [...]

- mike@mountaingoatsoftware.com

The iterative and incremental nature of agile makes an agile approach seemingly less compatible with [...]

- mike@mountaingoatsoftware.com

Velocity can be great for predicting how much a team can deliver in a given period. But it needs to [...]

- mike@mountaingoatsoftware.com

Succeeding with agile isn’t just about knowing where to start, it’s about knowing where to go next—w [...]

- mike@mountaingoatsoftware.com

Here’s what to do when facing a complex user story that cannot be split but is too large for one spr [...]

- Scott Hanselman

As I said on social media today, it's 2019 and I'm updating the Firmware on a Zune, fight [...]

- Scott Hanselman

I'm going to try to finished my Relationship Hacks book in 2019. I've been sitting on it t [...]

- Scott Hanselman

My son and I were working on an Adafruit NeoTrellis M4 Mainboard over the holidays. This amazing lit [...]

- Scott Hanselman

I'm on vacation for the holidays and I'm finally getting some time to play video games. I [...]

- Scott Hanselman

I blogged about DOSBox five years ago! Apparently I get nostalgic around this time of year when I [...]

Meta