44

When designing a SQL server data schema and the subsequent queries, sprocs, views, etc. does the notion of a clustered index and order of data on disk make any sense to consider for DB designs made explicitly to be deployed on SSD platforms?

http://msdn.microsoft.com/en-us/library/aa933131(v=sql.80).aspx
"A clustered index determines the physical order of data in a table."

On a physical disk platform, the design to consider them makes sense to me as a physical scan of the data to retrieve "sequential" rows can be more performant than a seek through the table.
On an SSD platform, all data read access uses an identical seek. There is no concept of "physical order" and data reads are not "sequential" in the sense that bits are stored on the same piece of silicon.

So, in the process of designining an application database is the clustered index consideration relevant to this platform?

My initial thought is that it is not because the idea of "ordered data" doesn't apply to SSDs storage and seek/retreival optimization.

EDIT: I know the SQL Server will create one, I'm just philosophizing about whether it makes sense to think about it during design/optimization.

Improve this question
1

3 Answers 3

Reset to default
35

Ask yourself another question: If the entire database is in memory and I never have to touch the disk, do I want to store my data in an ordered B-tree or do I want to store my data in an unordered heap?

The answer to this question will depend on your access pattern. On most cases your access requires single row look-up (ie. seeks) and range scans. These access patterns require a B-Tree, otherwise they are inefficient. Some other access patterns, common in DW and OLAP, are always doing aggregates over the entire table end-to-end always and they do no benefit from range scans. As you drill further other requirements come to light, like the speed of insert and allocation into a heap vs. B-Tree may play a role for huge ETL transfer jobs. But most times the answer really boils down to one question: do you seek or range-scan? The overwhelming number of times the answer is YES. And therefore the overwhelming number of times the design requires a clustered index.

In other words: just because is cheap to read it from disk in random order does not imply that you can trash your TLBs and L2 lines in a 64Gb RAM scan bonanza...

Improve this answer
1
  • The cost of looking up the row in the base heap, even in memory, will be always higher than the cost of retrieving the row directly in the seek. Not only from the locality of the memory access, but also from sheer number of instructions involved (The lookup is basically a join, with all the join operator machinery).
    – Remus Rusanu
    Commented Nov 28, 2011 at 22:47
23

If you use a well-chosen clustered index, you are more likely to get all the related data you need in fewer pages of data. That is, you can hold the data you need in less memory. This gives a benefit regardless of whether you use spinning disks or SSD.

But you're correct that the other benefit of a clustered index -- to read/write related data sequentially instead of with many disk seeks -- isn't a significant benefit for SSD, where seeks are not such a huge performance overhead as they are with spinning disks.

Re @Matthew PK's comment.

Of course location A in RAM is just as quick as location B in RAM. That's not the point. I'm talking about the case when all the data you need won't fit in RAM if the data is scattered among many pages. Any given page may contain only a small amount of data you're interested in. So the RDBMS has to keep loading and purging pages as you access A, B, and other rows. That's where you get the performance penalty.

It would be better for every page to be full of data you're interested in, in the hopes that all of the subsequent row requests are served from pages in RAM. Using a clustered index is a good way to ensure that your data is grouped together onto fewer pages.

Improve this answer
0
13

Yes, it absolutely still does make sense. You're thinking too low-level in your approach. SQL Server (in a very very simplified explanation) stores clustered data in a B-tree architecture. This allows for fast data retrieval based on the clustered index key values.

A heap (no clustered index) has no sequential order of data. The most important thing to consider here that is in a heap the data pages are not linked in a linked list.

So the answer is yes, it still makes sense to have clustered indexes created on tables, even on an SSD. It's all based on how much data SQL Server has to sift through to get to the resulting data. With a clustered index seek, it is minimized.

Reference: http://msdn.microsoft.com/en-us/library/ms189051.aspx

Improve this answer
2
  • There will be a clustered index. The point was whether or not seeks along it matter on the SSD platform
    – Matthew
    Commented Nov 28, 2011 at 19:54
  • 5
    Yes, the seeks matter. 3 reads as opposed to 300 reads is faster no matter what the medium you are using.
    – Thomas Stringer
    Commented Nov 28, 2011 at 19:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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