SQLCAT Blogs

Fast Track: improving performance through correct LUN Mapping and Storage Enclosure configuration

AlexeiK — Thu, 03 May 2012 14:49:00 GMT

Author: Alexei Khalyako

Reviewers: Thomas Kejser, Marcel Franke (PM One); Kevin Cox; Erik Kraemer; Kun Cheng; Murshed Zaman; Chuck Heinzelman

During the recent ‘Proof of Concept’ we were testing mid-range Fast Track configuration. Fast Track configuration looked like: server with 32 cores, more than 200 GB of RAM and storage capacity about 40TB.

Before getting started looking at the SQL Server database performance, we obviously wanted to measure the maximum throughput of the storage and then to rely on it as the theoretical maximum of IO that system could deliver. According to the documentation of the hardware vendor the Maximum Consumption Rate is around 7,5 GB/sec, therefore in the real life we were expecting to see the IO in the range of 6-6,5GB/sec.

The very first runs of SQLIO brought quite surprising numbers: we couldn’t exceed 4,7 GB/sec.

Comment: following SQLIO command was used for analysis

ECHO ------ Sequential read block size 256 thread on Concurrent --------------------

sqlio -kR -t10 -s%1 -fsequential -o1 –b512 -LS -FCreate_big_test_file_Param.txt >> .\TestCycle3\sqliotest_sr_t10_b256_all.txt timeout /T 60

where:

-kR - means that ‘READ’ workload is used

-fsequential - workload is sequential

-b512 - block size 256, which is quite typical size for the data warehouse type of workload

Looking at the Performance Monitor, we also observed that instead of the nice flat line across all LUNs we have got absolutely uneven performance of the LUNs with the fluctuation up to 100 MB/sec between slowest and the fastest LUN.

All those observations indicated that we have a problem, but what exactly was the reason for such the underperforming behavior? Since we tested only the throughput of the system storage using SQLIO, we may conclude that the problem is on the hardware or hardware configuration site.

What should we then examine? Following components typically impact the data throughput capabilities:

PCI-slots -> HBA-> Network to the Switch ->Switch -> Network to the storage controller-> storage controller.

First we checked if the HBAs are installed in the right PCI-E slots. In the past we observed couple of cases where HBAs were installed in the slots which were not able to deliver the throughput the HBA can consume.

Note: PCIe x1 v1.0 can deliver about 250 MB/sec. PCIe x4 v1.0 slot can deliver aggregated throughput around 1GB/sec. PCIe x1 v2.0 provides with double performance of the v1.0, which is about 500 MB/sec. Accordingly, PCIe x4 v2.0 could deliver up to 2 GB/sec. Therefore, in order to get anticipated throughput, it would be recommended then installing 8Gb dual port HBA into the PCIe x4. However, not all motherboard PCIe slots are equal. You need to read the fine-print for the motherboard spec for full details.

Even though current hardware typically has enough high throughput PCI-E slots, there are still a couple of the ‘slow’ slots on the motherboards and it is quite understandable if during assembling of the box people could overlook in which slot they put the HBA. So, checking this first could be the easy step to do and very fast to fix if this was a reason for the low throughput.

However, in our case the HBAs were ‘sitting’ in the right slots. What’s next then? Since cabling looked correct, we had to check the mapping of the HBAs to the LUNs.

Checking the mapping on our configuration we observed each port A of the each individual HBA was mapped to all ports A of available storage enclosures. Following picture may help to illustrate configuration.

However even according to the vendor there must be explicit mappings created, matching up a single storage port with a single HBA port on the server. So, one HBA port must be connected to only one storage enclosure, like illustrated on the following picture:

The diagram show only how the ‘Active’ MPIO paths were mapped. For failover you can setup two paths per HBA with the MPIO policy ― ‘Failover Only’‖. This will direct MPIO to use a single path only and failover to the second or secondary path when the first one fails.

With described above configuration, where we fixed mapping issue we’ve got throughput increased about 20% and brought it up from 4,7 GB/sec to 5,7 GB/sec. During the SQLIO runs all LUNs were showing much more attractive picture

Additional configuration settings change of the Read Ahead option on the storage from the value ‘Default ‘ to ‘ 32MB’ helped to raise performance to the ~6,7 GB/sec which was additional +14% gain comparable to that we have got from the re-mapping the HBA-Storage Enclosure configuration.

Conclusion: Reference architectures and appliances give us greatly balanced configurations which help to speed up Data Warehouse deployments and vendors give very clear guidance on how to set it up for better performance. However, the old know wisdom “Trust but verify” is still true and may help your setup look way better.

Always On Availability Group: Connection to readable secondary fails when login SIDs are different or missing

sqlcat — Mon, 23 Apr 2012 14:17:00 GMT

When using ApplicationIntent=ReadOnly to access a readable secondary via the Availability Group Listener, the login SIDs have to be the same between primary and secondary. The connection attempt generates an error saying the login does not exist. This...(read more)

Tuning Spatial Point Data Queries in SQL Server 2012

sqlcat — Wed, 18 Apr 2012 04:48:00 GMT

Written by : Ed Katibah, Milan Stojic, Michael Rys, Nicholas Dritsas Technical reviewers : Chuck Heinzelman Introduction : Spatial Point Data queries require particular tuning efforts to enhance performance and improve overall application throughput...(read more)

Who is using AlwaysOn?

SanjayMishra — Tue, 17 Apr 2012 19:19:00 GMT

Several customers are already running SQL Server 2012 AlwaysOn in production. You can find the case studies with some of these reference customers here:

http://www.microsoft.com/casestudies/Case_Study_Search_Results.aspx?Type=1&Keywords=AlwaysOn&LangID=46

DO NOT use Windows Failover Cluster Manager to perform Availability Group Failover

SanjayMishra — Fri, 30 Mar 2012 19:12:25 GMT

Author: Sanjay Mishra
Contributors: David P Smith (ServiceU)
Reviewers: Chuck Heinzelman, Mike Weiner, Prem Mehra, Kevin Cox, Jimmy May, Tim Wieman, Cephas Lin, Steve Lindell, Goden Yao

Windows Server Failover Cluster (WSFC) is the foundation for SQL Server 2012 AlwaysOn Availability Group functionality. The Availability Group (AG) is registered as a resource group within the Windows Server Failover Cluster. Figure 1 shows an availability group in the Windows Failover Cluster Manager (FCM) interface.

Figure 1: Availability Group service in the Failover Cluster Manager interface

Even though Availability Group (AG) is a resource group within the Windows Server Failover Cluster, DO NOT use the Failover Cluster Manager (FCM) to perform certain operations on the AG:

DO NOT change the preferred owners and possible owners settings for the AG. When an AG is created, the preferred owners and possible owners settings for the AG are established based on the primary and secondary servers information provided to SQL Server. Whenever a failover happens, the preferred owners and possible owners settings for the AG are reset based on the new primary. This is automatically done for you by the AG, so do not try to manually configure these settings.
DO NOT change the preferred owners and possible owners settings for the AG listener. Similar to the AG discussion above the AG Listener settings are handled automatically.
DO NOT move the AG between nodes using the Windows Failover Cluster Manager. The FCM doesn’t provide or have any awareness as to the synchronization status of the secondary replicas. Therefore, if the replica is not synchronized and the AG resource is failed over, the failover will then fail which can lead to extended downtime. The recommended ways to perform AG failover include SQL Server Management Studio and T-SQL statements.
DO NOT add or remove resources in the AG resource group.

Note that the FCM does not prevent you from performing any of these operations. However, we recommend against executing these actions through FCM, as doing so may result in unintended outcomes, including unexpected downtime.

Concurrent ADD NODE operation yields unexpected results in a SQL Server Failover Cluster Instance

SanjayMishra — Fri, 30 Mar 2012 18:43:00 GMT

Author: Sanjay Mishra
Contributors: David P Smith (ServiceU)
Reviewers: Chuck Heinzelman, Mike Weiner, Prem Mehra, Kevin Cox, Jimmy May, Min He

Installing a SQL Server Failover Cluster Instance (FCI) usually involves performing a setup on the first node, and after that, running setup to ADD NODE on the other node(s) in the FCI. A recent SQLCAT lab engagement required a 4-node FCI; and, with the intention of saving overall setup time, we tried to be “smart” by:

First, running setup on Node1.
Then, running setup on Node2 because we wanted to test the user interface when performing an “add node” operation
Then, concurrently running setups on Node3 and Node4 (setup on Node4 started a few seconds later than Node3). These were run concurrently because we just needed to get the nodes added to the FCI as quickly as possible to start application testing.

After the concurrent setups on Node3 and Node4 were complete, we looked at the properties of the FCI, and what we saw surprised us. Node3 was missing from the possible owners list of all the resources. We expected the possible owners list to be Node1, Node2, Node3, and Node4, but what we saw were Node1, Node2, and Node4 listed as possible owners, but not Node3.

After further analysis, we discovered what happened during the concurrent setup on Node3 and Node4.

After initial install on Node1 and Node2(possible owners = Node1 & Node2)
Node3 setup started (possible owners=Node1 & Node2)
Node4 setup started a few seconds later (possible owners=Node1 & Node2)
Node3 setup finished: possible owners set to previous owners (Node1 & Node2) PLUS this node (Node3)
Node4 setup finished: possible owners set to previous owners (Node1 & Node2) PLUS this node (Node4)
End result: Possible owners=Node1, Node2, Node4

Expected result: Possible owners=Node1, Node2, Node3, Node4

Upon further research, we found out that SQL FCI setup is not designed for concurrent setups on multiple nodes of an FCI. In fact, concurrent setup on multiple nodes of an FCI is not a supported operation. The correct approach to install this multi-node FCI is:

Run setup on Node 1, and once it is complete
Run setup on Node 2, and once it is complete
Run setup on Node 3, and once it is complete
Run setup on Node 4

Technical Reference Guides for Designing Mission-Critical Solutions using SQL Server are available

sqlcat — Wed, 07 Mar 2012 01:57:00 GMT

The Technical Reference Guides for Designing Mission-Critical Solutions provide planning and architecture guidance for various mission-critical workloads deployed by users. These guides
reflect the knowledge gained by the CAT team while working with customers on mission-critical deployments, many of these deployments being the result of deep customer engagements by the CAT organization.

...(read more)

SQL Server 2012 AlwaysOn High Availability and Disaster Recovery Design Patterns

SanjayMishra — Thu, 22 Dec 2011 08:05:00 GMT

Author: Sanjay Mishra
Contributors: Justin Erickson, Mike Weiner
Reviewers: Prem Mehra, Juergen Thomas, Steve Howard, Chuck Heinzelman, Jimmy May

SQL Server 2012 AlwaysOn provides flexible design choices for selecting an appropriate high availability and disaster recovery solution for your application. SQL Server AlwaysOn was developed for applications that require high uptime, need protection against failures within a data center (high availability) and adequate redundancy against data center failures (disaster recovery). http://msdn.microsoft.com/en-us/sqlserver/gg490638 provides an overview of high availability and disaster recovery solutions available in SQL Server 2012 AlwaysOn.

Through working with customers who are evaluating and deploying SQL Server 2012 AlwaysOn currently, we have seen the following design patterns emerge as end-to-end HA+DR solution:

Using Multi-site Failover Cluster Instance (FCI) for local high availability and disaster recovery solution
Using Availability Groups (AG) for local high availability and disaster recovery solution
Using Failover Cluster Instance (FCI) for local high availability, and Availability Groups (AG) for disaster recovery solution

We expect most of the SQL Server 2012 AlwaysOn deployments to match one of these design patterns or contain slight variations.

So, how do these three design patterns compare and contrast? This blog highlights the salient features of each of these design patterns. A detailed whitepaper on each of these will be developed and published in near future.

Multi-site Failover Cluster Instance (FCI) for HA and DR

The ability to implement a multi-site FCI as a HA and DR solution has been available in the SQL Server product for a number of previous releases, and many customers have been successfully using the solution (example: http://sqlcat.com/sqlCat/b/whitepapers/archive/2010/09/20/sql-server-high-availability-and-disaster-recovery-for-sap-deployment-at-qr-a-technical-case-study.aspx). In earlier versions of SQL Server, multi-site FCI required a stretch VLAN. SQL Server 2012 removes that requirement (along with a number of other improvements to the failover cluster instance technology) enabling multi-site FCI to be more commonly adopted as a HA and DR solution.

Multi-site FCI requires storage level replication (provided by the storage vendor) to maintain a copy of the databases at the DR site. Even though there are separate storage volumes at each site, to SQL Server, this looks like a Shared Storage solution. Other important attributes of this solution are:

The unit of failover for both local HA, and remote DR is SQL Server instance.
No requirement on database recovery model, as storage level replication is used for maintaining the remote copy of the data.
The DR copy of the data is not readable.

The whitepaper http://sqlcat.com/sqlcat/b/whitepapers/archive/2011/12/22/sql-server-2012-alwayson_3a00_-multisite-failover-cluster-instance.aspx provides architetcure details and best practices for this solution.

Availability Group for HA and DR

Using Database Mirroring for local high availability, and combining it with Log Shipping for a disaster recovery solution is a popular deployment architecture prior to SQL Server 2012 (example: http://sqlcat.com/sqlcat/b/whitepapers/archive/2010/10/29/high-availability-and-disaster-recovery-for-microsoft-s-sap-data-tier-a-sql-server-2008-technical-case-study.aspx).

With SQL Server 2012, the Database Mirroring and Log Shipping solution can be replaced with an Availability Group solution with multiple secondaries.

This is considered a non-shared storage solution, as each SQL Server in the topology has its own copy of data and does not need to share storage. Other important attributes of this solution are:

The unit of failover for local HA, and DR is the Availability Group (a group of one or more databases).
The database is required to be in the FULL recovery model.
The DR replica can be utilized as an Active Secondary (Readable Secondary Replicas, Backup on Secondary Replicas).

Failover Cluster Instance for local HA and Availability Group for DR

Using Failover Cluster Instance for local high availability, and combing it with database mirroring for a disaster recovery solution is a popular deployment architecture prior to SQL Server 2012 (example: http://sqlcat.com/sqlcat/b/whitepapers/archive/2009/08/04/high-availability-and-disaster-recovery-at-serviceu-a-sql-server-2008-technical-case-study.aspx).

With SQL Server 2012, the Database Mirroring can be replaced with an Availability Group for the DR solution, while continuing to use Failover Cluster instance for local HA.

This architecture is a combined Shared Storage and Non-Shared Storage solution. Other important attributes of this solution are:

The unit of failover for local HA is the SQL Server instance.
The unit of failover for DR is the Availability Group (a group of one or more databases).
The database is required to be in the FULL recovery model.
The DR replica can be utilized as an Active Secondary (Readable Secondary Replicas, Backup on Secondary Replicas).

Summary

A brief outline and comparison of three common HA/DR design patterns with SQL Server 2012 AlwaysOn is provided above. A detailed document on building and deploying each of these solutions will be discussed in a whitepaper to be published soon.

Helping to make Hadoop easier by going Metro!

sqlcat — Wed, 14 Dec 2011 18:27:00 GMT

We are proud to announce that the community technology preview (CTP) of Apache TM Hadoop TM -based Services for Windows Azure (or Hadoop on Azure) is now available. As noted in on the SQL Server Data Platform Insider blog, the CTP is by invite only http...(read more)

A Computed Column Defined with a User-Defined Function Might Impact Query Performance

sqlcat — Mon, 28 Nov 2011 22:15:00 GMT

Author: Kun Cheng Reviewers: Shaun Tinline-Jones, Silvano Coriani, Steve Howard, Thomas Kejser, Sanjay Mishra A computed column is computed from an expression that can use other columns in the same table. The expression can be a noncomputed column...(read more)

Writing New Hash Functions for SQL Server

sqlcat — Mon, 28 Nov 2011 14:21:44 GMT

Author: Thomas Kejser Contributors/Reviewers: Alexei Khalyako, Jerome Halmans, Fabricio Voznika, Sedat Yogurtcuoglu, Mike Ruthruff, Tobias Ternstrom and Steve Howard In this blog, I will explore ideas for extending SQL Server with new, fast hash functions...(read more)

What’s so BIG about “Big Data”?

sqlcat — Tue, 15 Nov 2011 05:54:00 GMT

As announced during the PASS Summit 2011 Day One Keynote , we are diving deeper into the world of Big Data by embracing and contributing to the open source community and Hadoop. We’ve had a lot of good coverage on this topic with some examples...(read more)

Successfully execute an INSERT, UPDATE and DELETE against a Database Snapshot

sqlcat — Tue, 18 Oct 2011 02:06:00 GMT

Author : Shaun Tinline-Jones Reviewers : Mike Ruthruff, Sanjay Mishra, Alexei Khalyako Not too long ago an ISV that developed solutions using SQL Server as the RDBMS, asked me how they could query a database as at a point in time. This was a relatively...(read more)

SQLPASS 2011 SQLCAT Track

denny.lee — Sat, 08 Oct 2011 22:49:00 GMT

The SQLPASS 2011 summit is upon us again and all of our flocking to our fair city Seattle for our yearly technical-and-karoke fest!

Do you want deep technical dives on all things SQL Server and SQL Azure? Then check out the SQLCAT track at this year's SQLPASS 2011 Summit in Seattle. We will be diving into technical issues surrounding OLTP, Data Warehousing, BI, Azure and everything in between. In addition to the tracks, do not forget about the "SQL Clinic" and you can find SQLCAT members through out SQLPASS in our green shirts (and/or doctors lab coats).

And don’t forget to check out the Birds of Feather event – the tech talk luncheon.

Session Code	Session Name	Speakers
DBA-414-M	Are You Smarter Than An MCM?	Cindy Gross, Jimmy May, Lara Rubbelke, Pam Lahoud, Robert Davis
DBA-323-M	Improving Your PowerShell Productivity	Dan Jones
AD-313-M	SQL Server Engine Team - Unplugged	Alex Verbitski, Ashit Gosalia, Cihan Biyikoglu, Cipri Clinciu, Dana Kaufman, Eric Kang, Gus Apostol, Justin Erickson, Rohan Kumar, Sunil Agarwal, Susan Price
AD-405-M	SQLCAT: Building Highly Scalable Cloud Analytics Solutions with SQL Azure and StreamInsight (Project Austin)	Mark Simms
BIA-409-M	SQLCAT: Configuring and Securing Complex BI Applications in a SharePoint 2010 Environment with SQL Server Code Name "Denali"	Carl Rabeler, Chuck Heinzelman
DBA-413-M	SQLCAT: Enterprise SQL I/O Optimization Best Practices	Jimmy May, Mike Ruthruff, Thomas Kejser
DBA-332-M	SQLCAT: HA/DR Customer Panel -- SQL Server Code Name "Denali" AlwaysOn Deployment Consideration	Ayad Shammout, David Smith, Michael Steineke, Sanjay Mishra, Thomas Grohser
DBA-324-M	SQLCAT: Optimize SQL Server for Private Cloud part2: Best Practices and Lessons Learned	Alan Cranfield, Lindsey Allen, Xin Jin
DBA-328-M	SQLCAT: SQL Server Consolidation at Travelers – How we did it and Lessons Learned	Bob Crowley, Prem Mehra
DBA-325-M	SQLCAT: SQL Server HA and DR Design Patterns, Architectures and Best Practices using SQL Server Code Name "Denali" AlwaysOn	Sanjay Mishra, Mike Weiner
AD-406-M	SQLCAT: SQL Server Tier-1 Mission Critical Application at Progressive – Architecture Deployed and Lessons Learned	Brian Durkin, David Wilson, Prem Mehra
BIA-408-M	SQLCAT: Tier-1 BI in world of Big Data	Denny Lee, Thomas Kejser
AZ-302-M	SQLCAT: What are the Largest Azure Projects in the World and how do they Scale	Kevin Cox, Michael Thomassy
DBA-326-M	SQLCAT: What are the Largest SQL Server Projects in the World and how do they Scale	Kevin Cox, Nicholas Dritsas
BIA-313-M	Building the Perfect BI Semantic Model for Crescent	Kasper de Jonge, Carl Rabeler

Enjoy!

Top 10 recommendations for building data warehouses on Fast Track appliances

AlexeiK — Thu, 29 Sep 2011 13:55:00 GMT

Author: Alexei Khalyako

Reviewers: Stuart Ozer, Thomas Kejser, Eric Kraemer, Kevin Cox, Mike Ruthruff

Technical Editor: Beth Inghram

Make sure your intended workload is scan intensive. Avoid using Fast Track for query scenarios that mainly involve pinpoint lookup operations or very high volumes of small transactions, because these typically require support for more random IOPS than the Fast Track architectures are designed for.

To determine whether your workload is scan intensive, use the techniques and recommendations described in the Fast Track Reference Guide.

Consider using very few or no nonclustered indexes on fact tables. As stated in #1, the Fast Track architecture is designed for scan-intensive workloads. Typically, pinpoint queries that return few rows benefit from using nonclustered indexes. However, in a data warehouse environment , the query optimizer may decide to use a nonclustered index scan or seek with following clustered index lookup (in the query plan you will see a nested loop) trying to retrieve big amount of data. This will generate a lot of random I/O operations.

Apply data-loading and data-maintenance techniques that avoid or minimize data fragmentation.

Loading data in parallel directly into a clustered index table can cause extent fragmentation. In some projects we observed that if data was loaded directly into a partition of the partition table with multiple loading processes, clustered index fragmentation exceeded 90 percent. The best recommendation here would be to load data into the new filegroup using a single-threaded INSERT SELECT WITH MAXDOP1 statement, as illustrated in picture 1. We observed that for a single INSERT thread, a DOP level of 8 or less gives acceptable fragmentation without any significant impact on the performance.

Rebuilding the index is not the best solution, because this approach may leave some physical data fragmentation in the filegroup.

Picture 1: Architecture solution for separating volatile and static parts of the data

Put highly volatile tables into a separate filegroup. Highly volatile tables are tables in which many rows are deleted or updated, such as dimension tables; or smaller tables that are frequently re-created, such as staging tables.

Defining partitioning and filegroup strategy. We recommend using one filegroup per fact table. The practice of using a separate filegroup for each separate partition can be dangerous with the Fast Track architecture (an example in which this recommendation could be applied is described here). Because in Fast Track data warehouses every filegroup is likely to be spread across all available LUNs, applying this best practice will result in a huge number of files and filegroups to support, becoming a challenge to manage.

Picture 2. File group layout in a traditional data warehouse environment

Picture 2 shows an example of how this layout could appear in a Fast Track environment (this configuration is NOT RECOMMENDED)

Picture 3. A traditional filegroup layout used with Fast Track. Note the higher number of files in the filegroups that you will need to support.

Instead, consider the following approach for Fast Track.

Picture 4. Recommendation for Fast Track: one filegroup spread over multiple LUNs

Spreading the partition file group across the LUNs makes sense if you spread it the same way as the database files (create one file group across all the data LUNs, then each LUN should store only one file of this filegroup) ; however, this approach results in a HUGE number of files that you must then monitor and support.

Very often we saw implementations where several data-loading processes contained data from the range of a current time period (like current day data or current month data) and no or very few new entries for the previous time range. As an example, imagine a fact table partitioned by a DAY KEY table, where most of the load goes against the current day’s partition, and where the previous day’s partitions (let’s call them historical) are not touched at all. The current day range becomes a highly volatile part of the table, characterized by high level of fragmentation. To address this challenge, separate fragmented current data from stable historical data by using different file groups. After data is loaded into the current data range and late-arriving data is no longer expected, move data by using INSERT…SELECT with SORT IN TEMPDB into a historical table. The following picture illustrates this approach. First, create a table for the current data range. Make sure this table is in its own file group, which is spread across the LUNS. Finally, create a partitioned "historical table" with its own file group spread again all over the LUNs. To satisfy queries against the historical and current time ranges, consider using VIEW on TOP of both tables. The downside of this approach is that it will be required to manage the view in order to make sure that no data duplicates exposed to the client application. However the advantages you that you get are paying off:

a. online “SWITCH”, since you will not need to deal with the SCM_M_LCK limitation

b. you will have separated nicely historical (not fragmented) and current ( volatile) data nicely ability This approach will require you to manage the VIEW

Picture 5: Architecture solution for separating volatile and static parts of the data

Note: In order to make sure that queries touch only the ranges they need, create constraints on both tables:

ALTER TABLE dbo.February_Static ADD CONSTRAINT CK_DATE CHECK ([ChargingDateTime] >= '2009-02-01' and [ChargingDateTime] <'2010-02-26')

ALTER TABLE dbo.FebruaryCurrent_Day ADD CONSTRAINT CK_Current_DATE CHECK ([ChargingDateTime] >= '2009-02-08' and [ChargingDateTime] <'2010-02-29')

Instead of using the autogrow option for tempdb and log files, create them at the maximum size you expect to need. Make sure that all tempdb files are equally sized. This may significantly save time by eliminating wait time on the new extent allocations.

Loading data in parallel into the database is a great tool for moving a high volume of data from the destination to Microsoft SQL Server. One way to achieve parallel load is to load data into heap tables. However, this can introduce resource contentions such as PAGELATCH or ALLOC_FREESPACE_CACHE. In order to work around this situation, consider applying the hash-partitioning technique described in this technical article:

Resolving PAGELATCH Contention on Highly Concurrent INSERT Workloads.

One of the best strategies for loading a large amount of data and avoiding fragmentation is to load data into staging table and then move the data into the target partition of the partitioned table with the INSERT SELECT statement and MAXDOP(1) options. There are two ways to do this:

a. Use a staging table with a heap structure; load data into a partition, which may have clustered and nonclustered indexes built on it.

b. Use a staging table that has the same index built as the target partitioned table.

If you check the query plan of the first option, you will see that the output of the query is sorted before it is inserted into the clustered Index. The second option avoids a SORT operation.

The following pictures illustrate the query plans of these two options.

Picture 5. Inserting data from the heap staging table into the clustered index target partitioned table

Picture 6. Inserting data from the staging table into the target partitioned table, where both the staging and the partitioned tables have clustered indexes built on OrderDate

Follow performance best practices for building large-scale data warehouses as published here: http://sqlcat.com/top10lists/archive/2008/02/06/top-10-best-practices-for-building-a-large-scale-relational-data-warehouse.aspx