When a plan is trivial, it’s unnecessary to recompile the query even statistics has been updated. Optimizer generates trivial plan for very simple queries (usually referencing a single table). In XML plan, you will see statementOptmLevel="TRIVIAL". In such case, it’s futile and you won't get a better or different plan.
The demo code in the blog post uses SELECT queries only. In my examples in my own blog post, I'm concerned with INSERT queries. Are INSERT queries eligible for both trivial plans and plans that have been more fully optimized? If INSERT queries can use a trivial plan, is it possible for a cached trivial plan to remain in use when a better and/or different plan could be selected?
Perhaps the reader is familiar with the Paul White blog post referenced below, from 2013 January 26. In that blog post, Paul White discusses wide, per-index INSERT query plans and narrow, per-row INSERT query plans. How do these plan types relate to trivial plans?
OK. Enough jibber-jabber. Time to do something. Like create a stored procedure. Which will be used in a database with AUTO_UPDATE_STATISTICS = ON and AUTO_UPDATE_STATISTICS_ASYNC = OFF.
This stored procedure will drop two tables if they already exist, then create those two tables. The source table has three columns and a single index - the clustered primary key. The target table also has three columns and a clustered primary key. In addition the target table has two non-clustered indexes.
The @insert_no_rows parameter controls whether or not there will be an INSERT query from the empty source table to the empty target table. That wouldn't move any data - but maybe it will cache a plan?
The INSERT query is dynamic SQL - both in the stored procedure and when I issue it adhoc later. It isn't necessary for this query to be dynamic SQL - it was just easier that way to make sure the batch SQL text matched and plan re-use happened when it was a possibility. While writing this blog post at one point the query SQL text in the stored procedure has a different number of tabs preceding the formatted T-SQL than appeared in my adhoc query afterward and it took me forever to figure out why plan re-use wasn't occurring. :-)
That will drop the source and target tables, recreate the tables, and populate the source table with 1024 rows instead of the previous 1023.