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.
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.
- 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.