So that´s it. Thursday afternoon and I´m still amazed about this great week I had. Or let´s say: this extended weekend. The weather god was on our side and I nearly got a sunburn. You really have to think about it: 4 days at England´s seaside and sunshine every day. It felt almost like holidays. But there was also a lot to learn as well.
I´ve registered for the whole conference and I have to say “thank you” to the organizers (especially Martin Bell (blog)) who made it possible that I could attend at Klaus’ (blog|twitter) all day session although it was already full. They just got a bigger conference room in the hotel next door. Great!
Thursday – Training Day!
So, the Thursday was all about SQL Server Monitoring and Troubleshooting. Klaus did a great session covering a lot of different topics which I cannot describe all in detail. I have written 8 pages of session notes and have to rethink about all the stuff I have learned. And there was really a lot to learn. Klaus did an amazing job and I can definitely say his session has a lot of light bulb moments. So here´s the the agenda of the session (hopefully my notes are correct )
- SQL Server Query Lifecycle
- Performance Monitoring Methodology
- SQL Server Profiler
- Wait Stats
- Exec Plans
- SQLDiag & SQL Nexus
I cannot explain every module of the session, but now I know a little bit more in detail what happens inside SQL Server when you execute a SELECT statement (keywords relational engine & storage engine) or an UPDATE statement. Also I have now my red line regarding Performance Monitoring. Establish baseline, identify bottlenecks, make one change at a time (you really have to keep that in mind!), measure performance and repeat. Also, I´m now aware of the fact that an auto update statistics only happens when 20% of changes inside a table happened. Now think of large tables…
Another (in my eyes) important thing: look out for auto created statistics. If you find a lot of these then SQL Server is missing indexes.
But there were tons of other stuff as well. Just a few keywords:
- most prominent wait types,
- how to read execution plans and understand the common operators,
- plan cache
- memory clerks
- query memory
- and so on…
And, as Klaus said, this session was only the tip of the iceberg! If you’re now curious, look at his webpage and what kind of training he´s offering.
Friday – 1st Conference Day!
After a really cool Keynote by Mark Souza it was time to attend to my first session that day.
And what should I say about Conor Cunningham’s (blog) Update query deep dive session? Let me quote his 2nd note: Most of this talk is beyond what CSS will support if you call them. His session was really a deep dive into the Query Optimizer and I felt that I attended a Level 900 session! I really have to re-read his slides (the slides can be found here) as well as the chapter “The Query Optimizer” in the book SQL Server 2008 Internals. Maybe this will shed some light.
Next session for me was the Notes from the field: Cross team performance troubleshooting, presented by Justin Langford (blog|twitter) and Gavin Payne (blog) from Coeo. It was a practical guidance about the approach of a problem, e.g. how to identify the stakeholders and bring them together. I´m looking forward to get the presentation slides to take another look, maybe it´s possible. Justin and Gavin provided good information during that session. Now I have a new keep in mind: “Do not listen to hear-say or theories and do not fix issues on the fly”.
The last session session on that day was also presented by Gavin Payne: Mixing virtualisation with SQL Server – a checklist for success. Really interesting presentation where Gavin not only talked about good virtualization candidates, but also showed up a Virtualization 101 as well or what you should avoid or take care of when starting to virtualize SQL Servers (e.g. avoid RAID 5, think about resource pools inside the hypervisor…) You will find many useful question and hints in the slides.
Saturday – 2nd (but also last) Conference Day!
My Saturday began with the SQL Server Clustering for dummies – session presented by Mark Broadbent (blog|twitter). Wow, what a pace! But it was everything in the session you need to know when you want to start with clustering. And the slide deck is full of good advice, so take a look! As a reminder from Mark (100% ACK from my side): “The DBA should control the cluster.” It´s quite easy to explain: Think about the business needs. No one will ask: What happened to my windows server? But: Where is my database?
The forthcoming session was Consolidation and Virtualization Strategies (including journey to the cloud) presented by Ross Mistry (blog|twitter). Now that man knows how to turn a presentation into a real experience. Ross did not only show up the three reasons why to consolidate (costs, control, consistency) but also talked about the key enablers, how to consolidate (database and instance consolidation) as well as the Utility Control Point and how UCP helps you to keep control of the resources. And it even got better once Ross started to talk about private and public cloud solutions. Especially the private cloud solution (combined with an automation layer and a management layer) was very interesting for me and I instantly began to realize different opportunities. Maybe I get a chance in the future to work with cloud solutions as well. And now I cannot wait until the slides are available online….
After a little break that I took to visit the SQLSentry booth (Hey Nick, where´s the promised email ? ) it was time to visit Klaus Aschenbrenner again. This time Klaus talked about SQL Server 2008 Database Internals. I learned a lot about how SQL Server stores data physically, e.g. pages are grouped into extents, the difference between mixed and uniform extents and how SQL Server manages all data pages/extents with the help of GAM and SGAM pages. But Klaus also provided information about table metadata, allocation units and also gave a good insight into the structure of a data page and how important a good table design (e.g. column definition) is. The keyword is payload of a data page.
The next to last session I attended was about How DBAs can use PowerPivot for Performance Tuning /Troubleshooting. Andre Kamman (blog|twitter). Andre provided really good advice how to take advantage of PowerPivot to analyse performance data mixed with a good sense of humour (the short look onto the PowerPivot Client/Server architecture for example). Beside the sponsor sessions this was one of the most entertaining ones for me.
My last session of SQLBits8 was presented by David Morrison (blog) – The Dark Arts-a.k.a Performance Tuning, indexing and query plans. David showed a lot of examples regarding common misconceptions. One of the keyword here: SARGability. It is now a little bit easier to understand why functions break the SARGability and that sometimes subqueries are using the same exec plan as a join. Afterwards David disassembled a lot of query plans.
Marginal notes from SQLBits8
SQLBits8 was also a great chance to meet a lot of people in person I only know through their tweets and/or blogs. So I could thank John Sansom (blog|twitter)now directly for his great “Something for the weekend” blog series. If you don´t know his blog, take a look!
It was also a pleasure to meet Ola Hallengren. We talked for about an hour about his maintenance script, which you can find here. He showed me an option which is implemented in his solution for quite a long time now, but I wasn´t aware of it. On the other hand Ola got also Feedback from me how I´m using his solution and what modifications I made in the setup script (outside his procedure) regarding the backup locations.
It would too long to mention all the people I have met but I think they know who I mean.
And also a BIG thank you to all SQLBits organizers. It was a great experience for me and I´ll try to be there next time.