buy a monitoring tool :-

• SQL Diagnostics Manager
• Terratrax performance monitor
• MOM

for example.

first .. why?

Think you'll find access to the registry has been tightened by default on sql 2005 ( in fact I know it has ) to stop this type...

sounds to me that it's probably your application - does it use lots of dynamic/embedded sql, leading wildcard ( contains ) searches ?

I have a crm system that doesn't perform...

Hopefully if this is a production system then the replication will be documented. If not then your learning task would be to do so, that way you'd understand the structure...

This may sound a stupid question but how do you know profiler is causing the problem?

Are you saving the trace to disk?

I have a problem server which I profile, mainly...

Multi cores work fine, licensing is per socket not per core. Some of the intel dual core also hyperthread so you see 4 cpu in task manager per socket. On...

as far as I know there is no pass back of the message - although to be honest I normally got some type of error if things were amiss. Could...

either use perfmon or expand the display columns in task manager to display handles, pageing etc. and monitor.

As has been indicated it's not so much the tool or database but what is required as output.

You need to know what type of reports you want -  for example...

try running a dbcc updateusage(0) against the database(s)

I don't usually like posting a link but the msdtc stuff is too lengthy to write down, have a look at this http://blogs.msdn.com/sqlblog/archive/2006/11/15/we-upgraded-our-servers-to-windows-2003-sp1-and-now-our-data-driven-application-fails-with-strange-errors.aspx

bcp data out of table. Drop table. Create new table, bcp data back in.

This is usually my final step for a problem.

If it still fails then get on to...

try plotting the rate of data transfer into the destination tables, I assume the data is arriving?

inserts rarely give any issues including blocking, however, you mention only inserting if exists. I've encountered many cases of procs being written which branch internally thus being able to produce...

I'm not sure you can achieve this, the perfmon counters have no granularity so are out, your only method would be to use a profiler trace collecting the scan event...