There’s a secret to learning about SQL Server’s table partitioning feature: you need to get your hands on some code and really play with it in a test environment.
In this tutorial, Kendra will walk you through test scripts that set up a sample table partitioning environment. These scripts demonstrate gotchas to look out for and the cool features that can dramatically change your database’s performance.
Want to run the demos yourself? Download Table Partitioning Tutorial: Scripts.
Part 1: Our Mission
How can you use the demo and scripts from today in the best way possible? Find out here. 3.5 minutes
Part 2: How to create your test database and helper objects
It’s demotime! We create a test database with views that will help us quickly see critical information about out partitioned object. 3.5 minutes
Part 3: How to create partitioned objects
Next up we create a partition function. We create filegroups and files, and use a partition scheme to map the filegroups. We then create a partitioned object and indexes. Our helper views (created in Part 2), help us make sure things went as expected. 15 minutes
Part 4: Switch IN! How to use partition switching to add data to a partitioned table
Now for the cool stuff. In this session we explore how partition switching can allow us to snap a pile of data quickly into a partitioned table– and a major gotcha which can derail the whole process. 12 minutes
Part 5: Switch OUT! How to move data out of a partitioned table and merge boundary points
Deleting data is never fun… unless you’re switching data out of a partitioned table. But make sure to mind those best practices. In this section we show how to remove a lot of data from a partitioned table quickly and safely. 8 minutes
Part 6: Table partitioning takeaways
What are the big things we covered that you need to remember? Make sure you got all the important points here. 3 minutes
Learn More
We’ve got tons more information on table partitioning at http://brentozar.com/go/partitioning
Looking for the scripts? Download Table Partitioning Tutorial: Scripts.
See http://BrentOzar.com/go/eula for our End User Licensing Agreement which governs use of the scripts.
Ameena January 28, 2013 | 1:12 pm
Kendra,
Excellent demo and scripts. Thanks for posting it.
Ameena
Yakov Shlafman January 29, 2013 | 7:47 pm
Hi Kendra,
thanks
Excellent article!
Itzik Ben-Gan uses “Virtual Auxiliary Table of Numbers” in his articles and books.
for example here http://www.sqlmag.com/article/sql-server/virtual-auxiliary-table-of-numbers-103056
I think you have a typo… Create a taily table with 4 million rows… What for? You do not use more than 5,000 rows.
More comments are comming.
Kendra Little January 31, 2013 | 9:20 am
Whenever I load up a numbers table I just throw in a ton of rows in case I want to increase the rowcount by some factor. I love this method of creating the numbers table because it’s so fast!
Chandan Jha January 31, 2013 | 3:38 am
Thanks. I will go through these videos as soon as I can.
FYI,
I could not recognize Kendra as first time I have seen the glasses missing:-)
Regards
Chandan
Kendra Little January 31, 2013 | 9:18 am
Ha! I go through love/hate phases with contact lenses. I’m getting back into the “love” phase, I guess!
Dale Durthaler March 4, 2013 | 1:26 pm
Kendra, I just discoverd you last week and have gone through a number of your videos. I first found you when I was looking for info on SQL DBA jobs and I stumbled across your video “Getting Your First DBA Job”. I use to develope DB systems long ago and in a product called FOCUS. Became a Master in it but it wasn’t Relational. Took another job that exposed me to SQL, DB2 and Relational Database design. I always wanted to work only as a DBA but just never got the opportunity, lost job, struggled to get back into IT, finally landed back on my feet. My current role is ETL Informatica / Cognos but would love an entry level / Mid level DBA job. I’ve been reading all the MS MCITP books related to SQL Server database technology. You’re Video “Getting Your First” you mention that the DBA industry is (((HOT))). I agree, never wanted out of IT, but got caught in the down economy. Now I can’t seem to find anything that a guy that’s been out as long as I, in the DBA areana. I know I can do the job if there is a DBA shop set up in a Team Environment. Thought maybe you could help point me in a direction. I’m willing to relocate for the right $ and Location. Thougths. I’m taking a chance that this gets to you..
Dale.
Alex Kirilin March 15, 2013 | 2:04 am
Kendra,
Thank you! Excellent work.
Pingback: Four Underutilized Features of SQL Server Enterprise Edition (and One of Standard Edition) | The SQL Herald
.taty June 10, 2013 | 10:57 am
Hello,
I have a couple questions.
In your demo you mentioned that # of filegroups = # of ranges + 1. Yet, all files are created on the same logical volume. Does it affect performance to map all partitions to less number of filegroups or just to single filegroup?
Also, I understand why you recommend to create a ‘sandwich’ type of partitioning. But how can one leave first and last partition empty while loading data with BCP utility?
And thank you for the great step-by-step videos!
Nestor June 14, 2013 | 10:00 am
First of all congratulations on an amazing set of videos that gets us “very interested” in the world of table partitioning.
The videos are amazing as they explain (and we can test) how partitioning works, what needs to be taken care of and some of the things we need to pay attention when working with partitioned objects.
One thing that I haven’t seen discussed that much is “Partitioning Existing Tables”. If you ever have time it would be nice if you explained your recommended approach on this subject.
Let’s say you have a database with tables that are growing very large (and after plenty of analysis you’ve decided that partition will help), which are becoming very difficult to maintain. What would be better?
- Create a new (and partitioned) table, dump all the rows and start fresh?
- Partition the existing object and all its indexes?
- Would the same steps apply for this scenario?
Thanks for any future comments on this and once again, congrats and keep it up.
You just earned a new fan
Kendra Little June 15, 2013 | 9:36 am
Thanks for the comment, Nestor! I’m glad you enjoyed it.
For transitioning an existing partitioned table, I just always seek the path that’s least likely to end me up watching a production database with a long rollback in progress and nobody able to access the tables. I’ve just been there too often. So for this reason I prefer to load everything up in a new object and do a cutover to it whenever possible– and often this leads to the smallest downtime as well, with the best rollback opportunities.
This tends to be relatively straightforward for tables that are partitioned by date, where new data coming in all goes into more recent partitions. It can be much trickier when data can be modified across all partitions of the table. Michael Swart did some blog posts on strategies for migrating tables online that might be helpful in making this work: http://michaeljswart.com/2012/04/modifying-tables-online-part-1-migration-strategy/
Sometimes partitioning the existing data can be OK. I would just be really careful testing everything on a restored copy of the database first to make sure that the amount of IO, log, etc available is all OK, that the runtime is acceptable, and that if you need to roll it back you still have a plan in that scenario.
But I do have to admit, the “change the one copy of the data” approach just gives me the heebie jeebies. Man, do I hate watching a rollback on that one copy of the data.
Edit: if you have fancy storage and a TON of data you can get really creative to make this work using SAN snapshots and storage on secondary servers to offload some of the CPU usage/locking issues. Making notes to do a blog post discussing some options!