Database Consulting Group LLC Blog

ftp://ftp.research.microsoft.com/pub/tr/TR-2008-69.pdf

This excellent paper from James Larus at Microsoft Research discusses how software abstractions and raised software expectations have essentially squandered the hardware advances of the last 30 years.  Okay, perhaps squandered isn't the term, but the point is that software doesn't run any faster than it used to.  James points out legitimate reasons as well as less legitimate reasons for this, and looks to the implications of increased parallelism on all of this.

My take: On one hand, I am all for meaningful software abstractions.  I am also for language features that prevent us from shooting ourselves in the foot in obvious ways.  I think we could do much better, however:

• Missed abstractions, particularly relations, has led to excessively complex and bloated software. 
• Primitive data model - Today's software runs in a model based on allocating memory chunks and procedural APIs.  This leads to countless redundancies in the software stack, as well as poor interoperability and optimizability.
• Physical data dependence - by separating the logic from the implementation, we could continually improve software performance without rearchitecting the logic.
• Memory utilization - Garbage collection introduces more problems than it solves.  Value-based semantics and the relational model eliminated the need for GC.  Virtual memory is, in hind-site, a bad idea.  A much more effective architecture is to utilize memory as a cache for what are conceptually persistent data structures (e.g. DBMS architecture).
• Concurrency - again, if the programming model were shifted from references to memory to shared data structures, the issues with thread, process, and network concurrence are already solved.  Much more highly concurrent application are possible in ways that are either transparent or opaque to the developer.

If you've ever sat there for a minute or two waiting for an application to swap back into memory so that it can close, you probably know something is fundamentally wrong with today's architecture.

Posted by Nathan at 10:58 AM | Permalink | Comments (0) | TrackBacks (0)

It may seem that legacy--that is "old style" systems we've inherited or maybe even built but no longer like--is purely something we must live with now and forever.  It seems inevitable that our current systems, languages, and applications are doomed to eventually collect dust and an occasional deriding comment from some new intern who doesn't even know what it is like to boot a computer using floppies.  I say, however, nay!  How can I say this considering that it's always been this way!?  Stay with me.  First let's establish that some things never go out of style.  Like math for instance.  Math hasn't changed much since computers were born, and isn't likely to any time soon... or late.  Philosophers are still trying to decide whether math is somehow part of the universe, or if math is merely a cool abstraction we use to predict things about the universe.  Regardless, it certainly does give us a useful tool for modelling and predicting actual things.  Because of this, it has a timeless nature to it, even though we might change what symbols and tools we use to represent things. 

Computer software abstractions are unavoidably related to math, though in many cases the relation may seem like a cousin-in-law's friend's neighbor once removed.  It's actually this distance from more foundational principles that makes software so easily dated.  In the name of problem solving us Software mechanics have evolved some awfully patched up monsters.  This has been made possible by the kind hardware folks, who have been generous enough to make our obtuse layers of bandage-work actually perform in a reasonable manner (thanks guys).  One of the many prices to pay for so much bad software abstraction is the rapid manner in which our systems become legacy.  Legacy because they are so difficult and fragile to change that nobody wants to touch them.  Legacy because they are so laden with complexity that it is nearly impossible to tease out and salvage the essence as platforms change.  Legacy because we've got a newer better technology wheel we've reinvented and we must rebuild the old systems because now they just look old.

It's all of the non-essence stuff in our systems that makes us have to replace rather than extend or enhance them.  Think of any given system as being made up of two parts:  One part describes the essence of the problem for which the system was created, something like mortgage amortization, modeling customers and sales, or simulating paper documents.  The remainder of the application is a description of how to deal with the other layers of technology, such as the user interface technology, expression of concepts within the programming language, or interoperability with other applications.  The former is sometimes called essential complexity, the latter accidental complexity.  A math formula doesn't become dated, because it contains minimal "fluff" and maximal "content".  Imagine, however, if every few years we renamed all of the math operators, changed symbols, and required filled pages and pages to define simple equations.  It sounds unproductive, but you could probably sell lots of silly little books with cutesy animal pictures on the front of them to help the poor mathematicians keep up.

The closer we get to systems which describe the essence of their mandate, the less likely they are to become legacy.  In fact, it could be argued that as the accidental complexity approaches zero, it becomes impossible for a system to be dubbed legacy as it is merely a model of reality.  Yes, of course the facet of reality being modeled could change, and so our VHS tape collection tracking software may no longer be needed, but that's not the kind of legacy I'm talking about.  That does lead to a point however.  It is the changing "real world" that spurs our need to change our software models.  Given the information age, the "real world" for most domains is changing faster than ever.  The software abstractions, on the other hand, are more complex than ever, demanding incredible amounts of accidental complexity to achieve anything.  The result:  J2EE, .NET with CAB, LAMP... and a burnt out industry unable to recruit young people because the job is [raise pitch of voice] "boring."

The solution is seemingly simple: minimize accidental complexity.  Easier said than done right?  Actually not really, but [stepping up on soap box] we must take advantage of the "mathematics of data" when modelling data problems.  We must understand that everything we do, at all levels in the software stack, is data management.  Missing this fact is tantamount to trying to model physics using basic arithmetic.  We must define applications first and foremost in their "timeless" logical form, as a relational schema.  I spent the last two days dealing with COM programming.  I'll never get these back.  We must stop thinking that API based abstractions are somehow going to solve a problem; think REST, SOAP, CORBA, DCOM, RPC, etc.  Before I hop off of the soap box, let me vouch for myself.  The prescribed approach DOES work and DOES minimize accidental complexity.  I've now built several applications in Dataphor, a platform that only begins to realize the potential of declarative development, and I've seen that an application can truly be created from little more than defining structures which model reality.  If a system is defined in a high-level, declarative way, let the trendy winds howl as they may, that system will easily evolve, and may not ever need to be replaced.

As a quick disclaimer, I am of course not naive enough to believe that the best technology wins and that politics aren't a primary force in the dynamics of why we are where we are.  Nonetheless, I choose to ignore what I can't control and focus on what can be done in the present.  Also note that a declarative softwaretopia is inherently opposed to "hiding" logic.  This has all sorts of interesting technical ramifications, but it also has some interesting business ramifications.  Perhaps the open source movement is laying useful groundwork.

Posted by Nathan at 11:05 PM | Permalink | Comments (0) | TrackBacks (0)

Let me preface this with RANT!!! (just a little frustrated...)

I've heard arguments against structured exception handling in my day, but never any as convincing as what I encountered in SQL Server Management Studio 2005 recently. [insert sarcasm here]

Why bother with all that overhead and extra code when all you really need to do is monitor output messages?

For example, say you wanted to know if a certain UPDATE statement violated a constraint? How could you use this exciting, new technique???

First we need a problem:

"Hello Kitty"??? Any self-respecting developer knows the proper test is "Hello World!"…

So, if someone tries to fix the problem in the UI, how can we ensure the key isn't violated? By monitoring the return messages, of course! If the key hasn't been violated then we should get exactly one "(1 row(s) affected)" message, otherwise, we can pop the following modal dialogue:

"No row was updated.
The data in row 1 was not committed.
Error Source: Microsoft.VisualStudio.DataTools.
Error Message: The row value(s) updated or deleted either do not make the row unique or they alter multiple rows(2 rows).
Correct the errors and retry or press ESC to cancel the change(s)."

Perfect!

Wait a minute. Unless the trigger includes SET NOCOUNT ON the system will "count" the "update" to the view AND the update to the base table…

Oh well, let them eat cake!

Posted by Rob Reynolds at 07:14 PM | Permalink | Comments (0) | TrackBacks (0)

Today I spent several hours troubleshooting a long-running query in a (thankfully) test environment. Yesterday, everything was working fine. In addition, the same executable could be used successfully against a different instance of the database. However, in one particular database, the application would freeze at certain points, sometimes ending in an 'unspecified error', other times in a timeout.

The usual suspect in these cases is blocking, so that's the first thing I looked for. No luck though, the process shows as Suspended, but not blocked by any other process. The wait type for the process is PAGEIOLATCH_SH, but the waittime is always just a few milliseconds. The CPU and IO for the process are off the charts however, and increasing with every refresh. So it's doing a whole lot of reading, but there is no clear indication of what. Even more strange, once I was hung up on a certain point in the processing, other users could get past that point, but would hang up in different spots.

So I trace to find the last query executed by the application and run that in the query analyzer. Sure enough, it returns immediately. So it's reading a huge amount of data, but I only asked for a few rows, what is the server doing? And why is it different from the application versus the query analyzer? So I turned on all SP and query-related events in the profiler, and now the culprit is revealed. An SP:Statement event shows that what is happening is this:

SELECT StatMan([SC0], [SB0000]) FROM (SELECT TOP 100 PERCENT [SC0], step_direction([SC0] over (order by NULL) AS [SB0000] FROM (SELECT SUBSTRING([Image_Back], 1, 100)++substring([Image_Back], case when datalength([Image_Back])<=200 then 101 else datalength[Image_Back])-99 end, datalength([Image_Back])) AS [SC0] FROM [dbo].[CheckItemImage] WITH (READUNCOMMITTED, SAMPLE 6.712329e+001 PERCENT) ) AS _MS_UPDSTATS_TBL_HELPER ORDER BY [SC0], [SB0000]) AS _MS_UPDSTATS_TBL OPTION (MAXDOP 1)

This is happening during a recompile of a view involved in the select. The table in question only contains a few hundred thousand rows, but they are check images, so there is a huge amount of binary data involved, and this statistics query is basically reading the whole thing.

Updating statistics on the database did not resolve the issue, the server still tries to build the statistics to optimize the query. So I turned off Auto Create Statistics, and now the query runs instantly (turning off Auto Update Statistics did not work).

Of course, I don't want to turn off these features, they are great features. And I'm not faulting SQL Server in the slightest on this one. Query optimization is a difficult problem, and statistics are a great way to help prune the set of potential plans, but I will from this point on immediately suspect this scenario whenever I see a process suspended with a waittype of PAGEIOLATCH_SH.

To re-enable these features, I'll need to determine why the stats query is taking so long. I may be able to build those particular statistics manually, or it may involve actually archiving some of the older data in the table.

So why didn't it hang in the query analyzer? Turns out it did if the application wasn't already hanging on the same query, I just never tried that until after I understood what the problem was. I suspect this is because if the server is already computing a particular statistic for another plan, then it will just use the existing statistic or compile the query without statistics. In this case, that resulted in almost instant execution times.

In a production database, this would have meant several hours of downtime. I hope this post helps others that may be seeing the same behavior find the solution more quickly than I did.

Bryn Rhodes
Alphora

Posted by Bryn at 12:48 PM | Permalink | Comments (0) | TrackBacks (0)

Microsoft has a site where user's can give feedback about the user experience of Windows Vista:  http://www.istartedsomething.com/taskforce/

Props to them for being brave enough to open Pandora's Box.  Looking through all of the issues, however, it is clear how completely distinct each of the various applications that make up the user experience in Windows are.  It is clear that each application was built directly against the low-level Windows API at the time, rather than against a higher-level "user interface framework".  Looking at office applications, one can see that Microsoft must have at least some such framework there, but apparently decided to restrict its use to Office.

The point of this posting is that it is often tempting to add new features/applications without abstracting what is common, but this has many costs.  In Microsoft's case, the advantages they gleaned from years of adding new features with minimal impact on existing facets has now accumulated and they are faced with the prospect of having to basically re-write virtually every major and minor user interface of their Operating System.  ...or live with an even higher pile of bandages and tape, which seems likely but isn't going to help them compete with the likes of OSX.

My experience confirms that it always pays to abstract when there are at least two cases of something, but never abstract when there is one.

Posted by Nathan at 11:49 AM | Permalink | Comments (2) | TrackBacks (0)

I'm in the mood for a good rant, so sit back and loosen your face muscles as they might be strained making faces for a while.

First, let me start with a snippet from a Microsoft exec at Tech-Ed:

"...as much as we've expanded the tools and services, the one thing we continue to have is the commitment to a single programming model," Somasegar said. "The saying used to be write once, run anywhere, well we say 'learn once, run anywhere,' because once you learn the Microsoft programming model you can use it to build applications anywhere" on the platform, he said.

Is he talking about the same Microsoft I know?!  The company that has a development methodology for every mood.  Just use Sharepoint to build a... wait no, Infopath because it's XML data and... actually what you need is Expression Blend... er... Web... but no it's going to involve documents so extend Office... wait you're a real developer so you should be using Visual Studio to write VB... er... C#... wait it's a web app so Ruby.NET against SQL Server Express, no Compact Edition... wait the data is coming from different sources, try SQL Server Integration Services, wait, no you need BizTalk... with a slick UI built in ASP.NET... no Windows Forms, er... I mean WPF... using CAB, no wait that's sooo 2 years ago, you need SCSF Contrib, wait Prism with Enterprise Services, hitting web services through WCF, wait, no Entity Framework running on Windows Server, er... Singularity.  Sadly, for every one of the mentioned methodologies, there are dozens of others up Microsoft's proverbial sleeves.

It's great to allow teams to be autonomous and creative to a point, but what ever happened to conceptual integrity?  Is everything really just a subjective mess of competing, "different, but not necessarily better" technologies, or is there merit in actually throwing out some ideas, even when the customers are asking for them?

Why do we have so many competing ideas, and so little conceptual integrity?  I blame two major things: missed abstraction and solving the wrong problems.

Missed Abstraction

Somewhere in the shuffle we discovered addition, then went straight to solving calculus problems.  Without multiplication, algebra, geometry, and maybe some trigonometry, we're bound to flounder about in a pool of boggling complexity.  What did we miss?  The mathematics of data: the Relational Model.  No, not SQL, no, not even "Truly" Relational DBMSes, but relations as a first class part of real languages.  Instead we have "collections", "lists", "dictionaries" and other such low level primitives.  Bear with me.

We've decided that "data" is this stuff we pull out of database systems, exists for a while in some client, then is pushed back into the system for safe keeping.  But what of everything else in the computing environment?  The controls on forms, the images, messages, documents, hardware state.  No, we've decided, that's not data.  We have different systems for that stuff, involving complicated structures of 3gl languages.  Surely we wouldn't want to "normalize" it down to inefficient table structures and stuff it into a database system, nor would we want to stream it in it's encapsulated form into a database system BLOB where we couldn't query or manipulate it's parts anyway.

My friends, the database system is the worst thing that ever happened to the Relational Model.  The RM is nothing short of the mathematics of data, and a DBMS is just one kind of calculator.  SQL Systems of today are perhaps abacus' in the analogy.  Not only is SQL about the worst imaginable embodiment of the Relational Model, the notion of Database Systems in general has crippled our thinking about how to manage and think about data.  Relations should be an integral part of every modern language.  Queries should just be ordinary expressions of the language.  "Database Systems" simply become one kind of runtime in which the data can be shared and stored persistently.

The Relational Model gave us a) a formal basis for declaratively managing any kind of data; and b) a way to talk about logical things independent of physical things.  We've hardly begun to tap either of these benefits.   Consider every variety of application that doesn't use a general purpose DBMS, from Word Processors to Games.  Now consider the problems faced by the engineers of these applications including: concurrency, persistence, durability, integrity, remote access, consistency, querying, security, data manipulation, scaling up, and scaling out.  Everything... EVERYTHING we do is data management.  A conceptual model which raises the level of abstraction in which we can do data management is going to help us!

Solving the wrong problems

We don't need another method for invoking functions remotely.  That was not the problem.  Just look at SOAP.  We're encoding a procedure call into a highly inefficient language designed for text mark-up, hailed as unreadability, for the sake of human readability (XML).  Then we are sending that information to an HTTP server, which is nothing less than a protocol for invoking remote functions.  In return, we'll either get an error from HTTP or we'll get a SOAP response containing the error or results in XML format.

We don't need garbage collectors to sweep up all of our abandoned pointers.  GC doesn't actually solve problems such as memory leaks and seems to succeed only in limiting the scalability of applications.  I know I just offended some sensibilities, but I've been down some long an painful roads for this perspective.  Besides with proper utilization of value semantics, we wouldn't be so concerned with heap management.

We certainly don't need a another "post-relational" DBMS based on ideas that predate the Relational Model.  Though languages such as SQL seem ancient, the relational model will not become old any more than the notion of an integer will.  Okay, think of it as old and wise if you must.

We don't need more integration "solutions".  ETL has been done... and done... and done.  Nor is it the "answer".  If there was one way to look at all data, then suddenly integration looks no different than construction.  I'm talking about federation... another thing enabled by our friend the Relational Model.

We don't need another registry, repository, directory, or other pseudo database system.  These things have been around since the dark ages of computing and they've always been a blight.  The operating system itself should provide a consistent data management platform; used all the way from the core of the OS, all the way up to the highest levels of application logic.  The data structure of today's operating system is without a doubt the C struct.  The data structure of the future OS is the Relation.  Picture the Woodstock of software, with applications and systems holding hands and singing the same tune.  Picture full visibility into every code and data facet of every application and system.  Now *that's* open.

We don't need yet another method of querying, sorting, searching, and presenting data.  How many times are we doing to have to work out the logic to refresh forms properly at all the right points?!  Even still, people remain skeptical that such things can be automated; that it is somehow different from application to application.  I assert that it is not different.  Even the cleverest human interaction examples are mere specializations of the same, age old, patterns for the presentation and manipulation of data.  Yet developers are handed framework after framework with gaping holes where data presentation and manipulation happens.

Conclusion... finally! 

I've been accused of often being preachy and leaving people on a downer.  I realize that I am guilty of both of these things in this posting.  Frankly I'm not sure how to fix that other than to re-assure the reader that I hardly consider the situation hopeless.  As long as I am able, I will be working towards solving these issues.  In fact, I must admit a certain joy I receive in seeing such large scale problems because I believe that they are fixable, and I thoroughly enjoy working towards a solution.

Posted by Nathan at 11:58 PM | Permalink | Comments (2) | TrackBacks (0)