<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2005
»
SQL Server 2005 General Discussion
»
cluster index or non cluster index
18 posts, Page 1 of 2
1
2
»»
cluster index or non cluster index
Rate Topic
Display Mode
Topic Options
Author
Message
paco_jaco
paco_jaco
Posted Wednesday, October 03, 2007 4:01 PM
Forum Newbie
Group: General Forum Members
Last Login: Tuesday, May 05, 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
Aaron Ingold
Aaron Ingold
Posted Wednesday, October 03, 2007 4:22 PM
Right there with Babe
Group: General Forum Members
Last Login: Thursday, June 09, 2011 10:47 AM
Points: 779,
Visits: 221
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
Ninja's_RGR'us
Ninja's_RGR'us
Posted Wednesday, October 03, 2007 4:40 PM
SSC-Insane
Group: General Forum Members
Last Login: Today @ 2:51 AM
Points: 21,357,
Visits: 9,531
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
Aaron Ingold
Aaron Ingold
Posted Wednesday, October 03, 2007 5:03 PM
Right there with Babe
Group: General Forum Members
Last Login: Thursday, June 09, 2011 10:47 AM
Points: 779,
Visits: 221
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
Ninja's_RGR'us
Ninja's_RGR'us
Posted Wednesday, October 03, 2007 5:30 PM
SSC-Insane
Group: General Forum Members
Last Login: Today @ 2:51 AM
Points: 21,357,
Visits: 9,531
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
Grant Fritchey
Grant Fritchey
Posted Thursday, October 04, 2007 6:49 AM
SSChampion
Group: General Forum Members
Last Login: Yesterday @ 4:50 AM
Points: 13,371,
Visits: 25,143
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans
Product Evangelist for
Red Gate Software
Post #406734
tony.turner
tony.turner
Posted Thursday, May 13, 2010 3:03 AM
SSC Rookie
Group: General Forum Members
Last Login: Sunday, May 05, 2013 1:48 PM
Points: 25,
Visits: 154
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
GilaMonster
GilaMonster
Posted Thursday, May 13, 2010 3:11 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Yesterday @ 12:59 PM
Points: 37,640,
Visits: 29,895
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
tony.turner
tony.turner
Posted Thursday, May 13, 2010 6:57 AM
SSC Rookie
Group: General Forum Members
Last Login: Sunday, May 05, 2013 1:48 PM
Points: 25,
Visits: 154
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
GilaMonster
GilaMonster
Posted Thursday, May 13, 2010 7:21 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Yesterday @ 12:59 PM
Points: 37,640,
Visits: 29,895
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 »
18 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
