SQL Server 2005 rantings - User Defined Aggregate Functions are nice, but not there yet...

1. Why can't there be UDA's in T-SQL? Granted, it's easy to write it in CLR, but sometimes it would be simpler (and more appropriate) to write it in SQL. It also took me a while to figure out that indeed there is such limitation...

2. UDA's must be serializable. Why? I don't know yet (still need to figure that one out), although I have some ideas, but anyway it's besides the point - it's a must and I assume there are good reasons for that. The problem is that whenever you're doing something slightly more complicated than just an average or Product, you need to accumulate all the values until you get to Terminate() (e.g. a variation on STDEV). This means that this list you've just accumulated could grow significantly. Now to the pitfall - when you use user-defined serialization (which you would have to in this case), you must specific the maximum size that the UDA structure could grow to. This maximum size is limited to 8000 bytes (*sounds familiar...). So in my case, I'm using a UDA over double values, and thus I'm limited to aggregating a little below 1000 records. IMHO this reduces the practical usage of UDA's to about 50%...

3. I tried to write a UDA for decimal data. No matter what I did, it constantly produced a function defined to return decimal(18,0). In other words - no decimal numbers to the right of the dot. In the end I didn't have the time to find out the KB article talking about it, but I suppose there is - I pretty much tried everything. In my particular case using double values was an acceptable compromise - it won't always be that way...

Chain Letters Are Worse Than Viruses

Yesterday I got once again a Chain Letter. In case you don't know - I HATE CHAIN LETTERS!!!

This time, it was a pseudo virus alert. When I complained with the sender, urging her to stop sending me chain letters, she said: "But it's a VIRUS alert! I can't take the chance you'll miss it!"

What people don't understand, is that if 50% of the users would think like this sender, there would be no Internet. Nada, zip, nil, rien du tout, nothing, niets, kadachat...

Just do the math:

Let's assume 50% of the people believe in those nonsense and send such a virus alert to 20 other people.

Now assume it takes on average 5 minutes from the moment you get the email until you forward it (some a little more, some a little less).

In this worst case scenario, we flood the Net with 10^12 emails after 1 hour. Keeping the 5-minute window I assumed above, it's more than 3 BILLION emails per second. No need to calculate how many emails would be sent after 2 hours - there would be no Internet by then.

Fortunately for the Internet, most users know better than to forward Chain Letters...

XP Printing issues - Grrrr...!!!

D., my colleague came to me today asking for help with printing problems. At some point I decided to log off, but then I couldn't log on again. The machine kept saying that the current time is different than the network time. I couldn't log on with any of our domain users (including Administrator), so I had to log on with the local Admin user. Looking at the time, it looked fine. After digging and searching for 15 minutes, we discovered that the date differed - running some simulation D. had to move his clock one day ahead and forgot to move it back.

Once we moved the clock back to the right day, he could print flawlessly!

Things gives rise to many questions:

1. Why couldn't he print when his day was wrong? I understand there are synchronization issues involved - but in 2007 (almost 2008) these things shouldn't happen! Sometimes you really can not have all your computers synchronized. That's life!

2. Why did the error talk about time and not date?

3. Why couldn't we even log on with a domain user?

Grrrrrrrrrrrr........!@#!@#!@#@!@#!@#!@#

SQL Server Execution Plan - Rantings...

Let me say it outloud: SQL Server Execution Plan SUCKS!

In essence, SSEP (I'm too lazy to keep writing the whole name) is supposed to help you analyze how the SQL Engine performed a query (more specifically what optimization decisions were taken) in order to either alter the query and/or the tables to improve performance. As such, SSEP provides you with a nice graphical interface, that shows every element of the execution along with it's relative cost. You can then see more properties for each element, which supposedly will help you better understand it.

SSEP in SQL2005 has gone through very few changes since SQL2000, and it's a pitty. With a small query, SSEP can help you out - you can easily find the most costly element, analyze it and kill the culprit. With long and complicated queries, however, it's a nightmare. The most prominent issue here is USER EXPERIENCE!!! Let me explain:

In most cases, the first thing you do when you run SSEP is to search for the most costly sub-query and in that you look for the most costly query element. When you have a long and complicated query (which sometimes is inevitable), you can find yourself "surfing" the execution plan for many long minutes, just searching for the elements of interests, never being actually certain you got them all. You just keep scrolling up/down left/right and back again, trying to get a grasp of what's going on. There is a zooming feature, but it just isn't enough! There is no easy way to navigate through the data and search for specific elements. I mean - come on, would it have been so difficult to add, for example, a list of elements with the main properties (say object name, cost and physical and logical operation), allowing you to sort it and jump from that list to the relevant element in the execution plan?

Another USER EXPERIENCE issue is that of the data that comes along with each element. Granted, SSEP is aimed at advanced users (usually DBA level), but would it hurt someone to provide some more explanative descriptions?

Lastly, if you really want to improve the USER EXPERIENCE, you should guide the user (without forcing her hand) to find the real problem as fast as possible. For instance, you could use colors for the elements, such that costly operations would stand out (color table scan in red, index seek in green). Note that these colors should also stand out in the list mentioned above. You could even propose some solutions ad-hoc. For instance, if you see a very costly Bookmark (used to link between indexes and actual data in the tables), you can propose to add an index that will include all the fields required, effectively removing the need for a bookmark (because the engine will read the data directly from the index). There are tons of things you could do to make the user's life easier, and help her finish her job faster, so why not?!?