PASS Summit 2011 – Part IV
- by Tara Kizer
This is the final blog for my PASS Summit 2011 series. Well okay, a mini-series, I guess. On the last day of the conference, I attended Keith Elmore’ and Boris Baryshnikov’s (both from Microsoft) “Introducing the Microsoft SQL Server Code Named “Denali” Performance Dashboard Reports, Jeremiah Peschka’s (blog|twitter) “Rewrite your T-SQL for Great Good!”, and Kimberly Tripp’s (blog|twitter) “Isolated Disasters in VLDBs”. Keith and Boris talked about the lifecycle of a session, figuring out the running time and the waiting time. They pointed out the transient nature of the reports. You could be drilling into it to uncover a problem, but the session may have ended by the time you’ve drilled all of the way down. Also, the reports are for troubleshooting live problems and not historical ones. You can use Management Data Warehouse for historical troubleshooting. The reports provide similar benefits to the Activity Monitor, however Activity Monitor doesn’t provide context sensitive drill through. One thing I learned in Keith’s and Boris’ session was that the buffer cache hit ratio should really never be below 87% due to the read-ahead mechanism in SQL Server. When a page is read, it will read the entire extent. So for every page read, you get 7 more read. If you need any of those 7 extra pages, well they are already in cache. I had a lot of fun in Jeremiah’s session about refactoring code plus I learned a lot. His slides were visually presented in a fun way, which just made for a more upbeat presentation. Jeremiah says that before you start refactoring, you should look at your system. Investigate missing or too many indexes, out-of-date statistics, and other areas that could be leading to your code running slow. He talked about code standards. He suggested using common abbreviations for aliases instead of one-letter aliases. I’m a big offender of one-letter aliases, but he makes a good point. He said that join order does not matter to the optimizer, but it does matter to those who have to read your code. Now let’s get into refactoring! Eliminate useless things – useless/unneeded joins and columns. If you don’t need it, get rid of it! Instead of using DISTINCT/JOIN, replace with EXISTS Simplify your conditions; use UNION or better yet UNION ALL instead of OR to avoid a scan and use indexes for each union query Branching logic – instead of IF this, IF that, and on and on…use dynamic SQL (sp_executesql, please!) or use a parameterized query in the application Correlated subqueries – YUCK! Replace with a join Eliminate repeated patterns Last, but certainly not least, was Kimberly’s session. Kimberly is my favorite speaker. I attended her two-day pre-conference seminar at PASS Summit 2005 as well as a SQL Immersion Event last December. Did I mention she’s my favorite speaker? Okay, enough of that. Kimberly’s session was packed with demos. I had seen some of it in the SQL Immersion Event, but it was very nice to get a refresher on these, especially since I’ve got a VLDB with some growing pains. One key takeaway from her session is the idea to use a log shipping solution with a load delay, such as 6, 8, or 24 hours behind the primary. In the case of say an accidentally dropped table in a VLDB, we could retrieve it from the secondary database rather than waiting an eternity for a restore to complete. Kimberly let us know that in SQL Server 2012 (it finally has a name!), online rebuilds are supported even if there are LOB columns in your table. This will simplify custom code that intelligently figures out if an online rebuild is possible. There was actually one last time slot for sessions that day, but I had an airplane to catch and my kids to see!
