27

I've just set up a publication, and I'm attempting to get the snapshot to apply faster. So far the Distribution Agent is respecting the MaxBCPThreads settings, but the Snapshot Agent is not. I'm expecting it to split the files so the threads on the Distribution Agent would go and grab the data. But it doesn't appear to be able to do that any time I snapshot.

Some possible solutions that I've seen online where to update the agent profile (I originally just edited the agent step with the flag, and that worked for the dist agent but not for snapshot).

I tried updating the agent profiles and that hasn't made any difference. I also found people saying that you should have sync_method set to native so I checked my script and I had already created the publication with native mode specified.

I'm wondering if I'm missing a specific setting that MaxBCPThreads needs in order to split all the bcp files into different files each.

I thought I had solved my own issue: It looks like you have to have a clustered index with a distinct set of ranges to get SQL Server to split the files into partitions. But right now my index seems to have 0's for all ranges.

DBCC SHOW_STATISTICS

enter image description here

After additional testing, I've found that this seems to only work on replicated tables. If you were to replicate based on (indexed) views then it seems that you only get the 1 bcp file instead of the partitioned stuff you'd get from normal tables.

The question is: Why doesn't SQL replication partition bcp files for Indexed Views like it does with normal tables?

I'm replicating the indexed view itself without the table ("Indexed View as a Table"). The reason is I have to join identifying information for the database for the subscriber to use for other things. The only way I've found to do it so far is to manually split my views using BETWEEN, which isn't particularly efficient. I'm hoping I can get SQL Server to do what I'd expect when replicating a normal table.

1

1 Answer 1

1

The SQL Server replication doesn't partition BCP files for indexed views like it does with normal tables because the underlying implementation and usage of indexed views are different from regular tables. Indexed views are essentially views with a unique clustered index to improve query performance. They're designed to precompute complex queries or aggregations so that the result is available instantly.

When replicating indexed views, the Snapshot Agent creates a single BCP file for the entire view. This is because the indexed view represents a single result set, while regular tables can have multiple data partitions based on the primary key or partitioning column. The indexed view doesn't have the same partitioning scheme as a regular table, so SQL Server doesn't split the BCP files the same way.

To work around this limitation, you can try one of the following options:

  1. Manually split the indexed views using the BETWEEN clause, as you mentioned. Although it may not be the most efficient solution, it can help you partition the BCP files based on a specific range.

  2. Create a stored procedure that partitions the data in the indexed view and then replicate the stored procedure execution. This way, you can customize the partitioning logic and have more control over how the BCP files are generated.

  3. Consider replicating the base tables instead of the indexed view and then recreate the indexed view on the subscriber side. This approach requires additional work on the subscriber side, but it allows the BCP files to be partitioned as they would be with normal tables.

Remember that replication performance can be influenced by various factors, such as the hardware, network latency, and data volume. When dealing with large volumes of data, you might need to consider other replication strategies or data synchronization methods that better suit your needs.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.