SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


cluster index or non cluster index


cluster index or non cluster index

Author
Message
paco_jaco
paco_jaco
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 9
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.
Aaron Ingold
Aaron Ingold
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1959 Visits: 222
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.
Ninja's_RGR'us
Ninja's_RGR'us
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47501 Visits: 9671
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.
Aaron Ingold
Aaron Ingold
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1959 Visits: 222
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.
Ninja's_RGR'us
Ninja's_RGR'us
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47501 Visits: 9671
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.
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)

Group: General Forum Members
Points: 72099 Visits: 32910
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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
tony.turner
tony.turner
SSC-Enthusiastic
SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)

Group: General Forum Members
Points: 117 Visits: 180
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (151K reputation)SSC Guru (151K reputation)SSC Guru (151K reputation)SSC Guru (151K reputation)SSC Guru (151K reputation)SSC Guru (151K reputation)SSC Guru (151K reputation)SSC Guru (151K reputation)

Group: General Forum Members
Points: 151117 Visits: 45881
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


tony.turner
tony.turner
SSC-Enthusiastic
SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)

Group: General Forum Members
Points: 117 Visits: 180
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (151K reputation)SSC Guru (151K reputation)SSC Guru (151K reputation)SSC Guru (151K reputation)SSC Guru (151K reputation)SSC Guru (151K reputation)SSC Guru (151K reputation)SSC Guru (151K reputation)

Group: General Forum Members
Points: 151117 Visits: 45881
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search