I've just upgraded our Data warehouse to SQL 2016. I'm seeing some really interesting graphs in the Query Store (I love this feature!). Below is the weirdest example i've seen. 22 plans for the same query.
It's making me consider performance tuning of my ETL process and the pros and cons of temporary tables and how you could influence execution plan behavior.
My ETL process uses a number of stored procedures which use mix of standard and temporary #tables as staging tables. The #tables are typically used once and then dropped. Some are only a few thousand rows. Some are millions. SSMS advises that there are missing indexes, but on smaller tables would they make enough of difference to be worth the effort of adding them? Are better statistics sufficient?
It says that statistics are created automatically, when the #table is queried, and then presumably used by the optimizer.
My questions are: Is there much point or benefit in creating an index on a #table. And/or: Is it worth explicitly updating statistics as a step in the stored procedure before using it in queries given they're only used once.
Are the additional steps and overhead worth it? Would it result in significantly better or different execution plans?