11

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.