So, I'm just back from a full day of the SQL Down Under Code Camp and I was just on the phone with Mabster who encouraged me to blog about it - partly to get me off the phone, partly because he does want to know how it went, and partly so the stuff doesn't all fall back out of my brain before I go back tomorrow and learn more!

Mostly for the getting me off the phone though.

So I started the day by getting there fashionably late - it's a bit of a drive and I wasn't all that excited about the data dude presentation.  Fool me they switched the sessions, as I thought they would and I missed most of Adam Cogans presentation.

Session 1: Adam Cogan on His Rules and Tips for better Reporting Services.

Well, I missed it.  Most of it.  But we check back on Adam and his rules for development all the time and when I checked in there it seemed to be the topic he was working through so I was okay with that.

The one thing I did walk away with was finding out that his Code Auditor also looks through and checks Reporting Services code!

If you haven't checked out Adam's Code Auditor you can find it at: http://www.ssw.com.au/

Plus apparently if you review the trial and blog about your experiences you can have it for free (although the maintenance for it will cost you, but that's a small price to pay for a good code review program that is constantly updated - imagine that maintenance for a program that is maintained!).

Adam also had a limited supply of coffee mugs as swag for people who gave him their business cards - which annoyed me because the last 3 jobs I've had I haven't ever had a business card - and free swag is always appreciated.

Maybe I can convince the boss to get me some before Adam comes and delivers a talk to the Albury/Wodonga .NET Developer Group... and hope he does a repeat :)

Session 2: Grant Paisley - Business Intelligence talk

http://www.angrykoala.com.au

So Grant was supposed to be delivering a Reporting Services talk, but a bit of that had already been covered by Adam, so he gave a talk he'd performed before on Business Intelligence, which was much appreciated!

He started by showing us how to use Excel 2007 to retrieve data from a SQL Analysis Server cube.

Then moved to putting a report up on Excel Services for Sharepoint 2007 and showing us how to arrange Excel "reports" (as they become view only) to take parameters and show different graphs and reports.

And ended up showing us how to configure a dashboard for Sharepoint!

This is going to be something I think we're really going to get into at work over the next year so it was great to see these sorts of technology in action and really got the ideas flowing.

Session 3: Geoff Orr and Table Partitioning

Damn this for needing SQL Server Enterprise edition.

Really the talk was just brilliant, and showed how you can use table partitioning in SQL Server 2005 to make a rolling archive of data, placing it into archiving tables, and partitioning your database across multiple database files.

As the guy who inherited a system that archives its data every 6 months by deleting it from the database and moving it to another database, I really appreciated the talk and really saw the benefit.

I only wish I could implement something like this!

Session 4: Peter Myers - An introduction to Data Mining and SQL Analysis

Wow.  This was one hell of a talk for me.  Seeing how you could use Analysis Server to grab data from your system and create models for forecasting, for analyzing your clients data in real ways and really get information out of the data was brilliant.

This is one that I am going to be taking to the guys at work and encouraging at least one guy *hint* to really get into looking into this.

I don't know if I can use it in my section - and my boss will probably be in charge of that area... but the sheer effectiveness of a tool that can report more effectively on our business methods, our sales, and everything else is a wonder to see working.

I can't wait to get my hands on this technology - come on my new server!

I also talked to Peter after the presentation about a point he made during the presentation.  He mentioned how you needed good data, and a good amount of it to set up the analysis models that you run your system data over.

He also said that you don't need to re-process the model too often, unless you're adding a new product, or a new person, or a new customer or a new variable that is built into your model.

Or if you get a larger set of data.

So you probably want to re-process the model again every 3 to 6 to however many months to keep an effective model.

Re-processing the model changes the weightings and the variables taken into account for the data.

Well I asked Peter about re-processing on a schedule, so even if you don't add anything, the sheer amount of data will provide a more accurate model.  His answer was that it is as simple as putting a script in for SQL Server Agent on a schedule!

Pretty cool.

Session 5: Darren Gosbell - An Introduction to MDX

I am sure I would have gotten much more out of this one if I had any history with it...  the guys I was with weren't all that into it either - mostly because they were developers and integrators and haven't got that level of background with SQL.

I think most of the room was in the same boat.  If there was more of a full SQL background in the room I'm sure this presentation would have gone off!

I'm pretty certain that over the next year as I try to get more into SQL that I'll be kicking myself that I can't remember more of it.

Session 6: Greg Linwood and SQL Indexing

A session that goes straight to my heart.

Greg went through and showed us all the different types of indexes, how they work and why you'd choose one over the other.  There was also quite a bit of talk back and forth between the MVPs through this one.  I guess it goes to show you that there's no one true way of doing things and different things really work different ways for people and different circumstances.

My biggest problem will be remembering enough of this as I go through trying to improve my servers performance at work.

Things I have to remember:

sys.dm_db_index_usage_stats view

This shows which indexes are and aren't being used and how they're being used (scan/seek/user/system)

select * from table 

where database_id = DB_ID(N'database name')

and object_id = OBJECT_ID(N'dbo.database.table')

This shows stats about indexes on a particular table.

It's best to probably check out his powerpoint when it's released.

Session 7: Greg Linwood and SQL Performance Tuning

Again, brilliant.  Greg went through how SQL worked behind the scenes for memory management, then showed us some tools like the SQL Profiler and SQL Performance Monitor.

Also recommended the book: Inside SQL Server 2005 Querying for the chapter on using the Profiler.

I asked a question that probably came off as stupid when talking in a room full of tech and SQL guys, to an SQL performance guru, but it was something that I have been wondering for a little while.

Greg mentioned that for testing true performance sometimes the SQL Profiler's duration column wasn't reporting the truth because a query stays open for the duration that a program will keep that in context.

IE for an Access back end in my system an SQL query duration is minutes depending on how long someone might accidentally leave a drop-down active on a form even though it's only returning a couple of dozen records.  I found this out a while ago because I couldn't understand that.

So the better way to measure performance might be the reads in the profiler for the query.

Well I've been looking at that for a while, but it's tough to tell.  Some queries have very few reads but some could be up in the thousands - so how do you measure reads when you don't know the baseline of what to expect as a baseline?

Well he kind of avoided the answer to begin with which made me figure that I had asked a very stupid question (probably did!) but in the end said that you should at least try and get the queries down to at least under a thousand, depending on how frequently you use the query (lower is better for more frequent) and if the query should have been compiled (if it has been run recently it should be in the cache and quicker).

So there you go, if you've ever wondered what to look for in the SQL Performance Monitor for the Read value baseline - under 1000 is the key.

I guess it also depends on how many rows you are returning, and how much detail and if it is grouped by or something else.

Also checking against the execution plan to see how it's really building the query in the database.

Things I have to remember:

SQL Performance Monitor -> Buffer Manager -> Page Life Expectancy

This part of the performance monitor shows you how long the database pages are staying in active memory.  The shorter this is the less they are being kept in memory, which is a bad thing - because it means that the server isn't caching the data in memory, but instead going to disk more often.

Buffer Cache Hit Ratio

Shows how often the cache is accessed in relation to how often it's going to disk.

DBCC Memorystatus

I'll have to go back to the presentation notes for this one, but this SQL query shows the active buffer.  You can then read the value for Procedure Buffer (*8192 / 104857) to get the amount of active memory allocated to the Stored Procedure cache, to the Buffer Memory (*8192 / 104867) to get the amount of memory allocated to the page buffer (actual SQL Data Cached).  The math puts it into megabytes (MB).

Session 8: Rob Farley - Ranking and Windowing functions in SQL Server

Okay, so first I should possibly apologise - I said something stupid out loud to Rob during this presentation that possibly momentarily broke his concentration... mostly cos I thought I was helping, but I probably wasn't.

And hopefully its already forgotten.

I'd feel slightly guilty excepting he made comments through every other presentation including the marketing guy shilling the ITMasters courses :P

I've never heard of the functions that he brought up today - probably because they're for SQL 2005 and my server hasn't arrived yet - but this presentation really blew me away.

There was stuff during this that I was thinking back through some of my stored procs about how I could make things more efficient, throw away some of the temporary tables and other things that I have done to work around problems.

Chicken! Remind me that we should look at that query that I helped build for you for Reporting Services that used the temp table, I think we can do this much better now! :)

Basically there's a new set of keywords such as over(), row_number(), rank... seriously I will not do this justice and you should definitely download his powerpoint slides and the SQL that he gives out as soon as it's up online.

Rob is giving the presentation to the Adelaide SQL Server Group this Thursday night, so I really advise you to attend if you can.

Seriously, go and search for those keywords and see how powerful they are to give you new ways of doing your queries the easy way!

I really wish I could have this presentation on video to show Mabster and the other guys... it really could come in handy.

Things I have to remember:

How easy this was:

select count(*) over() as cnt, * from table

What does this do? Adds another column at the beginning of the table with the number of rows in the table... easy, huh? I guess thats not so difficult anyway, I wrote it to remember the keyword.

select * from (

select row_number() over (guid) as row)

where row between @startnumber and (@startnumber + 100)

order by row

Okay, I'm not totally sure about this code, he was flicking back and forth - but check the code he releases.

What it's supposed to do: Add a column to a table with a row number, for each row sorted by guid which in my mind would be a guid on one of our tables, or a unique identifier for each row in your db - but from there once you have a number for each row, it will return the rows between the start and end numbers.

Where would it be useful?  Well in the case of having a dataset where you wanted to return 100 rows of the set... and then if you had more results you are able to hit a next button, pass 100 to the procedure and get the next 100 results... without worrying about the order of the data, and making a long query, returning top(x) and working all that out.

Rob talked about it being useful for an ASP page, but it could also be useful if you wanted paging in your winform apps.

What else?

Well, I got to hang out with Shane and Anthony, and Anthony's guys from his work for the day... so the AWDNUG represented!

Even if certain members piked just because of serious health concerns.

Some people just aren't committed enough.

I really wish the whole day had been filmed for release on DVD... it's one thing to watch these presentations, and to get some powerpoint slides and code samples... its a whole 'nother thing to actually get the story behind the presentations - plus I'd like to be able to show other people who couldn't make it... and I'd like it to remind myself.

It's a shame that wasn't on offer here.

I also got to hang out a little with Adam Cogan during the first break and mentioned to him that I borrowed his Reporting Services presentation from DNR TV for my AWDNUG presentation last month.

He was really cool about it, and a great guy to talk to - obviously very committed to the community.

We also got to talk a little about Carl Franklin, and how DNR TV was done, so that was pretty cool, since I spend a tonne of time each week listening to Carl, so it was groovy to hear a little about that experience.

Plus Adam might be coming round to Albury to present to our group sometime, which would be really cool.

All in all it was a pretty packed day and I got a lot out of it.  I just hope to retain some of it and get to use some in the near future.

Now I've just gotta get some rest and work up the will to drive the couple of hours for tomorrows!

Still not sure if I'll go or not, we'll see how I go when I wake :)