I was trying some research on table variable with indexes and came across with this strange behavior which I can’t explain. If you can let me you are welcome.
Let me clear the cache first so we are clear.
So I created table variable with unique clustered index and insert data into the table variable.
So let us see the query plan.
So it uses, clustered index of the SalesOrderDetail table and the clustered index of the table variable.
Let us view the cashed plans.
So we have two rows.
First one of course, plan for the table variable insert and the second one (This is what I can’t understand) is for Sales.vOrders indexes view.
I am not using any Index view. Other thing is note here is, if I don’t have the indexes on the table variable. then this cached plan will not appear. Also, I do the simple select on the query which I am using to insert into table variable, then again this cached plan will not appear.
I don’t have a clue do you?
I can't find a version of AdventureWorks that contains the indexed view, but it looks like the optimizer has chosen to use the indexed view to fill the query because it has decided it is the fastest way to do so. My guess would be that the index on the view matches the index on the table variable so the optimizer doesn't have to do a sort.
ReplyDeleteThe parse tree is brought into cache when cost-based optimization looks to match an indexed view with a logical GET operation (the rule is MatchGet in this case, more generally the Match* rules). Indexed view matching is only performed during cost-based optimization, so queries that qualify for a TRIVIAL plan will not perform IV matching.
ReplyDeleteThings like adding ORDER BY or making the table variable a clustered table instead of a heap introduce plan choices, so a TRIVIAL plan cannot be used, and if the IV-matching logic runs as part of cost-based optimization, a parse tree is cached as part of that work.
In SQL Server 2012, a parse tree is not loaded for the sample query because the IV could not possibly match the query's needs. In R2, the tree is loaded because the IV references the same table as specified by the logical GET - a bit pointless since it does not project the needed columns. One more reason to upgrade, I suppose. Another small improvement: the 2012 plan does not include the rowcount top seen in the blog screenshot, and the plan is not cached unless the query is actually executed (before 2012, e.g. obtaining an estimated plan was enough).
Paul White
Correction to my previous comment. I was using DBCC FREEPROCCACHE instead of the DBCC FREESYSTEMCACHE('ALL') in the image above. The latter command also clears the View Definition Cache, which DBCC FREEPROCCACHE does not do. So, the change in behaviour I saw between R2 and 2012 turns out not to exist, it was just a consequence of this error. It seems SQL Server caches a parse tree at the same time it caches the view definition, though the parse tree is not actually used by the optimizer in the case of the test query.
ReplyDelete