October 12, 2005 at 9:32 am
I have searched for this answer (nothing satisfactory found) but would like to hear from experience if possible.
My client has a serious performance issue in a reasonably large database (~30Gb, SQL2000). What I have discovered is a clustered index on a table with 8 million rows - and inserts into that table 24/7! I suspect dropping the clustered index will help tremendously (and of course replacing it with a non-clustered index). I don't have the schema yet, this information is based on conversations with someone in their IT department.
At any rate, what will be the impact of dropping the index????? - this is a live system - never down! By impact, I mean "would this cause a catastrophe - i.e. NO data access for a period of time". They can live with horrible performance while the non-clustered index is built after the drop. I just don't want the database to completely "disappear" to the users during this adjustment while SQL server is "thinking".
Sorry for rambling...
David
October 12, 2005 at 9:47 am
I can't see any catastrophes on the horizon, but you should of course write the appropriate script and run it on a backup copy of the db just to make sure - with users accessing the db to simulate what is happening. You could also try increasing the fill factor on the clustered index and seeing whether this improves performance. Other people here will have more experience of this than I ...
October 12, 2005 at 9:47 am
What is the performance issue the customer is experiencing?
is it slow inserts?
is it slow updates?
Slow queries?
you need to be more specific.
the Choice of which column to use as clustered index is important, and just by switching it to a nonclustered index may not provide you long term benefits.
Depending on what/where the issues are will depend on what you should do.
If your having slowness on inserts is likely that the clustered index is on a non primary key column, and when new rows are inserted its causing Page splitting. Just redifine the clustered index with a fill factor of 70, (You may have to play with this value.) If queries are slow, you should analyze each query independently and see why they are slow.
Also index health could be contributing to the slowness. Make sure that the table/indexes are compact and not fragmented, Use dbcc showcontig to analyze the table and indexes.
The shotgun approach (just arbitrarily switching the index to nonclustered) in this situation may not be a good idea. You will mess with all existing query plans.
to change the index to nonclustered, you need to drop the existing index, and create a new index nonclustered on the same column. But with the table having a ton of records it may take a little time. Dropping the index will not "Bring down" the database querys will be slow until you get another/correct index on the column.
October 12, 2005 at 10:03 am
Specific to Ray's comments:
The performance issue is "as the end user sees it". The application has 2 components: one component that does inserts all day long (I don't have any metrics on these inserts) and the other component for retrieval (end user reports via intranet web app). No updates are done to existing rows.
I hadn't thought about the execution plan impact (thanks) and you are right about this being a shotgun approach - this comes from my lack of experience and lack of information from the client (db schema and some profile data) - but that's why I'm here.
Also, I didn't think about the Fill Factor that you and Phil both mentioned.
Thanks for the tips so far. I hope to get more information from them later today.
D
October 13, 2005 at 6:58 am
It's too early to talk about any solution without knowing the root cause of the issue. As Ray said, you need to know what action(s) is slow, insert or query (no updates based on your post)? They may caused by blocking if the query and inserts both have heavy load. In fact, page split does not cause big performance issue. What transaction isolation level is the webapp using? If the phenomena is both reading and inserting are slow, it is probably caused by blocking (reading and inserting blocks each other). This is very common in a database that has heavy read and insert load. You can try to change the isolation level to read uncommitted if applicable or use table hint (nolock).
Becareful to drop the clustered index. It's better to check whether the table has other indexes or not. When you drop the clustered index, all non-clustered indexes will be rebuilt, which may take a long time than you expected. And if the table has lots non-clustered index, that may be the reason that slows down the inserting. You can drop those indexes that are not abosulotely necessary.
October 13, 2005 at 7:08 am
Are there secondary indexes on this table? Dropping the clustered index will cause all other indexes on the table to be dropped and recreated. This will seriously interfere with access to this table for a period of time, although maybe not block it completely.
I would definately look into the fill factor and fragmentation issues before dropping the index. Keep in mind that it is very difficult to tackle fragmentation if they insist on 24/7 availability. You've got to have a regular maintenance window.
October 13, 2005 at 10:33 am
I think you wanted to say that if clustered index is not on identity column that can cause a lot of page splitting. Anyway, I agree we need much more info to give a good recommendation.
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply