Make sure that the users who use the appliction don't have rights to stop services (not SQL permissions, windows level permissions). Make sure no one but the server admins can...

Different join means a different execution plan. SQL probably generated one that doesn't have the same massive internal tables.

Can you post the plan with the outer join? I'd like to...

Good point. Would need 2 columns then. 1 a varchar that the insert puts the 'date' into,...

Service packs for which version of SQL? 7, 2000, 2005?

As for what differes, there's no single answer to that. Each of the service packs for each version fixes different things....

When you set up the trace, do you set it to write the trace to disk?

Could you explain a bit more what you want please?

Last modified in terms of data? Last modified in terms of schema? Something else?

You can do it via a SQL query.

ALTER DATABASE tempdb

MODIFY FILE (NAME = tempdev, FILENAME = < New directory>\tempdb.mdf');

GO

ALTER DATABASE tempdb

MODIFY FILE (NAME = templog, FILENAME =...

Ignoring memory for now...

Run performance monitor for an hour or so while the problems are occuring. Add the following counters:

Physical Disk:Avg sec/read

Physical Disk:Avg sec/write

SQL Server Buffer manager:Buffer cache hit ratio

SQL...

Bear in mind that using or posting brain dumps is against the rules of the exam (and a violation of the agreeemant that you accept upon writing the exam) and...

No. I didn't.

Will give it a shot when I get home, If I have a chance.

SQL shouldn't be doing the data type test before the actual insert. Instead...

It's not the number of rows. The query processor thinks there's a couple of cross joins happening.

Suggestion: Go carefull through the joined tables and make sure that all tables are...

The execution plan is warning of missing join predicates in a couple places. It thinks you have cartesian products forming

From the way the actual row count is increasing at points...

The reads have gone down, which is good. Has the CPU time also increased, or just the duration?

Did you run the query a couple times before taking measurements? If it...

Yes, it is. Write it properly and there won't be much of an impact.

Since the instead of fires before the data is inserted, you can't fire it only when...

Your first option uses correlated subqueries. It's a hidden row by row operation. It's gonna be slow, especially on larger result sets. The subqueries will run once for each row...