Take the 2-minute tour ×
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.

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.

share|improve this question
1  
Interesting question; I'd probably include clustered indexes becasue the optimizer is geared to use them. Think about little things like Primary Keys defaulting to use clustered indexes, and how all nonclustered indexes point to clustered indexes (if they exist). In short, there may be no difference in terms of file I/O, but I would think that the database engine would make certain assumptions based on their presence or absence. –  Stuart Ainsworth Nov 28 '11 at 19:43
    
Not sure what you meant by your edit, but clustered indexes are not required. So no, SQL Server does not have to create a clustered index for each table. –  Nick Chammas Nov 28 '11 at 20:14
    
@NickChammas I'm referring to the fact that SQL Server automatically created a clustered index on the primary key. –  Matthew Nov 28 '11 at 22:08
1  
Some papers on this general area (not specific to your question) Do Query Optimizers Need to be SSD-aware? and Query Processing Techniques for Solid State Drives –  Martin Smith Dec 30 '12 at 11:52
add comment

migrated from stackoverflow.com Nov 28 '11 at 19:49

This question came from our site for professional and enthusiast programmers.

3 Answers

up vote 25 down vote accepted

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...

share|improve this answer
2  
Even if a given access pattern requires a B-tree with a certain ordering, that does not necessarily imply that the actual table data needs to be stored in a B-tree with that ordering. (Put another way: just because you need an index, that doesn't necessarily mean you need a clustered index.) –  ruakh Nov 28 '11 at 21:35
2  
@ruakh: True. But if the majority of access patterns require an order then is a strong indication that order should be your clustered index. –  Remus Rusanu Nov 28 '11 at 21:46
    
@RemusRusanu I think that ruakh hits on my question to you. What benefit is there for choosing the clustered index over choosing a standard index; particularly in your example of an in-RAM DB... Is it solely Bill Karwin's example of the coincidence that data will be on the same page? –  Matthew Nov 28 '11 at 22:14
1  
@RemusRusanu - Does the read ahead mechanism still work exactly the same for SSDs as for "traditional" discs? –  Martin Smith Dec 1 '11 at 11:22
1  
@Martin Smith: read-ahead still occurs on SSDs, as the code will exercise what it knows to do. But the benefits of read-ahead are muted on SSDs: if an IO stall is hit, the wait time for the page to be fetched is usually very small on SSDs. –  Remus Rusanu Dec 1 '11 at 16:12
show 1 more comment

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.

share|improve this answer
    
...Or in RAM as well. Locality of reference does indeed make a difference in performance –  Conrad Frix Nov 28 '11 at 19:44
    
Right, by memory I mean RAM. Disks are storage, not memory. –  Bill Karwin Nov 28 '11 at 19:45
    
I don't understand how a lookup to location A in RAM is faster than a lookup to location B in RAM. Can you elaborate or post a link? I understand that fewer pages will take up less space. –  Matthew Nov 28 '11 at 19:56
2  
@MatthewPK - When you seek against a clustered index, any column you could possibly want from the table will, by definition, be in the clustered index. When you seek against a nonclustered index, you will have to perform extra lookups to retrieve the columns you asked for in your SELECT clause that weren't in the index. For a large number of IOPS you will feel the burden of these extra reads, SSDs or not. –  Nick Chammas Nov 28 '11 at 20:08
2  
@Matthew PK: lookup location A and lookup location B in RAM can be vastly different in time, see aristeia.com/TalkNotes/ACCU2011_CPUCaches.pdf. However, at a database page granularity level, the impact is restricted to the TLB misses which is not that big of an impact. –  Remus Rusanu Nov 28 '11 at 20:22
show 2 more comments

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

share|improve this answer
    
There will be a clustered index. The point was whether or not seeks along it matter on the SSD platform –  Matthew Nov 28 '11 at 19:54
4  
Yes, the seeks matter. 3 reads as opposed to 300 reads is faster no matter what the medium you are using. –  Thomas Stringer Nov 28 '11 at 19:56
    
but I'm not discussing the merits of having no indices, simply questioning the importance of a clustered versus standard index given identical seek times. –  Matthew Nov 28 '11 at 22:14
    
@MatthewPK What's your definition of a "standard index"? –  Thomas Stringer Nov 28 '11 at 22:26
    
good point, poor word use on my part, when I say "standard index" I mean all types of non-clustered indices. –  Matthew Nov 28 '11 at 22:37
add comment

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.