Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

cluster index or non cluster index Expand / Collapse
Author
Message
Posted Wednesday, October 3, 2007 4:01 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 5, 2009 8:45 PM
Points: 2, 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.
Post #406500
Posted Wednesday, October 3, 2007 4:22 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, January 17, 2014 11:05 AM
Points: 779, 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.
Post #406507
Posted Wednesday, October 3, 2007 4:40 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Thursday, November 20, 2014 8:50 PM
Points: 20,584, Visits: 9,623
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.
Post #406512
Posted Wednesday, October 3, 2007 5:03 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, January 17, 2014 11:05 AM
Points: 779, 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.
Post #406521
Posted Wednesday, October 3, 2007 5:30 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Thursday, November 20, 2014 8:50 PM
Points: 20,584, Visits: 9,623
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.
Post #406529
Posted Thursday, October 4, 2007 6:49 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:40 PM
Points: 14,015, Visits: 28,394
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
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #406734
Posted Thursday, May 13, 2010 3:03 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, November 13, 2014 12:35 AM
Points: 25, Visits: 172
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
Post #921090
Posted Thursday, May 13, 2010 3:11 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:43 PM
Points: 40,411, Visits: 36,861
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 2008, MVP
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

Post #921092
Posted Thursday, May 13, 2010 6:57 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, November 13, 2014 12:35 AM
Points: 25, Visits: 172
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
Post #921212
Posted Thursday, May 13, 2010 7:21 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:43 PM
Points: 40,411, Visits: 36,861
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 2008, MVP
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

Post #921243
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse