SQL Server Query is faster in SSMS when including execution plan

In SQL Server 2005 management studio I am testing a query which uses some table variables, one of which has a clustered unique constraint on it. I am noticing that the total execution time actually goes down by a good amount when I include the actual execution plan to analyze it.

What is the reason for 开发者_Go百科this, and should I only test for the total execution time when the option to include the execution plan is off.

Thanks!

---

Sounds a bit odd to me tbh. Are you sure that the difference you're seeing is not down to caching? I would always test the performance of a sproc by NOT including the execution plan, and I would clear the cache down before each run in order to have a fair comparison (on a test/dev db server, not production).

DBCC FREEPROCCACHE -- will clear the execution plan cache
DBCC DROPCLEANBUFFERS -- will clear the data cache

---

I just ran into this same issue, and after trying to understand how or why including the execution plan was speeding things up, I have now come to the conclusion that SSMS simply misreports the total execution time. The answer to your question is therefore that it isn't really running faster at all, sadly, and that yes, you should perform timings without the execution plan being included.

When initially performing tests where times were a few hundred milliseconds it was hard to diagnose, but once I was able to reproduce the issue with a slower query it became easier to see. In the screenshot below are five runs of a stored procedure which returns multiple result sets. The first three runs were without returning the execution plan, and the fourth and fifth were with. Despite the appearance of significantly faster responses (~200ms instead of ~5s), the query actually still took ~5s to complete. I guess there's a bug in the client statistics somewhere that is timing the execution plan being returned and not the full query under certain cirmcumstances.