SQL Cruise Miami 2011 – Day 4 – Indexes – DMVs – The Wrath of Poseidon
Thursday was our final day of SQL Cruise and SQL training on the Miami Cruise. We kicked off the morning with Brent presenting on the topic of Defensive Indexing. It was a compelling discussion, particularly because indexing as a topic was not breached until towards the final 30 minutes of class time. What was covered was a variety of methodologies that work to ensure minimal indexing without compromising performance. We discussed how to identify queries of interest (hint, they don’t always wear black hats and drink rye whiskey), FREE tools at our disposal to do so: SQL Sentry’s Plan Explorer, Adam Machanic’s sp_WhoIsActive, as well as the native SQL Profiler and SQLTeam’s ClearTrace. Sure, we can hammer in indexes to our databases to hold our performance issues in place, but at some point you end up with too many damn nails and your databases start to look bedazzled. Your databases get weighed down by supporting the indexes piled on to fix cumulative performance issues – causing, not resolving said issues over time. Implementing lighter weight solutions to improve performance without the need for the additional overhead incurred with adding more indexes is our goal here. We looked at those issues that cause performance issues that will not simply be fixed by adding indexes: statistics, table variables, parameter sniffing, and SARGability (among others.) Sure, seems odd that we discuss indexing minimally when the session is titled Defensive Indexing, but sometimes you need to weave when someone expects you to duck.
Once we wrapped up our discussion of identification and treatment of bad queries we found it appropriate to give things away! That’s right, thanks to SQL Sentry and their free Plan Exporer tool we dove into our Hairiest Execution Plan contest. While it was close, there were two entries that stood out amongst all others: Bill Sanscrainte and Mitch Bottell. We considered this a tie, though one of them (and we won’t say whose plan) managed to crash SQL Server Management Studio when we looked at the showplan natively! Each received a copy of Brent’s (and co-Authors’) book on SQL Server 2008 Professional Internals and Performance Tuning. Thank you Mich and Bill, and thank you SQL Sentry for a wonderful (and nicely priced) SQL tuning product for the community!
Before our lunch break, we spent 30 minutes discussing the MCM (Microsoft Certified Master) program, training, and testing for those interested. This was the only training material that could have been considered related to our initial cruise. The majority of training provided on this cruise was new material created specifically (and exclusively) for SQL Cruise Miami 2011 – with the exception of a surprise relating to an announcement at the end of this post for you SQL Professionals that were unable to join us on this cruise. More about that shortly.
Speaking of short, I was up after lunch to present on the topic of Dynamic Management Objects. The topic was based off of my work-in-process: The Periodic Table of Dynamic Management Objects. I’ve been working on developing a graphical tool to assist in understanding, simplifying, and utilizing the DMOs over the past year and the presentation was formed from that process. In the discussion we reviewed 30 of the more common, and useful DMOs. We examined their important columns, how they interact with one another, and how you can use them to troubleshoot performance issues, analyze user experiences, and mine metadata for reporting. An interesting side note: I may have hit the obscure record books as being the first SQL Server presenter to be interrupted by waterspouts! That’s correct, having had near perfect weather for the entire cruise, I stepped up to the podium and we hit a storm. Seas swelled. The ship lurched to starboard. College students could be heard scrambling for cover on the deck above the conference room and we caught side of waterspouts off the port side through our wall of windows perhaps 100 yards away. It was as though Poseidon himself was telling us it was time to consider wrapping things up and start looking towards calmer seas in Alaska a few months from now. Not being deterred we proceeded to work through the slide deck and spent 30 minutes covering various metadata collection methodologies. It was only after that mini session that we drew SQL Cruise to a close.
We reconvened shortly thereafter to have a group dinner at Cagney’s Steakhouse and review the training and recreational events for the week. In all, we considered this event a success.
While SQL Cruise Miami has returned to port we’re not done yet. We have some unfinished business to take care of and we’re announcing it here.
Our three largest sponsors are receiving an additional benefit and you, dear Landlubbers, will reap the benefits! Their support arrangement includes a dry dock option: We’ve recorded 15 minute snippets of some of our presentations from the recent SQL Cruise and those will be available on pages dedicated to each of the sponsors for the next 90 days! Starting this Thursday you will have an opportunity to view a sampling of the training available on a SQL Cruise!
But that’s not all! Tell ’em more Mr. Pardo!
Another benefit for those who have joined us on a SQL Cruise is access to all of our recorded training sessions for a year after your cruise returns to port. Any training we provide and record – even if it is outside the scope of SQL Cruise – will be made available to those who have joined us on our of our Cruise-and-Train adventures. Just one more thing we are doing for our new friends – the Cruisers of SQL Cruise!
This concludes our recap of SQL Cruise Miami 2011. Stay tuned, on Tuesday, March 29th we announce the training schedule for SQL Cruise Alaska.
Additional speakers
Microsoft’s Buck Woody, as previously announced, but also at least three Microsoft MVPs and one more in the making!
Expanding the training experience
Including panel discussions and Lightning Talks!
New Sponsors
We also will be welcoming new sponsors on board the SQL Cruise!