If you've ever taken a look at the auto generated statistics that SQL builds (typically on non indexed columns when you reference the column in a where clause) you'll see that they are named _WA_Sys...blahblah. But why? A quick search didn't reveal any good answer (though perhaps I just didn't ask the right question) and in practice why do we care? We never reference the statistics by name unless we're really deep into a problem.

My reason for ranting is based on an event earlier this week. I was doing an adhoc demo to show a new statistic being created via autocreate when I referenced a non-indexed column. It worked fine, but I wanted to show the result - which meant opening up 5 or so wrong ones before I got the right one. Given that the auto created ones are usually (always?) on a single column, would it hurt to name the statistic something more useful - like the column name? So while I'm complaining, why is it that I can view the statistics (same as dbcc showstatistics) via the properties for stats on indexes, but not for 'plain' stats?

Maybe there's a good reason.

There's a lot of buzz in the development community about LINQ in general, and for those that do data access just as much buzz about Linq to SQL. It's hard to describe in a sentence, but think of LINQ as a way to write queries against .Net objects using an almost TSQL syntax. Linq to SQL uses that same slightly different syntax to access SQL Server, but it does so by building an object layer (think of ORM) that hides the data access. For those of you that are DBA's, the first thing to know is that nothing changes server side, Linq to SQL still emits the same TSQL we know and love!

I've done enough development to appreciate encapsulation and to realize that writing a lot of the data access code is drudgery. Linq eliminates some of that and makes data access more object oriented, but I think it contains a couple serious flaws and one fixable flaw:

• SERIOUS. It supports stored procedures, but it does NOT encourage them. By default it generates dynamic sql directly against the base tables/views. I get that all tools can be used for good or eveil, but most developers will use the defaults because MS must think that is the best way to do it and because it's just easier to use the defaults (what I call in my classes 'vanilla'). We've spent 10 years trying to explain the benefits of stored procedures for performance, security, and and maintenance, and this could easily set us back years in that effort. At the least I'd like to see them offer a step in the wizard that generates or regenerates stored procedures. Even better is to figure out a way to start with dynamic sql in the early dev stage, then make it easy to create and transition to procs later in the cycle. DBA's, this can be lose-lose. If you stop them using dynamic SQL you spend a lot of time/energy on that fight and just make them mad, and if you don't, well, good luck on explaining to the auditors why everyone has select * access to tables.
• SERIOUS. If you proceed with dynamic sql in the data layer there is another flaw that should make the average DBA feel faint. You will not be able to easily answer the question 'what changed' when something goes wrong. Imagine your dev team making some 'minor' changes to code that has been working fine after your last round of tuning. They deploy and 10 minutes later performance drops sharply - what changed, or is it something else? When they send us proc changes it's a deliberate step in the data access cycle that let's us look at what they are changing, compare to the performance of the existing proc, and to make changes to it or to the supporting indexed to maintain performance. When the entire app uses dynamic sql you'll be back to Profiling to figure out what changed. Remember, all it takes is adding one column to a select to change a plan from one that is nicely covered by an index to one that uses a scan or worse.
• FIXABLE. It actually generates reasonably dynamic sql, at least on 1-2 tables in the examples I've seen. It uses sp_executesql and you would expect to get reuseable plans, but the way they generate it for strings the input params are sized based on actual rather than declared length, so you wind up with x number of variations on the same plan. Not as bad as using Exec(), but still not good. Turns out they do the same wrong thing with stored procedures AND they execute the proc using sp_executesql (for the latter I'm not sure there is much wrong with that approach, but doesn't seem right either). MS should be able to fix this easily.

The good news is that with coaching we can make it work and let the developers keep most of the cool new stuff.

It's funny - sorta - how you become used to the quirks of the tools you. I try not to rant too often here on the blog, but seriously, it's time for TSQL to lose the BEGIN/END syntax. It's clunky syntax that I think adds more bugs than it prevents. Think changing it would break too much code? Just leave the current behavior as the default and build in a SET option, perhaps SET BEGINEND OFF, that we can just add at the beginning of a procedure.

Maybe if we can get that one fixed we can also get ELSEIF?