Table Partitioning For Microsoft Sql Server

Transcript

Table Partitioning for Microsoft SQL Server with Brandon Reno About me I’ve worn many hats ● ● ● ● ● Database Developer Database Administrator ETL Developer Business Intelligence Developer Data Architect @BrandonReno +BrandonReno Overview ● ● ● ● What exactly is table partitioning? Why (and when) would you partition a table? How to get started with table partitioning Monitoring and maintaining your table partitions What is Table Partitioning? Why Partition? 1) Reduce seek time to find data 2) Reduce the amount of data that needs to be in memory in order to access “relevant” information 3) Improve maintenance performance A few terms ● ● ● ● ● Table Clustered vs Nonclustered Index Filegroup Partition Function Partition Scheme Table Nonclustered Index Clustered Index Filegroup Partition Function: The boundaries between “books”(partitions) Partition Scheme: Mapping your “books” to shelves (partitions to filegroups) Partitioning for maintenance ETL job is staging 1 million records each day, at 1 KB per record ~ 1 GB per day You’re required to keep 7 days of records for troubleshooting, but after that, the data is no longer needed. Switch statement ● ● ● Swaps a partition with a table of the same format All indexes must be “aligned” Metadata only operation (fancy way of saying “it’s fast”) Merge statement ● ● Merges two partitions into one Will cause SQL Server to read one partition and insert all of the records into the other Partitioning for Performance You receive orders in an online sales application, you have these requirements: 1. Open orders must be retrieved in < 1 second 2. Closed & completed orders must be retrieved in < 5 seconds Performance considerations Taking advantage of performance with partitioning does require additional consideration. 1. Is storage Tiering available? 2. Can your storage be physically separated? 3. Can your business identify what data they really need? Selecting a key 1. What data is most important to the business to access quickly? 2. Dates a. Where are clear maintenance windows for partition creation? Index Alignment Monitoring ● sys.partitions ○ ● ● ● Shows all partitions for all tables (even those with only 1) sys.partition_functions sys.partition_schemes sys.partition_range_values What does it all mean ● Table partitioning is a great way to improve performance in specific scenarios ○ ○ ● Improving maintenance times for high volume tables Improving application performance for the most critical records Selecting a partitioning key Resources http://www.brentozar.com/sql/table-partitioning-resources/ http://www.simple-talk.com/sql/database-administration/gail-shaws-sql-server-howlers/ https://msdn.microsoft.com/en-us/library/ms188730.aspx