What more do you want to know on CTEs? Here's a good, albeit fairly technical, article on CTEs.

http://blogs.msdn.com/craigfr/archive/2007/10/18/ctes-common-table-expressions.aspx

I'll give this a try.

Temp tables

Behave just like normal tables, but are created in the TempDB database. They persist until dropped, or until the connection that created them disappears. They...

You've got quite a few table scans in that query (Address, Business_Service, Business_Service_Category, Business_Sub_Category). Without the schema I can't offer suggestions, but if those are large tables, you may want...

SG (10/26/2007)

---

It's AFTER we moved process of server A that ServerA was slower than before.

Could it be a caching issue? Now we aren't using the server so much, nothing is...

It's not quite clear from what you said. Do you think profiler's showing too many events, or too few?

What transfers/sec is good depends on the hardware you have. Diffrent disks and disk configs have different max capacities. Maybe someone else will be able give some figures.

Page splits/sec should...

There's a fairly well-known issue around SQL and hyperthreading. I know it sounds strage, but see if you can get the hyperthreading disabled and see if the ETC improves at...

Put an order by on your select and your delete, or there's no guarentee that you'll get the same records. Especially since you have a nolock on the select.

Without an...

Has the amount of data been transfered increased? Has the size of the tables increased?

Is this ETC across networks? If so, is the network handling the load?

I would run profiler over the time that the ETL process runs. Capture the SQLBatchCompleted event (under T-SQL) and the RPC completed event (under stored procedures)

That will give you a...

Are the indexes the same between the original and new server? By default, nonclustered indexes are not copied when a table is replicated.

The problem with writing an article on indexing, is that the 'best' indexes for a table depend on a large number of factors, and th table structure is probably the...

For the exec plan cache query, run this in the db that has the stored procs you're interested in. DB will need to be in compat mode 90

SELECT creation_time, last_execution_time,...

If the procedure has been executed recently, it may be in the execution plan cache. If that's the case you can find the time the plan was created and the...

This works in SQL 2005, but ot in SQL 2000. In 2000, the only ways to do this are dynamic SQL or RowCount