Posts Tagged ‘Boston’

SQLSaturday 71 – Boston

April 5, 2011 1 comment

In the last year or so I’ve heard a lot about the great SQLSaturday events from the twitterati, bloggers, and co-workers and have been looking for my chance to attend one.  Turns out I picked a pretty good one to attend April 2nd in Boston!  Some of the big-name speakers included Andy Leonard, Grant Fritchey, Adam Machanic, and Paul White among others.  Also the event was held at the top-notch Babson Executive Center with great conference rooms and an awesome lunch (more like dinner!).  In addition to the great speakers there were numerous vendors to talk to, each with their own drawing box, which each attendee could deposit one or all of their 16 raffle entries into.  These great vendors and sponsors gave away a couple iPad2s, a couple xBox360s with Kinnect and other sought after prizes.

Even though there were 30 sessions lined up for the day, one could only reasonably sit in 5 of them.  There were many others that I would have enjoyed listening too, but I read the extracts before hand and settled on the following five:

SSIS – The New Stuff by Matt Masson (blog | twitter)

This session was advertised as being given by Andy Leonard, but in reality Matt did all the talking with Andy chiming in at times.  This turned out to be a good thing since Matt works on the SSIS development team and provided some great insight into the team and it’s priorities with the upcoming SQL Server Denali version of SSIS.  Some of the things I found most interesting from Matt’s talk include:

  • Improved undo/redo support!
  • SSIS in Denali will make it easier to do diffs between versions
  • They’ve improved the deployment architecture to make it easier to deploy as well as easier for DBAs to manage SSIS packages in SSMS
  • Improvements made that allow flexible order of authoring, meaning you can start with the destination and work backwards, rather than the typical method of starting with the source and designing your flows toward the destination.  I don’t see this as being terribly useful to me in most of my packages, but it is nice to know that you can work in a more disconnected manner if you need to.
  • Packages managed by SSIS server will allow DBA to turn on and off automatic logging as needed without having to modify the package(s).

Database Design for mere developers by Steve Simon (blog | twitter)

I walked into this session expecting to pick up some general design tips for developers who might have to design their databases from the ground up, including schema etc…  What we got was a highly specific talk about using user-defined functions instead of querying directly from views.  Maybe I didn’t understand the abstract fully before I selected this session, but I tried to stay interested even though the topic covered was not what I expected.  I did pick up some tips about handling recursion in SQL and plan to read more about what Jacob Sebastion has written on the topic thanks to this session.

SQL Query Optimization – It’s Not Rocket Science! by Paul White (blog | twitter)

I made sure I arrived early for this session to secure a seat as I’ve heard lots of good things about Paul White and his content and presentation.  I was not let down.  Paul is an engaging speaker and really knows his stuff and how to interact with the audience.  I learned a lot about how the SQL Query Optimizer works in broad terms that were easy to understand.  About halfway through the talk I was starting to wonder if I was going to get any tips I would be able to use on a day to day basis to make the SQL Query Optimizer happier.  Thankfully Paul came through in the final 5 minutes to give us some “take homes” that can help create databases and queries that will allow the optimizer to come up with good plans for returning the data we want, which include:

  • Use good relational design (set-based methodologies)
  • Use constraints which will help the optimizer know what data is allowed in a given column (I need to read more about how this exactly helps the optimizer, but it seems like a good idea)
  • Watch the stats.  Out of date stats can hurt queries when data has changed a lot since the stats were generated.  The optimizer may do one thing based on stats, when the reality is it should be doing something else to more efficiently optimize your query
  • Use computed columns when needed.  I didn’t quite catch the exact reason behind this but will learn more about why this is helpful
  • Use new features with care, as they haven’t been as honed in relation to the Query Optimizer over the years as other tried and true features have (one example from Paul: Filtered Indexes)
  • Different syntax can improve your query in some cases.

SSIS Performance Design Patterns by Matt Masson (blog | twitter)

Another enjoyable SSIS session by Matt Masson that touched on a number of design patterns.  What I enjoyed most about this talk was hearing about some of the clients Matt has worked with and some of their misunderstandings to how SSIS works.  One particular client misunderstood that all the data being moved in SSIS is done in memory.  He said the client was used to using a different ETL tool which moved data differently and as a result had designed their ETL system in SSIS similarly, resulting in them moving data to a physical server half way around the world and then back to the destination server sitting in the same data center as the source!  Another anecdote he highlighted was a client who was using a large number of SSIS servers in concert to manage a huge ETL import process that needed to scale up quickly to handle mobile phone data logs.  This really put the puny SSIS feeds I’ve been working on lately into perspective 🙂

Some tips I picked up from this session include:

  • Matt encourages the Source->Staging->Destination model for many feeds where you save the data to a table at the destination and then merge in changes updates using T-SQL because of the performance benefits of SQL server over SSIS
  • Make sure SSIS is the right tool.  He’s seen clients where they use SSIS to move data to and from the same database! An Insert Into statement would have worked just fine.
  • Use the database engine when you can.  It performs better than SSIS and you can utilize indexes to shorten your load times
  • Use a scientific approach to tuning SSIS packages (Measure, Hypothesize, Modify, Re-Measure)
  • Create Benchmarks to track performance of your package (Calculate pre-execute and post-execute steps that measure key performance indicators)
  • Use the SQL engine for sorting, not SSIS when possible.  If you have sorted data already, tell SSIS so using the IsSorted property
  • SSIS is designed for parallel processing, so utilize that!

15 Powerful SQL Server Dynamic Management Objects by Adam Machanic (blog | twitter)

This presentation was in the last time block of the day, which made it a bit more difficult to follow along with all the great demos Adam put on for us.  I took a few notes but mainly focused on absorbing all kinds of useful information we can glean from the DMOs.  This is one area that I think will be come more and more useful to me as I acquire more production database support responsibilities as my career progresses.  It was nice to meet one of the guys behind and author of the infamous Who Is Active stored procedure. I will definitely look into more of Adam’s blogs and scripts to learn more about using the Dynamic Management Objects in the future in order to put some of them to use.

Vendor Session: Axis Technology: Data Masking

Axis Technology is the maker of DMsuite which is a web-based data basking software product.  I was interested in this session because I know the challenges present when trying to create fake personally identifiable information in a test environment.  DMsuite appears to do a great job of cleansing the personal data and replacing it with fake, but meaningful replacement data.  I can definitely see myself having a use for this type of software at some point in the future in my career and it was good to see some of the things they can do to cleanse the data, yet keep it useable for testing/QA etc…

Overall SQLSaturday #71 Boston was a great success and I am looking forward to the next one I’m able to attend.  The only criticism I have is that for 4 of the 5 sessions I attended, the speakers seemed to be presenting sessions designed for longer time periods and not honed down well to allotted time (60 minutes).  I think this is attributed to the wealth of knowledge of these subject matter experts and the re-use of the presentations from different talks.  It would be nice to see them prepare ahead of time for a shorter presentation and not have to rush at the end to cover those last few slides.  All in all I can’t complain too much, since the event was provided for attendees for free!