Ramblings of a DBA

Tara Kizer
posts - 165, comments - 832, trackbacks - 75

My Links

Advertisement

News

Subscribe
Search this Blog

Archives

Post Categories

Work

PASS Summit 2011 – Part II

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.

Print | posted on Tuesday, October 18, 2011 8:54 PM | Filed Under [ SQL Server - PASS ]

Feedback

Gravatar

# re: PASS Summit 2011 – Part II

HI,

I have just gone through your post presentation, Its really good that you have shared the presentation among all of us.

one thing could notice in the above topic is usage of dynamic SQL. using Dynamic SQL slows your complex or big queries and will not have a proper execution plan. so it will recompile every time when executes. often you end up in declaring the dynamic variable length to MAX and quite tedious to read also.

of course this is my own and self observations, restricted to our personal experience. Keep posting your good articles.
1/16/2012 7:59 AM | Manivanan
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET