PASS Summit 2009 Day 1

This was my first PASS Summit and it has been amazing.

I went to the Pre-Con Session on Index Performance and really enjoyed it. I mean who wouldn't, an entire day analyzing index and statistical internals is just a really good time, right? At least thats what all of us at the session thought! I'll post more about this later.

Day 1:

Keynote

This was not impressive. Sure all the video and shiny was nice but it was all marketing speak. The two interesting bits were:

• The 192 CPU box they demonstrated SQL Server 2008 R2 running on. That is AMAZING hardware (from IBM of course) and SQL Server scaling on it was impressive.
• Hyper-V Migrations. Sure, it was done better and earlier by VMWare years ago but it's still amazing. The demo had them moving a VM from one machine to another while queries were running. No connection drops, at all. This tech is awesome, I can't wait until they are able to get rid of the IO overhead

Introducing Master Data Services

• Tooling on this is very nice and complete. Much better than the Purisma stuff I've seen. It seems to piggy-back on Sharepoint.
• I snagged John McAllister (the program manager at MS) and asked him about our specific issues.
• They haven't tested with a dataset our size before but would love to try it.
• Many to Many relationships are a hack in there too.

Ask the Experts

• I was asking about Entity Attribute Value modeling techniques. Generally it was recommended to go with the XML data type. When I raised scalability concerns with the volume of data we use they agreed it may not work.
• An interesting idea was to have a CLR trigger analyze the data instead of a set of web services.
• We also have an issue where we are doing a join outside of DB. There are about 12 queries that hit at once and they don't release query memory until the last row is pulled off the recordset. I need that memory released when the query is done, not when the recordset is delivered. I spoke with the JDBC and Database Engine folks. It was great.
• It was decided that it was unlikely the JDBC driver (though he recommended switching to Server Side cursors, which we did but they add 25% to the processing time).
• The queries all have an order by which means it should be storing the work in tempdb and not keeping the cache. I emailed the MS guy the query plans for further review.

Managing TempDB

• This was the least impressive. The talk was given by two very smart folks from MS but the presentation wasn't very polished.
• The most interesting thing was demonstrating the how snapshot isolation uses the tempdb. I wasn't familiar with the DMVs and performance counters they used for this.
• The major advice was the old chestnut of "match the number of cores to the number of tempdb files".
• I also liked how they pointed out the transaction log in tempdb follows different rules. Specifically there is no "REDO" information recorded because tempdb is reset on each startup anyway.
• There is also something special that happens with object creation in tempdb. Because the same objects are created over and over again (#t for instance) they cache the object definitions to avoid the insert time into the system tables for something so transient. Clever.

Designing and Building Private SQL Clouds (not SQL Azure)

• Awesome. Ryan Jones and Sarah Barela made a great team.
• They explained how MaximumASP is built it's own cloud infrastructure with a combination of Hyper-V, SQL 2008 Enterprise and Powershell.
• They leverage Hyper-V's migration features to move VMs around seamlessly.
• The SQL 2008 Enterprise license allows you to use as many SQL Enterprise instances as you want on a virtualized machine.
• The Query Governor and Performance Data Collector help a lot with managing VMs.

Wow. That was one amazing day.