Posted by DBMonitor on 10/26/07 01:35

On Oct 26, 7:39 am, Erland Sommarskog <esq...@sommarskog.se> wrote:
> DBMonitor (spamawa...@yahoo.com.au) writes:
> > Both query plans use indexes on for both the tables though. The plans
> > are almost identical.
>
> "Almost". Apparently, there is a subtle, but important difference.
>
> Would it be posible for you to post the query plans? If you are on SQL 2005
> you can save the graphical execution plan in a file and post that in an
> attachment. (Or put it on a web site with a link to it.)


The query plans are as follows:

-----------------
Subquery Query (Total cost 41.52%)
SELECT (0%)<-CS (0%)<-HM IJ (68%)<-IS Col1 (11%)
..................................<-IS Col2 (20%)

Standard Query (Total Cost 58.48%)
SELECT (0%)<-CS (0%)<-HM IJ (78%)<-IS Col1 (8%)
..................................<-IS Col2 (14%)

KEY:
CS - Comput Scalar
HM IJ - Hash Match/Inner Join
IS - Index Seek

-------------------------

The main difference is the hash match process. For the sub querys, the
row size is 15 for the query with the sub queries and 21 for the
standard query however the sub query query has more rows on it.

The only thing I can think of which is happening is some sort of page
io sharing problem. The database server is running on a virtual
machine and the last wait type for the queries are always
'PAGEIOLATCH_SH' and the process runs on one thread.

When I run it on a dedicated server, the times to run the queries are
almost identical and the lastwaittypes change to CXPACKET and uses
multiple threads.

[Back to original message]