Every month, PASS awards someone in the global SQL community their Outstanding Volunteer award. In September, it was LobsterPot Solutions’ Roger Noble! Roger wrote about it on his blog, so head over to there and read it. Obviously here at LobsterPot we’re very proud of him, having seen how much effort he puts in, running the BI Virtual Chapter Aus edition, being on the Program Committee for Summit and even creating a Pivot collection of the Summit sessions. Congratulations, Roger!
Improving your data story.
LobsterPot Solutions is an Australian SQL Server and Business Intelligence consultancy, offering consultancy and training services. LobsterPot Solutions specialises in the Microsoft Data Platform, including Power BI, SQL Server and Azure, from data resilience to data analytics, to Big Data and IoT, and of course performance tuning, health checks, and more. With experts in both Melbourne and Adelaide, we can help your organisation become more data-driven.
LobsterPot Solutions is a company of firsts. When the Microsoft Partner Network went live, we were the first company in Australia to become a Gold Competency Partner, the first in the world to gain the Gold Competency in Business Intelligence. Since then we have become the first to employ three Australian SQL MVPs, the first company in the whole Asian region to have an APS / PDW trainer on staff, and have been involved in training other trainers in the region.
More Services here.
News and Events
It goes without saying that LobsterPot Solutions is a great supporter of the SQL Server community, and PASS in particular. I thought it would be good to look at how each of the LobsterPot staff is going to be involved at the upcoming PASS Summit.
Rob’s been on the PASS board for over a year now, and is set to continue in this for some time. As a user group leader and current co-ordinator of the PASS’s semi-annual 24HOP event, he has a number of meetings and duties to go alongside the Board Meetings that will be on. If you’re going to be at the event, you shouldn’t have too much trouble finding Rob – he’ll even be involved in prayer meetings in the early mornings.
Roger is speaking this year! His session is on Thursday afternoon, and is on Reporting Services. As well as this, 2012 has seen Roger’s PASS involvement extend into leading the Australian arm of the BI Virtual Chapter. This means he’s been finding speakers for the monthly meetings he hosts, and you might see him wandering around the Summit in a VC leaders’ jacket.
Julie has been a co-presenter at the PASS Summit before, but this year is only giving a Lightning Talk. I shouldn’t say ‘only’, because the pressure in delivering ten minutes of content is at least as high as giving an hour or more. The Lightning Talks are punchy and poignant, energetic and expert-level. Julie’s also leading a First-Timers’ group at the Summit, and speaking at SQL Saturday events in Portland and Olympia either side of the Summit.
Ben and Heidi are both experiencing their second PASS Summit, and like Julie are both involved with the First-Timers. Ben is leading a First-Timers’ group and Heidi has been on the First-Timers’ committee this year, working on a team to find ways to help people at their first summit get the most out of it. The first time anyone experiences a PASS Summit, with 4000 people excited to learn about SQL Server and mix with other SQL Server experts, it can be hard to know what’s important. Being in an Australian time zone can be tricky for organising committees, and Heidi has really gone a long way in helping these people. Ben, Julie and Heidi will have a great time helping people realise the most.
This is Martin’s first PASS Summit. He’s experienced conferences around the world before, including a number of SQL Bits events in the UK. He’s taking part in the First-Timers’ event, but from the other side of the room.
Microsoft have introduced some new certifications for SQL Server 2012. Instead of Microsoft Certified Technology Specialist and IT Professional, they have moved to Solutions Associate and Solutions Expert with a new set of exams. All the LobsterPot consultants sat some of the new exams in beta, and all of us are now certified in SQL Server 2012!
Rob sat all seven exams and picked up both MCSE: Data Platform and MCSE: BI. Ashley got MCSE: BI too, while Martin and Roger are both only one exam off getting MCSE certifications. Ben and Heidi also passed exams, and in doing so, Heidi has become a brand new Microsoft Certified Professional!
So I put an early version out there to see what people thought – not expecting much. Well I can honestly say that the response has been overwhelmingly positive, I’ve been inundated with requests to finish it off as people were exited to build collections with their own data.
So I’m pleased to announce that the LobsterPot HTML5 PivotViewer is now an Open Source project hosted on CodePlex. You can find it here: http://lobsterpothtml5pv.codeplex.com.
The control is still very much a work in progress and there are still pieces of functionality that is missing. I’ll be updating the documentation over the next few days and the plan is to continue work on the control so that it can render static CXML based collections as well as its Silverlight counterpart.
If you’ve got an existing CXML based collection then please download the source and let me know how well it does/doesn’t work as well as if there are any bugs or functionality that is missing. The LobsterPot HTML5 PivotViewer has been built as a jQuery plugin with extensibility in mind. I’ll be posting more about ways that the control can be enhanced, including how get started extending it to work with other data sources.
Going forward the plan is to have two versions of the control: The open source version that will support static CXML based collections and a paid version that will be enhanced with dynamic collections, tile templates and additional views for mapping, data grids and charts. If you’re interested in having LobsterPot build a collection for you please contact us.
In May, I’ll be in the US. I have board meetings for PASS at the SQLRally event in Dallas, and then I’m going to be spending a bit of time in Chicago.
The big news is that while I’m in Chicago (May 14-16), I’m going to teach my “Advanced T-SQL Querying and Reporting: Building Effectiveness” course. This is a course that I’ve been teaching since the 2005 days, and have modified over time for 2008 and 2012. It’s very much my most popular course, and I love teaching it. Let me tell you why.
For years, I wrote queries and thought I was good at it. I was a developer. I’d written a lot of C (and other, more fun languages like Prolog and Lisp) at university, and then got into the ‘real world’ and coded in VB, PL/SQL, and so on through to C#, and saw SQL (whichever database system it was) as just a way of getting the data back. I could write a query to return just about whatever data I wanted, and that was good. I was better at it than the people around me, and that helped. (It didn’t help my progression into management, then it just became a frustration, but for the most part, it was good to know that I was good at this particular thing.)
But then I discovered the other side of querying – the execution plan. I started to learn about the translation from what I’d written into the plan, and this impacted my query-writing significantly. I look back at the queries I wrote before I understood this, and shudder. I wrote queries that were correct, but often a long way from effective. I’d done query tuning, but had largely done it without considering the plan, just inferring what indexes would help.
This is not a performance-tuning course. It’s focused on the T-SQL that you read and write. But performance is a significant and recurring theme. Effective T-SQL has to be about performance – it’s the biggest way that a query becomes effective. There are other aspects too though – such as using constructs better. For example – I can write code that modifies data nicely, but if I haven’t learned about the MERGE statement and the way that it can impact things, I’m missing a few tricks.
If you’re going to do this course, a good place to be is the situation I was in a few years before I wrote this course. You’re probably comfortable with writing T-SQL queries. You know how to make a SELECT statement do what you need it to, but feel there has to be a better way. You can write JOINs easily, and understand how to use LEFT JOIN to make sure you don’t filter out rows from the first table, but you’re coding blind.
The first module I cover is on Query Execution. Take a look at the Course Outline at Data Education’s website. The first part of the first module is on the components of a SELECT statement (where I make you think harder about GROUP BY than you probably have before), but then we jump straight into Execution Plans. Some stuff on indexes is in there too, as is simplification and SARGability. Some of this is stuff that you may have heard me present on at conferences, but here you have me for three days straight. I’m sure you can imagine that we revisit these topics throughout the rest of the course as well, and you’d be right. In the second and third modules we look at a bunch of other aspects, including some of the T-SQL constructs that lots of people don’t know, and various other things that can help your T-SQL be, well, more effective.
I’ve had quite a lot of people do this course and be itching to get back to work even on the first day. That’s not a comment about the jokes I tell, but because people want to look at the queries they run.
LobsterPot Solutions is thrilled to be partnering with Data Education to bring this training to Chicago. Visit their website to register for the course.
LobsterPot Solutions is a company that is fully dedicated to supporting the SQL Server community.
Its owner, Rob Farley, has supported conferences such as SQLBits and PASS, and has had significant involvement in local user groups, including running the Adelaide SQL Server User Group for over six years. He has even been appointed as an advisor to the PASS Board of Directors, and is currently running for election to become a director. This mindset is common in all LobsterPot staff, who are all recognised faces at conferences both around Australia and in the rest of the world.
With this in mind, LobsterPot Solutions is proud to be the first sponsor of the SQL Server Social events in Melbourne, and to help coordinate the events. The SQL Server community is one of the strongest technical communities around the world, and events such as SQL Server Social help strengthen it even further. If you haven’t been to one, please make a point of adding it to your diary and getting to know more of your fellow database professionals.
To find out more about the next SQL Server Social event, visit the SQL Server Social blog.
I’m sure Americans understand the joke that is the “World Series” of baseball. Everyone else rolls their eyes and knows that this ‘world’ means North America.
Some people feel similar with PASS, unfortunately. The North American Conference and the Global Summit are one in the same, and the 2013 Summit is to be held in Charlotte, North Carolina, to bring it “closer to database pros in the Eastern US who may not have been able to make the cross-country trip in the past.” The 24 Hours of PASS events have been split into two sets of 12, because numbers show that fewer people attend the sessions when America sleeps. “Two Days of Free SQL Server Training” is actually two Nights where I live, and most other people outside Europe and the Americas. (I’m actually on the organising committee for 24HOP, and am hoping to see this change back again soon)
And so it’s very exciting to see that there are people within PASS who are trying to Globalise the organisation more. And that’s GlobaliSe with an eSs, not a Zed. Microsoft’s Mark Souza and the current PASS President Rushabh Mehta are among those keen to make this happen, and I was recently invited to Stockholm for some meetings with representatives from around the world to discuss how to make PASS more global (which unfortunately, also means less US-centric).
It’s an interesting challenge – trying to think how to cater best for people who are essentially a minority in an organisation. It’s clear that the majority of PASS members are in the US. This is no surprise – it’s also the place that more SQL Server licenses are sold. But there is also a large portion of the SQL Server world which is not being targetted by PASS. The US market is comparatively low-hanging fruit, but there’s something to be said for reaching further afield. The US community will still benefit from most initiatives that are designed to cater better for non-US folk, but the non-US folk are less likely to benefit from US-ones.
Part of the challenge will be around metrics. If PASS measures success by the number of members, then it makes sense to focus on the more-populated areas of America. On the other hand, if success is measured in other ways, perhaps even ones that are more qualitative than quantitative, then globalisation can make better sense.
The meetings in Stockholm were very interesting, and it was good to hear opinions from other parts of the world, not just Americans (or Australians). But one of the most interesting things happened towards the end of it.
Three of us were invited to be non-voting advisors on the PASS Board of Directors – Raoul Illyés (Denmark), James Rowland-Jones (UK) and me (Aus). These appointed positions, only for a year (based on what the board is allowed to do). See page 4 of http://www.sqlpass.org/Portals/0/PASS%20Bylaws%20–%20June%202009.pdf, "The Board may also choose to appoint any number of non-voting advisors to the Board by a majority vote for a period of up to one year each."
Yes, these are currently non-voting positions that we’re taking on. But we do still have the ability to go through the election process to earn the ability to vote on board issues. I’m not sure we really need it though – this appointment will give the three of us (and in turn, our respective regions) a decent voice, even if the final decision is left to the existing people. In time, who knows how we can influence PASS and how PASS can influence the SQL world.
Finally – this isn’t about PASS taking over the world. It’s about PASS being better placed to support the worldwide community. This will probably involve supporting non-PASS events (such as SQLBits) too, and possibly even reducing the focus on requiring communities to sign up as official PASS chapters. A lot of things might change, but the hope and focus is on making PASS a better vehicle for supporting the worldwide SQL Server community.
The annual PASS Summit in North America is the largest SQL Server event in the world. There is no other event on its scale anywhere. So it is with great honour that LobsterPot’s Rob Farley will be delivering a pre-conference seminar at the 2011 event, held in Seattle from October 11-14.
The seminar that Rob will be delivering is a slightly-modified version of the one that he delivered at the SQLBits conference in the UK in 2010, and is called “Fixing Queries Using Advanced T-SQL Constructs”. The details of both it and the other LobsterPot submissions for the PASS Summit can be seen at http://lobsterpot.com.au/lobsterpot-submissions-for-sqlpass.
The official announcement from PASS about the chosen seminars will be around June 1st, and the announcement about breakout sessions is expected to be a few weeks later.
In March, Martin Cairney joined the team. Martin is a long-time member of the local Adelaide SQL Server community, and has been a friend of mine for some years now. He has spoken at the Adelaide SQL user group before, and when 2007 saw him move to the UK for a while, I wished him all the best. Martin went on to speak at the first SQLBits event, and became a regular at those conferences until he came back to Adelaide last year. He also spoke at SQL community events around the UK, establishing a good profile there. It was definitely good to get him on board a couple of months ago.
This month, Heidi Hasting brings the number of people in the company to seven. Heidi has worked with some of our staff before, and I see her having a massive future with the company.
You can see the new website for yourself! It’s clearly much better than the old site, which was little more than a placeholder. The new one has the attention of a proper web designer (instead of just being a bit of HTML thrown together by me), runs on the WordPress platform, and features a picture of Glenelg jetty, taken by my lovely wife. Most Adelaide beaches are just amazing, and it’s nice to be able to show one off on the website.
But perhaps the biggest news – we have a Melbourne presence now!
Martin has moved to Melbourne, and we’re picking up clients there. I imagine I’ll make some trips over there myself in coming weeks/months, hopefully speak at the odd user group, visit clients, and maybe see some friends and family as well.
Expanding out of Adelaide has been on my mind for some time. While Adelaide is a great place to do business, and everyone should consider moving here, it’s small compared to many places (only the fifth largest city in Australia). Establishing LobsterPot in other places makes good sense. Melbourne is a great city – I used to live there, and my brothers and their families still do. We’ve had the occasional Melbourne-based client before, but never enough to consider having a consultant based there. We do have a consultant there now, and we are well-and-truly open for Melbourne business.
May 6th, 2011
(by Rob Farley)
My guys are great! When PASS started accepting abstract submissions for their Summit (in October this year), some of the LobsterPot employees immediately started looking into ideas for talks they could do. We rate communication as one of our key values at LobsterPot, and all my staff are keen presenters.
Roger Noble was at the PASS Summit with me last year, and has since spoken at both the Adelaide SQL Server User Group and Adelaide SharePoint User Group. Considering the work he’s done in data visualisation with PivotViewer over the past year, he was keen to be able to submit a session on that. This technology is seriously cool stuff – quite a few of our clients have been very interested in it and are now using PivotViewer to get at their data in new ways. You can see examples of Roger’s work at http://pivot.lobsterpot.com.au
Get even more from PivotViewer (Roger Noble)
With the release of the Silverlight PivotViewer control from Microsoft in June 2010 we saw the beginning of a new direction for data visualisation and interactivity, allowing data to be browsed and filtered in ways that highlighted information that could have easily been missed. This session will show you how to take the PivotViewer control and enhance it even further to provide even more ways to display your data, including placing information on maps, and showing extra information in the PivotViewer tiles according to the zoom level. From sourcing data from PowerPivot and SharePoint 2010, using Visual Studio 2010 to add new functionality and improvements in future versions this session will show the range of ways that PivotViewer can effectively be used in your organisation.
Like most of the team, Ashley Sewell has been doing a bunch of work with clients implementing cubes and reports. The talk he’s put in reflects a very common emotion that he gets from clients when they first start talking about Business Intelligence. They want to know that they’re not just getting their data in a different format, but that they’re going to be able to reach into the data themselves and realise that ‘Analysis’ aspect of SSAS. Ashley used to be tertiary lecturer, and understands the importance of giving presentations that are useful as well as at an appropriate technical level. This talk will be excellent, and I really hope it gets picked.
So you’ve got a Cube. What’s Next? (Ashley Sewell)
Did you ever get to the end of an Analysis Services session thinking "Cubes sound great but what can I show the analysts and execs back at work to woo them?". If your answer is yes then this session is for you. You will be taken through some of the Business Intelligence reporting and dashboarding available using a combination of PerformancePoint Services 2010 and Reporting Services 2008 R2 with particular emphasis on combining the best of each offering to maximise the impact of your dashboards. You can expect to leave this session with a deep enough understanding (and a list of gotchas) to enable you to create your own dashboards and data visualizations that bring the data within your cube to life on the web.
As expected, I’ve put a few submissions – a pre-conference seminar and two regular sessions.
The pre-con is an enhanced version of the one I did at SQLBits 7. In that, I go through a bunch of T-SQL queries that could have been fixed using T-SQL that most people aren’t aware of. For example – many people would shy away from something like ORDER BY MAX(OrderDate) DESC, but if you understand what’s going on there, when it’s good and when it’s bad, then it can be just fine. This pre-con got excellent feedback at SQLBits, and I think it will please PASS Summit delegates as well.
Fixing Queries With Advanced T-SQL Constructs (Rob Farley)
Have you inherited queries that are not your own, and are finding that performance isn’t so great? Removing cursors in favour of set-based queries is useful, but even set-based queries can perform poorly. Understanding the impact that various constructs can have on a query plan could be key to resolving many of these issues. In this seminar, irrepressible SQL MVP Rob Farley will take a look at some real-life queries and take the audience through examples of constructs that can have significant effects on tuning. This will include complex nested joins, join simplification, procedural functions, SARGability v residuality with predicates, better execution plan reading, start-up parameters, force hints, complex sorting, ORs, effective Dynamic SQL, GROUP BY v DISTINCT, unique indexes, temporary tables, APPLY considerations, and more. You’ll discover profoundness in things you thought you knew, and you’ll even see when a covering index that returns a single row can be a bad thing. This will be a day spent in Management Studio, not PowerPoint. If you want to know how to persuade the Query Optimizer to do a better job of running your query, this day is for you. The examples will apply to a variety of versions, with most of it being useful even in a SQL 2005 environment.
Another talk that I’ve done in the past is one called “Understanding SARGability (to make your queries run faster)”. In fact, last year this talk was a ‘stand-by’ for the PASS Summit. I also gave it at SQLBits 7, with Brent Ozar (@BrentO) and Buck Woody (@BuckWoody) heckling me from the back. Brent tweeted “Okay, wow, @robfarley is a seriously good presenter”, and although he got my Twitter handle wrong (it’s @rob_farley), I was very flattered. It’s one of my second-favourite tweets still.
Over recent times, I’ve found that people really don’t seem to understand the significance of having predicates fall into the category of “Residual”. I wrote about it recently, and was quite interested to see some of the reactions that people had when they talked about it with me. Jack Li wrote a post last week about a query which took longer than expected because a Hash Match was putting a lot of data into a single bucket. This is a common problem that gets missed, because of the impact of having the selective predicate treated as residual. I’m going to write more posts on that in the coming weeks, and a lot of that will be covered in one of the talks I’ve submitted this year. Residualiciousness isn’t a real word, but I figure that shouldn’t stop me.
Joins, SARGability and the Evils of Residualiciousness (Rob Farley)
You wouldn’t believe how often people just aren’t using their indexes effectively, whether it be searching for data, or joining tables. Quite often, this comes down to predicates becoming residual. Yes, residuality is a problem, and once a predicate has become residualicious, you may as well be scanning instead of seeking. There’s so much more to SARGability than people think, and people can often miss out on significant performance benefits by not appreciating this – particularly with new and improved query hints becoming available in SQL 2008 R2 SP1 and Denali. SARGable means Search ARGument Able and relates to the ability to search through an index for a value. Unfortunately many database professionals don’t really understand it – especially in regard to joins – leading to queries which don’t run as well as they should. In this talk, you’ll learn how to tell whether a predicate is being used correctly, and to evaluate what’s really going on in your Seek or Join. You’ll even learn to use new features in SQL 2008 R2 SP1 and Denali to affect the residuality of your predicates. This is a talk involving lots of demos, showing plenty of queries and execution plans.
The other talk I’ve submitted was inspired by a conversation with my friend Jamie Thomson (@jamiet), who unfortunately won’t be at the PASS Summit this year (he does have an excellent reason though). I happened to mention something which I considered an important consideration about queries used for SSIS, and he told me I had to write a blog post about it. I eventually did, and it got me thinking about a bunch of things that SSIS Tuning Talks (like those that Jamie gives) which are very relevant to tuning T-SQL, but yet almost never get mentioned by standard talks. Some of them get covered in the pre-con seminar too, and I’m sure I’ll have to get blog posts written on some of these things over coming months.
Tuning T-SQL Using Lessons Learned From SSIS (Rob Farley)
We see presentations telling us how to tune T-SQL, looking at things like how a covering index can help avoid an expensive lookup, and the importance of set-based thinking. But there can be a lot more to finding bottlenecks in an execution plan, and there are significant parallels with the kind of concerns we have when tuning SSIS Data Flows. This session will look at some of the things that SSIS gurus explain when in looking at how to make SSIS run faster, and draw strong parallels to things that many query tuners don’t realise. If only they paid attention to the SSIS world! There will be a lot of examples in this session, explaining what’s happening in query plans and the ways that you can persuade your queries to run more like your SSIS packages, and vice-versa.
Looking at the long list of abstract submissions this year (and right now there’s about three hours for more to come in), I think it’s going to be a great event. With buzz around Denali and a stronger community than ever, I think it’s going to be huge.
There will be handful of LobsterPot employees there, and I hope you will be too!