Post reply

table partitioning

  • September 3, 2016 at 12:53 am

    #329933

    Hello,

    I am a beginner in this topic.

    Is there a simple tutorial somewhere which shows me how to partition a table by month?

    I have not been able to find such a site.

    thank you

  • September 3, 2016 at 1:10 am

    #1899074

    Okay... having added a bunch of references about partitioning, read this one[/url] first... It's Gail Shaw's... (don't know why I didn't start there!). Be sure you understand what partitioning does for you (and what it doesn't) before going through all that work.

    Maybe Pinal Dave[/url]'s explanation?

    There are lots of them. Check on the MS website... around here...

    Here's another[/url]

    This one[/url] looks good...

    You should understand WHY you're partitioning your tables before you do it... Pretty sure Gail Shaw wrote something about that.

    http://sqlmag.com/database-administration/how-dynamically-create-and-maintain-table-partitions

  • September 3, 2016 at 10:22 am

    #1899095

    arkiboys (9/3/2016)

    Hello,

    I am a beginner in this topic.

    Is there a simple tutorial somewhere which shows me how to partition a table by month?

    I have not been able to find such a site.

    thank you

    To be honest and whether you're using "Partitioned Tables" or "Partitioned Views", partitioning isn't "simple", especially if you want to do it correctly.

    To answer your question, the following is one of the simplest tutorials that I've seen to date. It even includes the single lines of code necessary for each step.

    https://technet.microsoft.com/en-us/library/ms191174(v=sql.105).aspx

    There are a shedload of caveats, though, that are not explained in that document and is almost casually mentioned in much of the official documentation and many of the examples in blogs don't even touch on them.

    For example, if you've used "Table Partitioning" and if you have a DR situation where you need to do a restore of the database, you can do it "piece-meal" to quickly restore the latest partitions to "get back in business" and then take your time restoring the older, usually read-only data. What they DON'T tell you is that you can't actually do a backup of any new data occurring until ALL of the partitions have been restored.

    In that same vein, you can't restore just (for example), 3 months worth of data and successfully recover the database because it won't allow you to change the partition scheme or function until all of the related filegroups have been restored. (Personally, I "drank the Kool-Aid" and found that out the hard way and may have to undo the "Table Partitioning" in favor of another method known as "Partitioned Views", which is the only way that I can see to solve this problem).

    There's also the subject of keys. All unique indexes will have the partitioned column added to them unless you have an "un-aligned" index. Unless you're very careful with what you pick for a partitioning key, that would make DRI (Declared Referential Integrity) through the use of FKs very difficult if not impossible in many cases). Having an "un-aligned" index will make it so that you cannot use some of the possibly important functionality of "Partitioned Tables", such as SWITCH in or out.

    There are a million other little caveats, as well. For example, you've decided that it would be a great idea to make the older file groups "Read Only" so that you can greatly reduce your backups to backing up only the latest filegroups where data has actually been added or changed. One of the things that you'd want to do is rebuild indexes in the file/filegroup before you make them "Read Only". The trouble is that rebuilding an index that's over 128 extents (that's only 8MB) causes the original to be kept until the new index is rebuilt and committed. That means that the partition file in the file group will be twice the size that it needs to be and that remainder will be EMPTY. If you make the filegroup ready only, then you've just locked up a large amount of value free-space in a Read-Only file where it can never be used again. "Compressing" a partition through reindexing without that penalty is certainly possible (I do it on my call recordings table) but it's anything but "simple" to do.

    Partitioning, in one form or another, has some huge benefits when it comes to nightly maintenance (index maintenance, backups, etc) and some other benefits when it comes time to quickly dropping off older partitions and is totally worth doing for those things. I wouldn't count on it for performance though. Even the single row lookups that were being done on the system suffered a nasty performance hit. I can't speak for others but on every partitioned table I've seen so far, performance has been less than that of a properly indexed monolithic table. Even so-called "Partition Elimination" hasn't provided the panacea of performance that many claim, in comparison.

    To summarize, the first link I provided above does provide the "simple" example you are looking for. But, don't be mislead... if you think that the partitioning you just did or a planning to do was or will be "simple", then you've probably done it wrong or are getting ready to. Take the time (and it WILL take some time but it's worth it) to understand all the caveats that partitioning (no matter the form) is going to lead to so that you're not in the position of having to possibly undo it like I currently am.

    My recommendation for the best source of information is to download "Books Online" and lookup "partitioned tables [SQL Server]" in the index and follow each sub-link to the bitter end. Take notes and, when you've done that, then do it again to pickup on all the stuff you didn't actually understand when during the first read. Then, visit pages like the following and understand them.

    https://www.simple-talk.com/sql/database-administration/gail-shaws-sql-server-howlers/

    There are also a large number of good links at the following article, some containing videos.

    https://www.brentozar.com/sql/table-partitioning-resources/

    Last but not least, there's another good set of videos that you should look at but only with the understanding of some of the caveats that I previously mentioned.

    http://download.microsoft.com/download/D/4/9/D495A766-EBB7-4BDE-A911-9BDDE6287DFE/HDI-ITPro-TechNet-winvideo-MCM_17_Partitioning(2).wmv

    http://download.microsoft.com/download/B/C/4/BC43D927-AC6C-4228-B390-29EB5D3E7EA5/HDI-ITPro-TechNet-winvideo-MCM_18_PartialDBAvailability(2).wmv

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • September 3, 2016 at 10:26 am

    #1899096

    thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply