Friday, 18 June 2010

The SQL Server MasterClass Experience

SQL Server MasterClass 2010 was a very high quality, one day event presented by Paul S. Randal (Twitter | Blog) and Kimberly L. Tripp (Twitter | Blog) - not that they need any introduction of course. In fact if you don't know of them, then perhaps you should close down your SSMS window and switch off your machine - after reading my blog of course :). Maybe uninstall SQL Server too...

The A-Team
With two of the best SQL Server trainers in the industry offering a packed schedule of sessions, it was an event I'd been looking forward to attending since first hearing about it and it certainly lived up to the expectations.

That DevOps Thing
The keynote on "Bridging the Gap Between Development and Production" was a great start, reminding me of the "Dev and Ops" track from QCon London earlier this year (check out Sam Newman's talk: From Dev to Production Through Build Pipelines and Teamwork). Developers should appreciate DBA perspsectives, and DBA's should appreciate developer perspectives. Bring the two sides together, work more closely, share knowledge and think and behave as a team. It was a captivating session, and one that really struck a chord with what led me to blog about the "Rise of the SQL Server DevBA" back in March. Prevent pain later, by thinking ahead about how things will perform in a production environment with production-sized data volumes is a must. But don't just "think" about it, test it - and no, testing it out on a development database with a small amount of data does not qualify as testing!

Who ya gonna call? Mythbusters!
A session on busting SQL Server myths followed. I recommend checking out the "misconceptions" category of posts in Paul's blog here. Go on, you may just find you have a misconception about an aspect of SQL Server! It was clear long before this point in the day, that this event was extremely good value for money and the quality was truly top-notch.

After a couple of platinum partner presentations and lunch, the next session was on database recovery techniques. For me, as a developer, this was one I was prepared to not be as relevant to me. However, I was surprised by just how engrossed I was by the topic and the demonstrations. That's truly a great credit to Paul and Kimberly's style of effortless and engaging presenting skills.

The GUID, The Bad and The Ugly
The penultimate talk of the day was on GUIDs and how you really need to understand the implications of using them as a primary key. Especially if it's a clustered key. Random keys like this are just screaming out to cause performance problems. Think about it - every time you insert a new record with a randomly generated key, the position of that row in the clustered index will be random - it won't be just added on to the end like it would if you have a monotonously increasing value like an IDENTITY. So where that record needs to go, there might not be space meaning SQL Server has to create space by performing page splits. This can add a significant extra amount of work and load, degrading performance markedly. Then there's the fact that the GUID key will be present in each and every nonclustered index too - it's hardly the smallest of keys (16 bytes for GUID compared to 4 bytes for an integer for example). This topic was something I investigated a while a go, when I wanted to demonstrate the issues you can get with random keys - I spent some time monitoring the Page Splits performance counters, as one way of visualising the impact. So this was another session that really struck a chord.

A final session on Essential Database Maintenance closed off a fantastic MasterClass from the best in the business. To pick out just one nugget, check out Paul's blog post on I/O error 825 - if you're not monitoring for this read-retry error already, then make sure you do as it can flag up an impending I/O subsystem failure earlier, giving you time to do something about it!

A few of us headed down to the bar for drinks afterwards (very well deserved for Paul and Kimberly I should add!), and it was a pleasure to meet them both and give my thanks for a superb day. I really enjoyed chatting to everyone; it topped off the day perfectly.

I would jump at the chance to attend future talks by Paul and Kimberly - if you get the chance to, I cannot recommend it highly enough. Awe-inspiring knowledge, that is shared in such an effortless and enjoyable way, bouncing off each other. It was also a great opportunity to put faces to some of the names of other SQL tweeps - even if I did suck at playing the "try to recognise people from their Twitter pic" game.

Monday, 14 June 2010

SQL Server XML datatype with CDATA

So today I learnt something new - it turns out the XML datatype in SQL Server does not preserve CDATA sections.
e.g.
DECLARE @XML XML
SET @XML = '<Test><NodeA><![CDATA[Testing cdata section <woop!>]]></NodeA></Test>'
SELECT @XML

Results
------------------------------------------------------------------
<Test><NodeA>Testing cdata section &lt;woop!&gt;</NodeA></Test>
After a quick dig around, I found this MS Connect case. I personally would like to see it accept whatever you pass in without silently altering it, as long as it's well-formed XML of course.