October 3, 2007 at 4:01 pm
Hi everyone, i want to know when i should use a cluster index and when is better for using a non cluster index.
Please if someone can answer my question, would help me a lot.
Regards.
October 3, 2007 at 4:22 pm
Boy, that's a question that could start forum wars... 😉
First thing you have to understand is the difference between a clustered and nonclustered index. Clustered indexes define the physical sort order on disk of the whole table. Nonclustered indexes are seperate but connected to the table and store a small subset of the table, with pointers back to the location of the main table data.
More often than not, the clustered index is going to have better performance when it is used. Not always, because I've seen fully covered queries by nonclustered indexes that were able to be fulfilled faster using a more narrow (meaning fewer bytes allocated to columns) than by a full clustered index scan. But you want to have your clustered index cover the most commonly scanned, data. Usually that's also your primary key (which is why a clustered index is created by default on a PK in SQL Server), though not always.
Here's my general advice... learn well how clustered and nonclustered indexes work, how the query optimizer uses them and how they affect both writes to and reads from the table. Then test in your particular scenario and see which approach you can get better performance with. And re-evaluate your indexes on occassion to ensure that as the data changes your indexing strategy is still applicable.
October 3, 2007 at 4:40 pm
One general hint for the clustered index is when you need to a range scan (Select all data between those 2 dates). One other great candidate is when you need to return the data into a particular order. Select all order items from this order ordered by itemid (item Order).
As it's already been said, this can easily start a war because there's a lot of different opinions on the subject. The bottom line is that you have to try all the configurations that you think can give you the best performance for your server, for your expected load, then see what configuration actually gives you the best results.
On the long run, you'll get the best results by doing that. I know this is not really satisfying, but that's where the experience of a dba really shows. Tuning is an art more than a science.
October 3, 2007 at 5:03 pm
Ninja adds a good point there. Think about the order of both inserts and scans. We had a table which was used for auditing actions from another table. The person who had originally set it up had used some combination of the primary key from the main table and a GETDATE() value which was inserted at audit record creation as the clustered index/primary key. While I agree with the use as the primary key, we had some problems with it as the clustered index. Because we could be adding audit records which might not necessarily sort into a convenient order based on the primary key we were having huge page split issues and quick fragmentation of indexes. I switched the clustered index to just be based on the audit log date, create a nonclustered index on the old primary key columns and was able to increase performance across the table significantly. Inserts to this table went much more quickly and we still had good read performance.
October 3, 2007 at 5:30 pm
This is what I mean by fully testing the configuration; test all the operations that need to be done in the system, in the real amount they will be done in real life. Take the trace of a full hour (normal), and even a full hour where the load is higher than ususal. Then run that trace on all configurations, then figure out what configuration gave you the most bang for your buck.
This is not a fast process, but this is pretty much the only way to get all you can get out of a machine.
October 4, 2007 at 6:49 am
I agree with what everyone has written and, since I didn't see it, I'll add one more thing. The reason you have to be so picky about the clustered index is because you only get one. Further, once you've got a clustered index on the table, any other indexes point to the cluster, not to the data directly (mainly because the clustered index stores the data).
Ninja made the most important point; test, test, test.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 13, 2010 at 3:03 am
Ninja's_RGR'us (10/3/2007)
One general hint for the clustered index is when you need to a range scan (Select all data between those 2 dates). One other great candidate is when you need to return the data into a particular order. Select all order items from this order ordered by itemid (item Order).
As I understand the "Advanced scanning" topic in BOL, the order in which rows may be returned cannot be guaranteed without an ORDER BY clause. In single user testing it will always be so, but in a multi-user environment it may not be so
May 13, 2010 at 3:11 am
Alert: 3 year old thread.
Even as a single user, you cannot guarantee that SQL will return data in a particular order without an ORDER BY. If, however there is an ORDER BY, and SQL can use an index to retrieve the data ordered, it avoids a sort operation.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 13, 2010 at 6:57 am
GilaMonster (5/13/2010)
If, however there is an ORDER BY, and SQL can use an index to retrieve the data ordered, it avoids a sort operation.
As I understand the section in BOL, this is no longer necessarily true
May 13, 2010 at 7:21 am
tony.turner (5/13/2010)
GilaMonster (5/13/2010)
If, however there is an ORDER BY, and SQL can use an index to retrieve the data ordered, it avoids a sort operation.As I understand the section in BOL, this is no longer necessarily true
It is true. What that section is saying is that SQL will not necessarily return the data in a particular order if ORDER BY is not specified due to, among other things, the advanced scanning options available (the 'merry-go-round' scan.)
If you specify an ORDER BY statement and there's an index that SQL can use to retrieve the requested information already in that order then the query optimiser will pick that index and will put restrictions in place to ensure that no non-order preserving operations occur. It is an optimisation, sorts are expensive and if SQL can avoid doing a sort by taking advantage of the already sorted index, it will do so.
If there's no ORDER BY specified then the optimiser, knowing that it doesn't matter what order the rows are returned in, can use any operators or scan methods it has access to.
In fact, to quote from that section of BoL
This mechanism is also called "merry-go-round scanning" and demonstrates why the order of the results returned from a SELECT statement cannot be guaranteed without an ORDER BY clause.
If you note, the part of my post that you quoted saying is wrong referred to the case where there is an ORDER BY.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 13, 2010 at 12:45 pm
Thanks for the clarification, I stand corrected
May 26, 2010 at 11:23 am
I'm pleased to find it stated repeatedly here that the index design should always be specific to the situation, and tested for correctness.
I always object to the commonly held mantras that;
"In general every table should have a clustered index", "in general, the clustered index should be on the primary key" and "in general, clustered indexes perform better".
None of these things seem true to me. Or at least, the performance differences being discussed only apply in specific applications and not at all as a general rule.
In my experience, in the real world,
1. Most data (by volume) is entered and referenced sequentially - it's naturally sorted in the way it is entered in a heap.
2. Most data is not updated very much. The whole forwarding pointers thing is not a significant issue, especially if you're routinely maintaining your indexes.
3. By design, primary keys are less and less likely to be useful in semantic queries - there's a general design movement towards abstracting physical keys from the data so that we're using integers or GUID's as a physical key where a larger, logical semantic key could be built from the data also but would be i/o expensive to use. Primary keys are also essential in joins, or should be. A primary key should ideally be as small as possible (actually that may not always be true either, depends on the application).
4. Clustered indexes are most appropriate where the data is routinely manipulated in sorted batches rather than working with single rows. This is most commonly true of date ranged data, and most commonly in warehousing / olap / reporting applications.
5. I've just been going through the SQL 2008 70-432 exam course material. It states that
"A clustered index does not physically store the data on disk in a sorted order because doing so creates a large amount of disk input/
output (I/O) for page split operations. Instead, a clustered index ensures that the page chain of the index is sorted logically, allowing SQL Server to traverse directly down the page chain to locate data"
... I'm afraid I don't understand what sorting a page chain "logically" means.
Anyone?
May 26, 2010 at 12:27 pm
reuben.anderson (5/26/2010)
... I'm afraid I don't understand what sorting a page chain "logically" means.
Each page in the index (and this goes for all indexes, not just clustered indexes) has, as part of the page header, the page number of the next and previous page in the index.
If you ever read the 'chose your own adventure' books, each story fragment in them has a next page to go to. You can kinda think of an index working that way. Read this page, then go to page 75 and read that, then to page 21 and read that.
If an index has no fragmentation, then logical and physical order match, and the 'next page' will always have a higher page ID than the current page.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 27, 2010 at 1:49 am
Ah ok I see, so when necessary the "previous" and "next" index page pointers are updated.. the physical page isn't moved... a new row is inserted on correct physical data page, but due to fragmentation the data page is not necessarily in the correct physical order.
Cool. Thanks,
What do you think of my arguments earlier?
February 15, 2011 at 11:13 pm
A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages.
A nonclustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a nonclustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.
Suvendu 🙂
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply