Monitoring progress on a join

I'm doing a big old join on one table with 10,000,000 rows, and
another with
400,000 rows. As you can imagine, this is taking a long time.

Is there any way to monitor the progress of the join after executing
the sql statement (more specifically, from code)?

(Oh, and any good practices for speeding up this join would be
appreciated, too).

Thanks,

AndrewAndrew (ajperrins@.hotmail.com) writes:
> I'm doing a big old join on one table with 10,000,000 rows, and
> another with
> 400,000 rows. As you can imagine, this is taking a long time.
> Is there any way to monitor the progress of the join after executing
> the sql statement (more specifically, from code)?

If it's a SELECT statement that produces a result set, I don't think
there is much you can monitor. You can keep an on the cpu, physical_io
and memusage columns in sysprocesses, but since you don't know what
the target values, you can only see that the process is working, but
not how much work that is left.

If you are also inserting the data into a table, a SELECT with NOLOCK
on the table can give some progress indication. On the INSERT that
is, so if finding the qualifying rows is what takes time, you will
still not see much.

> (Oh, and any good practices for speeding up this join would be
> appreciated, too).

Without any knowledge about the query and the tables, it is difficult
to give precise advice. But selective indexes for the WHERE condition.
If you are not including too many columns, a covering index or two may
be worth investigating.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||In addition to Erland's notes: if your query is about the only thing
running on the machine, and you have collected information from previous
runs, then you can use

select @.@.cpu_busy, @.@.io_busy

to monitor the cpu and i/o usage before and during your execution, and
deduct the progress.

Hope this helps,
Gert-Jan

Andrew wrote:
> I'm doing a big old join on one table with 10,000,000 rows, and
> another with
> 400,000 rows. As you can imagine, this is taking a long time.
> Is there any way to monitor the progress of the join after executing
> the sql statement (more specifically, from code)?
> (Oh, and any good practices for speeding up this join would be
> appreciated, too).
> Thanks,
> Andrew|||On 15 Sep 2003 12:01:53 -0700 in comp.databases.ms-sqlserver,
ajperrins@.hotmail.com (Andrew) wrote:

>I'm doing a big old join on one table with 10,000,000 rows, and
>another with
>400,000 rows. As you can imagine, this is taking a long time.
>Is there any way to monitor the progress of the join after executing
>the sql statement (more specifically, from code)?
>(Oh, and any good practices for speeding up this join would be
>appreciated, too).

The tools you want are in Query Analyser: Show Execution Plan, also
Index analysis (on that data you might want to leave that running
overnight <g>). Don't know about "from code" :-\

--
A)bort, R)etry, I)nfluence with large hammer.

(replace sithlord with trevor for email)