Who is online?  142 guests and 1 members
Home  »  Blogs  »  PatrickLeBlanc  »  Minimizing Fragmentation when Moving data using a Switch

Minimizing Fragmentation when Moving data using a Switch

change text size: A A A

posted  3/17/2010 11:53:13 PM  by PatrickLeBlanc

Yesterday was the first day of the SQL Server 2008 R2 airlift.  I attended a great session on Implementing a Fast Track Data Warehouse.   Lots of really interesting information was discussed during the meeting. However, an offline discussion about switching partitioned data in from a stage table provided a good tip that could help reduce fragmentation.

If you are constantly switching data into your partition table you could potentially introduce some unwanted fragmentation in your database.  The following steps should help to reduce the fragmentation:

1.       Instead of creating the stage table on the destination partition filegroup, create it on another filegroup that resides on a separate set of disks.

2.       Insert the data into the staging table.

3.       Create the clustered index for the stage table on the filegroup of the destination partition using a MAXDOP = 1.  This will ensure sequential  processing of the data, and moves the data to the destination filegroup.

4.      Perform the SWITCH into the partition, which will be a META data move.

When using the new Fast Track Warehouse methodology in SQL Server R2 minimizing fragmentation is vital to successful implementation. 

Talk to you soon,

Patrick LeBlanc

Founder www.TSQLScripts.com and www.SQLLunch.com.

Visit www.BIDN.com, Bring Business Intelligence to your company.

 

tags :   Fast Track, Warehousing, Fragmentation, Partitioning
  Rate This Blog Entry:  register  or  login

Author

PatrickLeBlanc PatrickLeBlanc (Member since: 11/7/2009 12:24:22 AM)

View PatrickLeBlanc 's profile

Comments (no comments yet)

Leave a comment


 
Type the characters:
 *
 
   

Join Business Intelligence Developer Network for FREE Today!

It's fast, easy and free! Submit articles, get your own blog, ask questions & give answers in the forums, and become a better developer, faster.

enter your email address:

Blogs RSS Feed

PatrickLeBlanc's latest blog posts

  • Where do you get your motivation and drive?
    9/6/2010 7:04:58 PM by PatrickLeBlanc  - Views: 58  - Comments: 3
    I was asked by someone the other day, where do you get your drive and motivation? For a second, I was stumped. After giving it a little thought I realized that my drive has several sources. The first ...
  • MDX Puzzle #7 - Solution
    9/3/2010 1:23:35 PM by PatrickLeBlanc  - Views: 10764  - Comments: 4
    Puzzle #7 had an interesting twist. You were required to add a column to the result set that dynamically displayed a currency type based on the existing currency code. As always, there are a couple of...
  • Join me for Lunch - SQL Lunch #33 (Indicators, Sparklines and Databars)
    8/30/2010 5:24:16 PM by PatrickLeBlanc  - Views: 30318  - Comments: 4
    Join me for Lunch - SQL Lunch #33 (Indicators, Sparklines and Databars) #33-SSRS 2008 R2 - Indicators, Sparklines and Databars Speaker: Patrick LeBlanc Add To Outlook: Add To Calendar Live Meeting URL...
  • MDX Puzzle #7
    8/29/2010 9:40:12 PM by PatrickLeBlanc  - Views: 8954  - Comments: 3
    I have presented you with a couple of easy puzzles in the last few posts. So, let's step it up a little with one that is a little more challenging. I have to be honest, this puzzle was sent to me by o...
  • MDX Puzzle #6 - Solution
    8/27/2010 6:15:17 PM by PatrickLeBlanc  - Views: 223  - Comments: 4
    Puzzle #6 should not have presented too much of a challenge, it's intentions were to introduction you to the ORDER function that is available in MDX. Ok, so this is what I started with: This is a very...
  • MDX Puzzle #6
    8/25/2010 2:41:01 AM by PatrickLeBlanc  - Views: 166  - Comments: 0
    As we continue with the puzzles, I will continue to introduce new MDX functions. In this puzzle the requirements are as follows: Columns : Reseller Sales Amount Rows : Product Subcategories Filters : ...
  • SQL Saturday #28 - Lessons Learned
    8/23/2010 12:57:43 AM by PatrickLeBlanc  - Views: 131  - Comments: 1
    After spending a week in Tampa, FL at a client site I've had some time to unwind and reflect back on SQL Saturday #28. I have received some really positive feedback from all individuals involved. Whil...
  • SQL Reporting Services - Line Graph with a Vertical Bar
    8/11/2010 1:57:45 AM by PatrickLeBlanc  - Views: 1056  - Comments: 0
    So, I was recently asked if you could display a vertical bar on a line graph to denote the last day of the month. Initially, I had no clue how to solve this problem. I talked to a couple of people and...
  • MDX Puzzle #5 - Solution
    8/10/2010 2:43:38 PM by PatrickLeBlanc  - Views: 158  - Comments: 1
    Again I want to apologize for the slow posting of the solution to this puzzle, but I have been hard at work on SQL Saturday #28 . Fortunately, I carved out some time to write it up. The solution to th...
  • SQL lunch #28-SSIS Design Patterns 1a: Introduction to SSIS Design Patterns
    8/8/2010 9:22:40 PM by PatrickLeBlanc  - Views: 211  - Comments: 0
    In this three part series SQL Server MVP Andy Leonard will discuss SSIS Design Patterns . Speaker: Andy Leonard Add To Outlook: Add To Calendar Meeting URL: https://www.livemeeting.com/cc/usergroups/j...
  • SQL Saturday #28 Update
    8/6/2010 4:47:45 PM by PatrickLeBlanc  - Views: 142  - Comments: 0
    We are just about a week away from probably the biggest FREE training event to ever take place in Baton Rouge, LA. Heck this is probably the biggest FREE technology event in Louisiana. I really don't ...
  • SQL Saturday #28 Announces: Asks The Experts
    8/3/2010 3:18:49 AM by PatrickLeBlanc  - Views: 311  - Comments: 1
    Do you have a challenging SQL Server or .Net issue that is causing you to have sleepless nights? Well, we have convinced 12 of our speakers (Experts) to allocate an additional hour of their day strict...
  • MDX Puzzle #5
    8/1/2010 10:15:31 PM by PatrickLeBlanc  - Views: 184  - Comments: 0
    Writing YTD totals and Running totals using T-SQL can take a little effort. However, with MDX it's not too difficult. In this puzzle I will write an MDX statement that produces a monthly YTD or Runnin...
  • MDX Puzzle #4 - Solution
    7/30/2010 2:48:02 PM by PatrickLeBlanc  - Views: 340  - Comments: 1
    This puzzle may not have been much of a challenge, but it introduced two new MDX concepts. I have to admit, one of my colleagues and I chatted about this puzzle, and I realized that my requirements we...
  • 2010 3rd Quarter Goals
    7/28/2010 2:21:47 PM by PatrickLeBlanc  - Views: 175  - Comments: 2
    Now that we are into the third quarter of 2010 I decided to post an update of my 2nd quarter goals and post goals for the 3rd quarter. If you all have been following my blog or me on twitter (patrickd...
  • MDX Puzzle #4
    7/28/2010 12:52:59 PM by PatrickLeBlanc  - Views: 338  - Comments: 1
    This puzzle is rather simple, but it does introduce a few new things. Using T-SQL you typically filter queries with a WHERE clause. You can also do the same with MDX, but just like T-SQL there are a f...
  • SQL Saturday Wiki
    7/25/2010 9:34:40 PM by PatrickLeBlanc  - Views: 251  - Comments: 3
    This past weekend I had the opportunity to hangout with some of the brightest upcoming SQL Server Professionals and a couple of old-timers. We were gathered in Orlando, FL by Andy Warren to work on th...
  • SQL Server 2008 R2 for the DBA Recording and PowerPoint
    7/23/2010 1:15:54 PM by PatrickLeBlanc  - Views: 366  - Comments: 3
    I recently had the opportunity to do two presentation on SQL Server R2 for the DBA. In the first session there were approximately 150 attendees and at the second there were approximately 400 attendees...
  • .NET speaker Idol, Guess who won?
    7/22/2010 2:57:31 AM by PatrickLeBlanc  - Views: 256  - Comments: 5
    Last night (July 21, 2010) I had the honor of being a speaker at the Baton Rouge .Net User Group . This wasn't their normal meeting. It was their 3rd Annual Speaker Idol . The rules are as follows: 1....
  • Free Data Warehouse Training - SQL Lunch #27 Let's Get Dimensional
    7/21/2010 7:02:52 PM by PatrickLeBlanc  - Views: 181  - Comments: 0
    Join Adam Jorgensen, tomorrow on the SQL Lunch to learn about Dimensional Modeling . Go to SQL Lunch and add this event to your calendar or use the link in this posting. To receive notifications about...
  • SQL Server 2008 R2 for the DBA Webcast
    7/21/2010 6:42:35 PM by PatrickLeBlanc  - Views: 209  - Comments: 3
    Tomorrow at 11:00 AM EST I will be giving a talk on SQL Server 2008 R2 for the DBA. Here is the description: Microsoft SQL Server 2008 provides a number of enhancements and new functionality, building...
  • Practice Your PASS Session on the SQL Lunch
    7/18/2010 11:59:12 PM by PatrickLeBlanc  - Views: 196  - Comments: 0
    We at the SQL Lunch would like to extend an invitation to all of the PASS speakers. If you are looking for a place to practice your PASS session please email us at webmaster@sqllunch.com . Don’t worry...
  • MDX Puzzle #3 Solution
    7/15/2010 4:02:29 PM by PatrickLeBlanc  - Views: 146  - Comments: 0
    Download Script Sorry that it’s taken me so long to write this post, but I have been a little busy with the SQL Lunch . I am back now, so let’s solve this puzzle. This puzzle introduced a new keyword,...
  • Free T-SQL Training - SQL Lunch #25 (Transact-SQL Development and Deployment)
    7/13/2010 2:50:32 PM by PatrickLeBlanc  - Views: 208  - Comments: 0
    Join SQL Server MVP, Andy Leonard, tomorrow on the SQL Lunch to learn some snappy tips and tricks on Transact-SQL development and deployment . Go to SQL Lunch and add this event to your calendar or us...
  • SQL Saturday #28 “Refer a Friend Contest”
    7/12/2010 7:41:27 PM by PatrickLeBlanc  - Views: 285  - Comments: 0
    With all the MSDN give away contests, I decided to add another. Here are the official rules for the twitter referral registration contest for SQL Saturday #28 in Baton Rouge, LA on August 14, 2010 on ...
Blogs RSS Feed

Latest community blog posts

  • PowerPivot Installation failure
    6 hours, 29 minutes ago by MarkGStacey  - Views: 60  - Comments: 0
    Sometimes, when installing PowerPivot Services, you may get the message "sharepoint availability check for existing farm" FAIL Or "user account requirement for farm administrator" Even though your use...
  • Alternating Colors in your Sql Svr Report
    19 hours, 56 minutes ago by SMcDonald  - Views: 28  - Comments: 0
    When creating a report, it's nice to add color to it, especially when attempting to separate the rows. Here is a quick how to for you: 1. After you create your report, highlight one of your rows: 2. G...
  • Ten interview question you should'nt ask
    23 hours, 15 minutes ago by sqlscottgleason  - Views: 303  - Comments: 6
    So a good friend of my is leaving is current role, I wanted to write down a few things He should'nt ask during the interview of his next job. 15. How frequently do your accountants audit petty cash? 1...
  • MDX - Calculating the Percent of a Named Set
    9/8/2010 12:14:06 PM by DevinKnight  - Views: 41  - Comments: 0
    Getting the percent of something in MDX seems like a pretty easy task but can be complicated when extra logic is needed to be built in. In this example a named set has been added and the requirement i...
  • Restarting SQL Server using a Batch Script
    8/29/2010 5:17:21 PM by briankmcdonald  - Views: 34  - Comments: 0
    In a previous post, I showed you how one could Restart, Stop and Start the services for an instance of SQL Server using the SSMS GUI. Again, please do not execute this on your PRODUCTION server, but h...
  • Where do you get your motivation and drive?
    9/6/2010 7:04:58 PM by PatrickLeBlanc  - Views: 58  - Comments: 3
    I was asked by someone the other day, where do you get your drive and motivation? For a second, I was stumped. After giving it a little thought I realized that my drive has several sources. The first ...
  • SQLMotivation for your Monday
    9/6/2010 3:39:24 PM by AdamJorgensen  - Views: 40  - Comments: 0
    Motivational time again! Today I want to talk about being lazy with your data. More likely than not, it’s not your data, it’s your firm’s data. Make sure you’re taking precautions. There are so many f...
  • Breaking in (Entry level advice)
    9/3/2010 1:44:17 PM by sqlscottgleason  - Views: 34940  - Comments: 3
    How does one get started in this field? One thing that I see from time to time is someone who just keeps taking class after class hoping that a resume full of classes will make their resume took impre...
  • MDX Puzzle #7 - Solution
    9/3/2010 1:23:35 PM by PatrickLeBlanc  - Views: 10764  - Comments: 4
    Puzzle #7 had an interesting twist. You were required to add a column to the result set that dynamically displayed a currency type based on the existing currency code. As always, there are a couple of...
  • Script Indexes With Your Table
    9/3/2010 11:34:51 AM by BradSchacht  - Views: 2761  - Comments: 0
    Scripting tables inside of SQL Server Management Studio is a very simple task. Just right click and select script table. The problem is that, but default, the indexes on a table are not part of the sc...
  • Retrieving Report Names for SSRS Subscription Jobs
    9/1/2010 6:00:10 PM by briankmcdonald  - Views: 1420  - Comments: 0
    The way Reporting Services creates subscriptions is through generating a SQL Server Agent job. This is apparent when you look at the jobs on the database server that Reporting Services is configured t...
  • Pirates of PASS: Curse of the Elections Process
    9/2/2010 6:52:12 PM by sqlchicken  - Views: 10124  - Comments: 3
    Pirate Code: They're more like guidelines really... The last few weeks we've watched the drama unfold in regards to the PASS BOD elections. We've seen people attack criticize the NomCom, the board, PA...
  • What are plan guides?
    9/2/2010 1:34:14 AM by indupriya  - Views: 6306  - Comments: 0
    Just a while ago I was watching a video on sqlshare Creating a Plan Guide for a Parameterized Statement By Andy Warren . Only then I heard about plan guides. I didn't know what plan guides were. Here ...