sp_executesql performance issue

declare @sql nvarchar(1024)
set @sql = 'select * from customer where lastName = @lastName'
exec sp_executesql @sql, '@lastName nvarchar(80)' , 'Zz'

Suppose the customer has index on lastName. There is no lastName = 'Zz'. Sql will choose the index seek on the table. And cached the execute plan.

Then if the value of lastName changed in next execution, such as:
exec sp_executesql @sql, '@lastName nvarchar(80)' , 'Smith'
Suppose the customer has 1MM records with lastName = 'Smith'. Then the performance will be very bad becase it will use the cached plan by index seek.

We can change it:
set @sql = @sql = ' option (recompile)'
exec sp_executesql @sql, '@lastName nvarchar(80)' , 'Smith'
Then SQL server will recompile the query. It will choose the primary key index scan which is better.

distribution cleanup slow because of immediate_sync

The flag affects the following code:
if @has_immediate_sync = 0     delete TOP(5000) MSrepl_transactions WITH (PAGLOCK) from    MSrepl_transactions with (INDEX(ucMSrepl_transactions)) wherepublisher_database_id = @publisher_database_id andxact_seqno <= @max_xact_seqno andxact_seqno <> @last_xact_seqno andxact_seqno <> @last_log_xact_seqno andxact_seqno <> @second_largest_log_xact_seqno --ensure at least two log entries are left, when there existed more than two log entriesOPTION (MAXDOP 1)elsedelete TOP(5000) MSrepl_transactions WITH (PAGLOCK) from MSrepl_transactions with (INDEX(ucMSrepl_transactions)) wherepublisher_database_id = @publisher_database_id andxact_seqno <= @max_xact_seqno andxact_seqno <> @last_xact_seqno andxact_seqno
xact_seqno <> @last_log_xact_seqno and <> @second_largest_log_xact_seqno and --ensure at least two log entries are left, when there existed more than two log entries-- use nolock to avoid deadlocknot exists (select * from MSrepl_commands c with (nolock) wherec.publisher_database_id = @publisher_database_id andc
c.xact_seqno = MSrepl_transactions.xact_seqno and .xact_seqno <= @max_xact_seqno)

IN [dbo].[sp_MSdelete_dodelete] and [dbo].[sp_MSdelete_dodelete], if @has_immediate_sync = 0 then it will check the table MSrepl_commands. The table has 160MM records, so the performance will be very bad.OPTION (MAXDOP 1)
To check whether the flag is set to true:
select  immediate_sync,* from distribution.dbo.MSpublications

Find one publication in a DB has the flag = true, and this affects all publication in same database.To set the flag to false by: EXEC sp_changepublication @publication='Publication_name', @property='immediate_sync',@value='FALSE'

If the flag is true, the cleanup the job only remove the records specified by@max_distretention = 72.
EXEC dbo.sp_MSdistribution_cleanup @min_distretention = 0, @max_distretention = 72. So these two tables will keep data up to 72 hours.

This caused the table MSrepl_commands had 160MM records. The size of distribution DB is 70GB.

After setting the flag to false, the cleanup job removed replicated transactions and commands in distribution DB.
The "Distribution clean up: distribution" job takes from 1 hour to 20 seconds.
The records number in MSrepl_commands goes down to 2.6MM.The size of distribution DB goes down to 2GB.

Replicate table with foreign key

Replicate table with foreign key having "ON DELETE CASCADE".
ALTER TABLE T2 WITH CHECK ADD CONSTRAINT [FK_T2_PKT1] FOREIGN KEY(PKT1)REFERENCES T1 (PKT1)ON

Replicte T1 and T2.

Delete from T1 where PKT1 = 1

On distributor, the following commands will be distributed:
Delete from T1 where PKT1 = 1
Delete from T2 where PKT2 = 2 --maybe more commands for all records in T2 have the foreign key of PKT1

It will work perfect.

But if I create a stored procedure(usp_delete_T1) to delete records in table T1, and replicate the execution of the SP.
exec usp_delete_T1 @PKT1 = 1 on publisher, it will delete records from T1 and T2.

The distributor will send:
 exec usp_delete_T1 @PKT1 = 1 to subscriber.

The problem is only the record in T1 will be deleted from T1. The records in T2 will not be deleted.
So we cannot replicate the execution of usp_delete_T1.