SQL Rally Day 2: The Home Stretch

After a fantastic Day 1, and a great night of bonding over SQL Karaoke, the morning of Day 2 was full of promise.   

After a quick breakfast, and a Starbucks my friend Dan Taylor (@DBABulldog | Blog) and I headed over to the beautiful Marriot World Center and SQL Rally Day 2.  

Arriving we quickly caught up with Andy Warren (@SQLAndy|Blog), Jorge Segarra (@SQLChicken|Blog), Kendal Van Dyke (@SQLDBA|Blog),  Jason Strate (@StrateSQL|Blog), and 1 half of my future Law Firm, Bigun’s & Balls, Jack Corbet (@UncleBiguns|Blog).

We traded notes on Day 1, and the night that followed.  As you can see some incredibly talented loud Doppelgangers were out last night impersonating Jorge, Jason, and me.  We would be offended if they weren’t so damn handsome.  The Incredible Picture's taken by Tim Mitchell (@Tim_Mitchell | Blog | Flickr)

 “But Balls,” you say, “What about the sessions?”

Glad you asked Dear Reader, and Away We Go!

PERFORMANCE TUNING ANALYSIS SERVICES BY DEVIN KNIGHT

A lot of my focus this week was on BI.  It is not a subject that I’ve ventured into a lot, but not due to lack of interest.  Devin (@Knight_Devin|Blog) was one of the speakers in the Pre-Con I attended, and as much as I’m looking forward to using Analysis Services, as a DBA in our world it will only be a matter of time before you need to tune it.  Devin is a practiced speaker that is very at ease in front of a crowd, he makes presenting look deceptively easy, which is a sign of a truly great presenter.

Devin started by discussing Parent Child relationships in Dimensions and how you can have a variable number of levels, and possibly recursion.  In those cases when you know the number of layers that you require you should attempt to flatten out your tables to avoid such recursion.

He discussed troubleshooting techniques, making comparisons to DBA tuning work to enable those of us without BI experience to make an easy transition in understanding the technology.  When Devin hears there are performance issues we should double check our Attribute Relationships, the same way we would Indexes on a poorly performing query.

He went on to discuss the Formula Engine and the Storage Engine, how each cache’s data, and the Multi-Threaded nature of the Storage Engine vs. the sometimes Single Threaded nature of the Formula Engine.  Partitioning was discussed as well as when we would want to apply partitioning, and how as a Best Practice SQL Partitioning and Cube Partitions should be aligned.

Next we reviewed Aggregations, when we should build them.  HINT*At the end of Development, after your Dimensions are complete.  Make sure to cleanup non-needed columns as they will cause additional overhead that you could avoid to make your cube more efficient.

SQL SERVER STORAGE ENGINE UNDER THE HOOD: HOW SQL SERVER PERFORMS I/O BY THOMAS GROHSER

I remember seeing Thomas (@TGrohser) present on the topic of NUMA, Non Uniform Memory Access, during the Fall 24 hours of PASS in 2009.  He is an extremely knowledgeable presenter, and has used SQL Server in some amazingly large ways. 

Thomas discussed the 3 Characteristics for measuring I/O’s Capacity, Throughput, and Latency.  He discussed how outstanding writes to the Transaction log where handled in different bit sizes in 2000, 2005 (x86), 2005 (x64), and 2008.  We discussed how the gradual increase would affect the performance of systems, and it gives DBA’s another tool to use when discussing the infamous “WHY” question that always accompanies the “Upgrade” conversation.

ZERO TO OLAP CUBES IN 60 MINUTES BY ADAM JORGENSEN FEAT. JULIE SMITH

Adam (@Adam_Jorgensen) walked we the audience through creating a cube in 60 minutes.  But we don’t just create a Cube Dear Reader, we build reports against it as well.  One of the biggest things that I got out of this weekend is that BI is not difficult, but it takes practice.  Once you begin to understand it, and use it, like any other skill we have, it will get sharper.  The first step is often the hardest, but Adam showed us that we should jump in and start.

His hand suffering from a wound, opening a box at his office defending Nun’s and Orphan’s from a knife wielding maniac, kept him from driving.  So he asked the audience for a Volunteer and Julie Smith (@datachix1 | Blog) came to his rescue, to help him build his Cube “ItsSoSmall”. 

Step 1. Get Data for the Cube, create data source (check), create Data Source View (check), create Cube (check),  SAVE, Deploy Cube (check), Hide Surrogate Keys (check), and Congratulations you’ve done it!  Seriously hijinks ensued; we discussed Named Calculations vs. MDX Calculations and before you know it our hour was up, and we had a cube. 

Adam is a phenomenal Speaker, he is one of the top Industry experts in BI, and it is clear why.  I’m inspired to work more with Cubes and SSAS after this week.  

PAGE AND ROW COMPRESSION HOW WHEN AND WHY BY BRADLEY BALL

I’m going to do this one a little different Dear Reader, I wasn’t in the audience.  That’s me up front, proud as all get out to be one of the speakers at the First EVER SQL RALLY!  I had worked up a new demo because I wanted to make sure that I showcased the internals of what happens when you compress.

It went over very well, others had some errors that I needed to proof out.  Every presentation is different and a big part of that is the audience, I got some great questions from Jim Murphy (@SQLMurph|blog) and had a really great discussion with SQL MVP Louis Davidson (@DrSQL|Blog) who had some great questions about compression Internals.

To be honest my favorite part is the questions, they push us they make us grow as professionals, and they help us learn more about the things we love to do.  I’ll be doing a couple posts following up on those questions later this week.  And I’ll get my Scripts and Deck Posted tomorrow as well.

This was the close to a great week.  I met new friends, and bonded more with those that I’ve been fortunate enough to make this year.  I was honored to be Invited to Present, and I can’t wait to keep presenting.  If you were in my session Thank You for attending!  I hope I get the chance to see you all again!

Thanks,

Brad