(Closing in a few days!)
September Survey – 7 Questions to WIN
We’re giving away not one, but two 2-year memberships to SSWUG.ORG – all you need to do is give us your thoughts about SQL Server and Bananas. No, really. It’s only 7 questions, and you can win…
Seriously, could use your feedback – please stop by the survey site and take our quick survey – I’ll be sure to share the results when the survey is completed in one week.
SSWUG.TV – We’ve unveiled a new name for our video-based content, which will be one of many upcoming site revitalization efforts. In this episode, we have a two-part interview with SQL Azure MVP Ike Ellis and updates coming from the Microsoft Build conference.
[Watch the Show]
SelecTViews – Interview with Kalen Delaney, author of SQL Server 2008 Internals. We also have news about Hitachi Data Systems purchase of BlueArc, the upcoming PASS Summit 2011 and more.
[Watch the Show]
Trigger Tension
I love triggers. They are great tools for handling transition states between one version of software to another as recommended by Ambler in his book Database Refactoring. In the same book he also uses them as methods to enhance third party products, integrating them into your own systems.
What they are not good at is resolving a bad application framework. An application with SQL Sprawl may often result in an over-abundance of triggers overcoming the issues of the architecture. SQL Sprawl is a term I use when the database access code is found all throughout a system, not in centralized logical locations.
It does not matter if you are using stored procedures or parameterized queries, if your SQL is embedded in user screen code it is near impossible to get an overall view of your system logic.
Using a trigger starts out rather innocently. Then the technique becomes a kind of drug, and escalates to where there are triggers on a number of tables, all interrelated. At this point you can no longer trace the sequence of what “Should” be happening in the system.
Today I had the joy of handling one of these scenarios. A customer had simply created the last straw that broke the proverbial camel’s back. They added a trigger to one table rarely updated directly to capture that update. However, they were finding ¼ million updates per minute occurred on this table every minute through a chain of triggers.
Of course you can turn off cascading triggers in the database. But doing that broke the business logic underlying the triggers…they had to remain.
So here is the conundrum; how do you find the triggers, and the sequence of events causing these massive updates? Running SQL Trace works pretty well. I could capture the last trigger in the line, the one on the last table with ¼ million rows. Finding the previous activity causing this update was a more difficult problem because the applications had a combination of stored procedures and inline SQL.
Using SQL Trace I was finally able to resolve the sequence (wish there were a stack trace like in .Net…I could just make the trigger throw an error). It was further complicated by the fact that some of the ancestor triggers weren’t even in the same database, or run under the same process ID, making it more difficult to reduce the noise.
The lesson to take away today is, a word of caution. Too much of a good thing may cause more problems than it solves. In the case of triggers, especially cascading triggers, this is often true.
Share your insights or comments on this or any other topic. It would be nice to hear if you have a technique you have used to track down cascading triggers fired from multiple databases. Send your comments to btaylor@sswug.org.
Cheers,
Ben
Featured Article(s)
SQL Server Dump Sniffer (Part 1)
While it is possible to set up monitoring for SQL Server memory dumps using the different error numbers associated with conditions that result in a SQL Server memory dump, it is also fairly simple to write up a program using .NET’s FileSystemWatcher class to monitor for new .mdmp files.
Featured White Paper(s)
How to Implement an Effective SharePoint Governance Plan
Written by AvePoint
Moving past the "what" and "why" of governance, an even… (read more)
Featured Script
dba3_sysStatistics_sysDBA
not a script just an explanation of the purpose of two databases often used on SS2k and earlier DBMS’…. (read more)