Overview
Today was mostly about Table Design, Partitioning, with a little of Indexing. Partitioning is a big, complex topics with a lot of intricate details and interactions. If you are thinking about implementing partitioning using either partitioned tables or partitioned views, do your homework. Don’t just implement either without knowing the background of both. This is not a simple subject.
Another key take away is, be smart when choosing between heaps and clustered indexes. Heaps have their place, but good CIs are the better choice in most cases. Also understand the effect that choosing certain CIs will have on your non-clustered indexes. Beware choosing a bad CI.
After hours, SQL Sentry did a demo on their Event Manager & Performance Adviser products. Practical experience behind the products. The disk info they give is amazing. Tracking down blocking and deadlocks look really easy using their tool.
Table Design Strategies & Partitioning
- Partitioned views more likely than partitioned tables
- Neither solves all problems by itself
- A single large table toes NOT have to be one table
- Partitioned tables – EE only
- Partitioned views – Any edition
- http://sqlskills.com/BLOGS/KIMBERLY/post/Little-Bobby-Tables-SQL-Injection-and-EXECUTE-AS.aspx
- Staging filegroups, once clean, Rebuild Clustered Index on destination FG
- PV: check constraints after load
- PV: nothing protects you from creating different indexes on participating tables
- PV: make sure indexes match
- Re-enabling constraints should be done WITH CHECK
- PT Function = Logical, Scheme = Physical
- Do Partition Lab, then read Kim’s 2005 WP, then read Ron’s 2008 WP using DATE, then Online Ops lab
- Using functions in defining the partition function is a one time calculation upon creation
- Switching out tables is easier than switching in
- Requirements are checked when switching in
- Set next used file group
- Constraint
- SPLIT Function
- SWITCH
Indexing for Performance
- SQL Server has an error 666, if you exhaust a uniquifier (INT) on a poorly chosen clustered index
