THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Merrill Aldrich

Technical content about Microsoft data technologies. All opinions expressed are purely my own and do not reflect positions of my employer or associates.

Chasing the ISV, or, “That code makes my teeth hurt.” T-SQL Tuesday (ish) #21

Jenga EffectThis month’s T-SQL Tuesday – a blog party dreamed up by’s Adam Machanic ( blog | @AdamMachanic ) – is about that code we’ve all written that we don’t really like to think about too often. You know the stuff. I can’t help but imagine the next poor guy who comes across some of mine and thinks, “What the … How in … Seriously?”

I have two gems to share today. They share the theme, “Chasing the ISV,” because they are both SQL Agent jobs that essentially follow ISV code and constantly, 24 x 7, mop up problems with applications.

The first example I’ll put up here is one I blogged about briefly last year. We had an ISV system that was riddled with pretty horrible triggers, which I was hesitant to touch for fear of the “Jenga effect.” Pull just one line of trigger code out and – who knows?

Said system had a production issue because a value stored in two places, that was supposed to match (winning system here) no longer did. I discovered that the underlying reason was that the tables in play had triggers that could not handle a set of rows, but just one row at a time – a too-common anti pattern.

IF Problem GOTO Workaround

TSQLWednesday_2C948C01So, what to do. Production issue. Deep breath. Here goes:


UPDATE TOP (1) dbo.someTable SET field3 = 'NEW'
WHERE field2 = 'NEW' AND field3 = ''


Into a SQL Agent job with that, executed once every few minutes.

So. Wrong.

Also fixed the issue. And it’s still in use, I believe. I try not to think about it.

IN ( Pain, Suffering )

The second bit of pure awesome I have to offer today takes a bit more explanation. I think I should anonymize this one to protect the guilty. (That first example has, believe it or not, the real column names.)

Another ISV provided a system to one of my past employers that, let’s say for argument, provided a web application to manage distributed offices in locations all over the country. The structure of the system required that people at the company be able to view one or a few of these locations’ data, but not the data from other locations. So the system had a row-level security mechanism to filter the data by groups - a fairly standard idea, but theirs was not a typical implementation at all. The way the initial version of this software operated was, roughly:

  1. An administrator for the application would change some aspect of the security groups using the web application.
  2. The system would, upon clicking Submit, create (or recreate) one or several view definitions, inside of which there was a SELECT with a static IN (,,) list of any and all the locations that a given group or individual could see on the system. The view would provide the row-level security. The IN(,,) list could have a few or hundreds of items. A classic data-as-code anti-pattern.
  3. The system was very busy, so altering the view would often fail or cause … “side effects.”

The company actually instituted a rule that security changes like this could only be made after hours, and made staff stay late to do that work. If this happened during the day, the system would generally grind to a halt, and none of the 1,000 or so concurrent users could do anything. Why? The IN() lists in these views could have hundreds of values, and they worked against millions of rows of data. That meant that with no meaningful stats to work with about these embedded lists, the query optimizer would always pick a terrible query plan for vital processes on the system.

The ISV almost fixed this problem. Nearly.

When complaints surfaced, they went in and added a more scalable feature that would use a many-to-many table to store the security relationships. The table could be indexed, and would have stats, which was wonderful, and it solved the performance issue.

When they used it.

Here’s the rub: the were so fearful about disrupting their customers’ existing deployments – a noble but ultimately doomed posture – that they kept the old view mechanism too. Any old features of the product used the old system, while new or rewritten features used the new system. Now their code had to maintain both systems, and attempt to be sure they matched. Which they did. Mostly. Except that time we had to have one of their developers spend a week untangling the thing. But I digress.

If I recall, and this was a few years ago, so I may have this wrong, it seems like their first table-based implementation didn’t exactly work, and they had to create a second one, and then they kept all three systems, with some massive stored procs to keep them all in sync. And the proc worked, except when there was a logic problem like duplicate rows in the tables, and then it didn’t anymore.


This is where our little DBA group came flying in with our superhero capes and a brilliant, yet horrible, workaround. I can’t quite remember who created this, so my apologies if I cannot credit the specific individual, but I know I, erm, “tuned” it over the years. I also vividly remember the phone calls and the shouting when this workaround was not operating as designed.

We made a SQL Agent job to run every few minutes. The code for the job was, essentially:

  1. Examine the text for all the views in the database, via syscomments, where the view name matched some text pattern that makes it look like one of these security views.
  2. If the view definition contains one of these IN(,,) clauses then rewrite the view, via dynamic SQL, as a join between the security tables.

Like the example above, this job would basically sit there and chase the underlying system, and if a security change was made, it would locate the offending view definition and rewire it on the fly to use the more performant and optimizer-friendly tables.

I think the vendor has since fixed the view definition issue. Happily, I don’t have to work on this system any longer, so I can’t say whether the views themselves persist to this day.

Here’s the code, unvarnished but anonymized. I found it on an old flash drive:

IF EXISTS ( SELECT * FROM syscomments sc1    
   INNER JOIN sysobjects so ON =   
   WHERE so.type ='V'    
      AND LIKE 'FOO\_%' ESCAPE '\'    
      AND sc1.text LIKE '% IN (%)'   ) 
   SELECT     'ALTER VIEW dbo.' + + 
      ' AS SELECT f.* FROM f, bar l ' +
      ' WHERE = ' +
      ' AND = '     +  
       RIGHT(, len( - charindex('_',    
            AS alterViewCommand     
   INTO #viewCorrections     
   FROM syscomments sc1     
   INNER JOIN sysobjects so ON =   
      WHERE so.type ='V'    
      AND LIKE 'FOO\_%' ESCAPE '\'    
      AND sc1.text like '% IN (%)'     

   DECLARE @sql VARCHAR(4000)     

   WHILE EXISTS( SELECT 1 FROM #viewCorrections )    
         SELECT TOP 1 @sql = alterViewCommand FROM #viewCorrections

         PRINT @sql

         EXEC ( @sql )

         DELETE FROM #viewCorrections WHERE alterViewCommand = @sql

       WAITFOR DELAY '000:00:05'

    DROP TABLE #viewCorrections
Published Wednesday, August 10, 2011 8:00 AM by merrillaldrich

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS


No Comments

Leave a Comment


This Blog


Privacy Statement