{"id":25,"date":"2013-08-24T15:35:37","date_gmt":"2013-08-24T15:35:37","guid":{"rendered":"http:\/\/www.sqlkitten.com\/?p=25"},"modified":"2013-08-24T15:35:37","modified_gmt":"2013-08-24T15:35:37","slug":"sql-saturday-223-storage-tuning-deep-dive-with-wes-brown-joh-sterrett","status":"publish","type":"post","link":"http:\/\/www.sqlkitten.com\/?p=25","title":{"rendered":"SQL Saturday #223 &#8211; Storage Tuning Deep Dive with <strike>Wes Brown<\/strike> Joh Sterrett"},"content":{"rendered":"<p><del><\/del>Intro to table partitioning<\/p>\n<ul>\n<li>Enterprise Edition required<\/li>\n<li>LOTS of data &#8211; highly transactional tables with 50GB+<\/li>\n<\/ul>\n<p>How can it help<\/p>\n<ul>\n<li>reduce maintenacne tasks &#8211; indexing, restores\/backups<\/li>\n<li>improves purging\/archiving<\/li>\n<\/ul>\n<p>Partition Table -&gt; Partition scheme -&gt; Partition function<\/p>\n<p>Selecting Parition Colums<\/p>\n<ul>\n<li>You only get one &#8211; pick wisely!<\/li>\n<li>Highly used filter &#8211; review indexes; review missing indexes\/stats; review queries<\/li>\n<li>column must be pasrt of clustered index or PK<\/li>\n<\/ul>\n<p>When partitioning on dates, rounding can affect the date and possibly what patition your data will be in<\/p>\n<p>Improving maintenance tasks<\/p>\n<ul>\n<li>backup\/restore filegroups based on priority<\/li>\n<li>index maintenance by partition<\/li>\n<li>new in SQL 2014 &#8211; rebuild index by partition; update statistics by partition; can also be done online in 2014<\/li>\n<\/ul>\n<p>Performance<\/p>\n<p>Partition Elimination &#8211; view in execution plan<\/p>\n<p>How does it improve archiving and purging &#8211;\u00a0can reduce the time processes take from hours to minutes by utilizing the sliding window<\/p>\n<p>Meta dataswap &#8211; requires schema lock; alter table &lt;blah&gt; switch partition 2 to &lt;blah_staging&gt;<\/p>\n<p>Sliding window goals<\/p>\n<ul>\n<li>split and merge emplty partitions<\/li>\n<li>switch to do metadata swaps<\/li>\n<li>minimize physical data movement<\/li>\n<\/ul>\n<p>Sliding Window Steps<\/p>\n<ol>\n<li>Create partition swap<\/li>\n<li>Insert partition swap meta data<\/li>\n<li>Create staging table<\/li>\n<li>Meta data swap<\/li>\n<li>Merge partitions 1 and 2<\/li>\n<li>Mark next used<\/li>\n<li>Split to create new partition<\/li>\n<li>Update processed partition swap meta data<\/li>\n<\/ol>\n<p><a href=\"http:\/\/johnsterrett.com\/go\/partition\">http:\/\/johnsterrett.com\/go\/partition<\/a><\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Intro to table partitioning Enterprise Edition required LOTS of data &#8211; highly transactional tables with 50GB+ How can it help reduce maintenacne tasks &#8211; indexing, restores\/backups improves purging\/archiving Partition Table -&gt; Partition scheme -&gt; Partition function Selecting Parition Colums You &hellip; <a href=\"http:\/\/www.sqlkitten.com\/?p=25\">Continue reading <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[1],"tags":[],"_links":{"self":[{"href":"http:\/\/www.sqlkitten.com\/index.php?rest_route=\/wp\/v2\/posts\/25"}],"collection":[{"href":"http:\/\/www.sqlkitten.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.sqlkitten.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.sqlkitten.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.sqlkitten.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=25"}],"version-history":[{"count":3,"href":"http:\/\/www.sqlkitten.com\/index.php?rest_route=\/wp\/v2\/posts\/25\/revisions"}],"predecessor-version":[{"id":28,"href":"http:\/\/www.sqlkitten.com\/index.php?rest_route=\/wp\/v2\/posts\/25\/revisions\/28"}],"wp:attachment":[{"href":"http:\/\/www.sqlkitten.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=25"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.sqlkitten.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=25"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.sqlkitten.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=25"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}