Have you seen this performance issue in 2k5/2k8?

Nov 6, 2008 at 11:59 PM
We use a very similar system for auditing (kudos for automating the generation process) but are still primarily a SQL2k shop. The update triggers generated by our scripts (I cannot take credit, they were written by a rather good architect that is sadly no longer with us) operate identically with respect to the use of IF UPDATED (... What we have found in a recent deep dive into performance tuning is an issue of the triggers recompiling every time. I am still in the process of testing, but am 95% confident it is the IF statements that lead to the recompile (we removed the IFs and let audit insert for every column and the recompiles all but disappeared in testing). With tables of over 30 columns, we are finding significant impact (more columns = more statements in the trigger = more compile time). This was compounded by the fact that most inserts in the true OLTP databases (submitted orders, invoices, etc) are by necessity wrapped in a transaction along with other inserts into supporting tables, therefore holding and sometimes escalating locks on the audit table as well as unnecessarily extending the locks held on the base table that is being audited. We have a couple of different solutions we are playing with and was hoping for some insight into how this works for you on SQL2005/8 to guide our efforts as we are making the jump with our next project.
Dec 3, 2009 at 10:56 PM
mtclassen wrote:
We use a very similar system for auditing (kudos for automating the generation process) but are still primarily a SQL2k shop. The update triggers generated by our scripts (I cannot take credit, they were written by a rather good architect that is sadly no longer with us) operate identically with respect to the use of IF UPDATED (... What we have found in a recent deep dive into performance tuning is an issue of the triggers recompiling every time. I am still in the process of testing, but am 95% confident it is the IF statements that lead to the recompile (we removed the IFs and let audit insert for every column and the recompiles all but disappeared in testing). With tables of over 30 columns, we are finding significant impact (more columns = more statements in the trigger = more compile time). This was compounded by the fact that most inserts in the true OLTP databases (submitted orders, invoices, etc) are by necessity wrapped in a transaction along with other inserts into supporting tables, therefore holding and sometimes escalating locks on the audit table as well as unnecessarily extending the locks held on the base table that is being audited. We have a couple of different solutions we are playing with and was hoping for some insight into how this works for you on SQL2005/8 to guide our efforts as we are making the jump with our next project.

 Talking about speed, What is a good round number to consider archiving audit data and dropping records?  I would imagine a well oiled server could handle millions of records.  I was just wondering.  I just activated the audit package today.

 

Rudy Hinojosa

Coordinator
Dec 4, 2009 at 11:51 AM

Hi Rudy, I’ve been working a contract in Boston this week, but I have read all you emails. This one in particular is interesting. As soon as I get a few extra hours I want to polish and release the next version of autoaudit.

-Paul

From: rudyhinojosa [mailto:notifications@codeplex.com]
Sent: Thursday, December 03, 2009 10:48 PM
To: pauln@sqlserverbible.com
Subject: Re: Have you seen this performance issue in 2k5/2k8? [AutoAudit:39392]

From: rudyhinojosa

mtclassen wrote:
We use a very similar system for auditing (kudos for automating the generation process) but are still primarily a SQL2k shop. The update triggers generated by our scripts (I cannot take credit, they were written by a rather good architect that is sadly no longer with us) operate identically with respect to the use of IF UPDATED (... What we have found in a recent deep dive into performance tuning is an issue of the triggers recompiling every time. I am still in the process of testing, but am 95% confident it is the IF statements that lead to the recompile (we removed the IFs and let audit insert for every column and the recompiles all but disappeared in testing). With tables of over 30 columns, we are finding significant impact (more columns = more statements in the trigger = more compile time). This was compounded by the fact that most inserts in the true OLTP databases (submitted orders, invoices, etc) are by necessity wrapped in a transaction along with other inserts into supporting tables, therefore holding and sometimes escalating locks on the audit table as well as unnecessarily extending the locks held on the base table that is being audited. We have a couple of different solutions we are playing with and was hoping for some insight into how this works for you on SQL2005/8 to guide our efforts as we are making the jump with our next project.

Talking about speed, What is a good round number to consider archiving audit data and dropping records? I would imagine a well oiled server could handle millions of records. I was just wondering. I just activated the audit package today.

Rudy Hinojosa

Read the full discussion online.

To add a post to this discussion, reply to this email (AutoAudit@discussions.codeplex.com)

To start a new discussion for this project, email AutoAudit@discussions.codeplex.com

You are receiving this email because you subscribed to this discussion on CodePlex. You can unsubscribe or change your settings on codePlex.com.

Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at codeplex.com

Dec 9, 2009 at 9:07 PM

Hi Paul,

I was just talking to the developers on my team on Monday about implementing a generic and consistent method for auditing using triggers which would catch all value and object changes in the database.  AutoAudit looks perfect for this and so I'm excited to implement it.  Based on your comment below though, I was wondering if you have an ETA for releasing that next version of AutoAudit?  I am going to need to customize AutoAudit some to fit our needs and am trying to determine if I should hold-off if your next release is imminent.

If it's soon (next month or so), but not imminent (next week or so), I will move forward with the current version and probably plan to upgrade.  In that case, do you know if the updates to the new version will be very substantial?  I;d be planning to migrate to that new version once you release it if it seems feasible for our timelines.

Thanks for this very high quality work!
Seth 

 

PaulNielsenSQL wrote:

Hi Rudy, I’ve been working a contract in Boston this week, but I have read all you emails. This one in particular is interesting. As soon as I get a few extra hours I want to polish and release the next version of autoaudit.

-Paul

From: rudyhinojosa [mailto:notificatio ns@codeplex.com]
Sent: Thursday, December 03, 2009 10:48 PM
To: pauln@sqlserverbible.com
Subject: Re: Have you seen this performance issue in 2k5/2k8? [AutoAudit:39392]

 

From: rudyhinojosa

mtclassen wrote:
We use a very similar system for auditing (kudos for automating the generation process) but are still primarily a SQL2k shop. The update triggers generated by our scripts (I cannot take credit, they were written by a rather good architect that is sadly no longer with us) operate identically with respect to the use of IF UPDATED (... What we have found in a recent deep dive into performance tuning is an issue of the triggers recompiling every time. I am still in the process of testing, but am 95% confident it is the IF statements that lead to the recompile (we removed the IFs and let audit insert for every column and the recompiles all but disappeared in testing). With tables of over 30 columns, we are finding significant impact (more columns = more statements in the trigger = more compile time). This was compounded by the fact that most inserts in the true OLTP databases (submitted orders, invoices, etc) are by necessity wrapped in a transaction along with other inserts into supporting tables, therefore holding and sometimes escalating locks on the audit table as well as unnecessarily extending the locks held on the base table that is being audited. We have a couple of different solutions we are playing with and was hoping for some insight into how this works for you on SQL2005/8 to guide our efforts as we are making the jump with our next project.

Talking about speed, What is a good round number to consider archiving audit data and dropping records? I would imagine a well oiled server could handle millions of records. I was just wondering. I just activated the audit package today.

Rudy Hinojosa

Read the full discussion online.

To add a post to this discussion, reply to this email (AutoAudit@discussions.codeplex.com)

To start a new discussion for this project, email AutoAudit@discussions.codeplex.com

You are receiving this email because you subscribed to this discussion on CodePlex. You can unsubscribe or change your settings on codePlex.com.

Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at codeplex.com

 

Coordinator
Aug 12, 2010 at 2:49 PM

I removed the IF UPDATED() in version 1.10b