February 6, 2009 at 9:46 am
If a heap table is created for audit purposes is there a reason to put a clustered index on the table?
I have an application with several hundred audit tables ranging in size from 10 rows to over 16 million rows. None of these tables have clustered indexes, only non-clustered primary keys.
Mostly inserts are performed on these tables, sometimes select statements are performed.
Lately the application has been having performance issues, I was wondering if putting clustered indexes on these tables would improve performance.
Any comments would be helpful. Thanks.
February 6, 2009 at 10:42 am
There is a really great blog post by Kimberly Tripp here and here. It would be worthwhile to read these and consider the things that are stated. Most times people avoid the clustered index as they think that the inserts are actually faster without them but Kim has a great discussion on that in the first like I provided. I can't begin to speak to that level so, it is better for you to read them.
Also please note the characteristics being Unique, Static, Narrow and Ever Increasing that she provides in the second link that I included.
My guess is that if you were to define the proper clustered index you would see some improvement.
One question to go along with this though, when you speak of performance degrading, is it in regards to the inserts or the selects from these tables?
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
February 6, 2009 at 10:46 am
David,
Thanks for your response. I will take a look at the blog you recommended. In answer to you question, I am trying to pinpoint the performance issue and this is just something I came across in my investigation. Not really sure what the issue is.
Thanks for the input.
February 6, 2009 at 10:48 am
Sorry, just trying to understand a bit though, are you getting complaints about performance as part of the audit data collection or as part of the "reporting" of that data? That would certainly help narrowing down future recommendations.
Thanks!
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
February 6, 2009 at 10:57 am
This is an application for higher education where students can go online to drop or add courses. Sometimes when trying to drop or add a course, the system hangs and no one can perform these functions. The only solution we found is to reboot the server. Obviously this is not good practice and we are trying to find the underlying cause.
Usually when any function is performed (droping or adding courses), the information is logged in these audit tables. That is what led me to investigate the audit tables to see if the process of inserting into these tables was causing the process to hang.
I have been running traces against the system but have not been able to see anything unusual, and we have the vendor looking into the issue as well. So far no answers to our problems.
I hope this narrows it down some more.
February 6, 2009 at 11:39 am
I've done a lot of work on audit logging. (Even wrote articles on it for this site.) Clustered indexes based on the insert sequence can definitely help with insert speed in many cases. It can also create a "hot spot" if too many processes try to insert too many rows at the same time, which can cause its own problems.
The best performance I've gotten out of audit logs of this sort is from having two tables. One for immediate inserts, one for archived data. Keep the immediate inserts table as small as possible by archiving daily or hourly (depending on activity). Sometimes a heap performs really well for that kind of high-speed, high-concurrency inserting, sometimes a clustered index on an ID column or on a time stamp is best. Depends on the level of activity and a couple of other factors.
On the archive table, I keep a number of indexes, definitely including a clustered index. On the immediate-insert table, no indexes except possibly a clustered index, no constraints, no foreign keys, etc.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 6, 2009 at 11:39 am
Any errors in the SQL Server Log or the application / system log for Windows? Very curious.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
February 6, 2009 at 11:44 am
Just to help but I believe the articles that GSquared was referring to was this[/url] and this[/url].
Please correct me if I am wrong.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
February 6, 2009 at 11:48 am
Yep, those are the ones.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 6, 2009 at 11:55 am
GSquared - Thanks for the reply. It was very helpful. I will check out your article for more information.
David - No errors in the Event logs.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply