SQL Server Performance Forum – Threads Archive

Explanantion of Explain Plan

I am attempting to optimize a query inside a stored procedure that has
approximately 16 joins, having about 10 inner joins, and 6 left joins. I have
a temporary table that has a primary key. This temporary table is used in one
of the left joins. I noticed that when running this stored procedure from within Management
Studio, having "Include Actual Execution Plan" on, that this temporary table
was not part of the execution plan. I turned off "Include Actual Execution
Plan" and "Set Statistics Profile On", with Results in Text. After running
the procedure again, I performed a text search for the temporary table from
the resulting plan, and it was not found. When I "Set Statistics IO On", the
temporary table is seen in the results of Statistics IO. So, I experimented further, removing the primary key from the temporary table.
This not only significantly reduced the reads on the stored procedure, but
changed the explain plan. Now this temporary table is included in the
"Include Actual Execution Plan" and "Set Statistics Profile On". Can anyone help explain why the temporary table does not appear in an explain
plan? I have always assumed that if a table is used in a SQL Statement, that
it would always be part of an explain plan? Yes/No? I am running SQL Server 2005 Standard edition, SP1, on Win2K.

---

The temporary table is created in the same store procedure?
Luis Martin
Moderator
SQL-Server-Performance.com All in Love is Fair
Stevie Wonder
All postings are provided “AS IS” with no warranties for accuracy.

---

Yes, the temporary table is created at the very beginning of the procedure and is entirely populated before it is used by the SQL Statement.

---

I will consider this as a bug, if a table referenced in the query does not appear in the execution plan. IS it possible to post a repro?
Roji. P. Thomas
http://toponewithties.blogspot.com

---

Unfortunately I cannot post the results. Too much proprietary info, and with 16 joins I do not want to rename items to protect the schema naming and relations.

---

Never heard of Ctrl+H – Find & Replace?

---

]]>