how SQL server knows to replicate only the stored proc execution (i.e. actual exec myupateproc t-sql command) instead of underlying table data that is being updated on the publisher (i.e. actually replicating 25 million update statement)?
SQL Server knows that when you set up article property as below :
You can explicitly set only to replicate SP defination, execution of SP or Execution in a serialized transaction of the SP while setting up Article property.
Refer here. When you select "serialized transaction of the SP", the entire execution is replicated as a single transaction there by reducing the Transactional Replication overhead i.e. With the replication of stored procedure execution, replication sends only the command to execute the stored procedure at the Subscriber, rather than writing all the updates to the distribution database and then sending them over the network to the Subscriber
Test it before hand as performing such a large update (25 million rows) will be way too much for replication to handle (depending on your replication topology - same distribution server as publisher, geographically separate publisher and subscribers, etc) and some caveats as described in BOL.
You can probably thing of another approach like --
- Remove the current table from replication.
- Do you data update and (To speed up things, you can truncate the table on Subscriber if the table is massive - so when you do a snapshot, replication wont have to perform delete on subscriber table)
- Add the table back to replication with nosync option
- Snapshot the publication, but this time only the snapshot of the table that was added will be generated.
The line from MSDN is talking about using EXEC(), like this:
SET @sql = 'SELECT foo FROM dbo.bar WHERE x = ''' + @x + ''';';
EXEC(@sql);
In my testing, modern versions of SQL Server are still able to reuse a plan like this, but there may be other variables (such as version, or for example if you add conditional WHERE clauses based on the presence of certain parameters - in which case that will generate a different plan).
If you use sp_executesql then the parameter values can still cause parameter sniffing issues (just like with normal SQL), but this has nothing to do with whether SQL Server can re-use the plan. This plan will get used over and over again, just as if you hadn't used sp_executesql at all, unless variables that would cause a direct query to get recompiled, in which case this one will get recompiled too (essentially, SQL Server doesn't store anything with the plan that says "this was executed from sp_executesql, but this one wasn't):
SET @sql = N'SELECT foo FROM dbo.bar WHERE x = @x;';
EXEC sys.sp_executesql @sql, N'@x varchar(32)', @x;
As a bonus, this has built-in protection against dynamic SQL, and avoids you having to worry about doubling up single quotes due to string delimiters. I blogged about some of this here and please read up on SQL injection here and here.
If you are having issues with plan re-use and/or parameter sniffing, some things you should look into are OPTION (RECOMPILE), OPTIMIZE FOR, optimize for ad hoc workloads and simple/forced parameterization. I addressed a few similar questions in response to a recent webcast here, it may be worth a skim:
The gist is: don't be afraid to use sp_executesql, but only use it when you need it, and only spend energy over-optimizing it when you have an actual performance issue. The example above is a terrible one because there's no reason to use dynamic SQL here - I've written this answer assuming you have a legitimate use case.
Best Answer
The Estimated plans (CTRL + L) show a tree structure. Actual plans (CTRL + M) do not.
I've never seen a tree for a procedure in an actual plan (and I never use estimated plans anyway)