The SQL Down Under Code Camp is over for the year.  Unless you go to the one in New Zealand :)

So I did the review of the day yesterday, and I thought I better polish it off with a review of todays sessions, and what was covered.

I'm mostly doing this from memory, because there wasn't so many notes to scribe down today - most of it will be covered by the powerpoint slides and the code samples when they make their way to the website.

Session 9 and 10: Itzik Ben-Gan on Cursors vs Set-based Queries

http://www.sql.co.il/

You know, I woke up a little late, and forgot that it was a good half hour to Wagga so I missed the first 10 minutes of Itzik's presentation... and I am totally kicking myself.

I was up in the air about whether to even attend today's sessions, and I ended up showing up after hearing Rob Farley talk about how Itzik was "the man" when it comes to SQL during his talk.

The guy wrote the book, in fact he wrote several books and on an SQL Server Magazine blog.

Needless to say as soon as I was there for my first five minutes I was so glad I showed up to the session.  I know Itzik has done several sessions around Australia and had some training around the place - if you ever get the opportunity definitely see this guy talk - out of everyone that presented, with all their assembled knowledge - if anyone knew their stuff it's this guy.

Basically Itzik went over the differences between Cursors and Sets when querying and showed the overhead associated with Cursors.

The idea being in most circumstances that Set based queries are much better.

There are circumstances that Cursors are better, and you can see them when the slides come up on the site - however most seem to be where you need to process calculations row by row.

And the great news is that the new order() and partition keywords can replace some of these ... and hopefully in the future if Microsoft bring in the framework to perform the ordered queries across aggregates then things will really improve.

Rob Farley started the training on this yesterday with his talk, and Itzik really continued it and took us further into why you'd use it, and showed us the performance gains.

Then he worked his way through a query and showed it going from 26 seconds and thousands of reads using Cursors, to 1 second and 2800 reads using Set based, to 56 milliseconds and 40 reads optimizing the Set based query.

1 second may not seem terrible to get 1000 records back, but when you're talking 1,000,000 rows it becomes 1,000 seconds (or 16 minutes).

So when you're talking extremely scalable solutions, every millisecond counts! :)

For those not in the know, if you look at how many reads are being done on your database, that is how many indexes and rows get looked up to return the data and how many will probably be locked along the way.

When I asked Greg Linwood yesterday he said that you should at least look at getting your queries down to below 1000 reads.  Now you've gotta remember that Greg deals with fairly large clients with millions of rows... so maybe when he's talking generally he's talking about that kind of scope.

I think the basic guideline on how many rows you would want a query to return is to look at how many reads it takes to return one specific row, and then multiply that by the amount of rows you expect to return.  Then try and work your way down to that.

You've got to remember that there will be some overhead, but yeah up in the high thousands is probably a bad thing and you need to look at your indexes and queries!

I have about 5 pages of code and notes from this session, and given that I work on development on one project, day in day out I really hope I can spend some time implementing what I learnt here... I've just got to make the time to do it.

I think the best thing is to choose one stored proc that is doing bad to your system, grab the query and try to optimize it piece by piece until you get it down.  See if you can work it out and maybe that'll show you what you can do with the rest of your system.

To remember:

set statistics io on

Set this command before running your query and it will tell you how many reads it's performing on the database.

Turn on the Execution Plan and learn how to read it!

Nuff said!

I really can't emphasize how good it is to see a guy talking like Itzik, he really knows his stuff and he's able to bring it across to the rest of us easily, and was an incredibly enjoyable session.

Itzik also has the code up for his session on his website.

Session 11: Peter Ward - Engineering 101 for the SQL DBA

Peter from WardyIT (http://www.wardyit.com/) spoke about the hardware considerations when getting SQL optimized.

He did mention at the start that fixing your queries, your indexes and your code is going to give you the best increase in performance at the start of his presentation, but went to mention that if you're looking at small performance increases when they're needed, then hardware could be the way to go.

I think his slides will talk for themselves, however the talk about the different raid setups and the small increases you can gain between them was interesting.

But like the man said, you have to find the solution that's best for you and benchmark your system until you get it set the way you need it.

Session 12: Trevor Andrews on SQL Management Objects

This session was pretty good - it covered SMO, which I hadn't really seen in action before.  I've used WMI to give myself a simple reporting tool that shows me the processor usage, memory, and HDD usage across various SQL boxes in our company, so I can monitor when issues occur what is going on, on the system but SMO takes it one step further.

SMO basically allows you to access various SQL counters, functions and objects within Visual Studio or scripting languages such as VBScript or Powershell.

Being able to do things like look at the settings for SQL Server databases within your application, or doing traces within your application, or watching SQL Agent Job statuses within small management console app.

I am seriously thinking about spending some time developing a small application that shows the information I already know how to get through WMI and mix it in with things like Job statuses, the current activities on the database in the form of a trace, being able to traverse the objects on the database and checking the stored proc buffer memory and the cache buffer memory like Greg Linwood showed yesterday.

Having one small app that can show me the current status of the server as it stands across many different measures.

I'm not saying it'd be useful to have running all the time, but to check out when you're having an ongoing issue would be brilliant.

I'm really glad that this sort of talk was done - opening our eyes onto something new.

Another thing that it brings to mind - given other people talking about unit testing throughout the day... the ability to run a test on the database from your application to check that the database looks the way you'd expect it to (rows, columns, keys).  Pretty cool.

To remember:

You need to install the SQL Client Programming add-ons from the SQL Server CDs.

Obviously you need the framework in order to program against it!

Include the following references into your project as a start:

• Microsoft.SQLServer.ConnectionInfo
• Microsoft.SQLServer.SMO

Also add the following in your using statements:

• using Microsoft.SqlServer.Management.Smo;

From there you can reference your server as:

• Server svr = new Server("servername");

In the examples, and the Microsoft examples there is examples of how to populate a form with the databases on your network as you'd get with the newer Microsoft tools such as Reporting Services.

Session 13: Peter Myers on Analysis Server Cubes

Peter continued on from his talk yesterday, and from Darren Gosbell's MDX talk (yeah it took all of one day for me to be kicking myself that I wasn't all that into his talk!).

I can't really go into details, there's a lot of information on Peter's slides, but it's really one talk that you have to see live as he goes through the tabs and the functions within SQL Analysis Services.

Seeing how a cube is worked was really pretty cool, but I'm still stuck from the fact that I've never really played with the technology itself, so I don't have that point of reference to get the session.

Still seeing what it produced, and how easy it was for Peter to manipulate the queries and the cubes and expressions was brilliant and I think it would be incredibly powerful for the company I work for.

It was good to see how it's done, even if it will take me a lot of practice to really get it :)

Session 14: Greg Low on Visual Studio Team Edition for Database Developers (DataDude)

To be honest, it was a great presentation on a program that doesn't really excite me that much.

Like I mentioned previously, I work on the one project, and it's basically in one location and there's not really the need to store changes to the database.

For testing it could be good though, being able to set up unit tests and such.

The presentation was a good one, and it seemed to be an interesting piece of software that could be useful - but the price is something I don't think my boss would be interested in, and the thing that sucks about it is - all the IT guys at my job are developers who have to do their own database stuff (we don't have dedicated DBAs).

So basically we'd be stuck getting VST for Developers and for Database Devs because they're separate SKUs.

I think we get enough out of Vault and we could always use nUnit for testing if required.

Plus although the software did seem interesting, the CTP wasn't all that exciting.

What else?

Well, Adam Cogan was back with more mugs!  And I saw a few guys go down and grab one off him without a business card, and Shane from the group did the same so I asked for one and he was nice enough to give me one.

Of course he's looking for cards to expand on his business, adding us to his list of clients, and his list of contacts... and seriously, if you're getting something from the guy I think that's fair enough - no one asked you to grab a mug off him :)

So, if/when I get business cards, Adam - you can expect one to be sent to you. Fair's fair.

Yeah I know that's really not all that exciting to him, but hey, it's the thought.

So I got my mug that I wanted (and seriously, nice swag they are too...) and there's also a print out of the courses on offer over at SSW around Australia.

A couple that sound interesting to me that you can find on the website (I think):

• Sydney Tech Breakfast - Introduction to Microsoft Expression Studio ($150 + GST for a 3 hour talk about WPF!) the 3rd of November, 2006.
• Sydney Tech Breakfast - SQL Server 2005: Advanced Indexing Strategies (again $150 + GST - 3 hrs) the 1st of December, 2006.

Both look like they could be really good sessions if you're in Sydney and want a jump start ahead of the rest of us.

I was also positioned in the middle 3rd row in, thanks to Shane wanting to be close to the action, and I was sitting behind Rob Farley for the day... part way through the day I heard him mention "crucible" and I saw on his laptop the orange on either side of this website! So I snooped a little and sure enough he was reading me.

So I tapped him on the shoulder and told him it was my site, and got to have a little bit of a talk to a SQL MVP!  Pretty cool guy, and Greg Geoff Orr also piped up to tell me that I should look into getting Enterprise edition for the Partitioning that I mentioned I wouldn't mind yesterday, but also mentioned some of it can be obtained with Partitioning Views in standard edition.  So thanks Greg Geoff and I will definitely be checking it out!

[edit: Can you believe I thanked a guy and got his name wrong?  Check out Geoff's blog and again thanks a whole lot Geoff!]

It's pretty cool that the real tech heads are fairly approachable guys at these events, and you know, if you want to meet some guys who truly know their stuff... if you want to see some great presentations and get some real insider knowledge from guys who work on multiple, large projects, day in and day out these events are a must.

I also had a chance to speak to Greg Low during one of the morning breaks, out on the grass and thank him for organizing the event and tell him how much I was getting out of it.  I probably should have saved that for the afternoon because by then I had so much more!

Seriously, this event has given me so much for two days out of my life - it has really gotten me excited about some of the possibilities in SQL, even if I don't get around to doing much with them... but it's really encouraged me to think beyond the square... to question the performance I'm getting and where the true bottlenecks are... and what I can do to improve my systems.

And without Greg and the other organizers getting together and arranging this, and taking the time out from their work and their families to make the presentations and arrange everything from getting the folks there, getting the meeting place and arranging things like the wireless and pizzas and websites... it's just a huge thing to get the little guys like me along and learning and building the community.

So again thanks Greg Low and everyone else involved.  It was a great weekend for me, and I hope to come along next year and see everyone else back there!