April 8, 2009 at 7:22 am
Hi all,
We had a slow running program last night, so I got the statistics for all the indexes and defragmented the indexes above 40%. There are 2 tables with AVG FRAG IN PERCENT as 27.01 and 29.80. Could it be because there is no clustered index for these 2 tables?? Will it make it any better if we add a clustered index to these tables?? Also, we dont have any primary key fields in the tables, so I'm not sure if it is possible to add clustered index...
Please give me your suggestions.
Thanks you very much for your support
April 8, 2009 at 7:40 am
could be related, may not be. All depends how the application/jobs uses the table. But generally all your tables should have a clustered index that fits how it's generally queried. And your clustered does not "need" a key... Can be done without.. but be careful & test it.. you don't want to go backwards in performance 🙂
April 8, 2009 at 7:47 am
Thanks Jamie. The tables were designed long time back and I'm not sure why there is no clustered index. There is 2 non-clustered indexes for this table:
Tablename: EST_WRK
Columns: city_code, media_rate
Non clustered Index 1: on est_wrk ( city_code, media_rate )
Non clustered Index 2: on est_wrk ( media_rate, city_code )
Is it possible to create clustered index for this table?
Thanks much.
April 8, 2009 at 7:52 am
The key part of your post is "...slow last night".
How has this process behaved in the past?... ask these questions to yourself:
1) Does this process always takes the same amount of time to run?
2) Is this process performance getting worst over time?
3) Did it process the standard volume of data?
4) Was any "special" process running at the same time?
5) There are new processes deployed on the box/database?
6) There are any configuration changes?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.April 8, 2009 at 7:58 am
Paul,
This table is not accessed by the program that ran last night. While collecting statistics for all the indexes, I happen to find this problem that there is no clustered index on these 2 tables and hence its fragmented. But these tables could be accessed by other programs, I'm not sure wihch ones. My client have asked, why we are not using clustered index on these tables, may be thats the reason its fragmented..
Thanks again.
April 8, 2009 at 8:15 am
psangeetha (4/8/2009)
Paul,While collecting statistics for all the indexes, I happen to find this problem that there is no clustered index on these 2 tables and hence its fragmented.
Sangeetha,
Tables gets fragmented due to many reasons not because it doesn't have a clustered index. If you have a clustered index on the table and there are lot of DML operations then due to page splits the fragmentation will occur
But these tables could be accessed by other programs, I'm not sure wihch ones. My client have asked, why we are not using clustered index on these tables, may be thats the reason its fragmented..
No definitely not, fragmentation and clustered indexes are totally different. Clustered indexes are a complimentary to your search queries and these should be maintained regularly according to your DML operations.
and also before we worry about fragmentation what is the size of your table that is having fragmentation? What is the table structure and the regular queries that are running against this table? If you post them then someone would be able to assist you.
April 8, 2009 at 8:23 am
That was fast Krishna 😀 100% agreed.
Sangeetha --
I still think you have to ask those questions to yourself -see my previous post.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.April 8, 2009 at 8:24 am
Thank you for your reply.
The table structure is as I said above, has 2 fields with 2 non-clustered indexes. The table has 1.5 million records and it has been hit by a lot of programs. So, may be if we could defrag the table, the performance can be improved in those programs?
Thanks
April 8, 2009 at 8:38 am
psangeetha (4/8/2009)
Thank you for your reply.The table structure is as I said above, has 2 fields with 2 non-clustered indexes. The table has 1.5 million records and it has been hit by a lot of programs. So, may be if we could defrag the table, the performance can be improved in those programs?
Thanks
Yep, sure you can defrag your tables, or you can rebuild them depends on what you chose as a rule of thumb 30% rebuild them. Depends on your business model and how much you can accept
Let me ask you, how did you identify that there is a performance hit on your applications? What kind of impact do they experience?
April 8, 2009 at 8:38 am
PaulB (4/8/2009)
That was fast Krishna 😀 100% agreed.
Thanks Paul that was kind of you 🙂
April 8, 2009 at 9:31 am
Thanks Paul and Krishna.
Our client has been facing this problem on a day to day basis(this is not the slow running program I wrote earlier). We found these 2 tables while rebuilding all the indexes when we worked on the performance issue for the earlier program. But apparently we came up with these two tables that are fragmented and this table est_wrk has been accessed on the software GUI for a lot of programs and all of them takes a few seconds to minutes to come up. So this has been there for a while like this but never looked into.
Also, I just found there is a view estwrk_vw created on this table as well with 1.5 million records and there is no index on this view. From the front end, the software gets the data from this view. Right now, it is doing a sequential search on estwrk_vw and bringing up data on the GUI. I guess, it makes sense to create an index on estwrk_vw??
Thanks again
April 8, 2009 at 10:16 am
Doing so much like creating index and rebuilding and all that stuff. But did you actually checked the execution plan of the query which is getting fired and how is it performing slowly.
Heap tables really really dont have that much of advantage than cluster index. Exceptions were it might be helpful.
April 8, 2009 at 10:48 am
psangeetha (4/8/2009)
Right now, it is doing a sequential search on estwrk_vw and bringing up data on the GUI. I guess, it makes sense to create an index on estwrk_vw??Thanks again
Humm.. yes you can create index on a view but with a lot of restrictions, you should be very careful. If your base table is highly transactional then its not a good idea and also the indexed view should be schema bound which makes it hard for you to make any changes to the underlying table and there are couple or restrictions check out books online.
Instead I would advise you to look at the base table itself. Look at the queries that are frequently run against this table, tune them first look at the execution plans and see whether the indexes are being used or not. You can post the plans someone should be able to help you out. And after you create proper indexes then you can analyze how frequently its getting fragmented and accordingly run a job either to defrag them or rebuild them. This would be the right approach.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply