Pass 2008

PASS presentation and updated demos

I’ve had several requests for the presentation and demos from my presentation at PASS USA last year , so here they are. This is the presentation that I also did at the January SQL usergroup meeting and at the February meeting of SADeveloper in Durban

The demos are for SQL 2008 and use the SQL 2008 AdventureWorks database (downloadable from Codeplex), but they should work without too many problems on SQL 2005 against the 2005 version of AdventureWorks

Presentation – Dirty Dozen

Demos –

The setup script adds a couple indexes, a column and a fair bit of data, so take a backup of Adventureworks before running that if you want to be able to get back to the original version.

PASS roundup

I don’t normally do posts that are mostly links. I figure that people can use google for that. As an exception though, here are some of the other people that have been blogging about the PASS summit this past week (in no particular order).

If I’ve missed anyone, blame google.

Steve Jones
Grant Fritchey
Andy Warren
SQL Batman
Joe Webb
Adam Machanic
Lukasz Pawlowsk
TJay Belt
Neil Hutson
Brent Ozar
Jeremiah Peschka


Louis Davidson

PASS – Last Day

The last day of PASS is here. It’s been a hectic week.

I spend the morning attending the MVP sessions. As such, I can’t say much about them. All I can say is that there’s some really exciting stuff coming in the next couple of years.

The presentation this afternoon went well, much better than I expected. Even though a couple of demos didn’t quite work. I noticed afterwards that one of the things I was demonstrating the optimiser couldn’t do, it can in SQL 2008. Oops. I’ll fix the demos before I upload them. I should get them up within a week.

The last session’s another case of how not to do things. Backups, security, encryption, database design, naming conventions, data types, stored procs. Why bother? :-) The audience was more involved than I would have expected at than I would have expected late on the Friday.

All done for another year. it’s been awesome. I have a few days to play tourist before going home.

PASS – Thursday

I decided to skip the keynote this morning and spend some time testing my presentation on the provided projector. Looks like it’ll be fine, we’ll see tomorrow.

The first session I attended was Bob’s level 500 session on debugging memory. He wasn’t joking that it was a level 500. It probably should have been more. It was absolutely excellent, as always.

The afternoon sessions started with another CAT presentation on performance troubleshooting, management data warehouse and extended events. Extended events were what I was most interested in. Bob briefly went over events in his usual high-speed style. the most intersting part was the system health check that’s included in 2008 and is on by default. Kinda an improvement over the default trace of 2005.

The events that caught my attention that the extende events can log are the deadlock (only in CU 1) that automatically logs the deadlock graph any time a deadlock occurs, the page split, which finally gives a way to see what pages are splitting and how often, and the checkpoint start and end events which will finally answer the question of how often checkpoints occur.

Lastly, I sat in on a session on spatical indexing. It’s interesting how the spatial indexing uses the underlying b-tree structure of SQL’s index architecture. The biggest problem seems to be that the optimiser doesn’t seem to cost spatical queries correctly in some cases, meaning hints are needed to get the queries running fast.

Wednesday at PASS

Wednesday is the first full day of the conference and the number of people around doubled.

The keynote was interesting. I’ve already written about that so I won’t say anymore.

The first session I decided to attend was by Mark Souza of SQLCat, the customer advisory team. He went over what they’ve seen with customers and how the various features of SQL 2008 have been used. Second session, after lunch was also by the CAT team and concerned security and auditing, covering some of the 2008 features, TDE auditing and the new key management options.

The last session I did was Paul Randal’s corruption survival. Paul’s always entertaining. He did a whole set of demos and told some rather frightening stories.

The day ended with a trip to the dojo and an MVP party. Photos to follow sometime.

PASS Keynote

The opening keynote for PASS was delivered by Ted Kummert, the vice president of the data and storage division at Microsoft. He gave us a look at some things that are in development at the moment and are expected to be released before the next major version of SQL.

The first of these is Kilimanjaro. This is not modifications or fixes to the current version of SQL, rather it’s a managed self-service tool for BI as well as managability improvementys for the higher end setups.. They expect to deliver this sometime in the first half of 2010.

One of the things included in Kilimanjaro is a massive improvement in multi-server management inĀ  Management Studio. It adds a concept called the SQL Fabric, which contaions a number of servers with a number of applications. The servers can be managed through the fabric control server, which can also provide an overview of how all the servers within the fabric are running, whether they are overloaded or have available capacity. the fabric controller also stores historical trends for the servers that it controls.


The second day of PASS

I attended Kimberly Tripp and Paul Randal’s session on database maintenance, from planning to post-mortum. Absolutely fantastic. I think it should be titled ‘A Database Comedy’ though. :-)

The day started with a discussion on sql internals and query processing details, and then, after lunch dove into details of various maintenance tasks, including file maintenance, log maintenance, index maintenance, backups and restores, consistency checking, and corruption checking and recovering from that.

After the session there was the usual opening party and the SSC gambling party. All awesome.

Photos so far –

The only minor problem is that the hotel’s free internet access isn’t working. Hopefully they’ll get that sorted soon.

The first day of PASS

The conference started with a bang with a full day workshop with Itzik Ben-Gan on advanced T-SQL querying and programming. The advanced adjective wasn’t a joke, if anything it was an understatement.

The workshop started with a look at the OVER clause, new in 2005 and unfortunatly not enhanced in 2008. Itzik covered the OVER clause as it applies to aggregates and also how it works with the ranking functions that it’s usually associated with. He also discussed some of the aspects of OVER that exist in the SQL standard but are not implemented (yet) in the product. Hopefully in the next release…… Itzik also looked at using the row number function to create a more powerful TOP.

He looked at ways to create custome aggregations, including cursors, CLR user-defined aggreates and T-SQL tricks to compute aggregates that there aren’t built-in commands for, like median and product.


Off to PASS

I’m leaving today for the PASS conference in Seattle. Looking forward (not) to 26 or so hours of flying.

I am looking forward to the conference. It’s going to be a different experience this year. It’s the first PASS conference where I’m anything other than a regular attendee. It’s going to be fun.

I’ll try to post each day about the events of that day. They’ll probably be short posts. I don’t expect to have much free time there.