Monday, 19 April 2010

The SQLBits VI Experience

I had high expectations about SQLBits VI and it certainly lived up to it! Not only did it have great speakers, who really know their stuff and have an effortless way of getting the information across, but it was also free. So it goes without saying that it was a popular event. There were 4 sessions going on at any one time with a theme of performance and scalability and plenty of choice across DBA, Dev and BI areas. It did mean some tough decisions had to be made between which ones to attend!

Monitoring and Tuning Parallel Query Execution

The first session I attended was held by Ramesh Meyyappan on the DBA track (you can see some of his webcasts here). I chose it as out of the 4 opening sessions, it seemed most relevant to my day job over the others. If I'm honest, I wasn't expecting to get much from the talk beforehand; not because I thought I knew it all - quite the opposite in fact, as I didn't understand some of the terms in the overview (CXPACKET, SOS_SCHEDULER_YIELD...you what? what's a doobry-wotsit-dicombobulater?). Nor did I appreciate the significance of the topic. These were terms I knew of before, but that I didn't actually know about - probably as I felt they were a bit too much out of my comfort zone so when I did come across the terms in the past, I didn't dive down into them to really find out about them.

What a great session it was. Ramesh certainly knows his stuff and I'm glad I opted for it. What was great about it, was the way Ramesh shared his knowledge - his ability to explain things in such a clear way that makes complete sense to people like me who were learning new things was superb, especially given the time constraints. I now have a greater appreciation of MAXDOP, CXPACKET, SLEEP_TASK & SOS_SCHEDULER_YIELD - seeing live demonstrations gets a big thumbs up from me. Seeing real examples of where running queries in parallel can be slower was very beneficial for me. CDs were given out with all examples on so I'll firing that up soon!

Inside the SQL Server Query Optimizer

Conor Cunningham, Principal Architect at Microsoft, was one of the must-see speakers for me and one I expected to draw a big crowd. He's a natural speaker, with an informal nature and did an entertaining talk which I rate extremely highly. "DMVs basically exist because I was p*ssed off" was a particularly entertaining moment. Hearing his insight into how the Query Optimizer works definitely made it a session well worth attending, as proved by the size of the audience - the room was packed. "Snug" was becoming a theme word for the day already!

Note to self: get on with reading "Microsoft SQL Server 2008 Internals" which is currently sitting on my desk.

Performance Monitoring and Tuning - Tips and tricks

Another DBA session by Ramesh Meyyappan that didn't disappoint. And another one that is on my list that I want to watch back again when the recordings are available.

At this point in the day, 3 out of 3 sessions I'd attended were on the DBA track. This just further emphasised something I previously blogged about: Rise of the SQL Server DevBA. As an SQL Server developer, I found these of so much value. If you want to build performant, scalable database systems, you can't be blind to these topic areas.

Time to switch to Dev sessions

After lunch, I attended 2 Dev sessions: "Denormalisation - Having your cake and eating it" by Mark Whitehorn & Yasmeen Ahmed followed by "Improving Database Performance by removing the Database" by Simon Munro. Denormalisation is a topic I'm comfortable with and the session was all theory. The other session was something I followed as a general theme at QCon recently (as I previously blogged about). So I personally didn't take as much away from these sessions, but this was something I should have anticipated from the session abstracts. I did enjoy some of Simon's slides in particular, prompting a few LOLs.

Icing on the cake

The final session of the day for me, was on the BI track: "Designing and Tuning High Speed Data Loading", held by Thomas Kejser from the SQL Customer Advisory Team. I really enjoyed his talk on the various techniques for quickly loading data into SQL Server and on minimally logged operations. Another session I want to watch back again as it topped off a brilliant day.

SQLBits - you know it makes sense

This was the first SQLBits I've attended and if SQL Server is your thing, I completely recommend you try to make it to future ones. I know I wouldn't hesitate to attend again if I have the chance. It's a great opportunity to sponge up more knowledge and meet up with other SQL Server professionals. I had a number of good chats with other attendees, in particular SQL Server tweeps John Samson (Twitter) and Vivekanand Serou (Twitter).

Overall, this kind of event is great for broadening your horizons. Never be satisfied just with what you already know - go out there and push to learn more. Sometimes the most valuable nuggets of information take you by surprise!

Keeping my eyes peeled for the next event to attend!

Wednesday, 14 April 2010

SqlBulkCopy ColumnMappings Mismatch

"The given ColumnMapping does not match up with any column in the source or destination"

This error caused me more head-scratching than it should have done today. I was using SqlBulkCopy to bulk insert some data into an SQL Server table from a .NET DataTable, something I've done before - it's not rocket science. But I hit this error upon calling WriteToServer and it took me a while (longer than it should have done really!) to track down the problem. The ColumnMappings I'd specified, to map the data columns in my DataTable to the underlying database table columns, all seemed to tally up - each column did exist in the source DataTable and the destination SQL Server table. I was expecting that I'd made a stupid mistake and just couldn't see the wood for the trees.

ColumnMappings are case-sensitive

The problem was because the name I'd given for the destination column name for one of the column mappings, did not match the case of the name of that column in the database. Once I'd eventually identified the problem (an "i" character should have been "I" to match exactly the database schema), it was a bit of a face-palm moment. I don't see any reason why it should be case sensitive and believe it should not matter. A bit more digging led me to this open Microsoft Connect bug report from some years ago.

I won't be tripping over this again!

Tuesday, 6 April 2010

SQL CAST to VARCHAR without size

What's the result of this, in SQL Server?
SELECT CAST(12345 AS VARCHAR)
As you expect, it's "12345". How about this?
SELECT CAST(1234567890123456789012345678901 AS VARCHAR)
You may be expecting it to return "1234567890123456789012345678901" but it will in fact throw an error:
Msg 8115, Level 16, State 5, Line 1
Arithmetic overflow error converting numeric to data type varchar.
It's all because a length was not given for the VARCHAR to convert the value to. By default, as documented in MSDN, if no length is specified it will default to 30 when using CAST or CONVERT. This results in the error.

This actually tripped me up a few years back now, and I think could easily trip others up. The way to avoid it is to make sure you explicitly specify the length of a VARCHAR (or CHAR) in all places - this is of course good practice, but sometimes it can be too easy to quickly write a CAST or CONVERT statement without giving proper thought to the size.

It's also worth a note that when declaring a VARCHAR variable without defining the length, it will default to 1. This is a more dangerous scenario as it can go unspotted as an error is not thrown. e.g.
DECLARE @Data VARCHAR
SET @Data = '123'
SELECT @Data 
Returns: "1"

Just in case you find yourself trying a quick CAST or CONVERT to a string without specifying the size, bear this in mind!