SQLSaturday #49 Planning Update

Planning for SQLSaturday #49 – Orlando is on-going and going well especially since we have a good head start.  We have our location, Seminole State College - Lake Mary Campus, our date, October 16th, and, at this writing 30+ sessions submitted, which would give use 6 tracks.

I have been tasked with sponsors, marketing, and messaging.  So far I have 2 sponsors signed up, no money yet, and have contacted many past sponsors personally and sent out our first mass email to the sponsors.  I’ve also added information to the event home page and sent out the first email to last year’s attendees.

For finding sponsors, I will continue to personally contact sponsors in addition to automated messaging from the SQLSaturday site.  The issue is trying to find the right tone and frequency.  It’s important to remember that the event is possible because of sponsors so I need to find as many as possible, and show them the value of sponsoring.

For marketing, we really rely on word of mouth and our previous attendee list.  Hopefully our previous attendees will return AND bring someone else.  We also rely on sponsors, speakers, and user groups to keep the event in front of people through blogs, Twitter, and other social media.  One of my goals this year is to try to get some media coverage in the local newspapers.  I’d like to see us at least get on the event calendars, but I also think there’s the possibility of an article in a technology or business section.  In Orlando we have bit of an advantage here by having the “godfather” of SQLSaturday, Andy Warren (@sqlandy) as one of the organizers.

What else should I be doing?  If you’ve had to find sponsors, how do you do it?  I welcome any and all comments.

East Coast PASS Event Options

After asking members about where the PASS Summit should be located and then deciding to keep it in Seattle for at least the next two years despite a clear majority of respondents desiring a central or east coast location, the board decided to look into holding a smaller event on the east coast in the spring.  You can read my original opinions on these decisions in my post, PASS Releases Survey Results & Summit Location – Reactions.

Since that post I have had the opportunity to work with Andy Warren (@SQLAndy) and Kendal Van Dyke (@SQLDBA) on a proposal to PASS to host the smaller east coast event in Orlando capped at 500 attendees for $299.  The event would be 2 days with a pre-con offered at $149.  As we went through the process of putting together the proposal my opinion changed for these reasons:

1. The involvement of local/regional people in organizing and staffing the event through Chapters.
2. It is inexpensive enough to draw people who probably wouldn’t attend the Summit , yet enough of a taste of the Summit to draw them to the Summit, especially if the event comes with a discount for the Summit.
3. It provides another step in the growth path for speakers by providing the opportunity for PASS Spotlight speakers to do a Pre-Con, and regular PASS/SQLSaturday speakers to do a Spotlight.
4. It provides a growth path for local leaders.  Run a chapter, a SQLSaturday, and a Regional PASS event, then you are, in my opinion, better prepared to move on to the PASS board or other PASS leadership position (regional mentor, committee manager, etc...)
5. The proposed event is small and inexpensive enough to happen in more than 1 location per year, especially if the Summit discount is offered.

After working with Andy to put together this proposal I was invited to a PASS board conference call (minutes here login required) where the board was going to discuss this proposal along with 2 other proposals.  The other 2 proposals were:

1. A $995 event that tries to bring as much of the Summit look and feel as possible.  In my opinion, this would be too close in cost and feel to the Summit so it would likely reduce Summit attendance.
2. A $499 event that would be bigger than our proposal, but smaller than the Summit.  This event would be very similar to the Orlando proposal with more frills and more PASS involvement and less local involvement.

It just happened to work out that Andy was only available for the first half of the call, so I was THE representative for the Orlando proposal for the second half of the call.  The great part of the call was that PASS HQ recommended our proposal right from the start, and while there were some good questions asked, the board members were onboard with this idea.  I was a little surprised by the lack of discussion, but I also think that may have been because the groundwork was laid before the meeting.  The board decided to have PASS HQ continue working with Andy and I to firm up numbers and plans for the Orlando plan.  I’m really excited to work on this event.  I’m involved with planning my second SQLSaturday right now and this event will definitely be beyond that experience.

You can read Andy’s take on the meeting and event here.  So what do you think about the idea?  Would you be willing to pay $299 + expenses for a training event like this?  Have we left anything out?

June OPASS Meeting Date Change

Andy Warren (@sqlandy) already blogged about the change here, but I there might actually be someone who reads my blog that doesn’t read his, so I’ll also blog about it here.

At OPASS we try to meet on the second Tuesday of every month which for June would be the 8th.  Well, I’ll be on vacation in NH and actually speaking at the Seacoast User Group that night, and Andy will be at TechEd as a member of the press.  This means that no one from the official OPASS leader list will be there and the person who opens up their office for the meeting (Andy) will not be there.  For this reason we are moving our meeting to the next week, June 15th, when Andy will be available.  Our sponsor, speaker, and topic will all be the same.

Sponsor: Tews Company

Speaker: Kendal Van Dyke (@SQLDBA)

Topic:  Paging DR Availability, You’re Wanted in the Recovery Room

I won’t be able to see you there, but I may make the Live Meeting.

5 Features SQL Server Doesn’t Need

Paul Randal (@PaulRandal) started this chain of blog posts with his post, What 5 Things Should SQL Server Get Rid Of? and I was tagged by Michelle Ufford (@SQLFool) in her post, Delete 5_Useless_Things FROM [SQL Server].  Many will be repeats, but that’s partly because SQL Server is such a good product and mostly because some features cause more problems than they solve.

1. Autoclose – I don’t know of anyone who uses it and I can’t think of any reason why you would want to use it.  Even most base laptops running SQL Server Express have enough resources to keep databases open.
2. Autoshrink – another one mentioned by almost everyone.  It causes more problems than it solves.  As a matter of fact I can’t think of any problems it actually solves in this day of terabyte drives, raid arrays, and SANS.
3. Encryption of Stored Procedures, Functions, and Views – Odds are whatever you are doing in T-SQL has been done before.  Not only has it been done before, but it has probably been published on a blog.  Jeremiah Peschka (@peschkaj) covers this in his recent post, Encrypted Stored Procedures and Their Effect on my Rug.
4. Master as the default database – changing and adding objects to the master database is not a recommended practice, so why would you make that the default database context when a user connects to SQL Server without specifying a database or having a default specified?
5. Primary Key Defaulting to the Clustered Index – Sure, often times the primary key is a surrogate key which is narrow, unchanging, and monotonically increasing so it may be a good choice for a clustered index, but this isn’t always the case.  A Primary Key is a constraint so I should have to choose if it is a clustered index or not.

You can check out Paul’s blog to see many of the other posts in this meme.  I’m a bit late to the party and I think most people I know I have already been tagged so I won’t be tagging anyone this time around.

Let’s Play, “Why did it do that?”

On my current project I decided to be flexible and allow the use of Linq to SQL to try to speed up development.  This will not be a large, heavily used database, so squeezing every drop of performance out of the database is not a big issue.  One of the reasons I agreed to it was so that I could see Linq to SQL in a real-world project and the queries generated.  Well, I saw my first “Why did it do that?” query.

Here’s a similar, simplified schema:

CREATE TABLE dbo.students
    (
    student_id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
    last_name VARCHAR(20),
    first_name VARCHAR(30),
    birth_date smalldatetime
    )
CREATE INDEX IX_last_name_first_name ON dbo.Students(last_name, first_name);

CREATE TABLE dbo.classes
    (
    class_id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
    class_name VARCHAR(25)
    )
CREATE INDEX IX_class_name ON dbo.classes(class_name);

CREATE TABLE dbo.student_classes
    (
    student_class_id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
    student_id INT REFERENCES dbo.students(student_id),
    class_id INT REFERENCES dbo.classes(class_id)
    )
CREATE UNIQUE INDEX UX_student_id_class_id ON dbo.student_classes(student_id, class_id)    

Here’s what Linq generated (modified to fit the new schema):

exec sp_executesql 
    N'SELECT 
        [t0].[student_ID] AS [Id], 
        [t0].[First_Name] AS [FirstName], 
        [t0].[Last_Name] AS [LastName], 
        [t1].[student_class_ID] AS [Id2], 
        [t1].[class_id] AS [ClassId], 
        [t1].[class_name] as [ClassName]
        (
            SELECT 
                COUNT(*)
            FROM 
                [dbo].[student_classes] AS [t3] INNER JOIN 
                [dbo].[classes] AS [t4] ON 
                    [t3].[class_id] = [t4].[class_id]
            WHERE 
                [t3].[Student_ID] = [t0].[Student_ID]
        ) AS [value]
    FROM 
        [dbo].[students] AS [t0] LEFT OUTER JOIN 
        (
        [dbo].[student_classes] AS [t1] INNER JOIN 
        [dbo].[classes] AS [t2] ON 
            [t1].[class_id] = [t2].[class_id]
        ) ON 
            [t1].[student_ID] = [t0].[student_ID]
    WHERE 
        ([t0].[Last_Name] LIKE @p0) AND 
        ([t0].[First_Name] LIKE @p1)
    ORDER BY 
        [t0].[student_ID], 
        [t1].[student_class_ID], 
        [t2].[class_id]', 
N'@p0 varchar(8000),@p1 varchar(8000)',
@p0 = 'Abb%', @p1 = 'Abb%'

Note the correlated sub-query to return the count of classes by student. This is not in the Linq query in .NET, but is added by the framework because of the one-to-many relationship between students and student_classes. Basically the student object contains an EntitySet of student_class objects and it looks like L2S needs to know the number of objects in the list. If this is needed you can return it by replacing the correlated sub-query with a CTE or derived table. Here’s an example of what I’d write:

SELECT
    S.student_id,
    S.last_name,
    S.first_name,
    SC.student_class_id,
    SC.class_id,
    C.class_id,
    C.class_name,
    CC.class_count
FROM
    dbo.students AS S LEFT JOIN 
    (
     dbo.student_classes AS SC JOIN
     dbo.classes AS C
        ON SC.class_id = C.class_id
    )
    ON S.student_id = SC.student_id LEFT JOIN
    (SELECT
        SC2.student_id,
        COUNT(SC2.student_class_id) AS class_count
     FROM
        dbo.student_classes AS SC2
     GROUP BY
        SC2.student_id) CC
        ON S.student_id = CC.student_id
WHERE
    S.last_name LIKE 'Abb%' AND
    S.first_name LIKE '%'
ORDER BY
    S.student_id,
    SC.student_class_id,
    C.class_id

This eliminates the correlated sub-query and eliminates a second scan of the classes table.  I’m still trying to understand why Linq needs the count.  Any other suggestions?

OPASS May Meeting Recap

OPASS had it’s May meeting on Thursday the 6th sponsored by Confio with Dean Richards presenting Tuna Helper for the SQL DBA, an overview of a performance tuning methodology.

We had a good turnout with 25 attendees, it seems performance tuning is a popular topic among the Orlando area SQL community.

We started the meeting with announcements which included SQLSaturday #38 – Jacksonville (May 8th), 24 Hours of PASS: Celebrating SQL Server R2 (OPASS is hosting the 10AM EDT Session), upcoming Virtual Chapter Events, and our upcoming schedule.

Next up was Andy Warren (@sqlandy) with an update on SQLSaturday #49 – Orlando and showing some changes to the SQLSaturday site including the Twitter feed on the home page of each event and the new networking page for those who have Twitter and/or LinkedIn accounts.  He also asked about having all the attendees do this at lunch:

Cha Cha Slide

Then David Waugh of Confio took a few minutes to talk a bit about Confio and their performance monitoring product Ignite8.  Then we broke for pizza and some chat before the main presentation.

Dean did a good job going over the basics of his perfomance tuning methods which involve three steps:

1. Identify – make sure you know why the process is running because sometimes the best way to tune is to eliminate an unnecessary process.
2. Gather – metrics on the identified processes.  You can’t know if you’ve improved anything if you don’t have something to compare it to.
3. Tune – make and test changes.  Dean recommended SQL Diagramming as a technique.  See www.singingsql.com for more information.

We then did our raffle with Confio providing some thumb drives and a single instance license for their Ignite8 monitoring product (an $1800 value).  Then we raffled the rest of our stuff, an Azure hat and T-shirt, Office 2007 Standard, an XBOX racing game, and a copy of the Windows 7 Pocket Administrator’s Consultant.

As usual we had some good discussion afterwards.  I spoke with Tim McAlily about TFS 2010 and then Dan, Kendal, and Andy about OPASS, starting a south Orlando user group, and PASS, especially about how PASS can become more relevant to the everyday lives of SQL Server professionals.

I know I say this every month, but I am always energized and recharged by the OPASS meeting.  This is why I recommend user group meetings to everyone I meet.

May OPASS Meeting Tonight

Join us tonight for our May OPASS meeting at the SQL Share Offices 225 S. Westmonte Drive, Suite 2010, Altamonte Springs FL.  Here are the details, including the Live Meeting link for the featured presentation:

Sponsor: Confio Software

Live Meeting: https://www.livemeeting.com/cc/usergroups/join?id=CSD3Q7&role=attend

Topic: Tuna Helper for SQL Server DBA's

Many DBAs and developers are faced with tuning poorly performing SQL statements. There is no way to learn everything you need to know about SQL tuning in an hour, but you can learn a process to employ when badly written SQLs are ruining database performance. However, many tuning projects fail because the process being used is inefficient. This presentation will walk through a process Confio Software uses with great success and will include topics such as: SQL diagramming, wait type data, column selectivity, and several more that will help you succeed on future tuning projects.

Speaker: Dean Richards

Dean Richards has over 20 years of performance tuning, implementation and strategic database architecting experience. Prior to Confio, Dean was a technical director for Oracle Corporation managing technical aspects of key accounts including short and long-term technical planning and strategic alliances. Specifically, Dean has focused his entire career on performance tuning of Oracle and SQL Server environments.

RSVP to webmaster@opass.org if you plan to attend in person so we have an idea about how much food and drink to provide.

Team Foundation Server 2010 and Visual Studio 2008

We recently decided to use Team Foundation Server as our source control and, really, project management system.  This decision was made just before the release of the 2010 products so we decided to move forward to TFS 2010 even though we might not have Visual Studio 2010 immediately (it ends up we did).  Because of this I needed to find out how or if you can connect to TFS 2010 using Visual Studio 2008 and Team Explorer 2008.  Well, it turns out that you can, but, at least at the time, the documentation on how-to wasn’t easy to find.  Thus I decided to share what I learned. 

The best article I found showed how to successfully connect using VS 2008, but when I tries it I couldn’t make it work. The next reference I found said that you needed to have installed Team Explorer 2008 and then install Visual Studio 2008 SP1.  Of course I already had VS 2008 SP1 installed before I installed Team Explorer 2008.  First I tried re-applying SP1 after installing Team Explorer 2008, no joy.  So I uninstalled it all and re-installed in the “proper” order: VS 2008, Team Explorer 2008, VS 2008 SP1.  Still no joy.  I found another article that shows how to make it work with a registry hack, but I wasn’t going to go there, I’d rather update to VS 2010.

I was on the verge of giving up when I finally found this patch from Microsoft.  Essentially MS released an update specifically for this issue, but I spent 2 days looking before I found it anywhere.

Hopefully this blog post will help someone else.

PASS Summit 2010 Content Survey

The Issue

Being involved in PASS and the SQL Server community via blogging and Twitter, I get to see and hear people’s opinions about PASS and community events regularly.  I also take part in the surveys and events that PASS sponsors whenever I can, so when PASS posted the content survey (results can be found here, requires free PASS membership) I made sure I took it.  Since I like to provide my input when I’m not asked, I believe I need to give my input when I am asked for it, so I took the time to complete the survey.  I was really interested by the reaction of people in the community to the survey.  I saw tweets about the survey being too long (52 questions) and of course the regular comments about it being biased once the results were released. 

The Community Response

A couple of community leaders have blogged about the survey already:

1. Brent Ozar(@BrentO) has an excellent post recapping the results.  Brent also laments the lack of responses (190) which surprises me as well
2. Steve Jones (@way0utwest) also had a post about PASS surveys and the way the release the results as well.

Each of these posts has a good discussion in the comments that you should read as well. 

The Unofficial Board Response

In response to Brent’s and Steve’s posts a couple of PASS board members responded with posts of their own (not necessarily the official PASS response):

1. Tom LaRock (@SQLRockstar) brings up the point that you “praise publicly and criticize privately”.  While I don’t totally agree with that point in this case, I do agree with the rest of his post.
2. Andy Warren (@SQLAndy) responded in his PASS Update #30 post by saying that, in the case, the PASS board “failed by doing more”.  In essence, the board was chastised for NOT releasing the PASS Summit Location survey results, but now IS getting chastised FOR releasing the results of this survey.  I can’t say I disagree with his point.

My Response

I applaud the board for doing the survey and getting the results out quickly.  Sure, I’d like to know how the board plans to use the responses, but I’d rather get the data first and then hear the interpretation, than the reverse. 

I also appreciate the work that went into putting the survey together, which was done by Allen Kinsel (@sqlinsaneo).  It takes a lot of time to craft a survey, maybe it could have been better, but some of us in the community have been asking for transparency and input and now we complain about how we were asked and how long it took to give our feedback?  I don’t get it.  Either we want to be involved in the process or we don’t.  Based on the lack of responses to the survey, I’d say we don’t.  My interpretation of the results is that most people don’t care, just give us a conference in November.  The real problem here is “How do we get more people involved?”

So How Do We Get People Involved?

I talked about this a little in a my post, Growing PASS Step One – Communication, where I ask the board to share their goals and vision for PASS.  I really think this is the key point, if a clear vision is shared, people can be inspired/energized by the vision.  Here are my ideas:

1. A survey about Summit content should be made available at the Summit and immediately following the Summit.  Hit people when they are excited about PASS.
2. Get it out to chapter leaders to use at a chapter meetings.  The attendees already show an interest in professional development and it helps advertise the Summit as well.  Many chapters have internet available on site and could provide ways to take the survey during the meeting.  You could even take it on paper at a chapter meeting.  Sure you’d need someone to compile the information, but you’d get a better response.  Let’s face it, the 200 people who responded to this year’s content survey are all people heavily invested in the community already, and you really need responses from those less involved.
3. SQLSaturday!  PASS owns this brand now, so use it!  Set up a PASS table and get people to take PASS survey’s there.  Take a survey and get entered to win a discount or comp to the Summit, or some other incentive.  In my experience, many SQLSaturday attendees are SQL Server novices who need to know about the Summit, and need to be encouraged to be involved.

I think these same ideas work for the PASS Elections as well.

Conclusion

I think the PASS board is heading the right direction as far as transparency and involvement are concerned, so they should be applauded for the attempt.  Could things be improved?  Sure, but are you willing to be the one who steps up to make the improvements?