Day 2:
Lab Time
I skipped out on the keynote this morning because it was more marketing fluff and PASS specific awards. I followed it on twitter while working through some training at the MS Lab. The lab is a bunch of machines setup with dual monitors, virtual machines and instructions. It's great because it allows you to go at your own pace.
I did labs on SQLCMD and Partitioning. SQLCMD is interesting because it allows you to do things like swap connections mid-script. Handy, but not exactly that I was looking for. The partitioning lab was awkward because the solution scripts were not setup right. I had to set the path for all the scripts that were read in pro programmatically (surprisingly enough, using SQLCMD).
Developing with SQL Server Spatial, Deep Dive into Spatial Indexing (Michael Rys)
This was fascinating. The fellow who gave this presentation is in charge of the Spatial feature at MS. He covered the indexing method they used, and why they chose it. It turns out to be based on the normal B+Tree model with a grid overlaying the information. Each square on the grid has three states:
- Definitely Not intersecting
- Definitely Intersecting
- Maybe Intersecting, Maybe Not
The Maybe was the interesting one. This be because Floating Point math is used to calculate the information and the the a margin of error for that listing was above a specific threshold.
The detail of the grid is recorded with internal tables (that can be queried) and clustered by the grid sequence),
He also explained why the Geography data type has fewer features that the planar Geometry datatype. The definitions are clearly defined for planar data but they are are ambiguous because of earth curvature for Geography. Until there is a standard definition (or possibly a huge user outcry?) they will not implement those functions for the Geography data type.
Finally, after the presentation I walked up to the man and thanked him for the incredible work his team has done. I explained about the performance improvement we had over Oracle Spatial and he was surprised. The goal of the SQL Spatial team was apparently to not be more than 3x slower than Oracle! Amazing, it is much faster in my experience.
SQLCAT: Customer Experience with Data Compression (Sanjay Mishra)
There are two types of data compression, Row and Page.
Row compression may take up to 10% CPU overhead and it uses the minimum bytes to store the info being collected regardless of the data type. In SQL 2008 R2 it will also support Unicode using the Standard Compression Scheme for Unicode. This is more efficient then UTF-8 because it adapt to your primary character-set being used in the field for that record. If you have a mix of English and Japanese it will make the dominant character set require only one byte per char if possible.
Page compression varies a lot in terms of CPU overhead. It does Dictionary and Prefix compression. These effectively mean that the DB will record a string only once and if that string is used again on the same page it will use a pointer instead of repeating itself. This means that each 8k page is compressed independently.
This doesn't improve IO time, it will in fact take longer to do inserts and updates. Select speed may improve and storage space needed should decrease a fair percentage. The case studies they showed were 30% - 40% savings, which adds up when talking about TB of SAN storage.
Dude, Where is my Memory? Understanding SQL Server Memory Management and Usage (Maciej Pilecki)
This was an awesome presentation. There wasn't much new there but he covered a lot of what I had to learn through blood, sweat and tears. He did a great job covering the 32-bit vs 64-bit memory model and some of the pitfalls associated with AWE. He also spent a good amount of time on the Lock Pages in Memory option. Sure, a lot of people posted about it when it came out, but it bears repeating. If your DB is being swapped out to disk for no discernible reason, set this option on! I've had SAN and Network drivers cause this problem for me and this was a lifesaver.
No comments:
Post a Comment