October 29, 2003 at 2:56 am
Hello,
I work in a very busy call centre environment where our poor old orders table gets inserted and updated quiet extensively by many users (over 150+).
The question is they have a policy here of not using clustered index but many non-clustered indexes (in fact on all their tables?).
When asked about this I was told that because of the amount of updates and inserts on the orders table it would be a performance hit more than a help?
The order table has a unique index key which surly must need a Clustered index to speed performance?
Thanking you in advance ....Ahsen
October 29, 2003 at 3:11 am
Clustered indexes change the way all the other indexes on a table work, so this would be a lot dependant on what other indexes are there. It also depends on how often that unique key is looked up. A relatively low fill factor (65-70%) can lower the impact of adding/updating records, but will slow down index seeks
Keith Henry
DBA/Developer/BI Manager
Keith Henry
November 1, 2003 at 3:06 am
Hi
What you have been told about clustered indexes is true? although they make up by increasing the performance of the select queries
try to index with the field that doesnt change often.
you can also have an optimum fill factor so that it could improve the insert/update performance
u can also create the index on a separate file so that it increases your performance
November 2, 2003 at 5:41 pm
Ahsen,
You SHOULD use the clustered indexes.
Each table should have a clustered index.
without a clustered index (among others) you cannot defragment your tables which hurts the performance, especially where large ranges are selected.
Clustered index speeds up the range queries (like, >, >= between...).
If you are afraid of the performance you could use a fill factor value as Keith suggested or you can define the clustered index on an identity column, so you can be shure that each and every new row will go at the end of the table.
But just as an info for you: Im running several SAP databases with over 1300 users where I have over 26000 table in each database and the average size of the database is over 200GB AND I have clustered indexes on every table!
And not on the identity columns!
So your users can be afraid of poorly written queries, missing or wrong defined indexes, small HW but definitly not of clusterd indexes.
Those are making the life of everybady just much easier!
Bye
Gabor
Bye
Gabor
November 2, 2003 at 6:27 pm
>> The order table has a unique index key which surly must need a Clustered index to speed performance?
No.
Depends on the nature of the system. A clustered index will help reading records probably (but maybe not depending on the fragmentation it may also cause) but may slow down inserts and updates.
The index decisions should be made taking into account the table structure and queries against it. A policy like this is unlikely to be correct in all cases but placing an incorrect clustered index is likely to have a bigger detrimental effect than not having one.
Cursors never.
DTS - only when needed and never to control.
Edited by - nigelrivett on 11/02/2003 6:30:17 PM
Cursors never.
DTS - only when needed and never to control.
November 2, 2003 at 10:17 pm
My only add to this is the "supposedly" the query optimizer "likes" clustered indexes. Not really sure how to test this, but the recommendation is always to have a clustered index, even if your queries do not use it.
Steve Jones
http://www.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/
November 3, 2003 at 2:20 am
quote:
Each table should have a clustered index.
Definately not!
quote:
without a clustered index (among others) you cannot defragment your tables which hurts the performance
Because of the way heaps work as opposed to clustered indexes the contiguousness of the table is less important, and heaps are much more efficent in shrinks etc.
quote:
A clustered index will help reading records probably
...and far from always
A bit of Indexes 101 might help here:
An un-indexed table is stored as a Heap. The FirstIAM entry in sysindexes points to the first IAM(Index Allocation Map) page which in turn points to all the extents (groups of 8 pages).
A non-clustered index points contains a Balanced Tree structure that allows SQL server to drill down quickly through the intermediate pages to the leaf level. Each Leaf contains a bookmark (format: file_id#:page_id#:postion_in_page#). If you look at the query plan of a query on a non-clustered index you will see an "Index Seek" followed by a "Bookmark Lookup"
A Clustered index has a similar structure except that each leaf contains the actual data, rather than a bookmark to the data page. The query plan now only has an "Index Seek"
A common analogy is that a normal index is like one at the back of a book where you would then have to go find the page, whereas a clustered index is like a telephone directory where you look up the name and the number is there next to it.
Your fill factor is how much space you leave on each index leaf for more records. Padding the index fills the intermedate pages the same amount
Now here's the downside to clusterd indexes: If you have a clustered index on a table you change the structure of every non-clustered index, because now the leafs contain a reference to the clustered index instead of a bookmark. In many cases the clustered index lookup is slower than the bookmark lookup, so while the index you choose to cluster is much quicker, every other index is now slower!
This is compounded if your clusterd index is large (e.g. on a varchar(50) field), across multiple fields or non-unique (In which case it needs an underlying layer to uniquely identify records). In all those cases if the performance of more than one index is important don't use clustered indexes.
Having said all that (hell am I still going on?) Most of the time a clustered index on an identity column is the way to go and keep things simple, but you should still be aware of all this stuff.
Keith Henry
DBA/Developer/BI Manager
Keith Henry
November 3, 2003 at 8:27 am
Viewing the responses, you 'll notice that you'll have to test your solution !
My default is to put a clustered index on a table and to remove it if it is proven this hurts performance to much. I've seen sql7 databases which had to mutch datapages per table because of the way they were used. Those tables did not have a clustered index and the maintenance-plan did not cleanup thos empty pages. Putting a clustered index on it resolved this problem.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply