I arrived in Seattle last Monday afternoon to attend PASS Summit 2011. I had really wanted to attend Gail Shaw’s (blog|twitter) and Grant Fritchey’s (blog|twitter) pre-conference seminar “All About Execution Plans” on Monday, but that would have meant flying out on Sunday which I couldn’t do.
On Tuesday, I attended Allan Hirt’s (blog|twitter) pre-conference seminar entitled “A Deep Dive into AlwaysOn: Failover Clustering and Availability Groups”. Allan is a great speaker, and his seminar was packed with demos and information about AlwaysOn in SQL Server 2012. Unfortunately, I have lost my notes from this seminar and the presentation materials are only available on the pre-con DVD. Hmpf!
On Wednesday, I attended Gail Shaw’s “Bad Plan! Sit!”, Andrew Kelly’s (blog|twitter) “SQL 2008 Query Statistics”, Dan Jones’ (blog|twitter) “Improving your PowerShell Productivity”, and Brent Ozar’s (blog|twitter) “BLITZ! The SQL – More One Hour SQL Server Takeovers”.
In Gail’s session, she went over how to fix bad plans and bad query patterns. Update your stale statistics!
- How to fix bad plans
- Use local variables – optimizer can’t sniff it, so it’ll optimize for “average” value
- Use RECOMPILE (at the query or stored procedure level) – CPU hit
- OPTIMIZE FOR hint – most common value you’ll pass
- How to fix bad query patterns
- Don’t use them – ha!
- Catch-all queries
- Use dynamic SQL
- OPTION (RECOMPILE)
- Multiple execution paths
- Split into multiple stored procedures
- OPTION (RECOMPILE)
- Modifying parameter values
- Use local variables
- Split into outer and inner procedure
- OPTION (RECOMPILE)
She also went into “last resort” and “very last resort” options, but those are risky unless you know what you are doing. For the average Joe, she wouldn’t recommend these. Examples are query hints and plan guides.
While I enjoyed Andrew’s session, I didn’t take any notes as it was familiar material. Andrew is a great speaker though, and I’d highly recommend attending his sessions in the future.
Next up was Dan’s PowerShell session. I need to look into profiles, manifests, function modules, and function import scripts more as I just didn’t quite grasp these concepts. I am attending a PowerShell training class at the end of November, so maybe that’ll help clear it up. I really enjoyed the Excel integration demo. It was very cool watching PowerShell build the spreadsheet in real-time. I must look into this more! On a side note, I am jealous of Dan’s hair. Fabulous hair!
Brent’s session showed us how to quickly gather information about a server that you will be taking over database administration duties for. He wrote a script to do a fast health check and then later wrapped it into a stored procedure, sp_Blitz. I can’t wait to use this at my work even on systems where I’ve been the primary DBA for years, maybe there’s something I’ve overlooked. We are using EPM to help standardize our environment and uncover problems, but sp_Blitz will definitely still help us out. He even provides a cloud-based update feature, sp_BlitzUpdate, for sp_Blitz so you don’t have to constantly update it when he makes a change. I think I’ll utilize his update code for some other challenges that we face at my work.
What an amazing week I had at PASS Summit 2011 in Seattle, WA! I hadn’t attended a PASS conference since September of 2005 when it was in Grapevine, Texas. It has grown so much since then. I am not sure how many people attended back then, but I’d guesstimate about 1500. They announced that at this year’s conference there were 4000 attendees. WOW!
Here are my favorite aspects of this conference:
- Networking! – Not only did I meet a lot of new people, but I also got to meet people in person that I’ve known on the Internet for years like Mladen Prajdić (blog|twitter) and Rob Volk (blog|twitter). I even met someone that I’d recently helped out in the SQLTeam forums.
- Learning – I took a lot of notes during the sessions I attended and plan on blogging very soon about them. It is amazing the amount of things you learn and the things that you unlearn. Yes I said unlearn. Some of the stuff that I thought I knew was either out-dated or just plain wrong.
- Fun, fun, fun – To say that this conference was fun would be an understatement. I had a blast!
- I attended the “Welcome Reception and Quizbowl” on Tuesday night, the “Exhibitor Reception” on Wednesday night, and the “Community Appreciation Party” at GameWorks on Thursday night. There were many other after-hours events to attend, but I had to make my kids a priority at night so I had to get back to my hotel room before 9pm so that I could Skype with them.
- It was very entertaining reading and posting with #sqlpass on Twitter. Twitter has changed the conference experience for the better.
I will definitely be able to do my job better due to attending this conference. The return on investment is HUGE!