PASS 2006, Day Two

After a few hours of much needed rest, day two has kicked off. I was invited to a vendor breakfast which made me feel special. I looked forward to having something different, other than the regular banana bread, mixed fruit, muffins, and croissants that have made up every breakfast to date. I had dreams of eggs, sausage, bacon, perhaps some pancakes as well.

I awoke about 6:20, had thoughts of going for a jog but remembered that the breakfast started at 7AM. No time for exercise, so I got ready and made my way across the street to the convention center. I eventually found the room and found out immediately that two things were not true. First, no eggs, or anything else I had a hankering for. Second, I was not so special, which was hammered home to me when I looked around later on and saw about two hundred other attendees.

Oh well. At least I got caught up on emails, and blogs, and everything else.

After breakfast I headed over to the keynote for the day given by Steve Ballmer. Steve can be a very dynamic and energetic speaker. Sadly, I did not get to see him swear or scream. But what I did get was a nice presentation about where Microsoft is heading. Mr. Ballmer also responded to questions regarding their work with Novell and Linux systems. I do not have the exact quote, but he commented that Microsoft is in business to make a profit, something that the shareholders demand, and open source does not allow for profit. Thus, Microsoft does not make its source code public. And when you look at it that way, it makes complete sense.

Hey, when did I get this cup of Kool-aid? And why does it make me sleepy?

Mr. Ballmer also gave a primal scream at the end, at the request of one attendee who had asked him to dance. Lacking the music to accompany him, Mr. Ballmer decided that a simple scream would have to suffice, so he gave a quick shout out and made his way off stage.

I would be remiss if I did not also mention two other items. First, there was a demo involving some collaboration software running on Windows Vista. In case you did not know, Vista is very, very cool. When the demo was over even Mr. Ballmer was impressed with some of the things he had seen. Second, we got to hear a nice anecdote involving Mr. Gates and Microsoft Excel. Let’s just say that Mr. Gates does not always know everything there is to know, even about his own software.

The first session on the day for me was presented by Andrew Kelly. It was on database maintenance, focusing on the standard items that should be in all of our toolboxes. I am happy to announce that we already do about ninety-five percent of what he discussed. The advantage to attending this session was to understand the differences in SS2K5 commands. For example, DBREINDEX is now ALTER INDEX REBUILD. The new command does the same job, but in a different way. Same for DBCC CHECKDB, in SS2K5 it has some added functionality. All in all, it was a good discussion, presented by a good speaker.

The best part of the session were the sidebars I had with some colleagues as the bullet points were addressed. We would briefly say things like “yeah, we do that”, or “we should be doing that”. The interaction with other professionals at this conference is quite invaluable. It allows you to measure up your shop, as well as yourself, to others, enabling you to build a comfort zone.

Simply put, nothing you would ever get from a book, or from sitting at a keyboard. You only get this by interacting with other humans that share similar interests and are willing to share similar experiences.

The second session was on dynamic managements views. The talk itself was not very interactive, and it was difficult for me to see the actual code snippets on the screen, but I was able to understand all the key concepts. Essentially, SS2K5 has the ability to report to you when indexes are missing or unused. This is quite valuable to a DBA that is looking to tune a server.

I can already imagine myself building some reports to let me know if an index has not been used within the last month, or if there is a particular query being run every day that could benefit from and additional index. The information being collected and reported is merely a starting point, and not something that should just be applied without doing more investigatory work. It is wonderful that we have the ability to get the knowledge we need to do our jobs effectively, but there is still a need for some human reasoning. In other words, the server is getting closer to healing itself, but thankfully it is not there yet, otherwise we would all be looking for work.

The third session was about the use of SSIS packages and server maintenance. While some of the things I saw were somewhat interesting, nothing was really applicable to my shop. But I was able to take away a few tips that I can use in SSIS, which is nice. I have not had a chance to use SSIS much, so any time I see it being used it is always helpful.

The last session of the day was about Cursors versus Sets, a topic I can be quite passionate about. Did I ever tell you that cursors are bad? Not quite Evil, but when used incorrectly cursors are a real drain on resources. The talk was given by Itzik Ben-Gan, who appears to be a leading expert in set theory. His session was fabulous, and every developer that programs against an MSSQL database server should be required by their company to attend a ninety minute session by Itzik. I feel very strongly about this.

Essentially, almost every cursor in existence can be rewritten into T-SQL statements. Well, that is the impression I got from the talk. The trick is, of course, being able to take a cursor and understand what is trying to be done and then apply set theory in order to arrive at the correct T-SQL commands. Hey, I never said it was easy!

The examples were wonderful, and it made me realize just how far I have to go before I would ever consider myself to be a tuning expert. See, I feel that no query should take longer than a few seconds. I have been able to tune many queries in my shop, getting them from minutes down to seconds. What I realize now is that I am using finger paints while guys like Itzik are on their backs working on the Sistine Chapel and going blind.

Typically, if I got a query to run in seconds I usually walk away from the job. I know now just how half-assed that can be. Well, not really, I may not have time to look into the query any longer, and I need to move on. Then again, if I was better at tuning, I would have been done by now. Itzik took a thirty second query down to two seconds. This is where I would have been satisfied. Not so for Itzik, as he then dived into the statistical information and saw that the query plan was doing
over 3000 logical reads. Well, that is just unacceptable, so he went one step further and got it down to 36 logical reads and a run time of 50ms.

Uh, yeah, I knew how to do that as well.

Coming to conferences like this allow me to be reminded that if something is worth doing, it is worth doing right. Looking at the number of logical reads is not something unknown to me, it is just something I neglect to inspect when tuning because for me the bottom line as been to just make things run better. Thirty seconds down to two seconds would have been enough, and I would move on to the next task. Going forward I can look a little deeper when tuning, and over time that will make be a better craftsman at my trade.

Overall, the second day was good, and I look forward to Friday.

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.