Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mkilley
Starting Member
5 Posts |
 Posted - 2003-04-23 : 04:32:45
|
| Hi all,Now I'm no expert on SQL Server, but I'm pretty sure that nearly everywhere I've looked the recommendation I've seen is that it's best to have a clustered index on an ID field (identity) for a table. Yet my team leader is saying that he doesn't want a single clustered index on any of our 60+ tables because clustering is a performance hit. Most of our tables are pretty straightward with fairly static data - e.g. Property or Client Addresses.Am I wrong, or is he?Does anyone have a link to an article that would help either one of us to see the error of our ways? I could do with something that puts as a bottom line - YES to clustered, or NO to clustered. He says that Books Online is wrong in suggesting clustered indexes for ID fields... ?Help!Cheers, Mike. |
|
|
Andraax
Aged Yak Warrior
790 Posts |
Posted - 2003-04-23 : 06:53:59
|
| Hi Mike!You should definately use clustered indexes on the most used search/join term of the table, eg the primary key in most cases. If you have very small amounts of data you don't need any index at all though.I don't understand why clustered indexes would be a performance hit...Links to articles:[url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da_5b1v.asp[/url][url]http://www.sql-server-performance.com/mr_indexing.asp[/url]Quote from Inside Microsoft SQL Server 2000 by Kalen Delaney:"Most tables should have a clustered index. If your table will have only one index, it generally should be clustered." |
 |
|
|
efelito
Constraint Violating Yak Guru
478 Posts |
Posted - 2003-04-23 : 10:13:52
|
| Most and generally are the key words in Kalen's quote. There are some situations where you wouldn't want a clustered index. If a table is constantly being hit with a heavy amount of inserts, a clustered index could cause a performance issue. SQL Server would constantly be worring about page splits and ordering the data when all you may be worried about is getting the data in there. Systems that are all insert and no select are very rare though.Like Kalen said, "Most tables should have a clustered index. If your table will have only one index, it generally should be clustered". Notice how she leaves room for exceptions. You will not find a bottom line of yes or no to clustering. As everything else in the database world, the answer is "It Depends". Maybe if you tell us a little more about your application we can help prove or disprove your case.Jeff Banschbach, MCDBAEdited by - efelito on 04/23/2003 10:14:41 |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-04-23 : 12:57:31
|
quote:
As everything else in the database world, the answer is "It Depends"
I always love that answer (mostly because it's true)Bottom Linequote:
Yet my team leader is saying that he doesn't want a single clustered index on any of our 60+ tables because clustering is a performance hit.
Does he or influence the person that signs your check?No Depends there.Let us know things like# Transaction day/hour# rows in tables or size of db# concurrent users/ connectionsIf there are no Clusters are there Unique Indexes (you'll have to rebuild them some time)Any RI Involved (skip this if there's no Unique Indexes)...ohh ectGood Luck btwOnly way to change his mind (and this is doubtful) is to prove it with metrics.Brett8-)PSquote:
Now I'm no expert on SQL Server
Same hereEdited by - x002548 on 04/23/2003 12:58:23 |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-04-23 : 21:34:23
|
quote:
Yet my team leader is saying that he doesn't want a single clustered index on any of our 60+ tables because clustering is a performance hit.
I'm being kind when I say that your team leader is an idiot. And I'm only being kind to him/her by calling them an idiot; I'm gravely insulting genuine idiots by making the comparison. As Brett suggested, metrics will tell you the real story, and unless your team leader has metrics that PROVES a clustered index is a performance hit, I'd suggest disregarding anything else he/she has to say about anything. Even if they tell you the sky is blue and the sun rises in the east, check it yourself.Ask your team leader to develop a phone book that has the names and phone numbers in a completely random order and ask them if clustering is such a bad idea after all. Because that's essentially what their advice will do to your tables. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-04-23 : 22:25:37
|
lol     Might post something sensible when I recover.quote:--------------------------------------------------------------------------------Yet my team leader is saying that he doesn't want a single clustered index on any of our 60+ tables because clustering is a performance hit.-------------------------------------------------------------------------------- When interviewing it's always as well to start of with 'what is a clustered index' before wasting time with a lot of deeper questions.Always cluster on ID - nope.In fact in v6 or 6.5 microsoft documentation said it was never a good idea - it was fun interviewing people who had taken the exams and didn't understand why that was wrong.It is possible that some tables could suffer from a clustered index. It may be that the team leader doesn't want people adding them while the database is being devleoped and will review the situation later.In general a clustered index means one less read to access the data page. A clustered index on an ID can cause hot spots in an oltp system. It can cause data page fragmentation on other fields.Probably not having a clustered index will cause less problems than a bad one so in that respect ...==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy.Edited by - nr on 04/23/2003 22:40:37 |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-04-24 : 10:06:19
|
quote:
I'm being kind when I say that your team leader is an idiot.
Been a rough week Rob?the problem is not technical because:quote:
Does he or influence the person that signs your check?
I still say money is money, and as a wise dba once told me, chaos means money, well at least to a consultant.And if you do what they say (AND get it in writing), they will always find to correct it later when it's broken.There is never time to do it right the first time, but there's always (an infinte amount) to time correct it.All that said, you can still get screwed.Sorry, just the nature of the beast. Some of the "best" people in the business are not the brightest, just the most political.MOOBrett8-)Edited by - x002548 on 04/24/2003 10:08:36PSAnd even if we win, if we win, HAH! Even if we play so far above our heads that our noses bleed for a week to ten days; even if God in Heaven above points his hand at our side of the field; even if every man woman and child joined hands together and prayed for us to win, it just wouldn't matter because all the really good looking girls would still go out with the guys from Mohawk because they've got all the money! It just doesn't matter if we win or if we lose. IT JUST DOESN'T MATTER!Rest of group: IT JUST DOESN'T MATTER! IT JUST DOESN'T MATTER..."Edited by - x002548 on 04/24/2003 15:06:20 |
|
|
|
|
|
|
|
|
Clustered vs. Non-clustered indexes