Covering Indexes

A Covering Index is one of my favorite performance techiques. B ascially - a covering index is one that completely covers the needs of the query without having to resort tot eh dreaded bookmark lookup. This last weekend I made a ScreenCast about Covering Indexes. Enjoy! And if you have any suggestiosn or requests for more SQL Server ScreenCasts, email me.

Carbonite!

I've been playing with a new utility - Carbonite! - and I love it. Bascially it's a background automatic file backup utility that slowly backs up all your files over the internet. If you're connected it's backing up. I can't tell it's running. This solves a huge problem for me since making backups is such a pain for independent IT workers. So I encourge you to try it. The first 15 days are free, then it's $5 a month no matter how many files it backs up.

Colorado PASS Camp

What a Day! 87 attendees, 16 speakers, 2 MVPs, 20 presentations, 25 large pizzas, an XBox 360, an iPod, and an MSDN subscription all at the Microsoft officies in Denver celebrating the SQL Server community in Colorado.

It's Done

SQL Server 2005 Bible is done; I turned in the last element last Friday and danced around the house. 54 chapters. 1418 pages. A publishing innovation I'm playing with is online ScreenCasts to augment the book's text and screenshots (Wiley's been great about letting me try new ideas). These are short 2-5 minutes videos that demonstrate a specific task or concept. I've posted the first one - Viewing Deadlocks using Profiler. About 50 ScreenCasts are scheduled in all. And if you have any suggestions or requests, send'em this way.

A glutton for punishment, I just signed to write another book. This one's on another passion of mine - merging object-oriented technology with the relational database engine. I've put about a year into developing an O/R Hybrid design in SQL Server. It has polymorphism, inheritance, code generation, complex association mapping, and first class objects - all hidden behind a T-SQL façade. The book will be out at the end of this year by Manning Publications, but you can play with the code as I work through the iterations.

An exciting project is the new Advanced Design and Optimization workshop's that's been through one beta round and about to go live. I love working with a small group and I've packed all my favorite database ideas into this one workshop.

May is a busy month and if you're in Colorado I'd love to see you at the Colorado PASS Camp, May 17th. I'm giving the keynote presentation on Performance Decisions. O/R Hybrid Designs is my topic at the Front Range Code Camp. And I'm presenting Data Modeling Key Decisions and Optimization Theory at DevTeach in Montreal. If you can't make it to one of these events, visit you local PASS Chapter.

As always, if you want to talk about SQL Server or database architecture, send me an email. I'd love to hear from you.

Complexity Kills

In today's New York Times there's a story about Microsoft's problems and Vista's delays, Windows Is So Slow, but Why? In the middle of the story is a quote that caught my eye, In an internal memo last October, Ray Ozzie, chief technical officer, who joined Microsoft last year, wrote, "Complexity kills. It sucks the life out of developers, it makes products difficult to plan, build and test, it introduces security challenges and it causes end-user and administrator frustration." I couldn't agree more. I strongly believe the number one reason why database projects fail, and the number one reason for poor performance, is an overly complex schema. In the name of normalization, data modelers who can't write a query to save their life develop horrid schemas with several times the necessary tables. Chapter one of my next book is about the Information Architecture Principle. In unpacking the principle, I believe the first attribute of a database is simplicity. It may seem an oxymoron to begin a 1400 page book about such a complex development tool as SQL Server 2005 by writing about simplicity, but without simplicity as a primary goal, I believe your database project , and the DBA who come after you, will suffer. When designing database schemas, every table you can eliminate and still meet the requirements is a victory to be celebrated. Sorry to get so passionate, but I've seen too many friends labor with systems that should be out of our misery. It's a shame. Truer words have not been said, Mr. Ozzie, Complexity Kills

Colorado PASS Camp is filling up

There's lots of great news about the upcoming Colorado PASS Camp! We heard today from Microsoft that the Colorado PASS Camp registration is almost full - 85 of the 100 slots have been spoken for. SQL Sentry has donated an XBox 360 for the solutions contest. And,the speaker schedule is almost full with an excellent line-up of local experts, I expect to be announce the speakers and their topics by the end of this week.

The Terminator;

SQL Server 2005 - big changes - little changes. One of the little changes that's been a big change getting used to is the statement terminator, the semicolon. I've been forcing myself to place semicolons after every statement in my code, and going back through old code and adding the semicolons. They say that 21 days with a new behavior creates a habit. Maybe;

I have learned a few things about statement terminators;

1) You must place a semicolon at the end of the statement before a Common Table Element (CTE), otherwise the parser doesn’t know the WITH is starting a CTE and the batch will blow up like a bad action movie pun;

2) You can not place a statement terminator after the IF condition. Just treat the IF Condition -Next Statement as one long continuous statement;

3) You can not place a statement terminator between a END TRY and a BEGIN CATCH. Those two statements seem function as one single statement;

Right now statement terminators are optional, will they become required, maybe. I hear that one reason we don't have intellisense is that it's difficult to parse half written T-SQL without a statement terminator. Given the choice - I'd take required semicolons if that means we get intellisense;

I'll be back;

The Where Clause

This morning Chuck Boyce invited me to co-host his podcast show, the Where Clause. What a blast! We talked about a few cool web pages , articles, and blogs, and then Chuck asked me about my O/R dbms research, and Optimization Theory. Look for the March 1st episode of the Where Clause

In other ground breaking news, today is March 1st - the public announcement for the Colorado PASS Camp - May 17th in Denver. We'll have a signup web page soon. Many thanks to Chris Shaw and Mike White for their help organizing this day of SQL.

Tom Rizzo's book Pro SQL Server 2005 with co-author Adam Machanic, is a good book if you already know SQL Server 2K and you want to get up to speed quickly. I recommend it.

Today, I'm wrapping up the author review for the Management Studio chapter for SQL Server 2005 Bible. After this only 10 chapters left to review. The end is in sight. If I get time I'd like to create a Camtasia screen cast on Using Management Studio.

The Cursor is Dead! Long Live the Cursor!

Adam Machanic is one of my favorite MVPs - I like his books, he's always willing to help in the newsgroups, he speaks well at the conferences, and when he has an opinion, I pay attention.

SQL Server 2005 Bible will have a chapter called "Kill the Cursor!" that works through a complex logic problem and solves it 7 different ways - 4 cursors and 3 set-based solutions. For complex logic, set-based solutions rule; but not so for cumulative totals.

In some text for a future publication, a tech reviewer disagreed with me when I said that cumulative totals (running sums) were best done with a cursor. The tech reviewer listed what appears to be an elegant correlated subquery that does in fact do running sums. The assumption was that set-based was always faster than a cursor.

Adam was on the thread and volunteered to run the tests...
Adam's Blog

SQL Down Under

At the European PASS Conference last week in Barcelona I sat in on Greg Low's session on Avoiding Recompiles in SQL Server 2005. Greg is the host of SQL Down Under and true SQL Server guru.

One of the key enhancements in SQL Server 2005 is statement level recompiles. I wrote about this in my TechNet article. Greg made the observation that with SQL Server 2005 you may see more recompiles but each recompile is much cheaper. He demonstrated recompiles and missed cache hits using SQL Profiler and explained in detail what causes recompiles and cache misses.

If you haven't listened to Greg Low and his SQL Down Under net radio show - I invite you to take the time to enjoy Greg and his guests. Ausie Ausie Ausie Oy Oy Oy!

Barcelona!

I'm here in Barcelona for the PASS European Conference. The city is wonderful - full of Gaudi architecture and easy to navigate. My wife, Edie, has been exploring the city while I'm at the conference. I enjoy hearing the mixture of the languages and meeting folks from all over Europe.

The PSS Boot Camp pre-con day had deep presentations by Ken Henderson, Bart Duncan, and Bob Ward. I especially enjoyed Bart's session on Query Optimization. Tomorrow morning I'm presenting What's New in T-SQL.

On an edible note, the profteroles at Il Golfo di Napoli are a light cream pastry covered in melted chocolate. Amazing. The best desert we've had in Barcelona - right around the corner from the conference hotel. We held our Board dinner at Los Caracoles in the Gothic quarter - very cool resturant!

Of Endpoints, CTPs, and Virtual PC

I’ve spent the better part of this week working with HTTP.sys endpoints in Yukon – well trying to. On my notebook nothing I tried would create the endpoint. On other machines the code would run great but not on my machine. I learned that XP SP2 still uses IIS 5.1 which listens directly and therefore can conflict with the kernel HTTP.sys when also listening on the same port. Nevertheless, switching ports did not solve the problem. I suspect that the problem is that I’ve uninstalled and reinstalled various CTP versions of Yukon one too many times. So running a clean new copy of Windows Server 2003 and the June CTP under Virtual PC is the only way I’m moving forward. And in this configuration, the HTTP endpoints run smoothly.