Today I’m hosting the Bi-monthly PASS Regional Mentor meetings and I thought this was a good time to publish an update and get back to blogging about PASS on a more consistent basis. Its been an interesting last 6 months leading the PASS Global Chapters portfolio and I expect it to be an interesting next 6 months as well!

RM Travel

Previously I wrote about getting a budget exception to provide funds for RM’s to attend PASS events and hopefully learn some lessons about how we can best implement a program like this. With the end of PASS’s Fiscal year on June 30 we ended the RM travel program. I believe we learned some very good lessons and provided some very valuable support for some PASS events.

Some basic statistics

11 RM’s were able to participate and they attended

9 Chapter meetings

6 SQL Saturdays

Overall I wish we would have had better participation (this is about 33% of RM’s) but, given the timelines we had because of the nature of the budget exception as well as the parameters we put on the allocations per RM for this trial this was about what I expected we’d have. We got some great feedback from the RM’s on these trips and I was especially appreciative of the feedback from RM’s that wasn’t the kindest and was brutally honest.

Chapter Tools Redesign

Ill admit to finding it mildly ironic that for as long as I’ve been a member of PASS we’ve had what I’ve often chalked up as “Data” problems. When building the blueprint for reworking the Chapter Tools my main goal was to provide great tools to better enable chapters to grow local communities & host great events. My secondary hopes were that in creating these tools that many of these data problems would be resolved by the ability to have one version of the truth.

In some ways over the last 3 months I’ve been disappointed with the amount of “visible” work that has been available to chapter leaders in the tools. But, since I’ve been having at least twice weekly meetings with PASS IT from the beginning I am confident that things are going as fast as they should be. I think its worth going through some of the challenges weve run into thus far if for no other reason than to remind myself what happened later!

We’ve hit a couple of detours that I didn’t account for in my expectations for the tools. PASS had several different DNN portals at several different versions. This is the basic explanation for why some chapter sites werent in single sign on and had other assorted issues where some chapters had functionality that others didnt. This Included one particularly interesting issue where some chapters had different lists of “members” because some members were in the chapters portal sign on and others were additionally in the main PASS sign on but the chapter leader couldn’t contact (or see) both lists! With the varrying DNN modules in use on over 150+ chapter portals PASS hosts, to get this consolidated and upgraded was no small feat. Today all of the groundwork has been completed and we now have a single DNN Portal with a single DNN version in use across all of the chapter sites.

We also had an outbreak of data issues (remember where I mentioned this earlier!) What we found was we had quite a lot of outdated data about chapters, including which ones are active and what their actual leader contact info is. This problem is a product of growth over the years and not having a system like the one we’re building to house all of the relevant data… This problem has been handled in some ways but, is still ongoing and PASSHQ is working on identifying the problems so we can fix them once and for all.

We also made a decision to build some webservices infrastructure now vs building it later in the project in order to have that infrastructure upfront instead of waiting till later to build it and retrofitting things at that point.

In addition to all of this we also had a couple of false starts with the outside web designer and the design of the public facing sqlpass.org changes that the chapter tools will be feeding in to. I’m optimistic that these have been resolved as we’re currently seeing good progress from that perspective

All in all these problems havent been huge but, they have impacted how long I expected it to take us to start making changes that would be visible to not only the chapter leaders but the PASS community at large

Chapter tools Beta

If you’re a chapter leader, today the beta of the chapter tools changes can be seen if you log into the PASS website and click on the “Chapter Dashboard” menu under chapters. The basic idea has been to replace all of the current CMM module features and enhance them as we move them over to a dashboard type interface. Today there is basic chapter profile editing, Chapter mailing list uploads/updates. As well as a new chapter bulk emailer to send messages to the entire list of your chapter members. If you have any comments or questions about these changes feel free to contact me.

In a (near) future post Ill outline a list of expected features that I’ve been compiling for this tools project.

Earlier this week I was working through an issue that I’ve seen one too many times lately and thought Id write about it. For various reasons *cough* Legacy *cough* I often seem to run across columns in tables that are defined with what would appear to be the wrong data type or worse, multiple data elements stuffed into one column!

In this case the table held various account codes in a char(06) column. These codes were things like ‘001’, ‘002’,’A’, ‘B’ etc

Each account code was for use in a different system. so, on system X it was an integer and system Y the codes were character based, etc

This data was used to join many different tables. Initially these joins were small and straightforward with predicates restricting the code table results to only include the proper data for the system being used.

pseudo code looks roughly like this

UPDATE DATA

SET DATADESC = CODE.MORE_DATADESC

FROM DATA

JOIN CODE

ON DATA.CODE_KEY = CODE.CODE_KEY

JOIN METADATA

ON META_KEY = CODE.CODE_KEY

AND DATESTAMP > = ‘9999-12-31’

WHERE DATA IS NULL

AND CODE.DATATYPE = ‘ACCT’

This is the execution plan from a typical update of 150Million rows

Notice the Metadata table and Codes table are joined before the data table so the resulting codes data is only going to include the 16 rows of smallint data which passes the implicit convert to smallint

As the rowcounts increased and joins changed the implicit data type conversions continued to work until one day they didn’t. Instead they started returning this error message

After some digging it turns out the optimizer had started using a different plan where it joined the tables together in a different order.

Heres that execution plan of the same update

Notice the Metadata table join is reordered and happens after the codes table is joined to the data table. When the data table is joined to the code table the error above occurs as character data in codes cant be converted to smallint during the join

To overcome this problem there are a couple of choices (admittedly there are more than a couple so this list isnt inclusive)

Best –> Change the source table to account for having different source systems account code types in different columns with the correct datatypes. Benefits are straightforward, the BIG drawback is the extra work for system redesign

Better –> Change the queries to use an explicit conversion on the joined columns changing the datatype on the data table from smallint to char(06). The benefit is the small amount of change needed, the drawback is the extra bytes involved per row joined

Good OK –> Add a query hint to force the join order. The benefit is the small amount of change needed, the drawback is in my mind we’re now forcing the optimizer to take a different path than it would if left to its own. I try to avoid hints unless there arent other options and in this case there are.

Other things that could be considered as solutions include temp tables, CTE’s, etc. all of which were more change then we wanted to pursue testing.

At the begining of this year, I gave the reigns of the annual summit program committee to Adam Jorgensen (Blog|Twitter) and I havent looked back… Much! Of course after being involved with a system for so many years its hard to completely let go (ok its not really hard to forget the work!) but, aside from the occassional questions I havent really looked a lot at whats happening in the Program Committee this year. Id like to congratulate those volunteers lucky enough to be chosen to help select abstracts for the summit and if I knew your names, Id call you out but it seems the public listing of volunteers has been removed. You’ve got a daunting task to get through the 800 or so abstracts that im sure will be submitted but, without you, the summit would surely be a lot more bland!! For those of us would be speakers in the SQL Server space, I also noticed something that unless youve been living under a rock youve probably already seen as well. Abstract submissions need to be completed by Sunday May 13 AKA 5 days from NOW!. Since no time is specified I’ll make a wild guess (yes its truly a guess) and say they close at Midnight Pacific time. but why deopend on a guess, make sure to get those abstracts submitted sooner rather than later. It appears as though the wait to get the results of the selection will be over before it really starts as they are due to be released on Jun 8

Mission Statement

I was asked by someone to write a mission statement for chapters. I don’t know if this qualifies in the truest definition of a mission statement or not but, I partially stole this from a friend and I hope he doesn’t mind: 2012 is going to be the year of the chapter.

Its been a busy quarter since I took over responsibility for chapters and there have many changes, hopefully this post can summarize the highpoints.

New Faces

During the past quarter we’ve seen exceptional growth of chapters, including seeing 12 new chapters formed across the globe!

Budget Exceptions

Last month I put forward some recommendations to the board of directors for increased spending for the Fiscal year 2012 (ends 6/30/2012) two of those proposals were accepted and funded.

Yesterday I had the opportunity to lead 2 separate meetings with our Regional Mentors in order to bring them up to speed on what those proposals are funding.

The first thing this is going to allow us to do is send a “Chapter Kit” or mailing to every PASS Chapter around the globe! I’m not going to spoil the surprise about what’s going to be inside but, we hope that Chapter leaders will find the contents valuable and can put them to good use.

The second program we’re going to implement is to fund some regional mentor travel to PASS chapter meetings and SQLSaturdays. The basics are that we’ve given each regional mentor a funding allocation and asked them to attempt to visit as many chapter meetings in their region as they can, with the primary focus being on chapters especially those that need help with speakers, or are otherwise struggling. We are attempting to fill a gap in speakers at local UG’s and allow the Regional Mentors to get out in the community and evangelize for PASS.

Both of these programs are new, and both are things that we’ve talked about trying for a good while. I’m expecting to see both of these have a positive impact on the community and I’m expecting to get lots of feedback during the process and learn some lessons while we see exactly how this is going to work and what impact its going to have.

Tools improvements

Since I started with chapters I’ve been pushing forward with a plan to revamp the existing tools our Chapter leaders use to manage chapters and I’m happy to say I’m starting to see some real movement towards helping with our needs in this area! I expect the first round of changes to start being rolled out within the next 45 days. As this project continues ill continue to write about the new and exciting changes we’re making.

Do you know someone in the PASS Community who is a hidden hero? Perhaps a chapter leader who is doing terrific work to help the SQL community. Maybe its a member who is putting in 110% to organize a SQL Rally. A virtual chapter leader who is organizing great sessions or perhaps its a speaker you’ve seen go above and beyond and speak at multiple chapters/virtual chapter events this month. There are so many people who contribute and make the SQL community so vibrant that finding those who stand out is tougher than ever!

These people are the lifeblood of the SQL Server community, they freely give of their time to make PASS the premiere SQL Server association and its about time we start recognizing them!

Since Ive been volunteering with PASS one thing that has always struck me is how rare it is that PASS actually recognizes outstanding volunteers. When I first joined, we only had the PASSion award which is awarded yearly at the Summit to the single volunteer who had the “most” outstanding contributions. In more recent years we’ve done a better job of recognizing additional people who are outstanding volunteers through the outstanding volunteers presentation at the Summit however, these volunteers all come from the passion award nominations and those nominations are only open just prior to the summit.

I’ve always thought that we should recognize volunteers across the organization in a more consistent way, and not revolve that recognition around the Summit. Luckily, the opportunity to do something about this lack of recognition knocked earlier this year and we’ve made progress. In last weeks connector there was an announcement about nominating volunteers to be recognized as outstanding volunteers of the month. The initial intent is that we’ll accept email nominations during every month for any volunteer who has made outstanding contributions to the community. Those submissions will be reviewed on the last day of the month and recognition will follow the next month. Initially, we are planning to recognize these outstanding volunteers with a nice certificate and highlight them in the connector newsletter. Its not much compared to what these people do day in and day out but with success over time, hopefully we can expand this a bit more. For a start though, I think this is a good first step down the right road.

We intend to have the first recognition cycle start in March so that means that submissions are due by 2/29 (tomorrow!) Please, take a moment and send us an email with as many details as possible about the contributions of an outstanding volunteer who has made an impact in the SQL community. As always with these types of things, the more details the better!