Precomputed Partitions

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Doug - 23 Jul 2008 18:20 GMT

We upgraded recently to SQL Server 2005 SP2 .

For some issues we are having, we are looking at precomputed partitiions
with our merge publication.

When I run a sp_helpmergesubscription I see:

keep_partition_changes = 1
use_partition_groups = 1

The system would have had to do these settings as we haven't done
anything yet with this process.

Looking at BOL, I get a bit confused. It says
"The use partition groups option provides the greatest performance
improvement
through the use of the precomputed partitions feature.
This option is set to true by default if the articles in your publication
adhere to a set of requirements.
For more information about these requirements,
see Optimizing Parameterized Filter Performance with Precomputed Partitions.
If your articles do not meet the requirements for using precomputed
partitions,
the keep partition changes option to is set to true."

From the above info, it seems only one of these properties should have been
set to 1
as the publication should either have met or not met the requirements.

Another location in BOL also states
"Optimize Synchronization

This option should be set to True only if Precompute Partitions is set to
False.
Set this option on the Subscription Options page of the Publication
Properties - <Publication> dialog box"

Again, I don't know why the system set them both to True if it says it
shouldn't be that way.

Can anyone tell me how the settings should really be for precomputed
partitions to work?

TIA,

Doug

Reply to this Message

Hilary Cotter - 24 Jul 2008 13:42 GMT

Keep partition changes means that if you update rows to a value where
they no longer meed the filtering condition the rows will be deleted
(@keep_partition_changes='false'), or remain
(@keep_partition_changes='true').

The precomputed partitions option will use triggers to keep track of
changes which are occuring in a partition. For example if you have two
sales people, Sam and Jack, and Jack sells a lot of stuff, these
triggers will write to a table which have references to all the stuff
that Jack sold. When the merge agent tries to sync with the subscriber
who gets Jack's data, the agent will reference this tracking table. If
you do not use precomputed partitions this metadata will not be kept
and the syncs will take longer, but all the DML will be faster.

So while these two options share the term partition in their name,
they are really talking about different concepts altogether.

> We upgraded recently to SQL Server 2005 SP2 .
>
[quoted text clipped - 42 lines]
>
> Doug

Reply to this Message

Doug - 24 Jul 2008 16:31 GMT

Hilary,

Thanks for the info.....in case you read this..another question.

The publication in question has many subscribers that are currently at MSDE
2000 SP4. Do ALL of the subscribers have to be at Express (2005) level
before Precomputed Patitions is turned on or can some subscribers still be at
the 2000 level? I'm thinking ALL the subscribers have to upgraded first, but
maybe(hopfully) not.

Thanks for any info,

Doug

> Keep partition changes means that if you update rows to a value where
> they no longer meed the filtering condition the rows will be deleted
[quoted text clipped - 59 lines]
> >
> > Doug

Reply to this Message

Hilary Cotter - 30 Jul 2008 12:24 GMT

Yes they should be all at SQL 2005 Express edition.

> Hilary,
>
[quoted text clipped - 73 lines]
>
> > > Doug

Reply to this Message