Any scheduled jobs that corresond to the slowdowns? Either on SQL or windows scheduled events?

I don't think I need to add serializable isolation as I will want my select statement to see the commited transaction so it doesn't insert a duplicate record.

I'm not sure...

I've before now left profiler running for a full day without issue. Just make sure you apply enough filters to get just what you're interested in (in this case, executions...

Can't you remove the duplicates?

Regarding profiler, can't you leave it runing for some time against the prod database? It would be best to catch the cause of the duplicate calls,...

If you're using 2005, you should be using the ALTER INDEX ... REBULD command, rather than the old DBCC DBREINDEX

Since you have your check with(nolock) and there's no transaction, it's quite possible for two calls at the same time to not find the record and then both insert it....

What this message means is that SQL issues an IO to windows and has not received a completion notification in 15 seconds.

A 15 sec delay on IOs is a...

You don't need to update statistics after doing dbreindex. DBReindex updates the stats as part of its operation. IndexDefrag doesn't.

Pleasure. This is one of the first real uses I've seen for them too.

Would it be possible for you to point me to a good reference that would explain what you mean by "the appropriate xml indexes on the column". Are you referring...

You can't use non-deterministic functions within a user-defined function. That include rand(), newid() and getdate() amoung others

What you can do is write a more generic function that takes 2 params,...

Look in books online for recursive common table expressions. They're exactly what you're looking for. Here's quick example. Should give you an idea.

WITH

MenuCTE(ParentID

You can search xml very efficiently in SQL 2005, if you use xquery and have the appropriate xml indexes on the column.

What do you want to do with the data...

Had a case of that recently. One of the business users complained that the faxng was always slow (we use a faxing system that saves the faxes in the db)....

In addition to the above points, check the fragmentation of the indexes on both your dev and prod boxes.

Is it possible for you to post the table and index structure...