Time away from a growing company

At LobsterPot, I’m very blessed to have excellent staff working for me. Someone asked me just today why we haven’t grown more than we have in the past year, but in just over a year I’ve brought on 3 staff members and have another one starting next month. And these aren’t sub-contractors – they’re actual employees. There are others I haven’t managed to get on board, but the company is strong and growing nicely.

imgoingtosqlbits200

Now, I’m taking a trip to the UK for a few weeks. I’m speaking at the excellent conference SQLBits, giving a sold-out pre-conference seminar and then giving two presentations on the subsequent days. It’s the second one I’ve done, and I really feel a connection with many of the UK SQL Community. I can list many good friends there, but am afraid to do it by name in case I miss someone.

While I’m away, I leave my company LobsterPot in good hands. Roger, David and Ben all have good relationships with our clients. I already trust them with the company name, and have no hesitation in telling my clients to contact them while I’m away. Ashley will be the same when he comes on board. I imagine that I will still want to be checking emails regularly, putting the odd document together, and so on – but the day-to-day activity of looking after clients is well in hand.

30000140 This year, our company has grown significantly. We had secured Gold Partner status with Microsoft when there were just two of us, based on having the necessary MCPs with subcontractors and plenty of happy clients. Since then though, the Partner Program has been transitioning into the Partner Network. In the past week we’ve received confirmation that we are eligible for the Gold Competency in Business Intelligence, as well as Silver Competencies in the Data Platform, Portals and Collaboration, Web Development and Software Development – the main areas that we use to complement our Business Intelligence offering. We currently have the Learning competency as well, but I don’t yet know if that will be transferred into a Silver Competency. I’d like it to, as Learning is definitely part of what we do, and one of our core values.

It feels strange going away for a few weeks, but I know the guys will continue to excel.

24 Hours of PASS – sargability resources

I just did a talk for 24 Hours of PASS on Sargability. I’ve done this talk before (about five months ago at the user group I run and at the PASS AppDev Virtual Chapter).

There are a number of useful resources out there about sargability. I’ve blogged about it before, Brent Ozar has blogged about it, and so on. One thing that I don’t believe has been mentioned much is the use of inverse predicates in a view to hope that one of the sides is treated as a Seek. You can see that being handled in my script, and I’ll try to write a blog post about it some time during October.

I’m also giving this talk on Friday Oct 1 at SQLBits in York, UK. I hope you can come to this – the line-up of speakers is just amazing this time.

Anyway, the script I used is now in a SkyDrive folder at http://cid-32a3bd12bf98b682.office.live.com/browse.aspx/24HOP%20-%20Sargability

And some other useful links that I showed during my talk are listed below

Aaron’s blog post with the great picture:
http://sqlvariant.com/wordpress/index.php/2010/09/1433/

Erin’s blog post about SQL not being able to add indexes on expressions:
http://erinstellato.com/index.php/component/content/article/1-latest-news/99-desperately-seeking-seeks

Connect items of interest:
https://connect.microsoft.com/SQLServer/feedback/details/566418/indexes-on-expressions
https://connect.microsoft.com/SQLServer/feedback/details/526431/make-more-functions-sargable

Thanks. Let me be the first to wish you a Happy Christmas and a Sargable New Year. See you at the PASS Summit in November I hope!

Table? No such thing…

No really – hear me out.TSQL2sDay150x150

Of course you create tables, and you query tables, and we say that data is stored in tables. The table is (rightly) a fundamental part of relational theory. But I find that when I think about queries and how they run, I need to approach the system thinking about the indexes that I’m querying, not the tables.

When you decide to find someone’s phone number, you have to make a decision about where you’re going to look that up. Do you pick up the White Pages, the Yellow Pages, your little black book, your kids’ school’s parent directory, or something else? In fact, this is a question that you ask yourself before you even consider whether the particular information is accessible or not. If you had no other option but to scan the entire White Pages (starting at page 1), you would probably just consider the information unobtainable.

And yet all these different sources of data are just different non-clustered indexes on the master set. They’re generally filtered, ordered in an appropriate way, and don’t contain all the information. The Yellow Pages is just businesses, ordered by business type. The White Pages doesn’t include the business type at all, and maybe the school’s parent directory doesn’t include the address. Each of them is designed for a particular type of use. The cost of maintaining them is considered (and presumably it’s understood that the benefits of having the index far outweighs of cost).

I know some of you will be telling me that not all tables have indexes – but every table in SQL Server is either a Heap or a Clustered Index, and if you consider that a Heap is simply an unordered index, then hopefully my points can stand.

When you write a query, you have the option of forcing a particular index to be used with a Hint. This is the kind of hint where you don’t get a choice. I’m sure you all know times when someone has “hinted” that a particular task needs doing, or that you should avoid telling a particular kind of joke, or etc. The kind of hint that if you can’t obey, an error situation will occur. So if you “hint” that a particular index should be used but that index isn’t there, you’ll get an error. So I try not to use index hints if possible.

But my dislike for index hints doesn’t stop me from wanting to consider indexes rather than tables. If I think about what data I want, I really ought to be thinking about the ideal index for my query, and expect to see that index being used in the execution plan. If it’s not being used, or it’s being used in a way that I didn’t expect, then I really ought to ask myself why and investigate the situation (and consider whether or not the way that the query is being run is acceptable or not – remember the analogy of not wanting to start on page one of the White Pages). Hopefully I’ll realise that the index I imagined wasn’t actually ideal when considering the statistics involved, or that the Query Optimizer has found some clever way to answer my query in a way that I didn’t expect, but sometimes you find that the way you’ve written the query is actually persuading the Query Optimizer to create a plan that’s not actually what you want. Correct, valid, but not necessarily ideal.

It’s very easy to just think in terms of tables. You need to reference tables in your query, which ultimately is a logical construct – but the plan that gets produced is what determines the performance, the locking, the cache use, etc, and the plan doesn’t talk about tables (and if it does, the word Table means Heap). By all means you should remember that two indexes that are based on the same ‘table’ may refer to the same data, and a perfectly valid plan could use a different index to what you’re expecting – but this doesn’t mean you shouldn’t give thought to what you consider the ‘correct’ index to be.

So move on from tables, and think in terms of indexes. You’ll learn more about how your queries get executed, and your indexing techniques will improve wildly. More importantly though, your queries will almost certainly run faster.

But to complete the story, it’s also completely valid to think of every index as a table. Each non-clustered index could be thought of as a table which the key is like the table’s clustered index, and the NCIX’s included columns are like the rest of the columns in the table. This helps you think about the cost associated with maintaining your indexes, and also helps you plan them. I’m not saying that you should create different tables instead of indexes by any means, but when you consider the idea that a table is an index, also consider that each index is a table – just to complete the picture.

Conor Cunningham speaking to the Adelaide SQL Server User Group

The guy behind the Query Optimizer, Conor Cunningham, is speaking on Sep 27 (well, Sep 28 here in Adelaide) to the User Group in a few weeks’ time. I only wish that he could be there in person, but as he lives in Texas, it’s much cheaper for everyone if he uses LiveMeeting. But it’s still a massive honour, as Conor is one of the most respected and knowledgeable people working in SQL Server anywhere, inside or outside Microsoft.

I’ve heard many people refer to his talk at SQLBits VI as the highlight of the conference, and this is the talk that he’s going to present for us as well – called “Inside the SQL Server Query Optimizer”. I’m expecting it to be excellent, and I only wish I could be there. Luckily, as it is via LiveMeeting, I hope to be tuned in. If you can’t be there (because you’ll be somewhere else), then feel free to join us via LiveMeeting as well. I’ll post a link in the comments here a few days beforehand. If you are due to be in Adelaide, then please try to be there in person, as user groups are so much more than just the content. Come to spend time with other SQL people. For those not in Adelaide, here’s a list of times around the world (thanks to timeandate.com).

Interestingly, on the same day, Adelaide is hosting another free event – about Windows Phone 7. My good friend Nick Randolph will be in town on the Monday and Tuesday to teach people about how to write applications for WP7. They’re planning to time lunch on the Tuesday to cater for anyone who wants to pop downstairs to Conor’s session, which is excellent of him. Nick is a terrific advocate for communities, having run groups and set up communities in both Perth and Sydney over the years. If you can get to his day (which remember, is FREE – just like the SQL User Group), then registration info is at his blog. Seats are limited for Nick’s event, so please only register if you’re very serious, but get in early.

Something for everyone at the SQLBits Training Day

Of course, the one not to miss is the one I’m doing, on Fixing Queries with Advanced T-SQL Techniques, but actually, they’re all excellent.

For example, Simon’s just blogged that Buck Woody’s seminar topic has changed. Instead of being on career development, it’s now on DBA skills for the non-DBA. This is a seminar that every .Net developer and Windows admin should be on. I can think of many sysadmins and helpdesk operators who should be sitting in the room listening to Buck explain this material. It’s the type of event that whole teams should attend, and I hope that dev shops from all around the UK (and beyond) will take advantage of this.

The Virtualisation day from Brent Ozar is amazingly relevant for just about everyone as well, considering that everyone wants to know about whether virtualisation is worth embracing or not. I’ve had plenty of clients ask me just that in the past year or two, and I would happily send people to Brent’s day to learn from one of the best.

Another topic of massive interest has to be Chris Webb’s day on tuning SSAS. I taught Chris’ MDX course in Adelaide recently (and will do so again in the not-too-distant future), and I can happily attest to Chris’ expertise in this area. With an increasing number of people venturing into the BI space, this will be a popular talk – as will Chris Testa-O’Neill’s seminar on Reporting Services. Chris is the owner of the Manchester accent heard on the Microsoft eLearning courses about SSRS, and at least if you’re in the room and don’t understand what he’s saying you can put your hand up and ask! (Only joking – Chris’ accent is perfectly understandable. He’s also going to be in Adelaide speaking to my user group in December, which will be a great time!)

Continuing the fantastic quality, the top-ranked precon from TechEd North America this year is being repeated. Maciej Pilecki’s seminar about SQLOS goes into SQL internals to a depth that few in the world can match. If you’re a DBA wanting to get deeper, then this is for you.

For database developers, Klaus Aschenbrenner is delving into SQLCLR and Service Broker – areas which I find are still very underappreciated.

And of course, my seminar, looking at the way that various query constructs translate into plans, and how to improve the way they’re handled. We’ll be looking at all kinds of things, leaving you wishing that you weren’t registered for the rest of SQLBits (because you’ll be wanting to connect back to work to fix up some of your queries). In fact, if you’re coming along to my stuff, feel free to bring some queries, and I’ll see if I can use them in some of my examples. That’ll get you some consulting time thrown into the price of the event.

So I really think there’s something for everyone. If you’re a database developer and not interested in my one, then do Klaus’, or one of the Chrisses’. If you’re a DBA, then get yourself onto Maciej’s or Brent’s, and get your non-DB colleagues onto Buck’s (and as many of them as you can). Just make sure you’re hanging around for the Friday and Saturday as well for one of the best SQL events in the world.

I’ll see you there!

PS: Did I mention that you can get a discount if you register today?

Q&A about my SQLBits precon

I received an email from someone who’s trying to decide whether or not to register for my precon at SQLBits 7 next month. He’s already coming to the event (which should be a given for anyone who can make it), but he was trying to work out a few things about my precon, which is called “Fixing Queries through Advanced T-SQL Techniques”.

He wrote:

Hi Rob,
I’m trying to decide on my SQLBits 7 choices and am looking at your Precon Thursday session hence I had a few questions.
I’ve (mostly) administered and developed in sql server for 10 years hence trying to gauge the level / suitablility.
How much do you go into execution plans?
Will there be any printed material for the course?
I find I learn from repetition hence dont want to do a course and forget it all by the next day!
Is there any reading you suggest?
Sorry to be fussy, paying from my own pocket not a company training budget you see!

I don’t think he’s very fussy at all. Paying for training out of your own pocket can be scary. Last thing you want is to feel like it’s a waste. So I replied:

Hi,

Execution plans will be a major part of the day. Just about every aspect of the day will look at the effect of various constructs on the plan, as the plan is where many performance issues can be seen.

As far as printed material is concerned – I wasn’t planning on giving much out before the event, because the day will be so focussed on queries and seeing how things are reflected in the plans (and I find this can be much more easily expressed verbally and with hand-waving than in written form). I will certainly provide the queries that are used, but was looking to focus on making you think, not read. The key points will be inserted as comments in the queries, so I do expect that you’ll have plenty of material to remind you what was seen once you get back to work – hopefully this will be more useful than giving you notes ahead of time. Plus, I’ll always answer emails about the content.

For reading material, I’d make sure you’re familiar with basic execution plan concepts, as that will probably give you an advantage over many of the people in the class. My hope is that you’ll leave the day with the skills to be able to evaluate the plans that your queries are producing, and have learned ways to improve them. There is an excellent chapter on Execution Plans in the book Inside Microsoft® SQL Server(tm) 2005: Query Tuning and Optimization – but I will also be explaining the key points for those less familiar with plans.

To really work out if this precon is for you, watch the video from SQLBits V at http://sqlbits.com/Agenda/event5/Designing_for_simplification/default.aspx. If this is stuff that interests you, and you want more (and in particular, looking at real queries), then definitely come along.

Hope this helps,

Rob

And if you (the reader) are trying to decide whether or not to do a precon (particularly mine), then I hope this helps you too. I can’t answer tonight’s questions for the other precon presenters, I can only comment on the type of things that I’ll be covering, but I can thoroughly recommend all of the things on offer. The list of presenters is tremendous, and I think this really helps demonstrate that SQLBits is very much a world-class conference.